SQL Exercises, Practice, Solution - Retrieve data from tables

SQL [33 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]

1. Write a SQL statement that displays all the information about all salespeople.

Sample table: salesman

Click me to see the solution with pictorial presentation

2. Write a SQL statement to display a string "This is SQL Exercise, Practice and Solution". 
Click me to see the solution with pictorial presentation

3. Write a SQL query to display three numbers in three columns. 
Click me to see the solution with pictorial presentation

4. Write a SQL query to display the sum of two numbers 10 and 15 from the RDBMS server. 
Click me to see the solution with pictorial presentation

5. Write an SQL query to display the result of an arithmetic expression. 
Click me to see the solution with pictorial presentation

6. Write a SQL statement to display specific columns such as names and commissions for all salespeople.  
Sample table: salesman

Click me to see the solution with pictorial presentation

7. Write a query to display the columns in a specific order, such as order date, salesman ID, order number, and purchase amount for all orders.  
Sample table: orders

Click me to see the solution with pictorial presentation

8. From the following table, write a SQL query to identify the unique salespeople ID. Return salesman_id. 

Sample table: orders

Click me to see the solution with pictorial presentation

9. From the following table, write a SQL query to locate salespeople who live in the city of 'Paris'. Return salesperson's name, city. 

Sample table: salesman

Click me to see the solution with pictorial presentation

10. From the following table, write a SQL query to find customers whose grade is 200. Return customer_id, cust_name, city, grade, salesman_id.  

Sample table: customer

Click me to see the solution with pictorial presentation

11. From the following table, write a SQL query to find orders that are delivered by a salesperson with ID. 5001. Return ord_no, ord_date, purch_amt.  

Sample table: orders

Click me to see the solution with result

12. From the following table, write a SQL query to find the Nobel Prize winner(s) for the year 1970. Return year, subject and winner. 

Sample table: nobel_win

Click me to see the solution with result

13. From the following table, write a SQL query to find the Nobel Prize winner in ‘Literature’ for 1971. Return winner. 

Sample table: nobel_win

Click me to see the solution with result

14. From the following table, write a SQL query to locate the Nobel Prize winner ‘Dennis Gabor'. Return year, subject. 

Sample table: nobel_win

Click me to see the solution with result

15. From the following table, write a SQL query to find the Nobel Prize winners in the field of ‘Physics’ since 1950. Return winner. 

Sample table: nobel_win

Click me to see the solution with result

16. From the following table, write a SQL query to find the Nobel Prize winners in ‘Chemistry’ between the years 1965 and 1975. Begin and end values are included. Return year, subject, winner, and country.  

Sample table: nobel_win

Click me to see the solution with result

17. Write a SQL query to display all details of the Prime Ministerial winners after 1972 of Menachem Begin and Yitzhak Rabin.  

Sample table: nobel_win

Click me to see the solution with result

18. From the following table, write a SQL query to retrieve the details of the winners whose first names match with the string ‘Louis’. Return year, subject, winner, country, and category.  

Sample table: nobel_win

Click me to see the solution with result

19. From the following table, write a SQL query that combines the winners in Physics, 1970 and in Economics, 1971. Return year, subject, winner, country, and category. 

Sample table: nobel_win

Click me to see the solution with result

20. From the following table, write a SQL query to find the Nobel Prize winners in 1970 excluding the subjects of Physiology and Economics. Return year, subject, winner, country, and category. 

Sample table: nobel_win

Click me to see the solution with result

21. From the following table, write a SQL query to combine the winners in 'Physiology' before 1971 and winners in 'Peace' on or after 1974. Return year, subject, winner, country, and category. 

Sample table: nobel_win

Click me to see the solution with result

22. From the following table, write a SQL query to find the details of the Nobel Prize winner 'Johannes Georg Bednorz'. Return year, subject, winner, country, and category.  

Sample table: nobel_win

Click me to see the solution with result

23. From the following table, write a SQL query to find Nobel Prize winners for the subject that does not begin with the letter 'P'. Return year, subject, winner, country, and category. Order the result by year, descending and winner in ascending.  

Sample table: nobel_win

Click me to see the solution with result

24. From the following table, write a SQL query to find the details of 1970 Nobel Prize winners. Order the results by subject, ascending except for 'Chemistry' and ‘Economics’ which will come at the end of the result set. Return year, subject, winner, country, and category.  

Sample table: nobel_win

Click me to see the solution with result

25. From the following table, write a SQL query to select a range of products whose price is in the range Rs.200 to Rs.600. Begin and end values are included. Return pro_id, pro_name, pro_price, and pro_com.  

Sample table: item_mast

Click me to see the solution with result

26. From the following table, write a SQL query to calculate the average price for a manufacturer code of 16. Return avg.  

Sample table: item_mast

Click me to see the solution with result

27. From the following table, write a SQL query to display the pro_name as 'Item Name' and pro_priceas 'Price in Rs.'  

Sample table: item_mast

Click me to see the solution with result

28. From the following table, write a SQL query to find the items whose prices are higher than or equal to $250. Order the result by product price in descending, then product name in ascending. Return pro_name and pro_price.  

Sample table: item_mast

Click me to see the solution with result

29. From the following table, write a SQL query to calculate average price of the items for each company. Return average price and company code. 

Sample table: item_mast

Click me to see the solution with result

30. From the following table, write a SQL query to find the cheapest item(s). Return pro_name and, pro_price.  

Sample table: item_mast

Click me to see the solution with result

31. From the following table, write a SQL query to find the unique last name of all employees. Return emp_lname.  

Sample table: emp_details

Click me to see the solution with result

32. From the following table, write a SQL query to find the details of employees whose last name is 'Snares'. Return emp_idno, emp_fname, emp_lname, and emp_dept.  

Sample table: emp_details

Click me to see the solution with result

33. From the following table, write a SQL query to retrieve the details of the employees who work in the department 57. Return emp_idno, emp_fname, emp_lname and emp_dept..  

Sample table: emp_details

Click me to see the solution with result

Practice Online

More to Come !

Query visualizations are generated using Postgres Explain Visualizer (pev).

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.