SQL Exercises: Departments with a sanction amount higher than average
37. From the following tables write a SQL query to find the departments whose sanction amount is higher than the average sanction amount for all departments. Return dpt_code, dpt_name and dpt_allotment.
Sample table: emp_departmentDPT_CODE DPT_NAME DPT_ALLOTMENT -------- --------------- ------------- 57 IT 65000 63 Finance 15000 47 HR 240000 27 RD 55000 89 QC 75000
Sample Solution:
Output of the Query:
dpt_code dpt_name dpt_allotment 47 HR 240000
Explanation:
The above SQL query is selecting all columns and rows from the 'emp_department' table where the department's allotment is greater than the average allotment of all departments. A subquery in the WHERE clause is used to calculate the average allotment of all departments from the 'emp_department' table, and the outer query is used to filter rows with a larger dpt_allotment than the average.
Visual Explanation:
Practice Online
Sample Database:
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous SQL Exercise: Departments with allotment amounts exceeding Rs. 50000.
Next SQL Exercise: Find departments with more than two employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics