Page 294 - PYTHON-12

B2. Give the output of the following SQL queries:
(i) SELECT COUNT(DISTINCT Dcode) FROM Stock;
Ans.
Count(DISTINCT Dcode)
3
(ii) SELECT Qty*UnitPrice FROM Stock WHERE ItemNo=5006;
Ans. Qty*UnitPrice
4400
(iii) SELECT MIN(StockDate) FROM Stock;
Ans. MIN (StockDate)
01-Jan-09

21. Write the SQL commands for (a) to (d) and output for (e):
Table: GRADUATE
S.NO NAME STIPEND SUBJECT AVERAGE DIV
1 KARAN 400 PHYSICS 68 I
2 DIWAKAR 450 COMP. Sc. 68 I
3 DIVYA 300 CHEMISTRY 62 I
4 REKHA 350 PHYSICS 63 I
5 ARJUN 500 MATHS 70 I
6 SABINA 400 CHEMISTRY 55 II
7 JOHN 250 PHYSICS 64 I
8 ROBERT 450 MATHS 68 I
9 RUBINA 500 COMP.Sc. 62 I
10 VIKAS 400 MATHS 57 II
(a) List the names of those students who obtained DIV I sorted by NAME.
(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming
that the STIPEND is paid every month.
(c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.
(d) To insert a new row in the GRADUATE table: 11,“KAJOL”, 300, “computer sc”, 75, 1
(e) Give the output of the following sql statement based on table GRADUATE:
(i) Select MIN(AVERAGE) from GRADUATE where SUBJECT=“PHYSICS”;
(ii) Select SUM(STIPEND) from GRADUATE WHERE div=2;
(iii) Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;
(iv) Select COUNT(distincts SUBJECT) from GRADUATE;
Ans. (a) SELECT NAME from GRADUATE where DIV = ‘I’ order by NAME;
(b) SELECT NAME,STIPEND,SUBJECT,STIPEND*12 from GRADUATE;
(c) SELECT SUBJECT,COUNT(*) from GRADUATE group by SUBJECT
having SUBJECT=‘PHYSICS’ or SUBJECT=‘COMPUTER SC’;
(d) INSERT INTO GRADUATE values(11,‘KAJOL’,300,‘COMPUTER SC’,75,1);
Computer Science with Python–XII 12.64 (iii) 475
(e) (i) 63
(ii) 800

(iv) 4