# 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.**

## SQL: Tips of the Day

**What is the most efficient/elegant way to parse a flat table into a tree?**

WITH RECURSIVE MyTree AS ( SELECT * FROM MyTable WHERE ParentId IS NULL UNION ALL SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id ) SELECT * FROM MyTree;

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

**Weekly Trends**- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook