Question
Consider the following DEPT and WORKER tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii): [Delhi 2015]
Table: DEPT
| DCODE | DEPARTMENT | CITY |
| D01 | MEDIA | DELHI |
| D02 | MARKETING | DELHI |
| D03 | INFRASTRUCTURE | MUMBAI |
| D05 | FINANCE | KOLKATA |
| D04 | HUMAN RESOURCE | MUMBAI |
Table: WORKER
| WNO | NAME | DOJ | DOB | GENDER | DCODE |
| 1001 | George K | 2013-09-02 | 1991-09-01 | MALE | D01 |
| 1002 | Ryma Sen | 2012-12-11 | 1990-12-15 | FEMALE | D03 |
| 1003 | Mohitesh | 2013-02-03 | 1987-09-04 | MALE | D05 |
| 1007 | Anil Jha | 2014-01-17 | 1984-10-19 | MALE | D04 |
| 1004 | Manila Sahai | 2012-12-09 | 1986-11-14 | FEMALE | D01 |
| 1005 | R SAHAY | 2013-11-18 | 1987-03-31 | MALE | D02 |
| 1006 | Jaya Priya | 2014-06-09 | 1985-06-23 | FEMALE | D05 |
Note: DOJ refers to date of joining and DOB refers to date of Birth of workers.
(i) To display Wno, Name, Gender from the table WORKER in descending order of Wno.
(ii) To display the Name of all the FEMALE workers from the table WORKER.
(iii) To display the Wno and Name of those workers from the table WORKER who are born between ‘1987-01-01’ and ‘1991-12-01’.
(iv) To count and display MALE workers who have joined after ‘1986-01-01’.
(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT(*)>1;
(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;
(vii) SELECT NAME, DEPARTMENT, CITY FROM WORKER W,DEPT D WHERE W.DCODE=D.DCODE AND WNO<1003;
(viii) SELECT MAX(DOJ), MIN(DOB) FROM WORKER;
