SQL Exercises: Departments with allotment amounts exceeding Rs. 50000

SQL SUBQUERY: Exercise-36 with Solution

36. From the following tables write a SQL query to find those employees who work for the department where the departmental allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.

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

 EMP_IDNO EMP_FNAME       EMP_LNAME         EMP_DEPT
--------- --------------- --------------- ----------
   127323 Michale         Robbin                  57
   526689 Carlos          Snares                  63
   843795 Enric           Dosio                   57
   328717 Jhon            Snares                  63
   444527 Joseph          Dosni                   47
   659831 Zanifer         Emily                   47
   847674 Kuleswar        Sitaraman               57
   748681 Henrey          Gabriel                 47
   555935 Alex            Manuel                  57
   539569 George          Mardy                   27
   733843 Mario           Saule                   63
   631548 Alan            Snappy                  27
   839139 Maria           Foster                  57

Sample Solution:

-- Selecting columns 'emp_fname' and 'emp_lname' from the 'emp_details' table
SELECT emp_fname, emp_lname 
-- Specifying the table to retrieve data from ('emp_details')
FROM emp_details
-- Filtering the results based on the condition that 'emp_dept' is in the set of 'dpt_code' values returned by a subquery
WHERE emp_dept IN
  -- Subquery: Selecting 'dpt_code' values from the 'emp_department' table where 'dpt_allotment' is greater than 50000
  (SELECT dpt_code 
     FROM emp_department 
       WHERE dpt_allotment > 50000);

Output of the Query:

emp_fname	emp_lname
Alan		Snappy
Maria		Foster
Michale		Robbin
Enric		Dosio
Joseph		Dosni
Zanifer		Emily
Kuleswar	Sitaraman
Henrey		Gabriel
Alex		Manuel
George		Mardy

Explanation:

The said query is selecting the first name and last name of employees from the 'emp_details' table, where the department number of the employee is present in the list of department codes that have an allotment greater than 50000. The subquery in the WHERE clause is selecting the department code from the 'emp_department' table where the dpt_allotment is greater than 50000.
The outer query then uses the IN operator to check if the department number of the employee is present in the list of department codes returned by the subquery, if true then the row is returned by the query.

Visual Explanation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: List all employees in departments 89 and 63.
Next SQL Exercise: Departments with a sanction amount higher than average.