Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
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 salaries are 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 identify employees who do not have a 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 who earn between 8000 and 12000 (Begin and end values are included.) and get some commission. These employees joined before ‘1987-06-05’ and were not included in the department numbers 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 the 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 the 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 salaries are not between 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 between 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 work 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 the 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 the employees whose managers hold the ID 120, 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 employees whose first names contain 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 the third 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 work in the departments that are not part of the department 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 the employees whose department numbers are included in 30, 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, the sum of all salary, and difference between the highest salary and lowest salaries 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. 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 Representatives or Salesmen. 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 the average salary of employees who receive a commission percentage for each department. 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 the departments where any manager manages 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 the departments where more than ten employees receive commissions. 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 do not have commission percentage and have salaries between 7000, 12000 (Begin and end values are included.) and who are employed 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 maximum salary falls between 12000 and 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 the employees whose first or last name begins with '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 the 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.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3