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:
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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 .....
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)
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)
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)
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 .....
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)
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)
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)
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
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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 .....
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
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)
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)
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)
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)
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 ....
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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
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)
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)
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)
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)
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)
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)
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)
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)
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)
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 ....
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)
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)
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)
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 ....
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)
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)
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)
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)
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)
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)
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 | ....
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)
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)
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)
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)
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:
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics