Question

Consider the following DEPT and WORKER tables. Write SQL queries for (i) to(iv) and find outputs for SQL queries (v) to (viii):
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.

To display Wno, Name, Gender from the table WORKER in descending order of Wno.

To display the Name of all the FEMALE workers from the table WORKER.

To display the Wno and Name of those workers from the table WORKER who are born between ‘1987-01-01’ and ‘1991-12-01’.

To count and display MALE workers who have joined after ‘1986-01-01’.

SELECT COUNT(), DCODE FROM WORKER
GROUP BY DCODE HAVING COUNT()>1;

SELECT DISTINCT DEPARTMENT FROM DEPT;

SELECT NAME, DEPARTMENT, CITY FROM WORKER W, DEPT D WHERE
W.DCODE=D.DCODE AND WNO<1003;

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

Solution

(i) SELECT Wno,Name,Gender FROM Worker
ORDER BY Wno DESC;

(ii) SELECT Name FROM Worker
WHERE Gender=’FEMALE;

(iii) SELECT Wno, Name FROM Worker
WHERE DOB BETWEEN ‘19870101’ AND ‘19911201;

(iv) SELECT COUNT(*) FROM Worker
WHERE GENDER=’MALE’ AND DOJ > ‘19860101;

(v)

(vi)

Department

MEDIA

MARKETING

INFRASTRUCTURE

FINANCE

HUMAN RESOURCE

(vii)

(viii)

MAX (DOJ)	MIN (DOB)
20140609	19841019

For Daily Free Study Material Join wiredfaculty