Bright Tutorials Bright Tutorials
CBSE Class 11 Informatics Practices Question 64 of 87

Structured Query Language (SQL) — Question 25

Back to all questions
25
Question

Question 19

Write SQL commands for (a) to (e) on the basis of PRODUCTS relation given below:

Table: PRODUCTS

PCODEPNAMECOMPANYPRICESTOCKMANUFACTUREWARRANTY
P001TVBPL100002002018-01-123
P002TVSONY120001502017-03-234
P003PCLENOVO390001002018-04-092
P004PCCOMPAQ380001202019-06-202
P005HANDYCAMSONY180002502017-03-233

(a) To show details of all PCs with stock more than 110.

(b) To list the company which gives warranty of more than 2 years.

(c) To find stock value of the BPL company where stock value is the sum of the products of price and stock.

(d) To show products from PRODUCTS table.

(e) To show the product name of the products which are within warranty as on date.

Answer

(a)

SELECT *
FROM PRODUCTS
WHERE PNAME = 'PC' AND STOCK > 110;
Output
+-------+-------+---------+-------+-------+-------------+----------+
| PCODE | PNAME | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+-------+---------+-------+-------+-------------+----------+
| P004  | PC    | COMPAQ  | 38000 |   120 | 2019-06-20  |        2 |
+-------+-------+---------+-------+-------+-------------+----------+

(b)

SELECT DISTINCT COMPANY
FROM PRODUCTS
WHERE WARRANTY > 2;
Output
+---------+
| COMPANY |
+---------+
| BPL     |
| SONY    |
+---------+

(c)

SELECT COMPANY, SUM(PRICE * STOCK) AS StockValue 
FROM PRODUCTS 
WHERE COMPANY = 'BPL';
Output
+---------+------------+
| COMPANY | StockValue |
+---------+------------+
| BPL     |    2000000 |
+---------+------------+

(d)

SELECT * FROM PRODUCTS;
Output
+-------+----------+---------+-------+-------+-------------+----------+
| PCODE | PNAME    | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+----------+---------+-------+-------+-------------+----------+
| P001  | TV       | BPL     | 10000 |   200 | 2018-01-12  |        3 |
| P002  | TV       | SONY    | 12000 |   150 | 2017-03-23  |        4 |
| P003  | PC       | LENOVO  | 39000 |   100 | 2018-04-09  |        2 |
| P004  | PC       | COMPAQ  | 38000 |   120 | 2019-06-20  |        2 |
| P005  | HANDYCAM | SONY    | 18000 |   250 | 2017-03-23  |        3 |
+-------+----------+---------+-------+-------+-------------+----------+

(e)

SELECT PNAME 
FROM PRODUCTS 
WHERE DATE_ADD(MANUFACTURE, INTERVAL WARRANTY YEAR) >= CURDATE();

There is no output produced because the warranty of all products has expired.