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
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));
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.
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Concatenate strings of a string field in a PostgreSQL 'group by' query:
ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook