=> ALL GTU QUERY

    

BY: H!mmat Dobar!ya(mail:hmt.dobariya@gmail.com)


1. Find Out the names of all the clients.
SQL> select "Name" from Client_master;

2. Print the entire client_master table.
SQL> select * from Client_master;

3. Retrieve the list of names and the cities of all the clients.
SQL> select "Name","City" from Client_master;

4. List the various products available from product_master table.
SQL> select Description from Product_master;

5. Find the name of all clients having ‘a’ as the second letter in their names.
SQL>select Name from Client_master where Name like '_a%';

6. Find out the clients who stay in a city whose second letter is ‘a’.
SQL> select Name from Client_master where City like '_a%';

7. Find the list of all clients who stay in ‘Bombay’ or city ‘Delhi’ or city ‘Madras’.
SQL> select Name from Client_master where City like 'Bombay' OR City like 'Delhi' OR City like 'Madras';

8. List all clients who are located in Bombay.
SQL> select Name from Client_master where City like 'Bombay';

9. Print the list of clients whose bal_due are greater than value 10000.
SQL> select Name from Client_master where Bal_due>10000;


10. Print the information from sales_order table of orders placed in month of January.
SQL> select *from sales_order where To_char(s_order_date,'MON')='JAN';

11. Display the order information for client_no ‘c00001’ and ‘c00002’.
SQL> select * from client_master where client_no in('C00001','C00002');

12. Find the product with description as ‘1.44 Drive’ and ‘1.22 Drive’.
SQL> select * from product_master where description in('1.44 Drive','1.2 Drive');

13. Find the products whose selling price is greater than 2000 and less than or equal to 5000.
SQL> select description from product_master where sell_price>2000 and sell_price<=5000;

14. Find the products whose selling price is more than 1500 and also find the new selling price as original price * 15.
SQL> select description,sell_price*0.15 from product_master where sell_price>1500;

15. Rename the new column in the above query as new_price.
SQL> select description,sell_price*15 “new_price” from product_master where sell_price>1500;

16. Find the products whose cost price is less then 1500.
SQL> select description,cost_price from product_master where cost_price<1500;

17. List the products in sorted order of their description.
SQL> select description,cost_price from product_master order by description;

18. Calculate the square root of the product ‘540 HDD’ by difference between its price and 100.
SQL> select sqrt(sell_price) from product_master;

19. Divide the cost of product ‘540 HDD’ by difference between its price and 100.
SQL> select cost_price/(sell_price-100) "COST OF PRODUCT" from product_master where description='540 HDD';

20. List the names, city, and state of clients not in the state of ‘Maharashtra’.
SQL> select name,city,state from client_master where state!='Maharashtra';
21. List the product_no, description, sell_price of products whose description being with letter ‘M’.
SQL> select product_no,description,sell_price from product_master where description like 'M%';


22. List of all orders that were canceled in the month of March.
SQL> select *from sales_order where order_status='Canceled' and To_char(s_order_date,'Mon')='May';

23. Count the total number of orders.
SQL> select count(s_order_no) "COUNT" from sales_order

24. Calculate the average price of all the products.
SQL> select avg(sell_price) "AVERAGE" from product_master;

 25. Calculate the minimum price of products.
SQL> select min(sell_price) "MINIMUM" from product_master;

26. Determine the maximum and minimum product prices. Rename the title as max_price and min_price respectively.
SQL> select min(sell_price)"MIN_PRICE", max(sell_price)"MAX_PRICE" from product_master;

27. Count the numbers of products having price greater than or equal to 1500.
SQL> select count(sell_price) "COUNT" from product_master where sell_price>=1500 ;

28. Find all products whose qty_on_hand is less than recorder level.
 SQL> select description from product_master where qt_on_hand<recorder_lvl;