w3resource

SQL Exercises, Practice, Solution - Retrieve data from tables

SQL [33 exercises with solution]

1. Write a SQL statement to display all the information of all salesmen.   Go to the editor

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".   Go to the editor
Click me to see the solution with pictorial presentation

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

4. Write a query to display the sum of two numbers 10 and 15 from RDMS sever.   Go to the editor
Click me to see the solution with pictorial presentation

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

6. Write a SQL statement to display specific columns like name and commission for all the salesmen.    Go to the editor
Sample table: salesman


Click me to see the solution with pictorial presentation

7. Write a query to display the columns in a specific order like order date, salesman id, order number and purchase amount from for all the orders.    Go to the editor
Sample table: orders


Click me to see the solution with pictorial presentation

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

Sample table: orders


Click me to see the solution with pictorial presentation

9. From the following table, write a SQL query to find the salespeople who lives in the City of 'Paris'. Return salesperson's name, city.   Go to the editor

Sample table: salesman


Click me to see the solution with pictorial presentation

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

Sample table: customer


Click me to see the solution with pictorial presentation

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

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) in the year 1970. Return year, subject and winner.   Go to the editor

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' in the year 1970. Return winner.   Go to the editor

Sample table: nobel_win


Click me to see the solution with result

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

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 'Physics' since the year 1950. Return winner.   Go to the editor

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 to 1975. Begin and end values are included. Return year, subject, winner, and country   Go to the editor

Sample table: nobel_win


Click me to see the solution with result

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

Sample table: nobel_win


Click me to see the solution with result

18. From the following table, write a SQL query to find the details of the winners whose first name matches with the string 'Louis'. Return year, subject, winner, country, and category.    Go to the editor

Sample table: nobel_win


Click me to see the solution with result

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

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 Physiology and Economics. Return year, subject, winner, country, and category.   Go to the editor

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.   Go to the editor

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.    Go to the editor

Sample table: nobel_win


Click me to see the solution with result

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

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 result by subject, ascending except ‘Chemistry’ and ‘Economics’ which will come at the end of result set. Return year, subject, winner, country, and category.    Go to the editor

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.    Go to the editor

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 manufacturer code equal to 16. Return avg.    Go to the editor

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.'    Go to the editor

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.    Go to the editor

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 of each company. Return average price and company code.  Go to the editor

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.   Go to the editor

Sample table: item_mast


Click me to see the solution with result

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

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.   Go to the editor

Sample table: emp_details


Click me to see the solution with result

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

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.