Write SQL Commands for the following queries based on the relations PRODUCT and CLIENT given below.

Table: Product
| P_ID | ProductName | Manufacturer | Price | ExpiryDate |
|---|---|---|---|---|
| TP01 | Talcum Powder | LAK | 40 | 2011-06-26 |
| FW05 | Face Wash | ABC | 45 | 2010-12-01 |
| BS01 | Bath Soap | ABC | 55 | 2010-09-10 |
| SH06 | Shampoo | XYZ | 120 | 2012-04-09 |
| FW12 | Face Wash | XYZ | 95 | 2010-08-15 |

Table: Client
| C_ID | ClientName | City | P_ID |
|---|---|---|---|
| 1 | Cosmetic Shop | Delhi | FW05 |
| 6 | Total Health | Mumbai | BS01 |
| 12 | Live Life | Delhi | SH06 |
| 15 | Pretty One | Delhi | FW05 |
| 16 | Dreams | Bengaluru | TP01 |
| 14 | Expressions | Delhi | NULL |

(i) To display the ClientName and City of all Mumbai- and Delhi-based clients in Client table.

(ii) Increase the price of all the products in Product table by 10%.

(iii) To display the ProductName, Manufacturer, ExpiryDate of all the products that expired on or before ‘2010-12-31’.

(iv) To display C_ID, ClientName, City of all the clients (including the ones that have not purchased a product) and their corresponding ProductName sold.

(v) To display productName, Manufacturer and ClientName of Mumbai City.

Here are the SQL commands for each specified query:
(i) To display the ClientName and City of all Mumbai- and Delhi-based clients in the Client table:
SELECT ClientName, City FROM Client WHERE City IN ('Mumbai', 'Delhi');
This query selects the ClientName and City fields from the Client table, filtering to show only those records where the City is either 'Mumbai' or 'Delhi'.
(ii) Increase the price of all the products in the Product table by 10%:
UPDATE Product SET Price = Price * 1.10;
This SQL command updates the Price of each product in the Product table by multiplying the current Price by 1.10, which effectively increases the price by 10%.
(iii) To display the ProductName, Manufacturer, and ExpiryDate of all the products that expired on or before ‘2010-12-31’:
SELECT ProductName, Manufacturer, ExpiryDate FROM Product WHERE ExpiryDate <= '2010-12-31';
This query retrieves the ProductName, Manufacturer, and ExpiryDate from the Product table for products whose ExpiryDate is on or before December 31, 2010.
(iv) To display C_ID, ClientName, and City of all the clients (including the ones that have not purchased a product) and their corresponding ProductName sold:
SELECT Client.C_ID, Client.ClientName, Client.City, Product.ProductName FROM Client LEFT JOIN Product ON Client.P_ID = Product.P_ID;
This query uses a LEFT JOIN to include all clients from the Client table and associates their corresponding ProductName from the Product table, including those entries where no matching product exists (indicated by NULL).
(v) To display ProductName, Manufacturer, and ClientName of clients from Mumbai:
SELECT Product.ProductName, Product.Manufacturer, Client.ClientName FROM Client JOIN Product ON Client.P_ID = Product.P_ID WHERE Client.City = 'Mumbai';
This query joins the Client and Product tables to fetch the ProductName, Manufacturer, and ClientName for all clients located in 'Mumbai'.
These SQL commands are crafted to extract specific information from the provided Product and Client tables in a relational database.

Answered by DanielJosephParker | 2025-07-06