SQL Exercises, Practice, Solution - Aggregate Functions

SQL [25 exercises with solution]

1. Write a SQL statement to find the total purchase amount of all orders.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

2. Write a SQL statement to find the average purchase amount of all orders.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

3. Write a SQL statement to find the number of salesmen currently listing for all of their customers.   Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

4. Write a SQL statement know how many customer have listed their names.   Go to the editor

Sample table: customer

Click me to see the solution with pictorial presentation

5. Write a SQL statement find the number of customers who gets at least a gradation for his/her performance.   Go to the editor

Sample table: customer

Click me to see the solution with pictorial presentation

6. Write a SQL statement to get the maximum purchase amount of all the orders.   Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

7. Write a SQL statement to get the minimum purchase amount of all the orders.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

8. Write a SQL statement which selects the highest grade for each of the cities of the customers.   Go to the editor

Sample table: customer

Click me to see the solution with pictorial presentation

9. Write a SQL statement to find the highest purchase amount ordered by the each customer with their ID and highest purchase amount.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

10. Write a SQL statement to find the highest purchase amount ordered by the each customer on a particular date with their ID, order date and highest purchase amount.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

11. Write a SQL statement to find the highest purchase amount on a date '2012-08-17' for each salesman with their ID.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

12. Write a SQL statement to find the highest purchase amount with their ID and order date, for only those customers who have highest purchase amount in a day is more than 2000.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

13. Write a SQL statement to find the highest purchase amount with their ID and order date, for those customers who have a higher purchase amount in a day is within the range 2000 and 6000. Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

14. Write a SQL statement to find the highest purchase amount with their ID and order date, for only those customers who have a higher purchase amount in a day is within the list 2000, 3000, 5760 and 6000.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

15. Write a SQL statement to find the highest purchase amount with their ID, for only those customers whose ID is within the range 3002 and 3007.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

16. Write a SQL statement to display customer details (ID and purchase amount) whose IDs are within the range 3002 and 3007 and highest purchase amount is more than 1000.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

17. Write a SQL statement to find the highest purchase amount with their ID, for only those salesmen whose ID is within the range 5003 and 5008.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

18. Write a SQL statement that counts all orders for a date August 17th, 2012.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

19. Write a SQL statement that count the number of salesmen for whom a city is specified. Note that there may be spaces or no spaces in the city column if no city is specified.  Go to the editor

Sample table: salesman

Click me to see the solution with pictorial presentation

20. Write a query that counts the number of salesmen with their order date and ID registering orders for each day.  Go to the editor

Sample table: orders

Click me to see the solution with pictorial presentation

21. Write a SQL query to calculate the average price of all the products.  Go to the editor

Sample table: item_mast

Click me to see the solution with results

22. Write a SQL query to find the number of products with a price more than or equal to Rs.350.  Go to the editor

Sample table: item_mast

Click me to see the solution with results

23. Write a SQL query to display the average price of each company's products, along with their code.  Go to the editor

Sample table: item_mast

Click me to see the solution with results

24. Write a query in SQL to find the sum of the allotment amount of all departments.  Go to the editor

Sample table: emp_department

Click me to see the solution with results

25. Write a query in SQL to find the number of employees in each department along with the department code.  Go to the editor

Sample table: emp_details

Click me to see the solution with results

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.