Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.
DVD
DECODE |
DTITLE |
DTYPE |
F101 |
Henry Martin |
Folk |
C102 |
Dhrupad |
Classical |
C101 |
The planets |
Classical |
F102 |
Universal Soldier |
Folk |
R102 |
A day in life |
Rock |
MEMMBER
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 |
(i) To display all details from the table MEMBER in descending order of ISSUEDATE.
(ii) To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
(iii) To display the DTYPE and number of DVDs in each DTYPE from the table DVD.
(iv) To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued (i.e., ISSUEDATE) in the year 2017.
(v) SELECT MIN (ISSUEDATE) FROM MEMBER;
(vi) SELECT DISTINCT DTYPE FROM DVD;
(vii) SELECT D.DCODE, NAME, DTITLE FROM DVD D, MEMBER M WHERE D. DC0DE=M.DCODE;
(viii) SELECT DTITLE FROM DVD WHERE DTYPE NOT IN ("Folk", "Classical");