Create the table Product with appropriate data types and constraints. Identify the primary key in Product.

Q. Consider the following table named "Product", showing details of products being sold in a grocery shop.

 

 
	 
PCode
 
	 
PName
 
	 
UPrice
 
	 
Manufacturer
 
 
 
	 
P01
 
	 
Washing Powder
 
	 
120
 
	 
Surf
 
 
 
	 
P02
 
	 
Tooth Paste
 
	 
54
 
	 
Colgate
 
 
 
	 
P03
 
	 
Soap
 
	 
25
 
	 
Lux
 
 
 
	 
P04
 
	 
Tooth Paste
 
	 
65
 
	 
Pepsodant
 
 
 
	 
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 percent 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.

(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; Chapter 1.indd 2411/26/2020 12:31:31 PM

Answer :-

(a) :-

i. Create table product ( Pcode char(4) , PName char(50) , UPrice int , Manufacturer char(50) );
ii. Pcode
iii. select PCode, PName , UPrice from product order by PName ;
iv. Alter table Product add column discount int ;
v. update product set discount = UPrice /10 where Uprice > 100 ;
vi. update product set Uprice = UPrice + (UPrice * 0.12) where Manufacturer = “Dove” ;
vii.  Select count(*) , Manufacturer from product group by Manufacturer ;


(b) :-

i =

 

 
	 
PName
 
	 
Average(UPrice)
 
 
 
	 
Washing Powder
 
	 
120
 
 
 
	 
Tooth Paste
 
	 
59.5
 
 
 
	 
Soap
 
	 
31.5
 
 
 
	 
Shampoo
 
	 
245
 
 

ii =

 

 
	 
Manufacturer
 
 
 
	 
Surf
 
 
 
	 
Colgate
 
 
 
	 
Lux
 
 
 
	 
Pepsodant
 
 
 
	 
Dove
 
 

iii =  

COUNT(DISTINCT  PName)
4

iv =

 

 
	 
PName
 
	 
MAX(UPrice)
 
	 
MIN(UPrice)
 
 
 
	 
Washing Powder
 
	 
120
 
	 
120
 
 
 
	 
Tooth Paste
 
	 
65
 
	 
54
 
 
 
	 
Soap
 
	 
38
 
	 
25
 
 
 
	 
Shampoo
 
	 
245
 
	 
245