Sample Question No. 6 for IP - Informatics Practices ( AISSCE 2014 ) _ A

Sample Question No. 6 for IP - Informatics Practices ( AISSCE 2014 ) _ A

[2+1+1+1+1+1+4]

Answer the following questions:

(a) Write the difference between COMMIT and ROLLBACK

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

(b) 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.

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