SQL Subquery Exercises: Find the first name and last name of employees working for departments which sanction amount is second lowest

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.

Sample table: emp_department

Sample table: emp_details

Sample Solution:

SELECT emp_fname, emp_lname 
FROM emp_details 
WHERE emp_dept IN (
  SELECT dpt_code
  FROM emp_department 
  WHERE dpt_allotment= (
    SELECT MIN(dpt_allotment)
    FROM emp_department 
    WHERE dpt_allotment >
 (SELECT MIN(dpt_allotment) 
      FROM emp_department )));

Output of the Query:

emp_fname	emp_lname
Alan		Snappy
George		Mardy

Pictorial Presentation:

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 the departments where more than two employees work. Return dpt_name.
Next: SQL SORTING and FILTERING on HR Database Exercises Home