Page 228 - Informatics_Practices_Fliipbook_Class12
P. 228
b. SELECT P.TNO, TNAME, COUNT(*) AS "NoOfBookings"
FROM TRAINS T, PASSENGERS P
WHERE (T.TNO = P.TNO)
GROUP BY P.TNO, TNAME
HAVING COUNT(*)>1;
4. Consider a database having three tables: SUPPLIER, PRODUCT, and SHIPMENT. A supplier has the following
attributes: supplier number (SNo), supplier name (Sname), and the location (SCity) of the supplier. Each product
has a product name (Pname). Products of different brands are distinguished from each other and are assigned different
product numbers (PNo). It is assumed that a product of a particular brand (Brand) is manufactured only in one city
(Pcity). An order is identified by (OrderNo) and includes a certain quantity (Quantity) of only one product
supplied by a particular supplier at a specific price (Price).
SUPPLIER PRODUCT SHIPMENT
SNo Sname SCity PNo Pname Brand City OrderNo SNo PNO Price Quantity
101 Aradhaya Pvt. L Mumbai 201 Refrigerator Whirlpool Chennai 1000 101 201 50000.00 50
102 XYZ Enterprises Bangalore 202 Washing Machine Samsung Kolkata 1001 101 202 70000.00 30
103 Komal Enterprise Delhi 203 Television Sony Mumbai 1002 102 202 90000.00 75
104 Cloudtail Patna 104 Television Samsung Kolkata 1003 104 205 25000.00 20
105 PQR Enterprises Uttrakhand 205 Washing Machine Whirlpool Chennai 1004 105 205 55000.00 30
106 Tech Solutions Bangalore 206 Refrigerator Whirlpool Delhi 1005 102 207 48000.00 50
107 XYZ Enterprises Mumbai 207 Microwave Oven IFB Mumbai 1006 105 210 23000.00 30
208 Microwave Samsung Mumbai 1007 104 209 60000.00 75
209 Air Fryer IFB Chandigarh 1008 102 208 65000.00 20
210 Air Fryer Philips Lucknow 1009 103 207 81000.00 30
1010 106 204 56000.00 30
1011 107 210 38000.00 50
1012 101 201 72000.00 50
Write a SELECT statement for the following queries:
(i) Queries involving tables PRODUCT and SHIPMENT
a. Retrieve the order number and name of the product supplied in each shipment.
b. Retrieve the order number of the products of the Whirlpool brand.
c. Retrieve the product names sold at a price greater than 70000.
d. For each product, retrieve the minimum price and the supplier number of the supplier supplying that product.
e. For each city, find the total quantity shipped from that city.
(ii) Queries involving tables SUPPLIER and SHIPMENT
a. Retrieve the supplier names for suppliers who supply the product with PNo 201.
b. Retrieve the names of suppliers who have supplied more than two orders.
c. Retrieve the number of orders supplied by each supplier.
d. Retrieve the supplier names for suppliers who supply from Mumbai, and the order exceeds 50000.
e. Retrieve the supplier names for suppliers who supply from Mumbai and supply quantity exceeding 40.
f. Retrieve the details of shipments supplied by suppliers from Karnataka.
214 Touchpad Informatics Practices-XII

