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

Relational Database and SQL — Question 28

Back to all questions
28
Question

Question 21

Write SQL commands for (i) to (vi) and write output for (vii) 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

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

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

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

(iv) To show number of products from each company.

(v) To count the number of PRODUCTS which shall be out of warranty on 20-NOV-2020.

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

(vii) Give the output of the following statements:

(a) SELECT COUNT(DISTINCT COMPANY) FROM PRODUCTS;

(b) SELECT MAX(PRICE) FROM PRODUCTS WHERE WARRANTY <= 3;

Answer

(i)

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 |
+-------+-------+---------+-------+-------+-------------+----------+

(ii)

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

(iii)

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

(iv)

SELECT COMPANY, COUNT(*) AS ProductCount
FROM PRODUCTS
GROUP BY COMPANY;
Output
+---------+--------------+
| COMPANY | ProductCount |
+---------+--------------+
| BPL     |            1 |
| SONY    |            2 |
| LENOVO  |            1 |
| COMPAQ  |            1 |
+---------+--------------+

(v)

SELECT COUNT(*)
FROM PRODUCTS
WHERE DATE_ADD(MANUFACTURE, INTERVAL WARRANTY YEAR) <= '2020-11-20';
Output
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(vi)

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.

(vii)

(a)

Output
+-------------------------+
| COUNT(DISTINCT COMPANY) |
+-------------------------+
|                       4 |
+-------------------------+

(b)

Output
+------------+
| MAX(PRICE) |
+------------+
|      39000 |
+------------+