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

Review of Database Concepts & SQL — Question 29

Back to all questions
29
Question

Question 18

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 Products 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 sum of the price and stock of the products.

(d) To show products from products table.

(e) To display the details of those products whose Name either ends with 'Y' or 'O'.

Answer

(a)

SELECT *
FROM PRODUCTS
WHERE STOCK > 110;
Output
+-------+----------+---------+----------+-------+-------------+----------+
| PCODE | PNAME    | COMPANY | PRICE    | STOCK | MANUFACTURE | WARRANTY |
+-------+----------+---------+----------+-------+-------------+----------+
| P001  | TV       | BPL     | 10000.00 |   200 | 2018-01-12  |        3 |
| P002  | TV       | SONY    | 12000.00 |   150 | 2017-03-23  |        4 |
| P004  | PC       | COMPAQ  | 38000.00 |   120 | 2019-06-20  |        2 |
| P005  | HANDYCAM | SONY    | 18000.00 |   250 | 2017-03-23  |        3 |
+-------+----------+---------+----------+-------+-------------+----------+

(b)

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

(c)

SELECT COMPANY, (PRICE + STOCK) AS STOCK_VALUE 
FROM PRODUCTS WHERE COMPANY = 'BPL';
Output
+---------+-------------+
| COMPANY | STOCK_VALUE |
+---------+-------------+
| BPL     |       10200 |
+---------+-------------+

(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 * FROM PRODUCTS 
WHERE PNAME LIKE '%Y' OR PNAME LIKE '%O';