dba lab

Document Sample
dba lab Powered By Docstoc
					Date: 31stoct,2012

DBMS LAB

Instructor:

Sehrish Akram

Eisha tir Razia

                                              LAB 03




    1. Create a query to display unique job codes from the employees table.
        select distinct(job) from emp

                                                JOB
     ANALYST
     CHOKIDAR
     CLERK
     MANAGER
     OPERATOR
     PRESIDENT
     SALESMAN
     cleaner
     masi
     operator




    2. Write a query to display the current date. Label the column Date.
        select sysdate"date" from dual
                                             SYSDATE
     31-OCT-12


    3. Show the names of all employees together with the number of years and the number of
       completed months that they have been employed.
       select ename,round(MONTHS_BETWEEN(sysdate,hiredate))"employees together "
       ,round(MONTHS_BETWEEN(sysdate,hiredate))/30"employed duration "from emp
        ENAME                 employees together                    employed duration
     SMITH                                              382                       12.7333333
 ALLEN                                             380                       12.6666667
 WARD                                              380                       12.6666667
 JONES                                             379                       12.6333333
 MARTIN                                            373                       12.4333333
 BLAKE                                             378                               12.6
 CLARK                                             377                       12.5666667
 SCOTT                                             306                               10.2
 KING                                              371                       12.3666667
 TURNER                                            374                       12.4666667
 ADAMS                                             305                       10.1666667
 JAMES                                             371                       12.3666667
 FORD                                              371                       12.3666667
 MILLER                                            369                               12.3
     ENAME                employees together                   employed duration
 ABBAS                                             250                       8.33333333
 usama                                             -14                        -.46666667
 BUTT                                              783                               26.1
 PArri                                            -195                               -6.5
 ANIS                                              250                       8.33333333

4. Display each employee’s name, hire date, and salary review date, which is the first Monday
   after six months of service. Label the column REVIEW. Format the dates to appear in the
   format similar to “Monday, the Thirty-First of July, 2000.”
   select ename,hiredate,round(ADD_MONTHS (hiredate,6))"REVIEW" ,
    NEXT_DAY (SYSDATE,'MONDDAY')"NEXT-
   DAY",TO_CHAR(HIREDATE,'fmdd,MONTH,YYYY') HIREDATE
   from emp

   ENAME        HIREDATE        REVIEW         NEXT-DAY              HIREDATE
 SMITH        17-DEC-80      17-JUN-81       05-NOV-12      17,DECEMBER,1980
 ALLEN        20-FEB-81      20-AUG-81       05-NOV-12      20,FEBRUARY,1981
 WARD         22-FEB-81      22-AUG-81       05-NOV-12      22,FEBRUARY,1981
 JONES        02-APR-81      02-OCT-81       05-NOV-12      2,APRIL,1981
 MARTIN       28-SEP-81      28-MAR-82       05-NOV-12      28,SEPTEMBER,1981
 BLAKE        01-MAY-81      01-NOV-81       05-NOV-12      1,MAY,1981
 CLARK        09-JUN-81      09-DEC-81       05-NOV-12      9,JUNE,1981
 SCOTT        19-APR-87      19-OCT-87       05-NOV-12      19,APRIL,1987
 KING         17-NOV-81      17-MAY-82       05-NOV-12      17,NOVEMBER,1981
 TURNER       08-SEP-81      08-MAR-82       05-NOV-12      8,SEPTEMBER,1981
 ADAMS        23-MAY-87      23-NOV-87       05-NOV-12      23,MAY,1987
 JAMES        03-DEC-81      03-JUN-82       05-NOV-12      3,DECEMBER,1981
 FORD         03-DEC-81      03-JUN-82       05-NOV-12      3,DECEMBER,1981
 MILLER       23-JAN-82      23-JUL-82       05-NOV-12      23,JANUARY,1982
   ENAME        HIREDATE        REVIEW         NEXT-DAY              HIREDATE
 ABBAS        12-JAN-92        12-JUL-92       05-NOV-12       12,JANUARY,1992
 usama        25-DEC-13        25-JUN-14       05-NOV-12       25,DECEMBER,2013
 BUTT         15-AUG-47        15-FEB-48       05-NOV-12       15,AUGUST,1947
 PArri        15-FEB-29        15-AUG-29       05-NOV-12       15,FEBRUARY,2029
 ANIS         12-JAN-92        12-JUL-92       05-NOV-12       12,JANUARY,1992


5. Display the last name and hire date of every employee who was hired in 1994.
   select ename,to_char(hiredate,'YYYY')from emp
   where to_char(hiredate,'YYYY')=1994
   output : no rows selected

6. Display the last name; hire date, on which the and day of the week employee started. Label
       a. The column DAY. Order the results by the day of the week starting with Monday.
   select ename,hiredate,to_char(hiredate,'DAY')"DAY" from emp
   order by 'DAY'
         ENAME                    HIREDATE                               DAY
 SMITH                    17-DEC-80                      WEDNESDAY
 ALLEN                    20-FEB-81                      FRIDAY
 WARD                     22-FEB-81                      SUNDAY
 JONES                    02-APR-81                      THURSDAY
 MARTIN                   28-SEP-81                      MONDAY
 BLAKE                    01-MAY-81                      FRIDAY
 CLARK                    09-JUN-81                      TUESDAY
 SCOTT                    19-APR-87                      SUNDAY
 KING                     17-NOV-81                      TUESDAY
 TURNER                   08-SEP-81                      TUESDAY
 ADAMS                    23-MAY-87                      SATURDAY
 JAMES                    03-DEC-81                      THURSDAY
 FORD                     03-DEC-81                      THURSDAY
 MILLER                   23-JAN-82                      SATURDAY
        ENAME                     HIREDATE                               DAY
 ABBAS                    12-JAN-92                      SUNDAY
 usama                    25-DEC-13                      WEDNESDAY
 BUTT                     15-AUG-47                      FRIDAY
 PArri                    15-FEB-29                      THURSDAY
 ANIS                     12-JAN-92                      SUNDAY




7. Create a query that displays the employees’ last names and commission amounts. If an
      a. Employee does not earn commission; put “No Commission.” Label the column COMM.

       select ename,comm,to_char(comm),'No Commission'"COMM" from emp;
     ENAME          COMM              TO_CHAR(COMM)                      COMM
 SMITH                                                           No Commission
 ALLEN                     300 300                               No Commission
 WARD                      500 500                               No Commission
 JONES                                                           No Commission
 MARTIN                  1400 1400                               No Commission
 BLAKE                                                           No Commission
 CLARK                                                           No Commission
 SCOTT                                                           No Commission
 KING                                                            No Commission
 TURNER                      00                                  No Commission
 ADAMS                                                           No Commission
 JAMES                                                           No Commission
 FORD                                                            No Commission
 MILLER                                                          No Commission
     ENAME          COMM              TO_CHAR(COMM)                      COMM
 ABBAS                  123 123                                  No Commission
 usama                  123 123                                  No Commission
 BUTT                   123 123                                  No Commission
 PArri                    00                                     No Commission
 ANIS                   123 123                                  No Commission




8. Using the DECODE function, write a query that displays the grade of all employees based on the
      a. value of the column JOB_ID, as per the following data:
              i. JOB                             GRADE
            ii. AD_PRES                          A
           iii. ST_MAN                           B
            iv. IT_PROG                          C
              v. SA_REP                          D
            vi. ST_CLERK                         E
           vii. None of the above                 0




9. Create a query to display unique job codes from the employees table.

10. Write a query to display the current date. Label the column Date.
11. Show the names of all employees together with the number of years and the number of
    completed months that they have been employed.

12. Display each employee’s name, hire date, and salary review date, which is the first Monday
    after six months of service. Label the column REVIEW. Format the dates to appear in the
    format similar to “Monday, the Thirty-First of July, 2000.”

13. Display the last name and hire date of every employee who was hired in 1994.

14. Display the last name; hire date, and day of the week on which the employee started. Label
       a. The column DAY. Order the results by the day of the week starting with Monday.


15. Create a query that displays the employees’ last names and commission amounts. If an
       a. Employee does not earn commission; put “No Commission.” Label the column COMM.

                                            Good Luck
                                            Select *from emp

  EMPNO     ENAME             JOB          MGR    HIREDATE        SAL   COMM DEPTNO
     7369 SMITH          CLERK              7902 17-DEC-80          800           20
     7499 ALLEN          SALESMAN           7698 20-FEB-81         7600    300    30
     7521 WARD           SALESMAN           7698 22-FEB-81         7250    500    30
     7566 JONES          MANAGER            7839 02-APR-81         2975           20
     7654 MARTIN         SALESMAN           7698 28-SEP-81         7250   1400    30
     7698 BLAKE          MANAGER            7839 01-MAY-81         2850           30
     7782 CLARK          MANAGER            7839 09-JUN-81         2450           10
     7788 SCOTT          ANALYST            7566 19-APR-87         3000           20
     7839 KING           PRESIDENT               17-NOV-81         5000           10
     7844 TURNER         SALESMAN           7698 08-SEP-81         7500      0    30
     7876 ADAMS          CLERK              7788 23-MAY-87         1100           20
     7900 JAMES          CLERK              7698 03-DEC-81          950           30
     7902 FORD           ANALYST            7566 03-DEC-81         3000           20
     7934 MILLER         CLERK              7782 23-JAN-82         1300           10
  EMPNO     ENAME             JOB          MGR HIREDATE           SAL   COMM DEPTNO
     7444 ABBAS          operator           4555 12-JAN-92        10000    123    10
     1234 usama          CHOKIDAR           1234 25-DEC-13           10    123    10
      420 BUTT           masi               9211 15-AUG-47            2    123    10
     7010 PArri          cleaner                 15-FEB-29           50      0    20
      744 ANIS           OPERATOR            777 12-JAN-92         4500    123    10
     1111 test           TEST               1111 01-JAN-46         7999      3    10

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:10
posted:3/3/2013
language:
pages:5