SQL Exercises: Employees working with the lowest sanction amount

SQL SUBQUERY: Exercise-39 with Solution

39. From the following tables write a SQL query to find the departments with the second lowest sanction amount. 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 'emp_department' where 'dpt_allotment' is equal to the minimum 'dpt_allotment' value returned by another subquery
  SELECT dpt_code
  FROM emp_department 
  WHERE dpt_allotment = (
    -- Subquery: Selecting the minimum 'dpt_allotment' value from 'emp_department' where 'dpt_allotment' is greater than the second minimum 'dpt_allotment' value returned by another subquery
    SELECT MIN(dpt_allotment)
    FROM emp_department 
    WHERE dpt_allotment > (
      -- Subquery: Selecting the second minimum 'dpt_allotment' value from 'emp_department'
      SELECT MIN(dpt_allotment) 
      FROM emp_department 
    )
  )
);

Output of the Query:

emp_fname	emp_lname
Alan		Snappy
George		Mardy

Explanation:

The said SQL query that retrieves the first name and last name of employees from the 'emp_details' table, where the employee's department code matches a department code from the 'emp_department' table that is part of a subquery.
The subquery finds the minimum value of the 'emp_department' table's "dpt_allotment" column, then finds the minimum value where it is greater than the previous minimum value.
Finally, the main query uses the IN operator to check if the department code of an employee is found in the 'emp_department' table, where the "dpt_allotment" value is equal to the second minimum value found in the subquery, and returns the first name and last name of the employees

Visual Explanation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find departments with more than two employees.
Next SQL Exercise: SQL SORTING and FILTERING on HR Database Exercises Home