Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 91 of 103

Review of Database Concepts & SQL — Question 40

Back to all questions
40
Question

Question 27

Write SQL queries based on the following tables:

Table: PRODUCT

P_IDProductNameManufacturerPriceDiscount
TP01Talcum PowderLAK40Null
FW05Face WashABC455
BS01Bath SoapABC55Null
SH06ShampooXYZ12010
FW12Face WashXYZ95Null

Table: CLIENT

C_IDClientNameCityP_ID
01Cosmetic ShopDelhiTP01
02Total HealthMumbaiFW0S
03Live LifeDelhiBS01
04Pretty WomanDelhiSH06
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 given any discount.

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

(v) 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;
Output
+---------------+-------+
| PRODUCTNAME   | PRICE |
+---------------+-------+
| BATH SOAP     |    40 |
| FACE WASH     |    45 |
| FACE WASH     |    55 |
| SHAMPOO       |   120 |
| TALCUM POWDER |    95 |
+---------------+-------+

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