w3resource logo


SQL exercises

SQL Exercises, Practice, Solution - SUBQUERIES

Secondary Nav

SQL [46 exercises with solution]

1. Write a query to display the name ( first name and last name ) for those employees who gets more salary than the employee whose ID is 163.  Go to the editor

Sample table : employees

Click me to see the solution

2. Write a query to display the name ( first name and last name ), salary, department id, job id for those employees who works in the same designation as the employee works whose id is 169.  Go to the editor

Sample table : employees

Click me to see the solution

3. Write a query to display the name ( first name and last name ), salary, department id for those employees who earn such amount of salary which is the smallest salary of any of the departments.  Go to the editor

Sample table : employees

Click me to see the solution

4. Write a query to display the employee id, employee name (first name and last name ) for all employees who earn more than the average salary.  Go to the editor

Sample table : employees

Click me to see the solution

5. Write a query to display the employee name ( first name and last name ), employee id and salary of all employees who report to Payam.  Go to the editor

Sample table : employees

Click me to see the solution

6. Write a query to display the department number, name ( first name and last name ), job and department name for all employees in the Finance department.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

7. Write a query to display all the information of an employee whose salary and reporting person id is 3000 and 121 respectively.  Go to the editor

Sample table : employees

Click me to see the solution

8. Display all the information of an employee whose id is any of the number 134, 159 and 183.   Go to the editor

Sample table : employees

Click me to see the solution

9. Write a query to display all the information of the employees whose salary is within the range 1000 and 3000.  Go to the editor

Sample table : employees

Click me to see the solution

10. Write a query to display all the information of the employees whose salary is within the range of smallest salary and 2500.  Go to the editor

Sample table : employees

Click me to see the solution

11. Write a query to display all the information of the employees who does not work in those departments where some employees works whose id within the range 100 and 200.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

12. Write a query to display all the information for those employees whose id is any id who earn the second highest salary.  Go to the editor

Sample table : employees

Click me to see the solution

13. Write a query to display the employee name( first name and last name ) and hiredate for all employees in the same department as Clara. Exclude Clara.  Go to the editor

Sample table : employees

Click me to see the solution

14. Write a query to display the employee number and name( first name and last name ) for all employees who work in a department with any employee whose name contains a T.  Go to the editor

Sample table : employees

Click me to see the solution

15. Write a query to display the employee number, name( first name and last name ), and salary for all employees who earn more than the average salary and who work in a department with any employee with a J in their name.  Go to the editor

Sample table : employees

Click me to see the solution

16. Display the employee name( first name and last name ), employee id, and job title for all employees whose department location is Toronto.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

17. Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN.  Go to the editor

Sample table : employees

Click me to see the solution

18. Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN.  Go to the editor

Sample table : employees

Click me to see the solution

19. Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN. Exclude job title PU_MAN.  Go to the editor

Sample table : employees

Click me to see the solution

20. Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any average salary of any department.  Go to the editor

Sample table : employees

Click me to see the solution

21. Write a query to display the employee name( first name and last name ) and department for all employees for any existence of those employees whose salary is more than 3700.  Go to the editor

Sample table : employees

Click me to see the solution

22. Write a query to display the department id and the total salary for those departments which contains at least one salaried employee.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

23. Write a query to display the employee id, name ( first name and last name ) and the job id column with a modified title SALESMAN for those employees whose job title is ST_MAN and DEVELOPER for whose job title is IT_PROG.  Go to the editor

Sample table : employees

Click me to see the solution

24. Write a query to display the employee id, name ( first name and last name ), salary and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.  Go to the editor

Sample table : employees

Click me to see the solution

25. Write a query to display the employee id, name ( first name and last name ), SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.  Go to the editor

Sample table : employees

Click me to see the solution

26. Write a subquery that returns a set of rows to find all departments that do actually have one or more employees assigned to them.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

27. Write a query that will identify all employees who work in departments located in the United Kingdom.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

28. Write a query to identify all the employees who earn more than the average and who work in any of the IT departments.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

29. Write a query to determine who earns more than Mr. Ozer.  Go to the editor

Sample table : employees

Click me to see the solution

30. Write a query to find out which employees have a manager who works for a department based in the US.  Go to the editor

Sample table : employees

Sample table : departments

Sample table : locations

Click me to see the solution

31. Write a query which is looking for the names of all employees whose salary is greater than 50% of their department’s total salary bill.  Go to the editor

Sample table : employees

Click me to see the solution

32. Write a query to get the details of employees who are managers.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

33. Write a query to get the details of employees who manage a department.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

34. Write a query to display the employee id, name ( first name and last name ), salary, department name and city for all the employees who gets the salary as the salary earn by the employee which is maximum within the joining person January 1st, 2002 and December 31st, 2003.  Go to the editor

Sample table : employees

Sample table : departments

Sample table : locations

Click me to see the solution

35. Write a query in SQL to display the department code and name for all departments which located in the city London.  Go to the editor

Sample table : departments

Sample table : locations

Click me to see the solution

36. Write a query in SQL to display the first and last name, salary, and department ID for all those employees who earn more than the average salary and arrange the list in descending order on salary.  Go to the editor

Sample table : employees

Click me to see the solution

37. Write a query in SQL to display the first and last name, salary, and department ID for those employees who earn more than the maximum salary of a department which ID is 40.  Go to the editor

Sample table : employees

Click me to see the solution

38. Write a query in SQL to display the department name and Id for all departments where they located, that Id is equal to the Id for the location where department number 30 is located.  Go to the editor

Sample table : departments

Click me to see the solution

39. Write a query in SQL to display the first and last name, salary, and department ID for all those employees who work in that department where the employee works who hold the ID 201.  Go to the editor

Sample table : employees

Click me to see the solution

40. Write a query in SQL to display the first and last name, salary, and department ID for those employees whose salary is equal to the salary of the employee who works in that department which ID is 40.  Go to the editor

Sample table : employees

Click me to see the solution

41. Write a query in SQL to display the first and last name, and department code for all employees who work in the department Marketing.  Go to the editor

Sample table : employees

Sample table : departments

Click me to see the solution

42. Write a query in SQL to display the first and last name, salary, and department ID for those employees who earn more than the minimum salary of a department which ID is 40.  Go to the editor

Sample table : employees

Click me to see the solution

43. Write a query in SQL to display the full name,email, and designation for all those employees who was hired after the employee whose ID is 165.  Go to the editor

Sample table : employees

Click me to see the solution

44. Write a query in SQL to display the first and last name, salary, and department ID for those employees who earn less than the minimum salary of a department which ID is 70.  Go to the editor

Sample table : employees

Click me to see the solution

45. Write a query in SQL to display the first and last name, salary, and department ID for those employees who earn less than the average salary, and also work at the department where the employee Laura is working as a first name holder.  Go to the editor

Sample table : employees

Click me to see the solution

46. Write a query in SQL to display the first and last name, salary and department ID for those employees whose department is located in the city London.  Go to the editor

Sample table : employees

Click me to see the solution

Practice Online


Go to top



Join our Question Answer community to learn and share your programming knowledge.

Solve these problems:

Java: How to convert a string to an integer in Java?

C#: Loops in c#

SQL: JOIN using more than 5 tables