SQL QUESTION 1

Consider the following tables PRODUCT and answer (b) and (c) part of this question
Relation :PRODUCT
PCODE PNAME  COMPANY    PRICE    STOCK    MANUFACTURE    WARRANTY
P001  TV      BPL       10000     200     12-JAN-2008         3
P002  TV      SONY      12000     150     23-MAR-2007         4
P003  PC      LENOVO    39000     100     09-APR-2008         2
P004  PC      COMPAQ    38000     120     20-JUN-2009         2
P005  HANDYCAM  SONY    18000     250     23-MAR-2007         3 


Write MySQL commands for following Statements
i.To show details of all the PC with stock more than 110.       
ii.To list the company which gives warranty for more than 2 years
iii.To find stock value of the BPL company where stock value is 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 are manufactured in 2009. 
vi.To show the PRODUCT name which are within warranty as on date.
vii) Show the products whose warranty is over
 
(c)Give the output of following MySQL statement.                  (i) Select COUNT (distinct company) from PRODUCT;
(ii) Select MAX (price) from PRODUCT where WARRANTY<=3;
(iii) select AVG (price) from PRODUCT where Company=”SONY”;
(iv) Select MIN (price) from PRODUCT where stock<200;


ANSWERS:

i)SELECT * FROM PRODUCT WHERE PNAME = ‘PC’ AND STOCK > 110;
ii)SELECT COMPANY FROM PRODUCT WHERE WARRANTY > 2;
iii) SELECT SUM(PRICE * STOCK) “STOCK VALUE” FROM PRODUCT
WHERE COMPANY = ‘BPL’;
iv)SELECT COMPANY, COUNT(DISTINCT PNAME) “PRODUCTS” FROM PRODUCT
GROUP BY COMPANY;
v)SELECT COUNT (*) FROM PRODUCT WHERE YEAR(MANUFACTURE ) = 2009;
vi)SELECT PNAME FROM PRODUCT WHERE YEAR(CURDATE())- YEAR(MANUFACTURE) <= WARRANTY; 
vii)SELECT PNAME FROM PRODUCT WHERE YEAR(CURDATE())- YEAR(MANUFACTURE) > WARRANTY;

output of following MySQL statement.                  
i. 4
ii.39000
iii.15000
iv.12000