w3resource

SQL exercises on employee Database: List the employees in department PRODUCTION or AUDIT who joined after 1991 and they are not MARKER or ADELYN to their name

SQL employee Database: Exercise-65 with Solution

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

65. From the following tables, write a SQL query to find those employees, excluding MARKER or ADELYN of the department PRODUCTION or AUDIT and joined after 1991. Return employee name.

Pictorial Presentation:

SQL exercises on employee Database: List the employees in department PRODUCTION or AUDIT who joined after 1991 and they are not MARKER or ADELYN to their name

Sample table: employees


Sample table: department


Sample table: salary_grade


Sample Solution:

SELECT e.emp_name
FROM employees e,
     department d,
     salary_grade s
WHERE e.dep_id = d.dep_id
  AND d.dep_name IN ('PRODUCTION',
                     'AUDIT')
  AND e.salary BETWEEN s.min_sal AND s.max_sal
  AND e.emp_name NOT IN ('MARKER',
                         'ADELYN')
  AND to_char(hire_date,'YYYY') >'1991';

Sample Output:

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

Practice Online


Sample Database: employee

employee database structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find those employees whose grade is 4 and salary between minimum and maximum salary. Return all information of each employees and their grade and salary related details.
Next: 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.

What is the difficulty level of this exercise?