Set 1 : Consider the following tables. Write SQL commands for the statements (i) to (iv).

Table : SENDER
SenderID SenderName SenderAddress SenderCity
ND01 R Jain 2, ABC Appts New Delhi
MU02 H Sinha 12, Newtown Mumbai
MU15 S Jha 27/A, Park Street Mumbai
ND50 T Prasad 122-K, SDA New Delhi
Table : RECIPIENT
RecID SenderID RecName RecAddress RecCity
KO05 ND01 R Bajpayee 5, Central Avenue Kolkata
ND08 MU02 S Mahajan 116, A Vihar New Delhi
MU19 ND01 H Singh 2A, Andheri East Mumbai
MU32 MU15 P K Swamy B5, C S Terminus Mumbai
ND48 ND50 S Tripathi 13, B1 D, Mayur Vihar New Delhi
(i)To display the names of all Senders from Mumbai.
(ii)To display the RecID, SenderName, SenderAddress, RecName, RecAddress for every Recipient.
(iii) To display Recipient details in ascending order of RecName.
(iv) To display number of Recipients from each City.
Ans:
(i)SELECT SenderName FROM SENDER WHERE SenderCity = ‘Mumbai’;
(ii)SELECT RecID, SenderName, SenderAddress, RecName, RecAddress FROM
RECIPIENT, SENDER WHERE RECIPIENT.SenderID = SENDER.SenderID;
(iii)SELECT * FROM RECIPIENT ORDER BY RecName;
(iv)SELECT COUNT(*) AS “No. of Recipients”, RecCity FROM RECIPIENT GROUP BY RecCity;

Set 2: Consider the following tables. Write SQL commands for the statements (i) to (iv).
Table: BOOKS
BID BNAME AUNAME PRICE TYPE QTY
COMP11 LET US C YASHWANT 350 COMPUTER 15
GEOG33 INDIA MAP RANJEET P 150 GEOGRAPHY 20
HIST66 HISTORY R BALA 210 HISTORY 25
COMP12 MY FIRST C VINOD DUA 330 COMPUTER 18
LITR88 MY DREAMS ARVIND AD 470 NOBEL 24

Table: ISSUED
BID QTY_ISSUED
HIST66 10
COMP11 5
LITR88 15
(i) Display book name and author name and price of computer type books.
(ii) To increase the price of all history books by Rs 50.
(iii) Show the details of all books in ascending order of their prices.
(iv) To display book id, book name and quantity issued for all books which have been issued.

Ans:
(i) select bname, auname, price from books where bid like “comp%”;
(ii) update books set price = price + 50 where bid like “hist%”;
(iii) select * from books order by price;
(iv) select bid, bname, qty_issued from books, issued where books.bid = issued.bid;
Set 3
Table: WORKERS
W_ID FIRSTNAME LASTNAME GENDER ADDRESS CITY
102 Sam Tones M 33 Elm St Paris
105 Sarah Ackerman F U.S. 110 New York
144 Manila Sengupta F 24 Friends Street New Delhi
210 George Smith M 83 First Street Howard
255 Mary Jones F 842,Vine Ave. Losantiville
300 Robert Samuel M 9 Fifth Cross Washington
335 Henry Williams M 12 Moore Street Boston
403 Ronny Lee M 121 Harrison St. New York
451 Pat Thompson M 11 Red Road Paris

Table: DESIG
W_ID SALARY BENEFITS DESIGNATION
102 75000 15000 Manager
105 85000 25000 Director
144 70000 15000 Manager
210 75000 12500 Manager
255 50000 12000 Clerk
300 45000 10000 Clerk
335 40000 10000 Clerk
400 32000 7500 Salesman
451 28000 7500 Salesman

(i) To display the content of WORKERS table in ascending order of LASTNAME.

(ii) To display First Name, Worker ID and Address of male Workers only.
(iii) To display the Minimum salary among Managers and Clerks from the table DESIG.
(iv) To display First Name and Salary from Workers and Designation Table for each worker.

Ans:
(i) SELECT * FROM WORKERS ORDER BY LASTNAME;
(ii) SELECT FIRSTNAME, W_ID, ADDRESS FROM WORKERS WHERE GENDER=’M’;
(iii) SELECT MIN(SALARY) FROM DESIG WHERE DESIGNATION IN(‘MANAGER’, ‘CLERKS’);
(iv) SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE WORKERS.W_ID=DESIG.W_ID;

Set 4:
Table: DOCTOR
ID NAME DEPT GENDER EXPERIENCE
101 John ENT M 12
104 Smith ORTHOPEDIC M 5
107 George CARDIOLOGY M 10
114 Lara SKIN F 3
109 K George MEDICINE F 9
105 Johnson ORTHOPEDIC M 10
117 Lucy ENT F 3
111 Bill MEDICINE F 12
130 Morphy ORTHOPEDIC M 15
Table: SALARY
ID BASIC ALLOWANCE CONSULTATION
101 12000 1000 300
104 23000 2300 500
107 32000 4000 500
114 12000 5200 100
109 42000 1700 200
105 18900 1690 300
117 11000 1000 300
111 41000 1600 200
130 21700 2600 300
(i) Display NAME of all doctors who are in “ORTHOPEDIC” having more than 10 years experience from the table
DOCTOR.
(ii) Display the average salary of all doctors working in “ENT” department using the DOCTOR and SALARY.
(Salary= Basic + Allowance)
(iii) Display the minimum ALLOWANCE of female doctors.
(iv) Display the highest consultation fee amount for all male doctors.

Ans:
i) Select Name from Doctor where Dept=”Medicine” and Experience>10
ii) Select avg(basic+allowance) from Doctor,Salary where Dept=”Ent” and Doctor.Id=Salary.Id
iii) Select min(Allowance) from Doctro,Salary where GENDER =”F” and Doctor.Id=Salary.Id
iv) Select max(Consulation) from Doctor,Salary where GENDER =”M” and Doctor.Id=Salary.Id

Set 5 :
Table: ITEM
P_ID ItemName Manufacturer Price
PC01 Personal Computer ABC 35000
LC05 Laptop ABC 55000
PC03 Personal Computer XYZ 32000
PC06 Personal Computer COMP 37000
LC03 Laptop PQR 57000

Table: CUSTOMER
C_ID CustomerName City P_ID
01 N.Roy Delhi LC03
06 H.Singh Mumbai PC03
12 R.Pandey Delhi PC06
15 C.Sharma Delhi LC03
16 K.Agarwal Banglore PC01

(i) To display the details of those Customers whose city is Delhi.

(ii) To display the details of Item whose Price is in the range of 35000 to 55000
(Both values included).
(iii) To display the CustomerName, City from table Customer, and ItemName and Price from
table ITEM, with their corresponding matching P_ID.
(iv) Display distinct cities from table CUSTOMER
Ans:
i) Select * from Customer Where City=”Delhi”
ii) Select * from Item Where Price>=35000 and Price <=55000
iii) Select CustomerName,City,ItemName, Price from Item,Customer where Item.P_ID=Customer.P_ID.
iv) SELECT DISTINCT City FROM Customer.

Set 6:

Table: PRODUCT
P_ID Product Name Manufacturer Price
TP01 TalcomPowder LAK 40
FW05 Face Wash ABC 45
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120
FW12 Face Wash XYZ 95

Table: CLIENT
C_ID Client Name City P_ID
01 Ankit Delhi FW05
06 Sumit Mumbai BS01
12 Amit Delhi SH06
15 Suchitra Delhi FW12
16 Ankita Banglore TP01
(i) To display the details of those Clients whose city is Delhi.
Ans: Select * from Client where City=”Delhi”
(ii) To display the details of Products whose Price is in the range of 50 to 100(Both values included).
Ans: Select * from product where Price between 50 and 100
(iii) To display the ClientName, City from table Client, and ProductName and Price from table Product, with
their corresponding matching P_ID.
Ans: Select ClientName,City,ProductName, Price from Product,Client where Product.P_ID=Client.P_ID.
(iv) To increase the Price of all Products by 10
Ans: Update Product Set Price=Price +10