Page 293 - PYTHON-12

(iii) To display the content of the entire EMPLOYEEs table, whose DOJ is in between ’09-Feb-2006’
and ’ 08-Aug-2009’.
(iv) To add a new row with the following content:
109,’Harish Roy’,’HEAD-IT’,’S02’,’9-Sep-2007’,’21-Apr-1983’
Ans. (i) SELECT * FROM employee ORDER BY doj DESC;
(ii) SELECT name, design FROM employee WHERE sgrade=S02 OR sgrade=S03;
(iii) SELECT * FROM employee WHERE doj BETWEEN ‘09-Feb-2006’ AND ‘08-Aug-2009’;
(iv) Insert into employee values(109, ‘Harish Roy’, ‘HEAD-IT’, ‘S02’, ‘9-Sep-2007’, ‘21-Apr-1983’);
(b) Give the output of the following SQL queries:
(i) SELECT COUNT(SGRADE),SGRADE FROM EMPLOYEE GROUP BY SGRADE;
(ii) SELECT MIN(DOB),MAX(DOJ) FROM EMPLOYEE;
(iii) SELECT SGRADE, SALARY+HRA FROM SALGRADE WHERE SGRADE =’S02’;
Ans. (i) COUNT (SGRADE) SGRADE
2 S03
2 S02
1 S01
(ii) MAX (DOB) MIN (DOJ)
22-Jul-1987 23-Mar-2003
(iii) SGRADE SALARY+HRA
P003 440000
20. Consider the following tables STOCK and DEALERS and answer (B1) and (B2) parts of this question:

Table: STOCK
ItemNo Item Dcode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 31-Mar-10
5003 Ball Pen 0.25 102 150 20 01-Jan-10
5002 Gel Pen Premium 101 125 14 14-Feb-10
5006 Gel Pen Classic 101 200 22 01-Jan-09
5001 Eraser Small 102 210 5 19-Mar-09
5004 Eraser Big 102 60 10 12-Dec-09
5009 Sharpener Classic 103 160 8 23-Jan-09

Table: DEALERS
Dcode Dname
101 Reliable Stationers
103 Classic Plastics
102 Clear Deals
B1. Write SQL commands for the following statements:
(i) To display details of all Items in the Stock table in ascending order of StockDate.
Ans. SELECT * FROM STOCK ORDER BY StockDate;
(ii) To display ItemNo and Item name of those items from Stock table whose UnitPrice is more than
` 10.
Ans. SELECT ItemNo, Item FROM STOCK WHERE UnitPrice>10;
(iii) To display the details of those items whose dealer code (Dcode) is 102 or Quantity in Stock (Qty)
is more than 100 from the table Stock.
Ans. SELECT * FROM STOCK WHERE Dcode=102 OR Qty>100; Relational Database and SQL
(iv) To display Maximum UnitPrice of items for each dealer individually as per Dcode from the table
Stock.
Ans. SELECT Dcode, MAX (UnitPrice) FROM STOCK GROUP BY Dcode;

12.63