Page 287 - PYTHON-12

Note:
C_ID is the Primary Key.
P_ID is the Foreign Key referencing P_ID of Client table.
Write SQL statements for the queries (i) to (iv) and output for (v) and (vi):
(i) To display the ClientName and City of all Mumbai and Delhi-based clients in Client table.
(ii) Increase the price of all the products in Product table by 10%.
(iii) To display the ProductName, Manufacturer, ExpiryDate of all the products that expired on or before
‘2010-12-31’.
(iv) To display C_ID, ClientName, City of all the clients (including the ones that have not purchased a
product) and their corresponding ProductName sold.
(v) Select COUNT(DISTINCT Manufacturer) from Product;
(vi) Select C_ID, Client_Name, City from Client where City Like ‘M%’;
Ans. (i) Select ClientName, City from Client
where City = ‘Mumbai’ or City = ‘Delhi’;
(ii) Update Product
set Price = Price + 0.10 * Price;
(iii) Select ProductName, Manufacturer, ExpiryDate from Product
where ExpiryDate < = ‘2010-12-31’;
(iv) Select C_ID, ClientName, City, ProductName from Client Left Join Product
on Client. P_ID = Product.P_ID;
(v) 3
(vi) 6 Total Health Mumbai
14. Consider the following two tables: STATIONERY and CONSUMER.

Table: Stationery
S_ID StationeryName Company Price StockDate
DP01 Dot Pen ABC 10 2011-03-31
PL02 Pencil XYZ 6 2010-01-01
ER05 Eraser XYZ 7 2010-02-14
PL01 Pencil CAM 5 2009-01-09
GP02 Gel Pen ABC 15 2009-03-19
Note:
(a) S_ID is the Primary Key.
Table: Consumer
C_ID ConsumerName Address P_ID
01 Good Learner Delhi PL01
06 Write Well Mumbai GP02
12 Topper Delhi DP01
15 Write & Draw Delhi PL02
16 Motivation Bengaluru PL01

Note:
(b) C_ID is the Primary Key.
(c) P_ID is the Foreign Key referencing S_ID of Stationery table.
Write SQL statements for the queries (i) to (iv) and output for (v) and (vi): Relational Database and SQL
(i) To display details of all the Stationery Items in the Stationery table in descending order of StockDate.
(ii) To display details of that Stationery item whose Company is XYZ and price is below 10.
(iii) To display ConsumerName, Address from the table Consumer and Company and Price from Stationery
table, with their corresponding S_ID.
12.57