w3resource

SQL Subqueries - Exercises, Practice, Solution

SQL subqueries on employee Database [76 Exercise with Solution]

You may read our SQL Subqueries tutorial before solving the following exercises.

[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]

Structure of employee Database:

employee database structure

1. From the following table, write a SQL query to find the managers. Return complete information about the managers.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
(6 rows)

Click me to see the solution

2. From the following table, write a SQL query to compute the experience of all the managers. Return employee ID, employee name, job name, joining date, and experience.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | hire_date  |       Experience
--------+----------+-----------+------------+-------------------------
  68319 | KAYLING  | PRESIDENT | 1991-11-18 | 26 years 2 mons 17 days
  66928 | BLAZE    | MANAGER   | 1991-05-01 | 26 years 9 mons 4 days
  67832 | CLARE    | MANAGER   | 1991-06-09 | 26 years 7 mons 26 days
  65646 | JONAS    | MANAGER   | 1991-04-02 | 26 years 10 mons 3 days
  67858 | SCARLET  | ANALYST   | 1997-04-19 | 20 years 9 mons 16 days
  69062 | FRANK    | ANALYST   | 1991-12-03 | 26 years 2 mons 2 days
(6 rows)

Click me to see the solution

3. From the following table, write a SQL query to find those employees who work as 'MANAGERS' and 'ANALYST' and working in ‘SYDNEY’ or ‘PERTH’ with an experience more than 5 years without receiving the commission. Sort the result-set in ascending order by department location. Return employee ID, employee name, salary, and department name.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | salary  | dep_name
--------+----------+---------+-----------
  66928 | BLAZE    | 2750.00 | MARKETING
  67832 | CLARE    | 2550.00 | FINANCE
(2 rows)

Click me to see the solution

4. From the following tables, write a SQL query to find those employees work at SYDNEY or working in the FINANCE department with an annual salary above 28000, but the monthly salary should not be 3000 or 2800 and who do not work as a MANAGER and whose ID contain a digit of '3' or '7' in 3rd position. Sort the result-set in ascending order by department ID and descending order by job name. Return employee ID, employee name, salary, department name, department location, department ID, and job name.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | salary  | dep_name | dep_location | dep_id | job_name
--------+----------+---------+----------+--------------+--------+-----------
  68319 | KAYLING  | 6000.00 | FINANCE  | SYDNEY       |   1001 | PRESIDENT
(1 row)

Click me to see the solution

5. From the following table, write a SQL query to find the employees of grade 2 and 3.Return all the information of employees and salary details.

Sample table: employees


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | grade | min_sal | max_sal
--------+----------+----------+------------+------------+---------+------------+--------+-------+---------+---------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |     3 |    1501 |    2100
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 |     2 |    1301 |    1500
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001 |     2 |    1301 |    1500
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001 |     3 |    1501 |    2100
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001 |     2 |    1301 |    1500
(5 rows)

Click me to see the solution

6. From the following table, write a SQL query to find those employees of grade 4 or 5 and who work as ANALYST or MANAGER. Return complete information about the employees.

Sample table: employees


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | grade | min_sal | max_sal
--------+----------+----------+------------+------------+---------+------------+--------+-------+---------+---------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001 |     4 |    2101 |    3100
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001 |     4 |    2101 |    3100
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |     4 |    2101 |    3100
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001 |     4 |    2101 |    3100
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001 |     4 |    2101 |    3100
(5 rows)

Click me to see the solution

7. From the following table, write a SQL query to find those employees whose salary is more than the salary of JONAS. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
(3 rows)

Click me to see the solution

8. From the following table, write a SQL query to find those employees who work as same designation of FRANK. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
(2 rows)

Click me to see the solution

9. From the following table, write a SQL query to find those employees who are senior to ADELYN. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary | commission | dep_id
--------+----------+----------+------------+------------+--------+------------+--------
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 | 900.00 |            |   2001
(1 row)

Click me to see the solution

10. From the following table, write a SQL query to find those employees of department ID 2001 and whose designation is same as of the designation of department ID 1001. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | dep_id | dep_name | dep_location
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+--------------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
(3 rows)

Click me to see the solution

11. From the following table, write a SQL query to find those employees whose salary is the same as the salary of FRANK or SANDRINE. Sort the result-set in descending order by salary. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
(1 row)

Click me to see the solution

12. From the following table, write a SQL query to find those employees whose designation are the same as the designation of MARKER or salary is more than the salary of ADELYN. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  .....
  

Click me to see the solution

13. From the following table, write a SQL query to find those employees whose salary is more than the total remuneration (salary + commission) of the designation SALESMAN. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
(4 rows)

Click me to see the solution

14. From the following table, write a SQL query to find those employees who are senior to BLAZE and working at PERTH or BRISBANE. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | dep_id | dep_name  | dep_location
--------+----------+----------+------------+------------+---------+------------+--------+--------+-----------+--------------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |   3001 | MARKETING | PERTH
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 |   3001 | MARKETING | PERTH
(2 rows)

Click me to see the solution

15. From the following tables, write a SQL query to find those employees of grade 3 and 4 and work in the department of FINANCE or AUDIT and whose salary is more than the salary of ADELYN and experience is more than FRANK. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67832	| CLARE	   | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
(2 rows)

Click me to see the solution

16. From the following table, write a SQL query to find those employees whose designation is same as the designation of SANDRINE or ADELYN. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  .....
  

Click me to see the solution

17. From the following table, write a SQL query to list any job of department ID 1001 which are not found in department ID 2001. Return job name.

Sample table: employees


Sample Output:

 job_name
-----------
 PRESIDENT
(1 row)

Click me to see the solution

18. From the following table, write a SQL query to find the highest paid employee. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
(1 row)

Click me to see the solution

19. From the following table, write a SQL query to find the highest paid employees in the department MARKETING. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
(1 row)

Click me to see the solution

20. From the following tables, write a SQL query to find the employees of grade 3 who joined recently and location at PERTH. Return employee ID, employee name, job name, hire date, and salary.

Sample table: employees


Sample table: department


Sample table: salary_grade


Click me to see the solution

21. From the following table, write a SQL query to find those employees who are senior to recently hired employee and work under KAYLING. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
(5 rows)

Click me to see the solution

22. From the following tables, write a SQL query to find those employees of grade 3 to 5 and location at SYDNEY. The employees are not in PRESIDENT designated and salary is more than the highest paid employee of PERTH where no MANAGER and SALESMAN are working under KAYLING. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
(1 row)

Click me to see the solution

23. From the following table, write a SQL query to find those employees who are senior employees as of year 1991. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
(1 row)

Click me to see the solution

24. From the following table, write a SQL query to find those employees who joined in 1991 in a designation same as the most senior person of the year 1991. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(4 rows)

Click me to see the solution

25. From the following table, write a SQL query to find the most senior employee of grade 4 or 5, work under KAYLING. Return complete information about the employees.

Sample table: employees


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(1 row)

Click me to see the solution

26. From the following table, write a SQL query to compute the total salary of the designation MANAGER. Return total salary.

Sample table: employees


Sample Output:

   sum
---------
 8257.00
(1 row)

Click me to see the solution

27. From the following table, write a SQL query to compute the total salary of employees of grade 3. Return total salary.

Sample table: employees


Sample table: salary_grade


Sample Output:

   sum
---------
 3300.00
(1 row)

Click me to see the solution

28. From the following table, write a SQL query to find those employees of department 1001 and whose salary is more than the average salary of employees in department 2001. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
(2 rows)

Click me to see the solution

29. From the following table, write a SQL query to find those departments where maximum number of employees work. Return department ID, department name, location and number of employees.

Sample table: employees


Sample table: department


Sample Output:

 dep_id | dep_name  | dep_location | count
--------+-----------+--------------+-------
   3001 | MARKETING | PERTH        |     6
(1 row)

Click me to see the solution

30. From the following table, write a SQL query to find those employees whose manager is JONAS. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
(2 rows)

Click me to see the solution

31. From the following table, write a SQL query to find those employees who are not working in the department MARKETING. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  .....
  

Click me to see the solution

32. From the following table, write a SQL query to find those employees who are working as a manager. Return employee name, job name, department name, and location.

Sample table: employees


Sample table: department


Click me to see the solution

33. From the following table, write a SQL query to find those employees who receive the highest salary of each department. Return employee name and department ID.

Sample table: employees


Sample Output:

 emp_name | dep_id
----------+--------
 KAYLING  |   1001
 BLAZE    |   3001
 SCARLET  |   2001
 FRANK    |   2001
(4 rows)

Click me to see the solution

34. From the following table, write a SQL query to find those employees whose salary is equal or more to the average of maximum and minimum salary. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
(1 row)

Click me to see the solution

35. From the following table, write a SQL query to find those managers whose salary is more than the average salary of his employees. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
(5 rows)

Click me to see the solution

36. From the following table, write a SQL query to find those employees whose salary is less than the salary of his manager but more than the salary of any other manager. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission  | dep_id
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+-----------+------------+------------+---------+-------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |		  |   1001
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |             |   1001
(2 rows)

Click me to see the solution

37. From the following table, write a SQL query to compute department wise average salary of employees. Return employee name, average salary, department ID as "Current Salary".

Sample table: employees


Sample Output:

 emp_name |        avgsal         | Current Salary
----------+-----------------------+----------------
 KAYLING  | 3316.6666666666666667 |           1001
 BLAZE    | 1633.3333333333333333 |           3001
 CLARE    | 3316.6666666666666667 |           1001
 JONAS    | 2251.4000000000000000 |           2001
 ....
 

Click me to see the solution

38. From the following table, write a SQL query to find five lowest paid workers. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(5 rows)

Click me to see the solution

39. From the following table, write a SQL query to find those managers who are not working under the PRESIDENT. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
(2 rows)

Click me to see the solution

40. From the following table, write a SQL query to find those departments where the number of employees is equal to the number of characters in the department name. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 dep_id | dep_name | dep_location
--------+----------+--------------
   2001 | AUDIT    | MELBOURNE
(1 row)

Click me to see the solution

41. From the following tables, write a SQL query to find those departments where the highest number of employees works. Return department name.

Sample table: employees


Sample table: department


Sample Output:

 dep_name
-----------
 MARKETING
(1 row)

Click me to see the solution

42. From the following table, write a SQL query to find those employees who joined in the company on the same date. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(2 rows)

Click me to see the solution

43. From the following table, write a SQL query to find those departments where more than average number of employees works. Return department name.

Sample table: employees


Sample table: department


Sample Output:

 dep_name
-----------
 MARKETING
 AUDIT
(2 rows)

Click me to see the solution

44. From the following table, write a SQL query to find those managers who handle maximum number of employees. Return managers name, number of employees.

Sample table: employees


Sample Output:

 emp_name | count
----------+-------
 BLAZE    |     5
(1 row)

Click me to see the solution

45. From the following table, write a SQL query to find those managers who receive less salary then the employees work under them. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(1 row)

Click me to see the solution

46. From the following table, write a SQL query to find those employees who are sub-ordinates of BLAZE. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(5 rows)

Click me to see the solution

47. From the following table, write a SQL query to find those employees who work as managers. Return complete information about the employees. Use co-related subquery.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
(6 rows)

Click me to see the solution

48. From the following table, write a SQL query to list the name of the employees for their manager JONAS and the name of the manager of JONAS.

Sample table: employees


Sample Output:

 emp_name | Manager for employees | manager for JONAS
----------+-----------------------+---------------------
 FRANK    | JONAS                 | KAYLING
 SCARLET  | JONAS                 | KAYLING
(2 rows)

Click me to see the solution

49. From the following table, write a SQL query to find those employees who receive minimum salary for a designation. Sort the result-set in ascending order by salary. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
(7 rows)

Click me to see the solution

50. From the following table, write a SQL query to find those employees who receive maximum salary for a designation. Sort the result-set in descending order by salary. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
(6 rows)

Click me to see the solution

51. From the following table, write a SQL query to find recently hired employees of every department. Sort the result-set in descending order by hire date. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001

Click me to see the solution

52. From the following table, write a SQL query to find those employees who receive a salary higher than the average salary of their department. Sort the result-set in ascending order by department ID. Return employee name, salary, and department ID.

Sample table: employees


Sample Output:

 emp_name | salary  | dep_id
----------+---------+--------
 KAYLING  | 6000.00 |   1001
 JONAS    | 2957.00 |   2001
 SCARLET  | 3100.00 |   2001
 FRANK    | 3100.00 |   2001
 BLAZE    | 2750.00 |   3001
 ADELYN   | 1700.00 |   3001
(6 rows)

Click me to see the solution

53. From the following table, write a SQL query to find those employees who earn a commission and receive maximum salary. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
(1 row)

Click me to see the solution

54. From the following table, write a SQL query to find those employees who do not work in the department 1001 but work in the same designation and salary as the employees in department 3001. Return employee name, job name and salary.

Sample table: employees


Sample Output:

 emp_name | job_name | salary
----------+----------+---------
 BLAZE    | MANAGER  | 2750.00
 ADELYN   | SALESMAN | 1700.00
 WADE     | SALESMAN | 1350.00
 MADDEN   | SALESMAN | 1350.00
 TUCKER   | SALESMAN | 1600.00
 JULIUS   | CLERK    | 1050.00
(6 rows)

Click me to see the solution

55. From the following table, write a SQL query to find those employees who get a commission percent and works as a SALESMAN and earn maximum net salary. Return department ID, name, designation, salary, and net salary (salary+ commission).

Sample table: employees


Sample Output:

 dep_id | emp_name | job_name | salary  | Net Salary
--------+----------+----------+---------+------------
   3001 | MADDEN   | SALESMAN | 1350.00 |    2850.00
(1 row)

Click me to see the solution

56. From the following table, write a SQL query to find those employees who gets a commission and earn the second highest net salary (salary + commission). Return department id, employee name, designation, salary, and net salary.

Sample table: employees


Sample Output:

 dep_id | emp_name | salary  | job_name | Net Salary
--------+----------+---------+----------+------------
   3001 | ADELYN   | 1700.00 | SALESMAN |    2100.00
(1 row)

Click me to see the solution

57. From the following table, write a SQL query to find those departments where the average salary is less than the averages for all departments. Return department ID, average salary.

Sample table: employees


Sample Output:

 dep_id |          avg
--------+-----------------------
   3001 | 1633.3333333333333333
(1 row)

Click me to see the solution

58. From the following tables, write a SQL query to find the unique department of the employees. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 dep_id | dep_name  | dep_location
--------+-----------+--------------
   1001 | FINANCE   | SYDNEY
   2001 | AUDIT     | MELBOURNE
   3001 | MARKETING | PERTH
(3 rows)

Click me to see the solution

59. From the following tables, write a SQL query to list the details of the employees working at PERTH.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(6 rows)

Click me to see the solution

60. From the following tables, write a SQL query to list the employees of grade 2 or 3 and the department where he or she works, is located in the city PERTH. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(4 rows)

Click me to see the solution

61. From the following table, write a SQL query to find those employees whose designation is same as the designation of ADELYN or the salary is more than the salary of WADE. Return complete information about the employees.

Sample table: employees


Sample Output:

emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id 
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  ....
  

Click me to see the solution

62. From the following table, write a SQL query to find those employees of department 1001 whose salary is more than the salary of ADELYN. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
(2 rows)

Click me to see the solution

63. From the following table, write a SQL query to find those managers who are senior to KAYLING and who are junior to SANDRINE. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
(1 row)

Click me to see the solution

64. From the following tables, write a SQL query to find those employees who work in the department where KAYLING works. Return employee ID, employee name, department location, salary department name.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | dep_location | salary  | dep_name
--------+----------+--------------+---------+----------
  67832 | CLARE    | SYDNEY       | 2550.00 | FINANCE
  69324 | MARKER   | SYDNEY       | 1400.00 | FINANCE
(2 rows)

Click me to see the solution

65. From the following tables, write a SQL query to find those employees whose salary grade is greater than the grade of MARKER. Return complete information about the employees.

Sample table: employees


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id | grade | min_sal | max_sal
--------+----------+-----------+------------+------------+---------+------------+--------+-------+---------+---------
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |     3 |    1501 |    2100
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001 |     3 |    1501 |    2100
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001 |     4 |    2101 |    3100
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001 |     4 |    2101 |    3100
  ....
  

Click me to see the solution

66. From the following tables, write a SQL query to find those employees whose grade same as the grade of TUCKER or experience is more than SANDRINE and who are belonging to SYDNEY or PERTH. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | dep_id | dep_name  | dep_location | grade | min_sal | max_sal
--------+----------+----------+------------+------------+---------+------------+--------+--------+-----------+--------------+-------+---------+---------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |   3001 | MARKETING | PERTH        |     3 |    1501 |    2100
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001 |   3001 | MARKETING | PERTH        |     3 |    1501 |    2100
(2 rows)

Click me to see the solution

67. From the following tables, write a SQL query to find those employees whose salary is same as any one of the employee. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
(4 rows)

Click me to see the solution

68. From the following tables, write a SQL query to find compute the total remuneration (salary + commission) of all sales person of MARKETING department. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(4 rows)

Click me to see the solution

69. From the following table, write a SQL query to find the recently hired employees of department 3001. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(1 rows)

Click me to see the solution

70. From the following tables, write a SQL query to find the highest paid employees of PERTH who joined before recently hired employee of grade 2. Return complete information about the employees.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
(1 row)

Click me to see the solution

71. From the following table, write a SQL query to find the highest paid employees work under KAYLING. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(1 row)

Click me to see the solution

72. From the following table, write a SQL query to find those employees whose net pay are higher than or equal to the salary of any other employee in the company. Return employee name, salary, and commission.

Sample table: employees


Sample Output:

 emp_name | salary  | commission
----------+---------+------------
 KAYLING  | 6000.00 |
 BLAZE    | 2750.00 |
 CLARE    | 2550.00 |
 JONAS    | 2957.00 |
....

Click me to see the solution

73. From the following table, write a SQL query to find those employees whose salaries are greater than the salaries of their managers. Return complete information about the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission| dep_id
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+----------+------------+------------+---------+-----------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001 |  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |		|   2001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001 |  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |		|   2001
(2 rows)

Click me to see the solution

74. From the following table, write a SQL query to find the maximum average salary drawn for each job except for PRESIDENT.

Sample table: employees


Sample Output:

          max
-----------------------
 3100.0000000000000000
(1 row)

Click me to see the solution

75. From the following table, write a SQL query to count the number of employees who work as a manager. Return number of employees.

Sample table: employees


Sample Output:

 count
-------
     6
(1 row)

Click me to see the solution

76. From the following table, write a SQL query to find those departments where no employee works. Return department ID.

Sample table: employees


Sample table: department


Sample Output:

 dep_id | count
--------+-------
   4001 |     0
(1 row)

Click me to see the solution

Keep Learning: SQL Subqueries, SQL Single Row Subqueries, SQL Multiple Row and Column Subqueries, SQL Correlated Subqueries, SQL Nested subqueries.

Practice Online


More to Come!

Structure of employee Database:

employee database structure

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/employee-database-exercise/subqueries-exercises-on-employee-database.php