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

JOINS and SET Operations — Question 13

Back to all questions
13
Question

Question 13

In a database there are two tables "Product" and "Client" as shown below :

Table : Product

P_IDProductNameManufacturePrice
P001MoisturiserXYZ40
P002SanitizerLAC35
P003Bath SoapCOP25
P004ShampooTAP95
P005Lens SolutionCOP350

Table : Client

C_IDClientNameCityP_ID
01Dreamz DisneyNew DelhiP002
05Life Line IncMumbaiP005
1298.4New DelhiP001
15AppoloBangloreP003

Write the commands in SQL queries for the following :

(i) To display the details of Product whose Price is in the range of 40 and 120 (Both values included).

(ii) To display the ClientName, City from table Client and ProductName and Price from table Product, with their corresponding matching P_ID.

(iii) To increase the Price of all the Products by 20.

Answer

(i)

SELECT * 
FROM PRODUCT 
WHERE PRICE BETWEEN 40 AND 120;
Output
+------+-------------+--------------+-------+
| P_ID | ProductName | Manufacturer | Price |
+------+-------------+--------------+-------+
| P001 | MOISTURISER | XYZ          |    40 |
| P004 | SHAMPOO     | TAP          |    95 |
+------+-------------+--------------+-------+

(ii)

SELECT C.CLIENTNAME, C.CITY, P.PRODUCTNAME, P.PRICE 
FROM CLIENT C, PRODUCT P 
WHERE C.P_ID = P.P_ID;
Output
+---------------+-----------+---------------+-------+
| CLIENTNAME    | CITY      | PRODUCTNAME   | PRICE |
+---------------+-----------+---------------+-------+
| DREAMZ DISNEY | NEW DELHI | SANITIZER     |    35 |
| LIFE LINE INC | MUMBAI    | LENS SOLUTION |   350 |
| 98.4          | NEW DELHI | MOISTURISER   |    40 |
| APPOLO        | BANGALORE | BATH SOAP     |    25 |
+---------------+-----------+---------------+-------+

(iii)

UPDATE PRODUCT 
SET PRICE = PRICE + 20;