Write SQL commands for (i) to (vi) and write output for (vii) on the basis of PRODUCTS relation given below:

Table: PRODUCTS

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

(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;

(i)

SELECT *
FROM PRODUCTS
WHERE PNAME = 'PC' AND STOCK > 110;

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

+---------+
| COMPANY |
+---------+
| BPL     |
| SONY    |
+---------+

(iii)

SELECT COMPANY, SUM(PRICE * STOCK) AS StockValue 
FROM PRODUCTS 
WHERE COMPANY = 'BPL';

+---------+------------+
| COMPANY | StockValue |
+---------+------------+
| BPL     |    2000000 |
+---------+------------+

(iv)

SELECT COMPANY, COUNT(*) AS ProductCount
FROM PRODUCTS
GROUP BY COMPANY;

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

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

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

(b)

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