Basic SQL Commands
QUESTION: Consider the following table ITEM given below, write the commands in SQL for 1 – 10 and output for 11 – 20
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
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.
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;
12. Select max(Purchase_Qty) from item where cost<5000;
13. Select avg(Cost) from item where Purchase_Qty>25;
14. Select cost+200 as ‘Selling_Price’ from item where Item_Name=’Scanner’;
15. Select S_No,Item_Name,Purchase_Qty from ITEM where Purchase_Qty>=10;
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;
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;
S_No DOP 3 2010-08-04 6 2010-08-14
18. Select avg(Cost), Max(Purchase_Qty) from ITEM;
avg(Cost) Max(Purchase_Qty) 4925 30
19. Select Item_Name from ITEM where Item_Name like “%e%”;
Item_Name Mouse Printer Scanner
20. Select * from ITEM where length(Item_Name)=3;
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