Basic SQL Commands
Last Updated :
26 Apr, 2021
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:
12. Select max(Purchase_Qty) from item where cost<5000;
OUTPUT:
13. Select avg(Cost) from item where Purchase_Qty>25;
OUTPUT:
14. Select cost+200 as ‘Selling_Price’ from item where Item_Name=’Scanner’;
OUTPUT:
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 |
Share your thoughts in the comments
Please Login to comment...