w3resource

SQL Exercises, Practice, Solution - Retrieve data from tables

SQL [33 exercises with solution]

1. Write a SQL statement that displays all the information about all salespeople.   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 SQL query to display three numbers in three columns.   Go to the editor
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.   Go to the editor
Click me to see the solution with pictorial presentation

5. Write an SQL 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 such as names and commissions for all salespeople.    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, such as order date, salesman ID, order number, and purchase amount for all 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 identify 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 locate salespeople who live 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 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 orders that are delivered by a salesperson with 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) for 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’ for 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 locate 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 the field of ‘Physics’ since 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 and 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 display 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 retrieve the details of the winners whose first names match 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 that combines 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 of 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 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.    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 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.    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 a manufacturer code of 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 for 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 the 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 retrieve 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.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ