Skip to content
Related Articles

Related Articles

Improve Article

Basic SQL Commands

  • Difficulty Level : Easy
  • 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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

S_NoItem_NameCompanyCostPurchase_QtyDOP
1MonitorABC6300202010-05-30
2CPUCDF8000302010-07-23
3MouseFGH250252010-08-04
4UPSHIJ2100102010-06-19
5PrinterJKL840042010-11-27
6ScannerJKL450062010-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_NoItem_NamePurchase_Qty
1Monitor20
2CPU30
3Mouse25
4UPS10

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

Ucase(Item_Name)Lcase(Company)
MONITORabc
CPUcdf
MOUSEfgh
UPShij
PRINTERjkl
SCANNERjkl

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

S_NoDOP
32010-08-04
62010-08-14

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

avg(Cost)Max(Purchase_Qty)
492530

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_NoItem_NameCompanyCostPurchase_QtyDOP
2CPUCDF8000302010-07-23
4UPSHIJ2100102010-06-19
My Personal Notes arrow_drop_up
Recommended Articles
Page :