SQL Exercise: List all employees names, salaries, and job grades

Last update on June 06 2024 13:32:47 (UTC/GMT +8 hours)

SQL JOINS on HR Database: Exercise-3 with Solution

3. From the following table, write a SQL query to find the first name, last name, salary, and job grade for all employees.

Sample table: employees

Sample table: job_grades

Sample Solution:

-- Selecting specific columns (E.first_name, E.last_name, E.salary, J.grade_level) from the 'employees' table, aliased as 'E', and the 'job_grades' table, aliased as 'J'
SELECT E.first_name, E.last_name, E.salary, J.grade_level 

-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'job_grades' table (aliased as 'J') based on the condition that 'E.salary' falls within the range specified by 'J.lowest_sal' and 'J.highest_sal'
FROM employees E 

JOIN job_grades J
 ON E.salary BETWEEN J.lowest_sal AND J.highest_sal;

Sample Output:

first_name	last_name	salary	grade_level
Shelli		Baida		2900.00		A
Sigal		Tobias		2800.00		A
Guy		Himuro		2600.00		A
Karen		Colmenares	2500.00		A
Irene		Mikkilineni	2700.00		A
James		Landry		2400.00		A
Steven		Markle		2200.00		A
Mozhe		Atkinson	2800.00		A
James		Marlow		2500.00		A
TJ		Olson		2100.00		A
Michael		Rogers		2900.00		A
Ki		Gee		2400.00		A
Hazel		Philtanker	2200.00		A
John		Seo		2700.00		A
Joshua		Patel		2500.00		A
Randall		Matos		2600.00		A
Peter		Vargas		2500.00		A
Martha		Sullivan	2500.00		A
Girard		Geoni		2800.00		A
Timothy		Gates		2900.00		A
Randall		Perkins		2500.00		A
Vance		Jones		2800.00		A
Donald		OConnell	2600.00		A
Douglas		Grant		2600.00		A
David		Austin		4800.00		B
Valli		Pataballa	4800.00		B
Diana		Lorentz		4200.00		B
Alexander	Khoo		3100.00		B
Kevin		Mourgos		5800.00		B
Julia		Nayer		3200.00		B
Laura		Bissot		3300.00		B
Jason		Mallin		3300.00		B
Renske		Ladwig		3600.00		B
Stephen		Stiles		3200.00		B
Trenna		Rajs		3500.00		B
Curtis		Davies		3100.00		B
Winston		Taylor		3200.00		B
Jean		Fleaur		3100.00		B
Nandita		Sarchand	4200.00		B
Alexis		Bull		4100.00		B
Julia		Dellinger	3400.00		B
Anthony		Cabrio		3000.00		B
Kelly		Chung		3800.00		B
Jennifer	Dilly		3600.00		B
Sarah		Bell		4000.00		B
Britney		Everett		3900.00		B
Samuel		McCain		3200.00		B
Alana		Walsh		3100.00		B
Kevin		Feeney		3000.00		B
Jennifer	Whalen		4400.00		B
Alexander	Hunold		9000.00		C
Bruce		Ernst		6000.00		C
Daniel		Faviet		9000.00		C
John		Chen		8200.00		C
Ismael		Sciarra		7700.00		C
Jose 	Manuel	Urman		7800.00		C
Luis		Popp		6900.00		C
Matthew		Weiss		8000.00		C
Adam		Fripp		8200.00		C
Payam		Kaufling	7900.00		C
Shanta		Vollman		6500.00		C
David		Bernstein	9500.00		C
Peter		Hall		9000.00		C
Christopher	Olsen		8000.00		C
Nanette		Cambrault	7500.00		C
Oliver		Tuvault		7000.00		C
Patrick		Sully		9500.00		C
Allan		McEwen		9000.00		C
Lindsey		Smith		8000.00		C
Louise		Doran		7500.00		C
Sarath		Sewall		7000.00		C
Danielle	Greene		9500.00		C
Mattea		Marvins		7200.00		C
David		Lee		6800.00		C
Sundar		Ande		6400.00		C
Amit		Banda		6200.00		C
Tayler		Fox		9600.00		C
William		Smith		7400.00		C
Elizabeth	Bates		7300.00		C
Sundita		Kumar		6100.00		C
Alyssa		Hutton		8800.00		C
Jonathon	Taylor		8600.00		C
Jack		Livingston	8400.00		C
Kimberely	Grant		7000.00		C
Charles		Johnson		6200.00		C
Pat		Fay		6000.00		C
Susan		Mavris		6500.00		C
William		Gietz		8300.00		C
Nancy		Greenberg	12000.00	D
Den		Raphaely	11000.00	D
John		Russell		14000.00	D
Karen		Partners	13500.00	D
Alberto		Errazuriz	12000.00	D
Gerald		Cambrault	11000.00	D
Eleni		Zlotkey		10500.00	D
Peter		Tucker		10000.00	D
Janette		King		10000.00	D
Clara		Vishney		10500.00	D
Lisa		Ozer		11500.00	D
Harrison	Bloom		10000.00	D
Ellen		Abel		11000.00	D
Michael		Hartstein	13000.00	D
Hermann		Baer		10000.00	D
Shelley		Higgins		12000.00	D
Steven		King		24000.00	E
Neena		Kochhar		17000.00	E
Lex		De Haan		17000.00	E

Code Explanation:

The said query in SQL that joins the 'employees' table with the 'job_grades' table using the "salary" column. It selects the "first_name", "last_name", "salary", and "grade_level" columns from the joined tables. The result set includes one row for each employee where their salary falls within the range specified by a job grade in the 'job_grades' table.

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

SQL Exercises: Display the first name, last name, salary, and job grade for all employees

Alternative Solutions:

Using WHERE Clause with Range Condition:


SELECT E.first_name, E.last_name, E.salary, J.grade_level
FROM employees E, job_grades J
WHERE E.salary BETWEEN J.lowest_sal AND J.highest_sal;

Using JOIN with ANSI-92 Syntax:


SELECT E.first_name, E.last_name, E.salary, J.grade_level
FROM employees E
JOIN job_grades J ON E.salary BETWEEN J.lowest_sal AND J.highest_sal;

Using Subquery with Range Condition:


SELECT E.first_name, E.last_name, E.salary,
       (SELECT grade_level
        FROM job_grades J
        WHERE E.salary BETWEEN J.lowest_sal AND J.highest_sal) AS grade_level
FROM employees E;