Write SQL queries based on the following tables:

Q. Write SQL queries based on the following tables:

PRODUCT:

 
 
 
	 
P_ID
 
	 
ProductName
 
	 
Manufacturer
 
	 
Price
 
	 
Discount
 
 
 
	 
TP01
 
	 
Talcum Powder
 
	 
LAK
 
	 
40
 
	 

 
 
 
	 
FW05
 
	 
Face Wash
 
	 
ABC
 
	 
45
 
	 
5
 
 
 
	 
BS01
 
	 
Bath Soap
 
	 
ABC
 
	 
55
 
	 

 
 
 
	 
SH06
 
	 
Shampoo
 
	 
XYZ
 
	 
120
 
	 
10
 
 
 
	 
FW12
 
	 
Face Wash
 
	 
XYZ
 
	 
95
 
	 

 
 
 
 

CLIENT:

 
 
 
	 
C_ID
 
	 
ClientName
 
	 
City
 
	 
P_ID
 
 
 
	 
01
 
	 
Cosmetic Shop
 
	 
Delhi
 
	 
TP01
 
 
 
	 
02
 
	 
Total Health
 
	 
Mumbai
 
	 
FW05
 
 
 
	 
03
 
	 
Live Life
 
	 
Delhi
 
	 
BS01
 
 
 
	 
04
 
	 
Pretty Woman
 
	 
Delhi
 
	 
SHO6
 
 
 
	 
05
 
	 
Dreams
 
	 
Delhi
 
	 
FW12
 
 
 
 

(i) Write SQL Query to display Product Name and Price for all products whose Price is in the range 50 to 150.

(ii) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC.

(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not giving any discount.

(iv) Write SQL query to display ProductName and price for all products whose ProductName ends with 'h'.

(v) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose city is Delhi.

(vi) Which column is used as Foreign Key and name the table where it has been used as Foreign key.

Answer =

(i)

Select ProductName , Price from PRODUCT where Price between 50 and 150 ;

(ii)
Select * from PRODUCT where Manufacture in (“XYZ”, “ABC”) ;

(iii)

Select ProductName , Manufacture , Price from PRODUCT where Discount is null ;

(iv)

Select ProductName , Price from PRODUCT where ProductName like “%h” ;

(v)

Select ClientName , City , P_ID and ProductName for where City = “Delhi” ;

(vi)

P_ID in CLIENT