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

To display all details from the table MEMBER in descending order of ISSUEDATE.

To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.

To display the DTYPE and number of DVDs in each DTYPE from the table DVD.

To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued (i.e ISSUEDATE) in the year 2017.

SELECT MIN(ISSUEDATE) FROM MEMBER;

SELECT DISTINCT DTYPE FROM DVD;

SELECT D.DCODE,NAME,DTITLE

FROM DVD D, MEMBER M WHERE D.DCODE=M.DCODE; SELECT DTITLE FROM DVD

WHERE DTYPE NOT IN ( ' Folk ' , ' Classical ' );

Answer

(i) SELECT * FROM MEMBER ORDER BY ISSUEDATE DESC;

(ii)SELECT DCODE,DTITLE FROM DVD WHERE DTYPE=’Folk’;

(iii)SELECT COUNT(*),DTYPE FROM DVD GROUP BY DTYPE;

(iv) SELECT NAME, ISSUEDATE FROM MEMBER WHERE
ISSUEDATE>=’2017-01-01’ AND ISSUEDATE<=’2017-12-31’;

(v) MIN(ISSUEDATE)
2016-12-13

(vi)DISTINCT DTYPE
Folk
Classical
Rock

(vii)

(viii) DTITLE
A day in life

Sponsor Area

Some More Questions From Databases Concepts and SQL Chapter

Sponsor Area

Sponsor Area