Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.

DVD

DCODE	DTITLE	DTYPE
F101	Henry Martin	Folk
C102	Dhrupad	Classical
C101	The Planets	Classical
F102	Universal Soldier	Folk
R102	A day in life	Rock

MEMBER

MID	NAME	DCODE	ISSUEDATE
101	AGAM SINGH	R102	2017-11-30
103	ARTH JOSEPH	F102	2016-12-13
102	NISHA HANS	C101	2017-07-24

1. To display all details from the table MEMBER in descending order of ISSUEDATE.
2. To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
3. To display the DTYPE and number of DVDs in each DTYPE from the table DVD.
4. To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued (i.e ISSUEDATE) in the year 2017.
5. SELECT MIN(ISSUEDATE) FROM MEMBER;
6. SELECT DISTINCT DTYPE FROM DVD;
7. SELECT D.DCODE,NAME,DTITLE
8. FROM DVD D, MEMBER M WHERE D.DCODE=M.DCODE; SELECT DTITLE FROM DVD
9. WHERE DTYPE NOT IN ( ' Folk ' , ' Classical ' );

 

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.

1. To display Wno, Name, Gender from the table WORKER in descending order of Wno.
2. To display the Name of all the FEMALE workers from the table WORKER.
3. To display the Wno and Name of those workers from the table WORKER who are born between ‘1987-01-01’ and ‘1991-12-01’.
4. To count and display MALE workers who have joined after ‘1986-01-01’.
5. SELECT COUNT(*), DCODE FROM WORKER
   GROUP BY DCODE HAVING COUNT(*)>1;
6. SELECT DISTINCT DEPARTMENT FROM DEPT;
7. SELECT NAME, DEPARTMENT, CITY FROM WORKER W, DEPT D WHERE
   W.DCODE=D.DCODE AND WNO<1003;
8. SELECT MAX(DOJ), MIN(DOB) FROM WORKER;