Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 78 of 91

Relational Database and SQL — Question 39

Back to all questions
39
Question

Question 30

Write SQL queries based on the following tables:

Table: PRODUCT

P_IDProductNameManufacturerPriceDiscount
TP01Talcum PowderLAK40NULL
FW05Face WashABC455
BS01Bath SoapABC55NULL
5H06ShampooXYZ12010
FW12Face WashXYZ95NULL

Table: CLIENT

C_IDClientNameCityP_ID
01Cosmetic ShopDelhiTP01
02Total HealthMumbaiFW05
03Live LifeDelhiBS01
04Pretty WomanDelhi5H06
05DreamsDelhiFW12

(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 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;
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
WHERE ProductName LIKE '%h';
Output
+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Face Wash   |    45 |
| Face Wash   |    95 |
+-------------+-------+

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