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. 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.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework