Page 231 - Informatics_Practices_Fliipbook_Class12
P. 231
Ans. a. 8
b. 123.23, 340
c. 21
d. 1979 11 26 November
e. IND ence
f. form actices
3. Consider the following table named “Product”, showing details of products being sold in a grocery shop.
PCode PName UPrice Manufacture
P01 Washing Powder 120 Surf
P02 Tooth Paste 54 Colgate
P03 Soap 25 Lux
P04 Tooth Paste 65 Pepsodent
P05 Soap 38 Dove
P06 Shampoo 245 Dove
a. Write SQL queries for the following:
i. Create the table Product with appropriate data types and constraints.
ii. Identify the primary key in Product.
iii. List the Product Code, Product name and price in descending order of their product name. If PName is the same then
display the data in ascending order of price.
iv. Add a new column Discount to the table Product.
v. Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the
UPrice is more than 100, otherwise, the discount will be 0.
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
vii. Display the total number of products manufactured by each manufacturer.
Ans. i. CREATE TABLE product
(PCode CHAR(3) PRIMARY KEY,
Pname VARCHAR(25) NOT NULL,
Uprice DECIMAL(10,2),
Manufacturer VARCHAR(30));
ii. PCode
iii. SELECT pcode,pname,uprice FROM product ORDER BY pname DESC,uprice ASC;
iv. ALTER TABLE product ADD discount DECIMAL(8,2);
v. UPDATE product SET discount=0.10*uprice WHERE uprice >100;
vi. UPDATE product SET uprice = uprice+0.12*uprice WHERE manufacturer="Dove";
b. Write the output(s) produced by executing the following queries on the basis of the information given above in the table
Product:
i. SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;
ii. SELECT DISTINCT Manufacturer FROM Product;
iii. SELECT COUNT(DISTINCT PName) FROM Notes Product;
iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
SQL: Working with Two Tables 217

