FURNITURE
(1)
NO ITEMNAME TYPE DATEOFSTOCK PRICE DISCOUNT
1 White lotus Double Bed 23/02/02 30000 25
2 Pink feather Baby cot 20/01/02 7000 20
3 Dolphin Baby cot 19/02/02 9500 20
4 Decent Office Table 01/01/02 25000 30
5 Comfort zone Double Bed 12/01/02 25000 25
6 Donald Baby cot 24/02/02 6500 15
7 Royal Finish Office Table 20/02/02 18000 30
8 Royal tiger Sofa 22/02/02 31000 30
9 Econo sitting Sofa 13/12/01 9500 25
10 Eating Paradise Dining Table 19/02/02 11500 25
ARRIVALS
NO ITEMNAME TYPE DATEOFSTOCK PRICE DISCOUNT
1 Wood Comfort Double Bed 23/03/03 25000 25
2 Old Fox Sofa 20/02/03 17000 20
3 Micky Baby cot 21/02/03 7500 15
a) To show all information about the Baby cots from the FURNITURE table.
Select * from furniture where type=’Baby Cot’;
b) To list the ITEMNAME which are priced at more than 15000 from the FURNITURE table.
Select itemname from furniture where price>15000;
c) To list ITEMNAME and TYPE of those items, in which date of stock is before 22/01/02from the
FURNITURE table in descending of ITEMNAME.
Select itemname,type from furniture where dateofstock<’22/01/02’ order by itemname
desc;
d) To display ITEMNAME and DATEOFSTOCK of those items, in which the discount percentage is more
than 25 from FURNITURE table.
Select itemname, dateofstock from furniture where discount>25;
e) To count the number of items, whose TYPE is "Sofa" from FURNITURE table.
Select count(*) from furniture where type=’Sofa’;
a) To insert a new row in the ARRIVALS table with the following data: 14,“Valvet
touch”, "Double bed", {25/03/03}, 25000,30
Insert into arrivals values(14, ‘Velvet Touch’,’Doublebed’,{25/03/03},25000,30)
b) Give the output of following SQL stateme
Note: Outputs of the above mentioned queries should be based on original data given in both
the tables i.e., without considering the insertion done in (f) part of this question.
(i) Select COUNT(distinct TYPE) from FURNITURE;
5
(ii) Select MAX(DISCOUNT) from FURNITURE,ARRIVALS;
30
(iii) Select AVG(DISCOUNT) from FURNITURE where TYPE="Baby cot";
18.33
(iv) Select SUM(Price) from FURNITURE where DATEOFSTOCK<{12/02/02};
66500
(2) Consider the following tables GAMES and PLAYER. Write SQL commands for the statements
(a) to (d) and give outputs for SQL queries (E1) to (E4)
GAMES
GCode GameName Number PrizeMoney ScheduleDate
101 Carom Board 2 5000 23-Jan-2004
102 Badminton 2 12000 12-Dec-2003
103 Table Tennis 4 8000 14-Feb-2004
105 Chess 2 9000 01-Jan-2004
108 Lawn Tennis 4 25000 19-Mar-2004
PLAYER
PCode Name Gcode
1 Nabi Ahmad 101
2 Ravi Sahai 108
3 Jatin 101
4 Nazneen 103
(a) To display the name of all Games with their Gcodes
Select Gname,Gcode from Game;
(b) To display details of those games which are having PrizeMoney more than 7000.
Select * from Games where prize money>7000;
(c) To display the content of the GAMES table in ascending order of ScheduleDate.
Select * from Games orderby schedule date’
(e1) SELECT COUNT(DISTINCT Number) FROM GAMES;
2
(e2) SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;
19-March-2004, 12-Dec-2003
(e3) SELECT SUM(PrizeMoney) FROM GAMES;
59000
(e4) SELECT DISTINCT Gcode FROM PLAYER;
Gcode
101
102
103
105
108
(3) Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of this
question:
WORKER
ECODE NAME DESIG PLEVEL DOJ DOB
11 Radhey 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-June-2009 14-Oct-1983
15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984
18 Sanya Clerk P002 19-Dec-2005 09-June-1983
PAYLEVEL
PAYLEVEL PAY ALLOWANCE
P001 26000 12000
P002 22000 10000
P003 12000 6000
(a) Write SQL commands for the following statements:
(i) To display the details of all WORKERs in descending order of DOB.
Select * from Worker orderby DOB desc;
(ii) To display NAME and DESIG of those WORKERs whose PLEVEL is either P001 or
P002.
Select Name ,Desig from Worker where Plevel=’Pool’ or
Plevel=’Poo2’;
(iii) To display the content of all the WORKERs table, whose DOB is in between ’19-
JAN-1984’ and ’18-JAN-1987’.
Select * from Worker where dob between ’19-jan-1984’ and ’17-jan-
1987’;
(iv) To add a new row with the following:
19, ‘Daya kishore’, ‘Operator’, ‘P003’, ’19-Jun-2008’, ’11-Jul-1984’
Insert into worker values(19,’Daya Kishore’,’operator’,’P003’,’19-jun-
2008’,’11-jul-1984’);
(b) Give the output of the following SQL queries:
(i) SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
5
(ii) SELECT MAX(DOB), MIN(DOJ) FROM WORKER;
’12-jul-1987’, ‘13-Sep-2004’
(4) Consider the following tables CABHUB and CUSTOMER and answer (a) and (b) parts of this
question:
CABHUB
Vcode VehicleName Make Color Capacity Charges
100 Innova Toyota WHITE 7 15
102 SX4 Suzuki BLUE 4 14
104 C Class Mercedes RED 4 35
105 A-Star Suzuki WHITE 3 14
108 Indigo Tata SILVER 3 12
CUSTOMER
CCode CName VCode
1 Hemant Sahu 101
2 Raj Lal 108
3 Feroza Shah 105
4 Ketan Dhal 104
(a) Write SQL commands for the following statements:
1) To display the names of all white colored vehicles
Select * from CABHUB where color=’white’
2) To display name of vehicle, make and capacity of vehicles in ascending order of
their sitting capacity
Select VehicleName , Make, Capacity from CABHUB orderby capacity;
3) To display the highest charges at which a vehicle can be hired from CABHUB.
Select Max(Charges) from CABHUB;
4) To display the customer name and the corresponding name of the vehicle hired
by them.
Select Cname Cu, VehicleName from Cabhub Ca, Customer Cu where
[Link]=[Link];
(b) Give the output of the following SQL queries:
1) SELECT COUNT(DISTINCT Make) FROM CABHUB;
4
2) SELECT MAX(Charges), MIN(Charges) FROM CABHUB;
35,12
3) SELECT COUNT(*), Make FROM CABHUB;
7
4
3
4) SELECT VehicleName FROM CABHUB WHERE Capacity = 4;
SX4
CClass
(5) Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown
in (g1) to (g4) parts on the basis of tables ITEMS and TRADERS:
ITEMS
CODE INAME QTY PRICE COMPANY TCODE
1001 DIGITAL PAD 12i 120 11000 XENITA T01
1006 LED SCREEN 40 70 38000 SANTORA T02
1004 CAR GPS SYSTEM 50 21500 GEOKNOW T01
1003 DIGITAL CAMERA 12X 160 8000 DIGICLICK T02
1005 PEN DRIVE 32GB 600 1200 STOREHOME T03
TRADERS
TCode TName CITY
T01 ELECTRONIC SALES MUMBAI
T03 BUSY STORE CORP DELHI
T02 DISP HOUSE INC CHENNAI
a) To display the details of all the items in the ascending order of item names (i.e.
INAME).
Select * from ITEMS order by Iname;
b) To display item name and price of all those items, whose price is in range of
10000 and 22000 (both values inclusive).
Select INAME, PRICE from ITEMS where PRICE between 10000 and
22000;
c) To display the number of items, which are traded by each trader. The expected
output of this query should be:
T01 2
T02 2
T03 1
Select Tcode, count(*) from ITEMS group by Tcode;
d) To display the price, item name and quantity (i.e. qty) of those items which
have quantity more than 150.
Select Price, Iname, QTY from ITEMS where Qty>150;
e) To display the names of those traders, who are either from DELHI or
from MUMBAI.
Select Iname from Traders where CITY=’Delhi’ or CITY=’Mumbai’;