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

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

PRODUCTS TABLE

 
 
  
 
	 PCODE 
	 PNAME 
	 COMPANY 
	 PRICE 
	 STOCK 
	 MANUFACTURE 
	 WARRANTY 
 
 
	 PO01 
	 TV 
	 BPL 
	 10000 
	 200 
	 12-JAN-2018 
	 3 
 
 
	 POO2 
	 TV 
	 SONY 
	 12000 
	 150 
	 23-MAR-2017 
	 4 
 
 
	 PO03 
	 PC 
	 LENOVO 
	 39000 
	 100 
	 09-APR-2018 
	 2 
 
 
	 PO04 
	 PC 
	 COMPAQ 
	 38000 
	 120 
	 20-JUN-2019 
	 2 
 
 
	 PO05 
	 HANDYCAM 
	 SONY 
	 18000 
	 250 
	 23-MAR-2017 
	 3

PCODE	PNAME	COMPANY	PRICE	STOCK	MANUFACTURE	WARRANTY
PO01	TV	BPL	10000	200	12-JAN-2018	3
POO2	TV	SONY	12000	150	23-MAR-2017	4
PO03	PC	LENOVO	39000	100	09-APR-2018	2
PO04	PC	COMPAQ	38000	120	20-JUN-2019	2
PO05	HANDYCAM	SONY	18000	250	23-MAR-2017	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 PRODUCT;

(b) Select MAX(price) from PRODUCT where WARRANTY<=3;

Answer =

(i)
Select * from PRODUCT where STOCK > 110 ;

(ii)
Select COMPANY from PRODUCT where WARRANTY > 2 ;

(iii)
Select STOCK + PRICE as stock from PRODUCT where COMPANY = “BPL” ;

(iv)
Select COUNT(PNAME) from PRODUCT group by COMPANY ;

(v)
Select Count(PName) from PRODUCTS where WARRANTY * 10000000 + MANUFACTURE < “20200920” ;

(vi)
Select PNAMEfrom PRODUCTS where WARRANTY * 10000000 + MANUFACTURE >= “20200920” ;

(vii)

(a)

COUNT(distinct company)

BPL

SONY

LENOVO

COMPAQ

(b)

MAX(price)

250

Path Walla