Get documents about "dba lab
Document Sample
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
