w3resource

SQL Exercises, Practice, Solution - exercises on employee Database

SQL employee Database [115 Exercise with Solution]

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

Structure of employee Database:

employee database structure

1. From the following table return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

2. From the following table, write a SQL query to find the salaries of all employees. Return salary.

Sample table: employees


Sample Output:

 salary
---------
 6000.00
 2750.00
 2550.00
 2957.00
 ....
 

Click me to see the solution

3. From the following table, write a SQL query to find the unique designations of the employees. Return job name.

Sample table: employees


Sample Output:

 job_name
-----------
 CLERK
 SALESMAN
 MANAGER
 PRESIDENT
 ANALYST
(5 rows)

Click me to see the solution

4. From the following table, write a SQL query to list the employees’ name, increased their salary by 15%, and expressed as number of Dollars.

Sample table: employees


Sample Output:

 emp_name |  Salary
----------+----------
 KAYLING  | $  6,900
 BLAZE    | $  3,163
 CLARE    | $  2,933
 JONAS    | $  3,401
 SCARLET  | $  3,565
 ....
 

Click me to see the solution

5. From the following table, write a SQL query to list the employee's name and job name as a format of "Employee & Job".

Sample table: employees


Sample Output:

   Employee & Job
---------------------
 KAYLING   PRESIDENT
 BLAZE   MANAGER
 CLARE   MANAGER
 JONAS   MANAGER
 SCARLET   ANALYST
 ....
 

Click me to see the solution

6. Write a query in SQL to produce the output of employees as follows.
Employee
JONAS(manager).

Sample table: employees


Sample Output:

      Employee
--------------------
 KAYLING(president)
 BLAZE(manager)
 CLARE(manager)
 JONAS(manager)
 SCARLET(analyst)
 ....
 

Click me to see the solution

7. From the following table, write a SQL query to find those employees with hire date in the format like February 22, 1991. Return employee ID, employee name, salary, hire date.

Sample table: employees


Sample Output:

 emp_id | emp_name | salary  |      to_char
--------+----------+---------+-------------------
  68319 | KAYLING  | 6000.00 | NOVEMBER  18,1991
  66928 | BLAZE    | 2750.00 | MAY       01,1991
  67832 | CLARE    | 2550.00 | JUNE      09,1991
  65646 | JONAS    | 2957.00 | APRIL     02,1991
  67858 | SCARLET  | 3100.00 | APRIL     19,1997
  ....
 

Click me to see the solution

8. From the following table, write a SQL query to count the number of characters except the spaces for each employee name. Return employee name length.

Sample table: employees


Sample Output:

 length
--------
      7
      5
      5
      5
      7
	  ....

Click me to see the solution

9. From the following table, write a SQL query to find the employee ID, salary, and commission of all the employees.

Sample table: employees


Sample Output:

 emp_id | salary  | commission
--------+---------+------------
  68319 | 6000.00 |
  66928 | 2750.00 |
  67832 | 2550.00 |
  65646 | 2957.00 |
  67858 | 3100.00 |
  ....
  

Click me to see the solution

10. From the following table, write a SQL query to find the unique department with jobs. Return department ID, Job name.

Sample table: employees


Sample Output:

 dep_id | job_name
--------+-----------
   3001 | MANAGER
   2001 | ANALYST
   3001 | SALESMAN
   1001 | MANAGER
   1001 | PRESIDENT
   ...
   

Click me to see the solution

11. From the following table, write a SQL query to find those employees who do not belong to the 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
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  ....
  

Click me to see the solution

12. From the following table, write a SQL query to find those employees who joined before 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
--------+----------+----------+------------+------------+--------+------------+--------
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 | 900.00 |            |   2001
(1 row)

Click me to see the solution

13. From the following table, write a SQL query to calculate the average salary of employees who work as analysts. Return average salary.

Sample table: employees


Sample Output:

          avg
-----------------------
 3100.0000000000000000
(1 row)

Click me to see the solution

14. From the following table, write a SQL query to find the details of the employee ‘BLAZE’.

Sample table: employees


Sample Output:

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

Click me to see the solution

15. From the following table, write a SQL query to identify employees whose commissions exceed their salaries. 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
--------+----------+----------+------------+------------+---------+------------+--------
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
(1 row)

Click me to see the solution

16. From the following table, write a SQL query to identify those employees whose salaries exceed 3000 after receiving a 25% salary increase. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

17. From the following table, write a SQL query to find the names of the employees whose length is six. Return employee name.

Sample table: employees


Sample Output:

 emp_name
----------
 ADELYN
 MADDEN
 TUCKER
 ADNRES
 JULIUS
 MARKER
(6 rows)

Click me to see the solution

18. From the following table, write a SQL query to find out which employees joined in the month of January. 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
--------+----------+----------+------------+------------+---------+------------+--------
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(1 row)

Click me to see the solution

19. From the following table, write a SQL query to separate the names of employees and their managers by the string 'works for'.

Sample table: employees


Sample Output:

         ?column?
--------------------------
 BLAZE works for KAYLING
 CLARE works for KAYLING
 JONAS works for KAYLING
 SCARLET works for JONAS
 FRANK works for JONAS
 .....
 

Click me to see the solution

20. From the following table, write a SQL query to find those employees whose designation is ‘CLERK’. 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
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(4 rows)

Click me to see the solution

21. From the following table, write a SQL query to identify employees with more than 27 years of experience. 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
  65679 | SANDRINE | CLERK    |      69062 | 1990-12-18 | 900.00  |            |   2001
(3 rows)

Click me to see the solution

22. From the following table, write a SQL query to find those employees whose salaries are less than 3500. 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
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  ....
  

Click me to see the solution

23. From the following table, write a SQL query to find the employee whose designation is ‘ANALYST’. Return employee name, job name and salary.

Sample table: employees


Sample Output:

 emp_name | job_name | salary
----------+----------+---------
 SCARLET  | ANALYST  | 3100.00
 FRANK    | ANALYST  | 3100.00
(2 rows)

Click me to see the solution

24.From the following table, write a SQL query to identify those employees who joined the company in 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
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  ....
  

Click me to see the solution

25. From the following table, write a SQL query to find those employees who joined before 1st April 1991. Return employee ID, employee name, hire date and salary.

Sample table: employees


Sample Output:

 emp_id | emp_name | hire_date  | salary
--------+----------+------------+---------
  63679 | SANDRINE | 1990-12-18 |  900.00
  64989 | ADELYN   | 1991-02-20 | 1700.00
  65271 | WADE     | 1991-02-22 | 1350.00
(3 rows)

Click me to see the solution

26. From the following table, write a SQL query identify the employees who do not report to a manager. Return employee name, job name.

Sample table: employees


Sample Output:

 emp_name     | job_name
--------------+--------------
  KAYLING     | PRESIDENT

Click me to see the solution

27. From the following table, write a SQL query to find the employees who joined on the 1st of May 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
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
(1 row)

Click me to see the solution

28. From the following table, write a SQL query to identify the experience of the employees who work under the manager whose ID number is 68319. Return employee ID, employee name, salary, experience.

Sample table: employees


Sample Output:

 emp_id | emp_name | salary  |       Experience
--------+----------+---------+-------------------------
  66928 | BLAZE    | 2750.00 | 26 years 8 mons 29 days
  67832 | CLARE    | 2550.00 | 26 years 7 mons 21 days
  65646 | JONAS    | 2957.00 | 26 years 9 mons 28 days
(3 rows)

Click me to see the solution

29. From the following table, write a SQL query to find out which employees earn more than 100 per day as a salary. Return employee ID, employee name, salary, and experience.

Sample table: employees


Sample Output:

 emp_id | emp_name | salary  |       Experience
--------+----------+---------+-------------------------
  68319 | KAYLING  | 6000.00 | 26 years 2 mons 12 days
  67858 | SCARLET  | 3100.00 | 20 years 9 mons 11 days
  69062 | FRANK    | 3100.00 | 26 years 1 mon 27 days
(3 rows)

Click me to see the solution

30. From the following table, write a SQL query to identify those employees who retired after 31-Dec-99, completing eight years of service. Return employee name.

Sample table: employees


Sample Output:

 emp_name     
--------------
  ADNRES      
  MARKER      
  SCARLET 
(3 rows)

Click me to see the solution

31. From the following table, write a SQL query to identify the employees whose salaries are odd. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

32. From the following table, write a SQL query to identify employees whose salaries contain only three digits. 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

Click me to see the solution

33. From the following table, write a SQL query to find those employees who joined in the month of APRIL. 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
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
(2 rows)

Click me to see the solution

34. From the following table, write a SQL query to find out which employees joined the company before the 19th of the month. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

35. From the following table, write a SQL query to identify those employees who have been working as a SALESMAN and month portion of the experience is more than 10. 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
(2 rows)

Click me to see the solution

36. From the following table, write a SQL query to find those employees of department id 3001 or 1001 and joined in 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
--------+----------+-----------+------------+------------+---------+------------+--------
  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
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  ....
  

Click me to see the solution

37. From the following table, write a SQL query to find the employees who are working for the department ID 1001 or 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
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  ....
  

Click me to see the solution

38. From the following table, write a SQL query to find those employees whose designation is ‘CLERK’ and work in the department ID 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
--------+----------+----------+------------+------------+---------+------------+--------
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
(2 rows)

Click me to see the solution

39. From the following table, write a query in SQL to find those employees where -
1. the employees receive some commission which should not be more than the salary and annual salary including commission is below 34000.
2. Designation is ‘SALESMAN’ and working in the department ‘3001’. Return employee ID, employee name, salary and job name.

Sample table: employees


Sample Output:

 emp_id | emp_name | salary  | job_name
--------+----------+---------+----------
  64989 | ADELYN   | 1700.00 | SALESMAN
  65271 | WADE     | 1350.00 | SALESMAN
  68454 | TUCKER   | 1600.00 | SALESMAN
(3 rows)

Click me to see the solution

40. From the following table, write a SQL query to find those employees who are either CLERK or 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
--------+----------+----------+------------+------------+---------+------------+--------
  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
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  ....
  

Click me to see the solution

41. From the following table, write a SQL query to identify those employees who joined in any month other than February. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

42. From the following table, write a SQL query to find those employees who joined in 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
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  ....
  

Click me to see the solution

43. From the following table, write a SQL query to identify the employees who joined the company in June 1991. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

44. From the following table, write a SQL query to search for all employees with an annual salary between 24000 and 50000 (Begin and end values are included.). 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
  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
(5 rows)

Click me to see the solution

45. From the following table, write a SQL query to identify all employees who joined the company on 1st May, 20th February, and 3rd December 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 |
-------|---------|---------|-----------|-----------|--------|-----------|-------|
66928  |BLAZE    |MANAGER  |68319      |1991-05-01 |2750.00 |           |3001   |
64989  |ADELYN   |SALESMAN |66928      |1991-02-20 |1700.00 |400.00     |3001   |
69000  |JULIUS   |CLERK    |66928      |1991-12-03 |1050.00 |           |3001   |
69062  |FRANK    |ANALYST  |65646      |1991-12-03 |3100.00 |           |2001   |

Click me to see the solution

46. From the following table, write a SQL query to find out which employees are working under the managers 63679, 68319, 66564, or 69000. 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
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(3 rows)

Click me to see the solution

47. From the following table, write a SQL query to find which employees joined the company after the month of June in 1991 and within this year. 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
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
(5 rows)

Click me to see the solution

48. From the following table, write a SQL query to find those employees who joined in 90's. 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
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  .....
  

Click me to see the solution

49. From the following table, write a SQL query to find those managers who are in the department 1001 or 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
--------+----------+----------+------------+------------+---------+------------+--------
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
(2 rows)

Click me to see the solution

50. From the following table, write a SQL query to identify employees who joined in the month of FEBRUARY with a salary range of 1001 to 2000 (Begin and end values are included.). 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
(2 rows)

Click me to see the solution

51. From the following table, write a SQL query to find those employees who joined before or after 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
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(4 rows)

Click me to see the solution

52. From the following table, write a SQL query to find employees along with their department details. Return employee ID, employee name, job name, manager ID, hire date, salary, commission, department ID, and department name.

Sample table: employees


Sample table: department


Sample Output:

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

Click me to see the solution

53. From the following table, write a SQL query to identify those employees who earn 60000 or more per year or do not work as ANALYST. Return employee name, job name, (12*salary) as Annual Salary, department ID, and grade.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_name | job_name  | Annual Salary | dep_id | dep_name  | grade
----------+-----------+---------------+--------+-----------+-------
 SANDRINE | CLERK     |      10800.00 |   2001 | AUDIT     |     1
 ADNRES   | CLERK     |      14400.00 |   2001 | AUDIT     |     1
 JULIUS   | CLERK     |      12600.00 |   3001 | MARKETING |     1
 WADE     | SALESMAN  |      16200.00 |   3001 | MARKETING |     2
 MADDEN   | SALESMAN  |      16200.00 |   3001 | MARKETING |     2
 ....
 

Click me to see the solution

54. From the following table, write a SQL query to identify employees whose salaries are higher than their managers' salaries. Return employee name, job name, manager ID, salary, manager name, manager's salary..

Sample table: employees


Sample Output:

 emp_name | job_name | manager_id | salary  | Manager | emp_id | Manager_Salary
----------+----------+------------+---------+---------+--------+----------------
 SCARLET  | ANALYST  |      65646 | 3100.00 | JONAS   |  65646 |        2957.00
 FRANK    | ANALYST  |      65646 | 3100.00 | JONAS   |  65646 |        2957.00
(2 rows)

Click me to see the solution

55. From the following table, write a SQL query to find those employees whose salary is between 2000 and 5000 (Begin and end values are included.) and location is PERTH. Return employee name, department ID, salary, and commission.

Sample table: employees


Sample table: department


Sample Output:

 emp_name | dep_id | salary  | commission
----------+--------+---------+------------
 BLAZE    |   3001 | 2750.00 |
(1 row)

Click me to see the solution

56. From the following table, write a SQL query to find the employees whose department ID is 1001 or 3001 and whose salary grade is not 4. They joined the company before 1992-12-31. Return grade, employee name.

Sample table: employees


Sample Output:

 grade | emp_name
-------+----------
     1 | JULIUS
     2 | WADE
     2 | MADDEN
     2 | MARKER
     3 | ADELYN
     3 | TUCKER
     5 | KAYLING
(7 rows)

Sample table: salary_grade


Click me to see the solution

57. From the following table, write a SQL query to find those employees whose manager name is JONAS. Return employee id, employee name, job name, manager ID, hire date, salary, department ID, employee name.

Sample table: employees


Sample Output:

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

Click me to see the solution

58. From the following table, write a SQL query to find the name and salary of the employee FRANK. Salary should be equal to the maximum salary within his or her salary group.

Sample table: employees


Sample table: salary_grade


Sample Output:

 emp_name | salary
----------+---------
 FRANK    | 3100.00
(1 row)

Click me to see the solution

59. From the following table, write a SQL query to search for employees who are working either as a MANAGER or an ANALYST with a salary between 2000 and 5000 (Begin and end values are included.) without commissions. 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
  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
(5 rows)

Click me to see the solution

60. From the following table, write a SQL query to search for employees working in PERTH or MELBOURNE and month part of their achieved experience is more than 10. Return employee ID, employee name, department ID, salary, and department location.

Sample table: employees


Sample Output:

 emp_id | emp_name | dep_id | salary  | dep_location
--------+----------+--------+---------+--------------
  64989 | ADELYN   |   3001 | 1700.00 | PERTH
  65271 | WADE     |   3001 | 1350.00 | PERTH
(2 rows)

Sample table: department


Click me to see the solution

61. From the following table, write a SQL query to find the employees who joined in 1991 and whose department location is SYDNEY or MELBOURNE with a salary range of 2000 to 5000 (Begin and end values are included.). Return employee ID, employee name, department ID, salary, and department location.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | dep_id | salary  | dep_location
--------+----------+--------+---------+--------------
  67832 | CLARE    |   1001 | 2550.00 | SYDNEY
  65646 | JONAS    |   2001 | 2957.00 | MELBOURNE
  69062 | FRANK    |   2001 | 3100.00 | MELBOURNE
(3 rows)

Click me to see the solution

62. From the following table, write a SQL query to find the employees of MARKETING department come from MELBOURNE or PERTH, are in grades 3 ,4, and 5 and have at least 25 years of experience. Return department ID, employee ID, employee name, salary, department name, department location and grade.

Sample table: employees


Sample Output:

dep_id |emp_id |emp_name |salary  |dep_name  |dep_location |grade |
-------|-------|---------|--------|----------|-------------|------|
3001   |66928  |BLAZE    |2750.00 |MARKETING |PERTH        |4     |
3001   |64989  |ADELYN   |1700.00 |MARKETING |PERTH        |3     |
3001   |68454  |TUCKER   |1600.00 |MARKETING |PERTH        |3     |

Sample table: salary_grade


Sample table: department


Click me to see the solution

63. From the following table, write a SQL query to find those employees who are senior to their 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
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001 |  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
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001 |  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 |  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
(6 rows)

Click me to see the solution

64. From the following tables, write a SQL query to determine which employees have a grade of 4 and a salary between the minimum and maximum. Return all information of each employees and their grade and salary related 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
--------+----------+----------+------------+------------+---------+------------+--------+-------+---------+---------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001 |     4 |    2101 |    3100
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001 |     4 |    2101 |    3100
  65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001 |     4 |    2101 |    3100
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001 |     4 |    2101 |    3100
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001 |     4 |    2101 |    3100
(5 rows)

Click me to see the solution

65. From the following table, write a SQL query to find those employees who joined after 1991, excluding MARKER or ADELYN in the departments PRODUCTION or AUDIT. Return employee name.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_name
----------
 ADNRES
 SCARLET
(2 rows)

Click me to see the solution

66. From the following table, write a SQL query to find the employees and their salaries. Sort the result-set in ascending order by salaries. 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
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  ....
  

Click me to see the solution

67. From the following table, write a SQL query to list employees in ascending order on department ID and descending order on jobs. Return complete information about the employees.

Sample table: employees


Click me to see the solution

68. From the following table, write a SQL query to sort the unique jobs in descending order. Return job name.

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
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  ....
  

Click me to see the solution

69. From the following table, write a SQL query to rank the employees according to their annual salary in ascending order. Return employee ID, employee name, monthly salary, salary/30 as Daily_Salary, and 12*salary as Anual_Salary.

Sample table: employees


Sample Output:

 emp_id | emp_name | monthly_salary |     daily_salary     | anual_salary
--------+----------+----------------+----------------------+--------------
  63679 | SANDRINE |         900.00 |  30.0000000000000000 |     10800.00
  69000 | JULIUS   |        1050.00 |  35.0000000000000000 |     12600.00
  68736 | ADNRES   |        1200.00 |  40.0000000000000000 |     14400.00
  65271 | WADE     |        1350.00 |  45.0000000000000000 |     16200.00
  66564 | MADDEN   |        1350.00 |  45.0000000000000000 |     16200.00
  ....
  

Click me to see the solution

70. From the following table, write a SQL query to find those employees who are either 'CLERK' or 'ANALYST’. Sort the result set in descending order on job_name. 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
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
  ....
  

Click me to see the solution

71. From the following table, write a SQL query to find the department location of employee ‘CLARE’. Return department location.

Sample table: employees


Sample Output:

 dep_location
--------------
 SYDNEY
(1 row)

Sample table: department


Click me to see the solution

72. From the following table, write a SQL query to find those employees who joined on 1-MAY-91, or 3-DEC-91, or 19-JAN-90. Sort the result-set in ascending 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
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(3 rows)

Click me to see the solution

73. From the following table, write a SQL query to find those employees who earn less than 1000. 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
(1 row)

Click me to see the solution

74. From the following table, write a SQL query to list the employees in ascending order based on 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
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  ....
  

Click me to see the solution

75. From the following table, write a SQL query to list the employees in the ascending order by job title and in descending order by employee ID. 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
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  ....
  

Click me to see the solution

76. From the following table, write a SQL query to list the unique jobs of department 2001 and 3001 in descending order. Return job name.

Sample table: employees


Sample Output:

 job_name
----------
 SALESMAN
 MANAGER
 CLERK
 ANALYST
(4 rows)

Click me to see the solution

77. From the following table, write a SQL query to list all the employees except the PRESIDENT and the MANAGER in ascending order of salaries. 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
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  ....
  

Click me to see the solution

78. From the following table, write a SQL query to find the employees whose annual salary is less than $25,000 per year. Sort the result set in ascending order of the 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
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.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
  ....
  

Click me to see the solution

79. From the following table, write a SQL query to list the employees who works as a SALESMAN. Sort the result set in ascending order of annual salary. Return employee id, name, annual salary, daily salary of all the employees.

Sample table: employees


Sample Output:

 emp_id | emp_name | Annual Salary |    Daily Salary
--------+----------+---------------+---------------------
  65271 | WADE     |      16200.00 | 44.3835616438356164
  66564 | MADDEN   |      16200.00 | 44.3835616438356164
  68454 | TUCKER   |      19200.00 | 52.6027397260273973
  64989 | ADELYN   |      20400.00 | 55.8904109589041096
(4 rows)

Click me to see the solution

80. From the following table, write a SQL query to list the employee ID, name, hire date, current date and experience of the employees in ascending order on their experiences.

Sample table: employees


Sample Output:

 emp_id | emp_name | hire_date  |    date    |           exp
--------+----------+------------+------------+-------------------------
  68736 | ADNRES   | 1997-05-23 | 2018-02-01 | 20 years 8 mons 9 days
  67858 | SCARLET  | 1997-04-19 | 2018-02-01 | 20 years 9 mons 12 days
  69324 | MARKER   | 1992-01-23 | 2018-02-01 | 26 years 9 days
  69062 | FRANK    | 1991-12-03 | 2018-02-01 | 26 years 1 mon 29 days
  69000 | JULIUS   | 1991-12-03 | 2018-02-01 | 26 years 1 mon 29 days
  ....
  

Click me to see the solution

81. From the following table, write a SQL query to list the employees in ascending order of designations of those joined after the second half of 1991.

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
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(5 rows)

Click me to see the solution

82. From the following table, write a SQL query to find the location of all the employees working in the FINANCE or AUDIT department. Sort the result-set in ascending order by department ID. 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
--------+----------+-----------+------------+------------+---------+------------+--------+--------+----------+--------------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001 |   1001 | FINANCE  | SYDNEY
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001 |   1001 | FINANCE  | SYDNEY
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001 |   1001 | FINANCE  | SYDNEY
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  ....
  

Click me to see the solution

83. From the following tables, write a SQL query to find the employees along with grades in ascending order. 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
--------+----------+-----------+------------+------------+---------+------------+--------+-------+---------+---------
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001 |     1 |     800 |    1300
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001 |     1 |     800 |    1300
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001 |     1 |     800 |    1300
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 |     2 |    1301 |    1500
  ....
  

Click me to see the solution

84. From the following table, write a SQL query to find the employees according to the department in ascending order. Return name, job name, department, salary, and grade.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_name | job_name  | dep_name  | salary  | grade
----------+-----------+-----------+---------+-------
 KAYLING  | PRESIDENT | FINANCE   | 6000.00 |     5
 CLARE    | MANAGER   | FINANCE   | 2550.00 |     4
 MARKER   | CLERK     | FINANCE   | 1400.00 |     2
 SANDRINE | CLERK     | AUDIT     |  900.00 |     1
 SCARLET  | ANALYST   | AUDIT     | 3100.00 |     4
 ....
 

Click me to see the solution

85. From the following table, write a SQL query to select all employees except CLERK and sort the results in descending order by salary. Return employee name, job name, salary, grade and department name.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 emp_name | job_name  | salary  | grade | dep_name
----------+-----------+---------+-------+-----------
 KAYLING  | PRESIDENT | 6000.00 |     5 | FINANCE
 FRANK    | ANALYST   | 3100.00 |     4 | AUDIT
 SCARLET  | ANALYST   | 3100.00 |     4 | AUDIT
 JONAS    | MANAGER   | 2957.00 |     4 | AUDIT
 ....
 

Click me to see the solution

86. From the following table, write a SQL query to find those employees who work in the department 1001 or 2001. Return employee ID, name, salary, department, grade, experience, and annual salary.

Sample table: employees


Sample table: department


Sample table: salary_grade


Click me to see the solution

87. From the following table, write a SQL query to list the details of the employees along with the details of their departments.

Sample table: employees


Sample table: department


Click me to see the solution

88. From the following table, write a SQL query to list the employees who are senior to 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
--------+----------+----------+------------+------------+---------+------------+--------+--------+----------+-----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001 |  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001 |  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
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.00 |            |   2001 |  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  ....
  

Click me to see the solution

89. From the following table, write a SQL query to find those employees who work in the department 1001. Sort the result-set in ascending order by salary. Return employee ID, employee name, salary and department ID.

Sample table: employees


Sample Output:

 emp_id | emp_name | salary  | dep_id
--------+----------+---------+--------
  69324 | MARKER   | 1400.00 |   1001
  67832 | CLARE    | 2550.00 |   1001
  68319 | KAYLING  | 6000.00 |   1001
(3 rows)

Click me to see the solution

90. From the following table, write a SQL query to find the highest salary. Return highest salary.

Sample table: employees


Sample Output:

   max
---------
 6000.00
(1 row)

Click me to see the solution

91. From the following table, write a SQL query to calculate the average salary and average total remuneration (salary and commission) for each type of job. Return name, average salary and average total remuneration.

Sample table: employees


Sample Output:

 job_name  |          avg          |          avg
-----------+-----------------------+-----------------------
 CLERK     | 1137.5000000000000000 |
 SALESMAN  | 1500.0000000000000000 | 2125.0000000000000000
 MANAGER   | 2752.3333333333333333 |
 PRESIDENT | 6000.0000000000000000 |
 ANALYST   | 3100.0000000000000000 |
(5 rows)

Click me to see the solution

92. From the following table, write a SQL query to calculate the total annual salary distributed across each job in 1991. Return job name, total annual salary.

Sample table: employees


Sample Output:

 job_name  |   sum
-----------+----------
 CLERK     | 12600.00
 PRESIDENT | 72000.00
 SALESMAN  | 72000.00
 ANALYST   | 37200.00
 MANAGER   | 99084.00
(5 rows)

Click me to see the solution

93. From the following table, write a SQL query to list the employee id, name, department id, location of all the employees.

Sample table: employees


Sample table: department


Sample Output:

 emp_id | emp_name | dep_id | dep_location
--------+----------+--------+--------------
  68319 | KAYLING  |   1001 | SYDNEY
  66928 | BLAZE    |   3001 | PERTH
  67832 | CLARE    |   1001 | SYDNEY
  65646 | JONAS    |   2001 | MELBOURNE
  67858 | SCARLET  |   2001 | MELBOURNE
  ....
  

Click me to see the solution

94. From the following table, write a SQL query to find those employees who work in the department ID 1001 or 2001. Return employee ID, employee name, department ID, department location, and department name.

Sample table: employees


Sample table: department


Sample Output:

emp_id | emp_name | dep_id | dep_location | dep_name 
--------+----------+--------+--------------+----------
  68319 | KAYLING  |   1001 | SYDNEY       | FINANCE
  67832 | CLARE    |   1001 | SYDNEY       | FINANCE
  65646 | JONAS    |   2001 | MELBOURNE    | AUDIT
  68736 | ADNRES   |   2001 | MELBOURNE    | AUDIT
  ....
  

Click me to see the solution

95. From the following table, write a SQL query to find those employees whose salary is in the range of minimum and maximum salary (Begin and end values are included.). Return employee ID, name, salary and grade.

Sample table: employees


Sample table: salary_grade


Sample Output:

 emp_id | emp_name | salary  | grade
--------+----------+---------+-------
  63679 | SANDRINE |  900.00 |     1
  68736 | ADNRES   | 1200.00 |     1
  69000 | JULIUS   | 1050.00 |     1
  65271 | WADE     | 1350.00 |     2
  66564 | MADDEN   | 1350.00 |     2
  ....
  

Click me to see the solution

96. From the following table, write a SQL query to create a list of the managers and the number of employees they supervise. Sort the result set in ascending order on manager. Return manager ID and number of employees under them.

Sample table: employees


Sample Output:

  manager_id | count
------------+-------
      65646 |     2
      66928 |     5
      67832 |     1
      67858 |     1
      68319 |     3
      69062 |     1
(6 rows)

Click me to see the solution

97. From the following table, write a SQL query to count the number of employees in each designation of a department. Return department id, job name and number of employees.

Sample table: employees


Sample Output:

 dep_id | job_name  | count
--------+-----------+-------
   3001 | MANAGER   |     1
   2001 | ANALYST   |     2
   3001 | SALESMAN  |     4
   1001 | MANAGER   |     1
   ....
   

Click me to see the solution

98. From the following table, write a SQL query to identify the departments in which at least two employees are employed. Return department id, number of employees.

Sample table: employees


Sample Output:

 dep_id | count
--------+-------
   3001 |     6
   1001 |     3
   2001 |     5
(3 rows)

Click me to see the solution

99. From the following table, write a SQL query to list the grade, number of employees, and maximum salary of each grade.

Sample table: employees


Sample table: salary_grade


Sample Output:

 grade | count |   max
-------+-------+---------
     4 |     5 | 3100.00
     1 |     3 | 1200.00
     5 |     1 | 6000.00
     3 |     2 | 1700.00
     2 |     3 | 1400.00
(5 rows)

Click me to see the solution

100. From the following table, write a SQL query to identify departments with at least two SALESMEN in each grade. Return department name, grade and number of employees.

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Output:

 dep_name  | grade | count
-----------+-------+-------
 MARKETING |     2 |     2
 MARKETING |     3 |     2
(2 rows)

Click me to see the solution

101. From the following table, write a SQL query to identify departments with fewer than four employees. Return department ID, number of employees.

Sample table: employees


Sample Output:

 dep_id | count
--------+-------
   1001 |     3
(1 row)

Click me to see the solution

102. From the following table, write a SQL query to find which departments have at least two employees. Return department name, number of employees.

Sample table: employees


Sample table: department


Sample Output:

 dep_name  | count
-----------+-------
 FINANCE   |     3
 MARKETING |     6
 AUDIT     |     5
(3 rows)s

Click me to see the solution

103. From the following table, write a SQL query to check whether the employees ID are unique or not. Return employee id, number of employees.

Sample table: employees


Sample Output:

 emp_id | count
--------+-------
  69324 |     1
  69062 |     1
  63679 |     1
  67858 |     1
  66564 |     1
  .....
  

Click me to see the solution

104. From the following table, write a SQL query to find number of employees and average salary. Group the result set on department id and job name. Return number of employees, average salary, department ID, and job name.

Sample table: employees


Sample Output:

 count |          avg          | dep_id | job_name
-------+-----------------------+--------+-----------
     1 | 2750.0000000000000000 |   3001 | MANAGER
     2 | 3100.0000000000000000 |   2001 | ANALYST
     4 | 1500.0000000000000000 |   3001 | SALESMAN
     1 | 2550.0000000000000000 |   1001 | MANAGER
	 ....
	 

Click me to see the solution

105. From the following table, write a SQL query to identify those employees whose names begin with 'A' and are six characters long. Return employee name.

Sample table: employees


Sample Output:

 emp_name
----------
 ADELYN
 ADNRES
(2 rows)

Click me to see the solution

106. From the following table, write a SQL query to find those employees whose name is six characters in length and the third character must be 'R'. 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
--------+----------+----------+------------+------------+---------+------------+--------
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(1 row)

Click me to see the solution

107. From the following table, write a SQL query to find those employees whose name is six characters in length, starting with 'A' and ending with 'N'. Return number of employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

108. From the following table, write a SQL query to find those employees who joined in the month of where the second letter is 'a'. Return number of 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
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(3 rows)

Click me to see the solution

109. From the following table, write a SQL query to find those employees whose names contain the character set 'AR' together. 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
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(3 rows)

Click me to see the solution

110. From the following table, write a SQL query to find those employees who joined in 90's. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

111. From the following table, write a SQL query to find those employees whose ID not start with the digit 68. Return employee ID, employee ID using trim function.

Sample table: employees


Sample Output:

 emp_id | btrim
--------+-------
  66928 | 66928
  67832 | 67832
  65646 | 65646
  67858 | 67858
  ....
  

Click me to see the solution

112. From the following table, write a SQL query to find those employees whose names contain the letter 'A’. Return complete information about the employees.

Sample table: employees


Sample Output:

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

Click me to see the solution

113. From the following table, write a SQL query to find those employees whose name ends with 'S' and six characters long. 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
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(2 rows)

Click me to see the solution

114. From the following table, write a SQL query to find those employees who joined in any month, but the month name contain the character ‘A’. 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
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  ....
  

Click me to see the solution

115. From the following table, write a SQL query to find those employees who joined in any month, but the name of the month contain the character ‘A’ in second position. 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
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(3 rows)

Click me to see the solution

Practice Online


More to Come!

Structure of employee Database:

employee database structure

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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

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