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

Q. Write SQL commands for (a) to (f) and write output for (g) on the basis of PRODUCTS relation given below:

Table:- PRODUCTS TABLE

PCODE
 
	 
PNAME
 
	 
COMPANY
 
	 
PRICE
 
	 
STOCK
 
	 
MANUFACTURE
 
	 
WARRANTY
 
 
 
	 
P001
 
	 
TV
 
	 
BPL
 
	 
10000
 
	 
200
 
	 
12-JAN-2018
 
	 
3
 
 
 
	 
PO02
 
	 
TV
 
	 
SONY
 
	 
12000
 
	 
150
 
	 
23-MAR-2017
 
	 
4
 
 
 
	 
P003
 
	 
PC
 
	 
LENOVO
 
	 
39000
 
	 
100
 
	 
09-APR-2018
 
	 
2
 
 
 
	 
P004
 
	 
PC
 
	 
COMPAQ
 
	 
38000
 
	 
120
 
	 
20-JUN-2019
 
	 
2
 
 
 
	 
P005
 
	 
HANDYCAM
 
	 
SONY
 
	 
18000
 
	 
250
 
	 
23-MAR-2017
 
	 
3
 
 

(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 number of products from each company.

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

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

(g) Give the output of the following statements:

(i) Select COUNT(distinct company) from PRODUCT.
(ii) Select MAX(price)from PRODUCT where WARRANTY<=3


Answer :-

a =  Select * from products where stock > 100 ;
b =  Select Company from products where warranty > 2 ;
c =  Select Sum ( price * stock ) as “Stock values” from products where  company = “BPL”;
d =  Select sum( stock ) from products group by company ;
e =  Select count (*) from product where ( warranty * 10000 ) + MANUFACTURE = 20201120 ;
f =  Select  Pname from product where  ( warranty * 10000 ) + MANUFACTURE = curdate() ;
g =  
i =  
COMPANY
BPL
SONY
LENOVO
COMPAQ

ii =
MAX(price)
39000