SQL Exercise: List the employee with their grade for the grade 4

Last update on December 20 2024 11:40:33 (UTC/GMT +8 hours)

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

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.

Pictorial Presentation:

Sample table: employees

Sample table: salary_grade

Sample Solution:

SELECT *
FROM employees e,
     salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
  AND s.grade = 4;

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)

Explanation:

The said query in SQL that retrieves all the employees whose salary falls within the salary range of grade 4 and displays all columns for each matching row from two tables, 'employees' and 'salary_grade' .

The WHERE clause first checks whether the employee's salary falls within the range of minimum and maximum salaries defined for grade 4 in the "salary_grade" table.

It then retrieve data only for employees who belong to grade 4.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: employee

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

Previous SQL Exercise: List the employees who are senior to their own manager.
Next SQL Exercise: List the employees in given department, joined after 91.