Write SQL queries based on the following tables:

Table: PRODUCT

P_ID	ProductName	Manufacturer	Price	Discount
TP01	Talcum Powder	LAK	40	Null
FW05	Face Wash	ABC	45	5
BS01	Bath Soap	ABC	55	Null
SH06	Shampoo	XYZ	120	10
FW12	Face Wash	XYZ	95	Null

Table: 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	SH06
05	Dreams	Delhi	FW12

(i) Write SQL query to display ProductName 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 given any discount.

(iv) Write SQL query to display ProductName and price for all products.

(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?

(i)

SELECT ProductName, Price 
FROM PRODUCT
WHERE Price BETWEEN 50 AND 150;

Output

+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Bath Soap   |    55 |
| Face Wash   |    95 |
| Shampoo     |   120 |
+-------------+-------+

(ii)

SELECT * FROM PRODUCT
WHERE Manufacturer = 'XYZ' OR Manufacturer = 'ABC';

Output

+------+-------------+--------------+-------+----------+
| P_ID | ProductName | Manufacturer | Price | Discount |
+------+-------------+--------------+-------+----------+
| BS01 | Bath Soap   | ABC          |    55 |     NULL |
| FW05 | Face Wash   | ABC          |    45 |        5 |
| FW12 | Face Wash   | XYZ          |    95 |     NULL |
| SH06 | Shampoo     | XYZ          |   120 |       10 |
+------+-------------+--------------+-------+----------+

(iii)

SELECT ProductName, Manufacturer, Price
FROM PRODUCT
WHERE Discount IS NULL;

Output

+---------------+--------------+-------+
| ProductName   | Manufacturer | Price |
+---------------+--------------+-------+
| Bath Soap     | ABC          |    55 |
| Face Wash     | XYZ          |    95 |
| Talcum Powder | LAK          |    40 |
+---------------+--------------+-------+

(iv)

SELECT ProductName, Price
FROM PRODUCT;

Output

+---------------+--------+
| productname   | price  |
+---------------+--------+
| Bath Soap     |  55.00 |
| Face Wash     |  45.00 |
| Face Wash     |  95.00 |
| Shampoo       | 120.00 |
| Talcum Powder |  40.00 |
+---------------+--------+

(v)

SELECT C.ClientName, C.City, P.P_ID, P.ProductName 
FROM PRODUCT P, CLIENT C 
WHERE P.P_ID = C.P_ID AND C.CITY = 'DELHI';

Output

+---------------+-------+------+---------------+
| ClientName    | City  | P_ID | ProductName   |
+---------------+-------+------+---------------+
| Cosmetic Shop | Delhi | TP01 | Talcum Powder |
| Live Life     | Delhi | BS01 | Bath Soap     |
| Pretty Woman  | Delhi | SH06 | Shampoo       |
| Dreams        | Delhi | FW12 | Face Wash     |
+---------------+-------+------+---------------+

(vi) The column used as a Foreign Key is P_ID in the CLIENT table, and it references the P_ID column in the PRODUCT table.