GeeksforGeeks App
Open App
Browser
Continue

# 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

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:

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

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

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

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

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

My Personal Notes arrow_drop_up