Computer Science With Python Chapter 9 Databases Concepts And SQL

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

DVD

MEMBER

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

Table: WORKER

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;