27-09-2019 ASSIGNMENT-1

G. D. GOENKA INTERNATIONAL SCHOOL

CLASS XI-CS ASSIGNMENT

Do practical and write Answers in NOTEBOOK  

Write SQL commands for the (1) to (6) on the basis of the table LAB:

NO	Item Name	Cost per item	Quantity	Date of purchase	Warranty	Operational
1	Computer	60000	9	21/05/96	2	7
2	Printer	15000	3	21/05/97	4	2
3	Scanner	18000	1	29/08/08	3	1
4	camera	21000	2	13/06/96	1	2
5	Hub	8000	1	31/10/99	2	1
6	UPS	5000	5	21/05/96	1	4
7	Plotter	25000	2	11/1/2000	2	2

1.      To select the ITEMNAME purchased after 31/10/97

2.      To list the ITEMNAME which are within the warranty period till present date

3.      To list the ITEMNAME in ascending order of the date of purchase where quantity is more than 3.

4.      To display ITEMNAME, COSTPERTM, and QUANTITY whose WARRANTY is over.

5.      To count the number of items whose cost is more than 10,000.

6.      To insert a new record in Lab table with the following data: 8,”VCR”, 10,000, 2, {2/2/2000},1,2.

7.      Give the output of the following SQL commands on the basis of LAB relation:

a.      SELECT MIN (DISTINCT Quantity) FROM LAB;

b.      SELECT MIN (warranty)FROM LAB Quantity = 2;

c.      SELECT SUM(CostPerItem) FROM LAB WHERE Quantity>2;

d.      SELECT AVG (Cost per item) FROM LAB WHERE Date of purchase <{1/1/99};