w3resource

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.

Sample table: emp_department
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:

SQL Subqueries Inventory Exercises: Find the departments which sanction amount is larger than the average sanction amount of all the departments.

Practice Online


Sample Database:

Model Database

Query Visualization:

Duration:

Query visualization of Find the departments which sanction amount is larger than the average sanction amount of all the departments - Duration

Rows:

Query visualization of Find the departments which sanction amount is larger than the average sanction amount of all the departments - Rows

Cost:

Query visualization of Find the departments which sanction amount is larger than the average sanction amount of all the departments - 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.



Follow us on Facebook and Twitter for latest update.