SQL Subquery Exercises: Find the departments which sanction amount is larger than the average sanction amount of all the departments

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

Sample Solution:

SELECT *
  FROM emp_department
  WHERE dpt_allotment >
  (
    SELECT AVG(dpt_allotment)
    FROM emp_department
  );

Pictorial Presentation:

Output of the Query:

dpt_code	dpt_name	dpt_allotment
47			HR			240000

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those employees who work for the department where the department allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.
Next: From the following tables, write a SQL query to find the departments where more than two employees work. Return dpt_name.