Page 286 - PYTHON-12

Table: Model

Model_ID Comp_ID Cost DateOfManufacture
T020 1 2000 2010-05-12
M032 4 7000 2009-04-15
M059 2 800 2009-09-23
A167 3 1200 2011-01-12
T024 1 1300 2009-10-14

Note:
(b) Model_ID is the Primary Key.
(c) Comp_ID is the Foreign Key referencing Comp_ID of Company table. Write SQL commands for queries
(i) to (iv) and output for (v) and (vi).
(i) To display the details of all the models in the Model table in ascending order of DateOfManufacture.
(ii) To display the details of those models manufactured in 2011 and whose Cost is below 2000.
(iii) To display the Model_ID, Comp_ID, Cost from the table Model, CompName and ContactPerson
from Company table, with their corresponding Comp_ID.
(iv) To decrease the cost of all the models in Model table by 15%.
(v) Select COUNT(DISTINCT CompHO) from Company;
(vi) Select CompName, contact(‘Mr.’,ContactPerson) from Company where CompName ends with ‘a’;
Ans. (i) select * from model
order by DateOfManufacture;
(ii) select * from model
where year(DateOfManufacture) = 2011 and cost < 2000;
(iii) select Model_ID, Comp_ID, cost, CompName, ContactPerson from Model, Company
where Model. Comp_ID = Company.Comp_ID;
(iv) Update Model
set Cost = Cost – 0.15*Cost;
(v) 3
(vi) Ajanta Mr. R. Mehta
Maxima Mr. B. Kohli
13. Consider the following two tables: PRODUCT and CLIENT.

Table: Product
P_ID ProductName Manufacturer Price ExpiryDate
TP01 Talcum Powder LAK 40 2011-06-26
FW05 Face Wash ABC 45 2010-12-01
BS01 Bath Soap ABC 55 2010-09-10
SH06 Shampoo XYZ 120 2012-04-09
FW12 Face Wash XYZ 95 2010-08-15
Note:
Computer Science with Python–XII 12.56 C_ID ClientName City P_ID

 P_ID is the Primary Key.
Table: Client

Delhi
Cosmetic Shop
FW05
1
6
Total Health
BS01
Mumbai
Delhi
SH06
Live Life
12
15
Delhi
Pretty One
FW05
16
TP01
Dreams
Bengaluru
NULL
Expressions
Delhi
14