SQL Exercises: Departments with a sanction amount higher than average

SQL SUBQUERY: Exercise-37 with Solution

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.

DPT_CODE DPT_NAME        DPT_ALLOTMENT
-------- --------------- -------------
      57 IT                      65000
      63 Finance                 15000
      47 HR                     240000
      27 RD                      55000
      89 QC                      75000

Sample Solution:

-- Selecting all columns from the 'emp_department' table
SELECT *
-- Specifying the table to retrieve data from ('emp_department')
FROM emp_department
-- Filtering the results based on the condition that 'dpt_allotment' is greater than the average 'dpt_allotment' returned by a subquery
WHERE dpt_allotment >
  -- Subquery: Selecting the average 'dpt_allotment' from the 'emp_department' table
  (
    SELECT AVG(dpt_allotment)
    FROM emp_department
  );

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.