Consider the following tables WORKER and PAYLEVEL and

Q. Consider the following tables WORKER and PAYLEVEL and answer the questions:

Table: WORKER

 

 
	 
ECODE
 
	 
NAME
 
	 
DESIG
 
	 
PLEVEL
 
	 
DOJ
 
	 
DOB
 
 
 
	 
11
 
	 
Sachin Patel
 
	 
Supervisor
 
	 
P001
 
	 
13-Sep-2004
 
	 
23-Aug-1985
 
 
 
	 
12
 
	 
Chander Nath
 
	 
Operator
 
	 
P003
 
	 
22-Feb-2010
 
	 
12-Jul-1987
 
 
 
	 
13
 
	 
Fizza
 
	 
Operator
 
	 
P003
 
	 
14-Jun-2009
 
	 
14-Oct-1983
 
 
 
	 
15
 
	 
Ameen Ahmed
 
	 
Mechanic
 
	 
P002
 
	 
21-Aug-2006
 
	 
13-Mar-1984
 
 
 
	 
18
 
	 
Sanya
 
	 
Clerk
 
	 
P002
 
	 
19-Dec-2005
 
	 
09-Jun-1983
 
 

Table: PAYLEVEL

 
	 
PLEVEL
 
	 
PAY
 
	 
ALLOWANCE
 
 
 
	 
P001
 
	 
26000
 
	 
12000
 
 
 
	 
PO02
 
	 
22000
 
	 
10000
 
 
 
	 
P003
 
	 
12000
 
	 
6000
 
 
 
	 

 
 

(a) To display details of all workers in descending order of DOB.

(b) To display the PLEVEL and number of workers in that PLEVEL.

(c) To display the PLEVEL and number of workers in that PLEVEL whose pay is greater than 15000.

(d) to display NAME and DESIG of those workers, whose PLEVEL is either P001 or P002.

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

(i) select count(PLEVEL), PLEVEL from WORKER group by PLEVEL;

(ii) select max(DOB), min(DOB) from WORKER;

Answer :-
(a) Select * from Worker order by Dob desc ;
(b) Select PLEVEL , ( count(*) ) as “No. of Worker ” from worker group by PLEVEL ;
(c) Select PLEVEL , ( count(*) ) as “No. of Worker ” from worker natural join Paylevel group by PLEVEL having pay > 15000 ;
(d) SElect Name , Desig from worker where Plevel in (“P001”, “P002” ) ;
(e)
(i)

 

 
	 
count(PLEVEL)
 
	 
PLEVEL
 
 
 
	 
1
 
	 
P001
 
 
 
	 
2
 
	 
P002
 
 
 
	 
2
 
	 
P003
 
 

(ii)

 
	 
max(DOB)
 
	 
min(DOB)
 
 
 
	 
20100222
 
	 
20040913