SQL Exercise: Employees joined before a date but not graded 4

SQL employee Database: Exercise-56 with Solution

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

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 table: salary_grade

Pictorial Presentation:

Sample Solution:

SELECT s.grade,
       e.emp_name
FROM employees e,
     salary_grade s
WHERE e.dep_id IN (1001,
                   3001)
  AND hire_date < ('1992-12-31')
  AND (e.salary BETWEEN s.min_sal AND s.max_sal
       AND s.grade NOT IN (4));

Sample Output:

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

Explanation:

The said query in SQL that retrieves "grade" and "emp_name" from the 'employees' and 'salary_grade' tables.

The query joins the 'salary_grade' and the 'employees' tables using the "WHERE" clause, which includes the employees that department ID must be either 1001 or 3001, employees must have been hired before December 31st, 1992 and the employee's salary must be within the range of the minimum and maximum salary for the corresponding grade in the 'salary_grade' table and also the grade must not be equal to 4.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Employees whose salary is in a range and location PERTH.
Next SQL Exercise: List the employees whose manager name is JONAS.