Question

Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of this question:

(a) Write SQL commands for the following statements:

(i). To display the name of all Workers in descending order of DOB.

(ii). To display NAME and DESIGN of those Workers, whose PLEVEL is either P001 or

(iii). To display the content of all the workers table, whose DOB is in between ’19-JAN- 1984′ and ’18-JAN-1987′.

(iv). To add a new row with the following: 19, ‘DayaKishore’, ‘Operator’, ‘P003′, ’19- Sep-2008’, ‘ll-Jul-1984’

(b) Give the output of the following SQL queries :

(i). SELECT COUNT (PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;

(ii). SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

(iii). SELECT Name,PAY FROM WORKER W,PAYLEVEL P WHERE W.LEVEL= P.PLEVEL AND W.ECODE<13;

(iv). SELECT PLEVEL, PAYLEVEL WHERE PLEVEL=”POO3″;

Answer 1

(a) (i). SELECT NAME FROM WORKER ORDER BY DOBDESC;

(ii). SELECT NAME, DESIGN FROM WORKER WHERE PLEVEL=”POOO1″ OR PLEVEL=”POO2″;

(iii). SELECT * FROM WORKER WHERE DOB BETWEEN ’19-JAN-1984 AND ’18-JAN-1987′;

(iv). INSERT INTO WORKER VALUES (19,”DayaKISHORE”, “oPERATOR”, “P0003”,’19-Sep-2008′,’11-Jul-1984′)’

(b)