Sponsor Area
TextBook Solutions for Uttarakhand Board Class 12 Computer And Communication Technology 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
| 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 ' );
(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)
| DCODE | NAME | DTITLE |
| R102 | AGAM SINGH | A day in life |
| F102 | ARTH JOSEPH | Universal Soldier |
| C101 | NISHA HANS | The Planets |
(viii) DTITLE
A day in life
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;
Sponsor Area
Sponsor Area
Mock Test Series
Mock Test Series