Basic SQL Commands

QUESTION: Consider the following table ITEM given below, write the commands in SQL for 1 – 10 and output for 11 – 20

TABLE: ITEM

S_No	Item_Name	Company	Cost	Purchase_Qty	DOP
1	Monitor	ABC	6300	20	2010-05-30
2	CPU	CDF	8000	30	2010-07-23
3	Mouse	FGH	250	25	2010-08-04
4	UPS	HIJ	2100	10	2010-06-19
5	Printer	JKL	8400	4	2010-11-27
6	Scanner	JKL	4500	6	2010-08-14

Write the SQL commands for 1. to 10.

1. To display the name of the item and cost of those items which were purchased after ‘2010-08-05’.

Select Item_Name,Cost from ITEM where DOP>"2010-08-05";

2. To display information of those items whose purchase quantity is more than 10 arranged by Date of Purchase.

Select * from ITEM where Purchase_Qty>10 ORDER BY DOP;

3. To display the average cost price of the items of ‘JKL’ Company.

Select avg(cost) from ITEM where Company="JKL";

4. To display all the information of those items whose name starts with letter ‘M’.

Select * from ITEM where Item_Name like "M%";

5. To display maximum cost price of the items which contain letter ‘J’ in the company name.

Select max(Cost) from ITEM where Company like '%J%;

6. To display all details of items whose purchase quantity is more than 5 arranged in decreasing order of Date of Purchase.

Select * from ITEM where Purchase_Qty>5 order by DOP desc;

7. To display S_No, Item_Name and cost of all those items where Item_Name has the letter ‘o’ in second position.

Select S_No,Item_Name,Cost from ITEM where instr(Item_Name,'o')=2;

8. To display Company, minimum of Purchase_Qty grouped by the month of purchase.

Select Company,min(Purchase_Qty) from ITEM group by month(DOP);

9. To show the structure of the table ITEM.

Desc ITEM;

10. To set Purchase_Qty as 31 of ‘JKL’ company. 

Update ITEM set Purchase_Qty=31 where Company='JKL'; 

Write the Output for the following SQL queries 11 – 20.

11. Select count(*) from item where cost>6000 and Purchase_Qty<25;
OUTPUT:

count(*)

2

12. Select max(Purchase_Qty) from item where cost<5000;
OUTPUT:

max(Purchase_Qty)

25

13. Select avg(Cost) from item where Purchase_Qty>25;
OUTPUT:

avg(Cost)

8000

14. Select cost+200 as ‘Selling_Price’ from item where Item_Name=’Scanner’;
OUTPUT:

Selling_Price

4700

15. Select S_No,Item_Name,Purchase_Qty from ITEM where Purchase_Qty>=10;
OUTPUT:

S_No	Item_Name	Purchase_Qty
1	Monitor	20
2	CPU	30
3	Mouse	25
4	UPS	10

16. Select Ucase(Item_Name), Lcase(Company) from ITEM;
OUTPUT:

Ucase(Item_Name)	Lcase(Company)
MONITOR	abc
CPU	cdf
MOUSE	fgh
UPS	hij
PRINTER	jkl
SCANNER	jkl

17. Select S_No, DOP from ITEM where month(DOP)=8;
OUTPUT:

S_No	DOP
3	2010-08-04
6	2010-08-14

18. Select avg(Cost), Max(Purchase_Qty) from ITEM;
OUTPUT:

avg(Cost)	Max(Purchase_Qty)
4925	30

19. Select Item_Name from ITEM where Item_Name like “%e%”;
OUTPUT:

Item_Name

Mouse

Printer

Scanner

20. Select * from ITEM where length(Item_Name)=3;
OUTPUT:

S_No	Item_Name	Company	Cost	Purchase_Qty	DOP
2	CPU	CDF	8000	30	2010-07-23
4	UPS	HIJ	2100	10	2010-06-19