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:

SQL exercises on employee Database: List the grade, employee name for the department id 1001 or 3001 but salary grade is not 4 while they joined the company before 1992-12-31

Sample Solution:

SELECT s.grade,
FROM employees e,
     salary_grade s
WHERE e.dep_id IN (1001,
  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)


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

employee database structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Follow us on Facebook and Twitter for latest update.