w3resource

SQL Exercises, Practice, Solution - SORTING and FILTERING on HR Database

SQL [38 exercises with solution]

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

1. From the following table, write a SQL query to find those employees whose salary is less than 6000. Return full name (first and last name), and salary.  Go to the editor

Sample table: employees


Sample Output:

     full_name     | salary
-------------------+---------
 David Austin      | 4800.00
 Valli Pataballa   | 4800.00
 Diana Lorentz     | 4200.00
 Alexander Khoo    | 3100.00
.....
(50 rows)

Click me to see the solution

2. From the following table, write a SQL query to find those employees whose salary is higher than 8000. Return first name, last name and department number and salary.  Go to the editor

Sample table: employees


Sample Output:

 first_name | last_name  | department_id |  salary
------------+------------+---------------+----------
 Steven     | King       |            90 | 24000.00
 Neena      | Kochhar    |            90 | 17000.00
 Lex        | De Haan    |            90 | 17000.00
 Alexander  | Hunold     |            60 |  9000.00
.....
(33 rows)

Click me to see the solution

3. From the following table, write a SQL query to find those employees whose last name is "McEwen". Return first name, last name and department ID.  Go to the editor

Sample table: employees


Sample Output:

 first_name | last_name | department_id
------------+-----------+---------------
 Allan      | McEwen    |            80
(1 row)

Click me to see the solution

4. From the following table, write a SQL query to find those employees who have no department number. Return employee_id, first_name, last_name, email,phone_number,hire_date, job_id, salary,commission_pct,manager_id and department_id.  Go to the editor

Sample table: employees


Sample Output:

employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+-------+--------------+-----------+--------+--------+----------------+------------+-------------
(0 rows)

Click me to see the solution

5. From the following table, write a SQL query to find the details of 'Marketing' department. Return all fields.  Go to the editor

Sample table: departments


Sample Output:

 department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
            20 | Marketing       |        201 |        1800
(1 row)

Click me to see the solution

6. From the following table, write a SQL query to find those employees whose first name does not contain the letter ‘M’. Sort the result-set in ascending order by department ID. Return full name (first and last name together), hire_date, salary and department_id.  Go to the editor

Sample table: employees


Sample Output:

     full_name     | hire_date  |  salary  | department_id
-------------------+------------+----------+---------------
 Kimberely Grant   | 2007-05-24 |  7000.00 |             0
 Jennifer Whalen   | 2003-09-17 |  4400.00 |            10
 Pat Fay           | 2005-08-17 |  6000.00 |            20
 Guy Himuro        | 2006-11-15 |  2600.00 |            30
.....
(100 rows)

Click me to see the solution

7. From the following table, write a SQL query to find those employees whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. These employees have joined before ‘1987-06-05’ and not included in the department number 40, 120 and 70. Return all fields. Go to the editor

Sample table: employees


Sample Output:

 employee_id | first_name  | last_name  |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+-------------+------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
         103 | Alexander   | Hunold     | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60
         108 | Nancy       | Greenberg  | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12000.00 |           0.00 |        101 |           100
         109 | Daniel      | Faviet     | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100
         110 | John        | Chen       | JCHEN    | 515.124.4269       | 2005-09-28 | FI_ACCOUNT |  8200.00 |           0.00 |        108 |           100
.....
(30 rows)                                                                               

Click me to see the solution

8. From the following table, write a SQL query to find those employees who do not earn any commission.Return full name (first and last name), and salary.  Go to the editor

Sample table: employees


Sample Output:

 full_name | salary
-----------+--------
(0 rows)

Click me to see the solution

9. From the following table, write a SQL query to find those employees whose salary is in the range 9000,17000 (Begin and end values are included). Return full name, contact details and salary.  Go to the editor

Sample table: employees


Sample Output:

     full_name     |        contact_details        | remuneration
-------------------+-------------------------------+--------------
 Neena Kochhar     | 515.123.4568 - NKOCHHAR       |     17000.00
 Lex De Haan       | 515.123.4569 - LDEHAAN        |     17000.00
 Alexander Hunold  | 590.423.4567 - AHUNOLD        |      9000.00
 Nancy Greenberg   | 515.124.4569 - NGREENBE       |     12000.00
.....
(26 rows)

Click me to see the solution

10. From the following table, write a SQL query to find those employees whose first name ends with the letter ‘m’. Return the first and last name, and salary.  Go to the editor

Sample table: employees


Sample Output:

 first_name | last_name | salary
------------+-----------+---------
 Adam       | Fripp     | 8200.00
 Payam      | Kaufling  | 7900.00
 William    | Smith     | 7400.00
 William    | Gietz     | 8300.00
(4 rows)

Click me to see the solution

11. From the following table, write a SQL query to find those employees whose salary is not in the range 7000 and 15000 (Begin and end values are included). Sort the result-set in ascending order by the full name (first and last). Return full name and salary.  Go to the editor

Sample table: employees


Sample Output:

       name        |  salary
-------------------+----------
 Alana Walsh       |  3100.00
 Alexander Khoo    |  3100.00
 Alexis Bull       |  4100.00
 Amit Banda        |  6200.00
 .....
(63 rows)

Click me to see the solution

12. From the following table, write a SQL query to find those employees who were hired during November 5th, 2007 and July 5th, 2009. Return full name (first and last), job id and hire date. Go to the editor

Sample table: employees


Sample Output:

    full_name     |   job_id   | hire_date
------------------+------------+------------
 Luis Popp        | FI_ACCOUNT | 2007-12-07
 Kevin Mourgos    | ST_MAN     | 2007-11-16
 Steven Markle    | ST_CLERK   | 2008-03-08
 Ki Gee           | ST_CLERK   | 2007-12-12
.....
(16 rows)

Click me to see the solution

13. From the following table, write a SQL query to find those employees who works either in department 70 or 90. Return full name (first and last name), department id.  Go to the editor

Sample table: employees


Sample Output:

   full_name   | department_id
---------------+---------------
 Steven King   |            90
 Neena Kochhar |            90
 Lex De Haan   |            90
 Hermann Baer  |            70
(4 rows)

Click me to see the solution

14. From the following table, write a SQL query to find those employees who work under a manager. Return full name (first and last name), salary, and manager ID.  Go to the editor

Sample table: employees


Sample Output:

     full_name     |  salary  | manager_id
-------------------+----------+------------
 Neena Kochhar     | 17000.00 |        100
 Lex De Haan       | 17000.00 |        100
 Alexander Hunold  |  9000.00 |        102
 Bruce Ernst       |  6000.00 |        103
.....

Click me to see the solution

15. From the following table, write a SQL query to find those employees who were hired before June 21st, 2002. Return all fields.  Go to the editor

Sample table: employees


Sample Output:

employee_id | first_name | last_name |  email   | phone_number | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------
         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         203 | Susan      | Mavris    | SMAVRIS  | 515.123.7777 | 2002-06-07 | HR_REP     |  6500.00 |           0.00 |        101 |            40
         204 | Hermann    | Baer      | HBAER    | 515.123.8888 | 2002-06-07 | PR_REP     | 10000.00 |           0.00 |        101 |            70
         205 | Shelley    | Higgins   | SHIGGINS | 515.123.8080 | 2002-06-07 | AC_MGR     | 12000.00 |           0.00 |        101 |           110
         206 | William    | Gietz     | WGIETZ   | 515.123.8181 | 2002-06-07 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110
(5 rows)

Click me to see the solution

16. From the following table, write a SQL query to find those employees whose managers hold the ID 120 or 103 or 145. Return first name, last name, email, salary and manager ID.  Go to the editor

Sample table: employees


Sample Output:

 first_name  |  last_name  |  email   |  salary  | manager_id
-------------+-------------+----------+----------+------------
 Bruce       | Ernst       | BERNST   |  6000.00 |        103
 David       | Austin      | DAUSTIN  |  4800.00 |        103
 Valli       | Pataballa   | VPATABAL |  4800.00 |        103
 Diana       | Lorentz     | DLORENTZ |  4200.00 |        103
.....
(18 rows)

Click me to see the solution

17. From the following table, write a SQL query to find those employees whose first name contains the letters D, S, or N. Sort the result-set in descending order by salary. Return all fields.  Go to the editor

Sample table: employees


Sample Output:

 employee_id | first_name | last_name |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
         100 | Steven     | King      | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90
         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         205 | Shelley    | Higgins   | SHIGGINS | 515.123.8080       | 2002-06-07 | AC_MGR     | 12000.00 |           0.00 |        101 |           110
         108 | Nancy      | Greenberg | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12000.00 |           0.00 |        101 |           100
.....
(26 rows)

Click me to see the solution

18. From the following table, write a SQL query to find those employees who earn above 11000 or the seventh character in their phone number is 3. Sort the result-set in descending order by first name. Return full name (first name and last name), hire date, commission percentage, email, and telephone separated by '-', and salary.  Go to the editor

Sample table: employees


Sample Output:

     full_name     | hire_date  | commission_pct |        contact_details         |  salary
-------------------+------------+----------------+--------------------------------+----------
 William Gietz     | 2002-06-07 |           0.00 | WGIETZ -  515.123.8181         |  8300.00
 Valli Pataballa   | 2006-02-05 |           0.00 | VPATABAL -  590.423.4560       |  4800.00
 Susan Mavris      | 2002-06-07 |           0.00 | SMAVRIS -  515.123.7777        |  6500.00
 Steven King       | 2003-06-17 |           0.00 | SKING -  515.123.4567          | 24000.00
.....
(25 rows)

Click me to see the solution

19. From the following table, write a SQL query to find those employees whose first name contains a character ‘s’ in 3rd position. Return first name, last name and department id. Go to the editor

Sample table : employees


Sample Output:

 first_name  | last_name | department_id
-------------+-----------+---------------
 Jose Manuel | Urman     |           100
 Jason       | Mallin    |            50
 Joshua      | Patel     |            50
 Lisa        | Ozer      |            80
 Susan       | Mavris    |            40
(5 rows)

Click me to see the solution

20. From the following table, write a SQL query to find those employees who are working in the departments, which are not included in the department number 50 or 30 or 80. Return employee_id, first_name, job_id, department_id.  Go to the editor

Sample table : employees


Sample Output:

 employee_id | first_name  |   job_id   | department_id
-------------+-------------+------------+---------------
         100 | Steven      | AD_PRES    |            90
         101 | Neena       | AD_VP      |            90
         102 | Lex         | AD_VP      |            90
         103 | Alexander   | IT_PROG    |            60
         104 | Bruce       | IT_PROG    |            60
.....
(22 rows)

Click me to see the solution

21. From the following table, write a SQL query to find those employees whose department numbers are included in 30 or 40 or 90. Return employee id, first name, job id, department id.  Go to the editor

Sample table : employees


Sample Output:

 employee_id | first_name |  job_id  | department_id
-------------+------------+----------+---------------
         100 | Steven     | AD_PRES  |            90
         101 | Neena      | AD_VP    |            90
         102 | Lex        | AD_VP    |            90
         114 | Den        | PU_MAN   |            30
.....
(10 rows)

Click me to see the solution

22. From the following table, write a SQL query to find those employees who worked more than two jobs in the past. Return employee id.  Go to the editor

Sample table : job_history


Sample Output:

 employee_id
-------------
         101
         176
         200
(3 rows)

Click me to see the solution

23. From the following table, write a SQL query to count the number of employees, sum of all salary, and difference between the highest salary and lowest salary by each job id. Return job_id, count, sum, salary_difference.  Go to the editor

Sample table : employees


Sample Output:

   job_id   | count |    sum    | salary_difference
------------+-------+-----------+-------------------
 AC_ACCOUNT |     1 |   8300.00 |              0.00
 ST_MAN     |     5 |  36400.00 |           2400.00
 IT_PROG    |     5 |  28800.00 |           4800.00
 SA_MAN     |     5 |  61000.00 |           3500.00
 AD_PRES    |     1 |  24000.00 |              0.00
.....
(19 rows)

Click me to see the solution

24. From the following table, write a SQL query to find each job ids where two or more employees worked for more than 300 days. Return job id.  Go to the editor

Sample table : job_history


Sample Output:

   job_id
------------
 AC_ACCOUNT
 ST_CLERK
(2 rows)

Click me to see the solution

25. From the following table, write a SQL query to count the number of cities in each country has. Return country ID and number of cities.  Go to the editor

Sample table : locations


Sample Output:

 country_id | count
------------+-------
 CH         |     2
 "          |     1
 US         |     4
 AU         |     1
 IT         |     2
.....
(15 rows)

Click me to see the solution

26. From the following table, write a SQL query to count the number of employees worked under each manager. Return manager ID and number of employees.  Go to the editor

Sample table : employees


Sample Output:

 manager_id | count
------------+-------
        205 |     1
        122 |     8
        120 |     8
        101 |     5
        103 |     4
.....
(19 rows)

Click me to see the solution

27. From the following table, write a SQL query to find all jobs. Sort the result-set in descending order by job title. Return all fields.  Go to the editor

Sample table : jobs


Sample Output:

job_id    |job_title                      |min_salary|max_salary|
----------|-------------------------------|----------|----------|
ST_MAN    |Stock Manager                  |      5500|      8500|
ST_CLERK  |Stock Clerk                    |      2000|      5000|
SH_CLERK  |Shipping Clerk                 |      2500|      5500|
SA_REP    |Sales Representative           |      6000|     12000|
.....

Click me to see the solution

28. From the following table, write a SQL query to find all those employees who are either Sales Representative or Salesman. Return first name, last name and hire date.  Go to the editor

Sample table : employees


Sample Output:

 first_name  | last_name  | hire_date
-------------+------------+------------
 John        | Russell    | 2004-10-01
 Karen       | Partners   | 2005-01-05
 Alberto     | Errazuriz  | 2005-03-10
 Gerald      | Cambrault  | 2007-10-15
 Eleni       | Zlotkey    | 2008-01-29
.....
(35 rows)

Click me to see the solution

29. From the following table, write a SQL query to calculate average salary of those employees for each department who get a commission percentage. Return department id, average salary.  Go to the editor

Sample table : employees


Sample Output:

 department_id |          avg
---------------+------------------------
            90 |     19333.333333333333
            20 |  9500.0000000000000000
           100 |  8600.0000000000000000
            40 |  6500.0000000000000000
.....
(12 rows)

Click me to see the solution

30. From the following table, write a SQL query to find those departments where a manager can manage four or more employees. Return department_id.  Go to the editor

Sample table : employees


Sample Output:

 department_id
---------------
            80
            50
            60
           100
            30
(5 rows)

Click me to see the solution

31. From the following table, write a SQL query to find those departments where more than ten employees work, who got a commission percentage. Return department id.  Go to the editor

Sample table : employees


Sample Output:

 department_id
---------------
            80
            50
(2 rows)

Click me to see the solution

32. From the following table, write a SQL query to find those employees who have completed their previous jobs. Return employee ID, end_date.  Go to the editor

Sample table : job_history


Sample Output:

 employee_id |    max
-------------+------------
         101 | 2005-03-15
         200 | 2006-12-31
         176 | 2007-12-31
(7 rows)

Click me to see the solution

33. From the following table, write a SQL query to find those employees who have no commission percentage and salary within the range 7000, 12000 (Begin and end values are included.) and works in the department number 50. Return all the fields of employees.  Go to the editor

Sample table : employees


Sample Output:

employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+-------+--------------+-----------+--------+--------+----------------+------------+-------------
(0 rows)

Click me to see the solution

34. From the following table, write a SQL query to compute the average salary of each job ID. Exclude those records where average salary is higher than 8000. Return job ID, average salary.  Go to the editor

Sample table : employees


Sample Output:

   job_id   |          avg
------------+------------------------
 AC_ACCOUNT |  8300.0000000000000000
 SA_MAN     |     12200.000000000000
 AD_PRES    |     24000.000000000000
 AC_MGR     | 12000.0000000000000000
.....
(10 rows)

Click me to see the solution

35. From the following table, write a SQL query to find those job titles where the difference between minimum and maximum salaries is in the range the range 12000, 18000 (Begin and end values are included.). Return job_title, max_salary-min_salary.  Go to the editor

Sample table : jobs


Sample Output:

      job_title       | salary_differences
----------------------+--------------------
 Finance Manager      |               7800
 Accounting Manager   |               7800
 Sales Representative |               6000
 Purchasing Manager   |               7000
 Marketing Manager    |               6000
(5 rows)

Click me to see the solution

36. From the following table, write a SQL query to find those employees whose first name or last name starts with the letter ‘D’. Return first name, last name.  Go to the editor

Sample table : employees


Sample Output:

 first_name | last_name
------------+-----------
 Lex        | De Haan
 David      | Austin
 Diana      | Lorentz
 Daniel     | Faviet
.....
(14 rows)

Click me to see the solution

37. From the following table, write a SQL query to find details of those jobs where minimum salary exceeds 9000. Return all the fields of jobs.  Go to the editor

Sample table : jobs


Sample Output:

 job_id  |           job_title           | min_salary | max_salary
---------+-------------------------------+------------+------------
 AD_PRES | President                     |      20000 |      40000
 AD_VP   | Administration Vice President |      15000 |      30000
 SA_MAN  | Sales Manager                 |      10000 |      20000
(3 rows)

Click me to see the solution

38. From the following table, write a SQL query to find those employees who joined after 7th September 1987. Return all the fields.  Go to the editor

Sample table : employees


Sample Output:

 employee_id | first_name  |  last_name  |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
         100 | Steven      | King        | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90
         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60
         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 2007-05-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60
(107 rows)

Click me to see the solution

Practice Online


More to Come !

Query visualizations are generated using Postgres Explain Visualizer (pev).

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