Table : STORE

Itemno Item Scode Qty Rate LastBuy

2005 Sharpener Classic 23 60 8 30-Jun-09
2003 Ball Pen 0.25 22 50 25 1-Feb-10
2002 Gel Pen Premium 21 150 12 24-Feb-10
2006 Gel Pen Classic 21 250 20 11-Mar-09
2001 Eraser Small 22 220 6 19-Jan-09
2004 Eraser Big 22 110 8 2-Dec-09
2009 Ball Pen 0.5 21 180 18 3-Nov-09

SQL> create table STORE

(itemno int, item char (20), scode int, qty int, rate int, lastbuy date);

SQL> insert into STORE values (2005,'Sharpener Classic', 23, 60, 8,'30-jun-09');

SQL> insert into STORE values (2003,'Ball Pen 0.25', 22, 50, 25,'1-Feb-10');

SQL> insert into STORE values (2002,'Gel Pen Premium', 21, 150, 12,'24-Feb-10');

SQL> insert into STORE values(2006,'Gel Pen Classic',21, 250,20,'11-Mar-09');

SQL> insert into STORE values(2001,'Eraser Small',22,220,6,'19-Jan-09');

SQL> insert into STORE values(2004,'Eraser Big',22,110,8,'2-Dec-09');

SQL> insert into STORE values(2009,'Ball Pen 0.5',21,180,18,'3-Nov-09');

Table: SUPPLIERS

scode sname
21 Premium Staioners
23 Soft Plastics
22 Tera Supply

create table SUPPLIERS (scode int, sname char(20));

SQL> insert into SUPPLIERS values(21,'Premium Staioners');

SQL> insert into SUPPLIERS values(23,'Soft Plastics');

SQL> insert into SUPPLIERS values(22,'Tera Supply');

1
Que) Write SQL commands for the following statements:
(i) Display details of all the items in the STORE table in ascending order of lastbuy.
Select * from STORE order by lastbuy;
(ii) Display Itemno and itemname of those items from STORE table whose rate is more
than 15 rrupees.
Select itemno,item from STORE where rate > 15;
(iii) Display details of those items whose supplier code (scode) is 22 or Quantity in Store
(Qty) is more than 110 from table STORE.
Select * from STORE where (scode=22 or qty >110);
(iv) To display minimum rate of items whose suppliers individually as per scode from
table STORE.
Select scode, min (rate) from STORE group by scode;
(v) Display item name, qty of items of supplier Tera Supply.
Select item,qty from STORE S, Suppliers P
where (S.scode=P.scode and P.sname='Tera Supply';

Que) Give output of the following SQL queries:

(i) SELECT COUNT(DISTINCT SCODE) FROM STORE;
Ans: COUNT(DISTINCTSCODE)
---------------------------------------
3
(ii) SELECT RATE*QTY FROM STORE WHERE ITEMNO=2004;
Ans: RATE*QTY
----------------
880
(iii) Select item, sname from STORE S, Supplier P where S.scode=P.scode and
itemno=2006;
Ans: ITEM SNAME
-------------------- --------------------
Gel Pen Classic Premium Staioners
(iv) SELECT MAX(LASTBUY) FROM STORE;
Ans: MAX (LASTBUY)
---------
24-FEB-10

2
 Table: WORKER

ECODE NAME DESIG PLEVEL DOJ DOB

11 Radhe Shyam Supervisor P001 13- Sep- 2004 23-Aug-1981
12 Chander Nath Operator P003 22-Feb-2010 12-Jul-1987
13 Fizza Operator P003 14-Jun-2009 14-0ct-1983
15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984
18 Sanya Clerk P002 19-Dec-2005 09-Jun-1983

SQL> create table WORKER

(ecode int,name char(20),design char(20), plevel char(5), DOJ date,DOB date);
SQL> insert into WORKER values (11,'Radhe Shyam','Supervisor','P001','13-Sep-2004','23-
Aug-1981');
SQL> insert into WORKER values (12,'Chander Nath','Operator','P003','22-Feb-2010','12-Jul-
1987');
SQL> insert into WORKER values (13,'Fizza','Operator','P003','14-Jan-2009','14-Oct-1983');
SQL> insert into WORKER values (15,'Ameen Ahmed','Mechanic','P002','21-Aug-2006','13-
Mar-1984');
SQL> insert into WORKER values (18,'Sanya','Clerk','P002','19-Dec-2005','9-Jun-1983');

Table:PAYLEVEL

PLEVEL PAY ALLOWANCE

P001 26000 12000
P002 22000 10000
P003 12000 6000

SQL> create table PAYLEVEL (plevel char (5), pay decimal, allowance decimal);
SQL> insert into paylevel values ('P001',26000,12000);
SQL> insert into paylevel values('P002',22000,10000);
SQL> insert into paylevel values('P003',12000,6000);

Que) Write SQL commands for the following statements:

(i) To display the details of all WORKERs in descending order of DOB.
Ans: Select * from WORKER order by DOB desc;

3
(ii) To display NAME and DESIG of those WORKERs, whose PLEVEL
is either P001 or P002.
Ans: Select NAME, DESIGN from WORKER where PLEVEL in (‘P001’,’P002’);
(iii) To display the content of all the WORKERs table, whose DOB is in
between '19-JAN-1984' and '18-JAN-1987'.
Ans: Select * from WORKER where ( DOB between '19-JAN-1984' and '18-JAN-1987' );
(iv) To add a new row with the following:
19, 'Daya Kishore', 'Operator', 'P003', '19-Jun-2008', '11-Jun-1984'
Ans: insert into WORKER values(19, 'Daya Kishore', 'Operator', 'P003', '19-Jun-2008',
'11-Jun-1984');
(v) Display pay and allowances of ‘Fizza’.
Ans: Select NAME, PAY,ALLOWANCES from WORKER W, PAYLEVEL P
where (W.PLEVEL = P.PLEVEL and W.NAME=’Fizza’);
(c) Give the output of the following SQL queries:
(i) SELECT COUNT (PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
Ans:COUNT(PLEVEL) PLEVEL
------------------- ------------
1 P001
2 P002
2 P003

(ii) SELECT MAX (DOB), MIN (DOJ) FROM WORKER;

Ans: MAX(DOB) MIN(DOJ)
----------------- -----------------
12-Jul-1987 13-Sep-2004

(iii) SELECT Name, Pay FROM WORKER W, PAYLEVEL P

WHERE W. PLEVEL = S. PLEVEL AND P.ECODE<13 ;
Ans: NAME PAY
-------------------- -----------
Radhe Shyam 26000
Chander Nath 12000

(iv) SELECT PLEVEL, PAY+ALLOWANCE FROM PAYLEVEL WHERE PLEVEL= 'P003' ;

Ans: PLEVEL PAY+ALLOWANCE
------------ --------------------------------
P003 18000

4
 Table: PROUDCT
PID Productname Manufacturer Price
TP01 Talcum Powder LAK 40
FW05 Face Wash ABC 45
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120
FW12 Face Wash XYZ 95

SQL> create table product (pid char(10),productname char(20),manufacturer

char(4),price int);
SQL> insert into product values('TP01','Talcom Powder','LAK',40);
SQL> insert into product values('FW05','Face Wash','ABC',45);
SQL> insert into product values('BS01','Bath Soap','ABC',55);
SQL> insert into product values('SH06','Shampoo','XYZ',120);
SQL> insert into product values('FW12','Face Wash','XYZ',95);
Table: CLIENT
CID ClientName City PID
01 Cosemetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi SH06
15 Pretty Woman Delhi FW12
16 Dreams Banglore TP01
SQL> create table client (cid int, clientname char (20),city char(10), pid char(5));
SQL> insert into client values(01,'Cosmetic Soap','Delhi','FW05');
SQL> insert into client values(06,'Total Health','Mumbai','BS01');
SQL> insert into client values(12,'Live Life','Delhi','SH06');
SQL> insert into client values(15,'Pretty Woman','Delhi','FW12');
SQL> insert into client values(16,'Dreams','Banglore','TP01');
Que) Write SQL commands for the following statements:
1) Display details of those clients whose city is Delhi.
Select * from CLIENT where city=’Delhi’;
2) Display details of Products whose price is in range of 50 to 100.
Select * from product where price between 50 and 100;

5
3) Display ClientName, City from table Client, ProductName and Price from table Product with
their corresponding matching PID.
Select ClientName, City, ProductName, Price
from Client C, Product P
where C.PID=P.PID;
4) Increase the price of all products by 10.
Update Product
Set Price=Price+10;
5) Display details of products in ascending order of price.
Select * from Product order by price asc;
(Que) Give the output of the following SQL queries:
1) SELECT DISTINCT City from Client;
City
-------
Delhi
Mumbai
Banglore
2) SELECT Manufacturer, MAX (Price), Min (Price), Count(*)
From Product Group By Manufacturer;
Manufacturer MAX(Price) MIN(Price) Count(*)
---------------------------------------------------------------------------
LAK 40 40 1
ABC 55 45 2
XYZ 120 95 2
3) SELECT ClientName, ManufacturerName
from Product, Client where Client.PID=Product.PID;
ClientName ManufacturerName
-------------------------------------------------------------------------
Cosemetic Shop Face Wash
Total Health Bath Soap
Live Life Shampoo
Pretty Woman Face Wash
Dreams Talcom Powder
4) SELECT ProductName, Price * 4 From Product where Price <50;
ProductName Price*4
-----------------------------------------------
Talcom Powder 160
Face Wash 180