w3resource

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

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 Expression: Display the first name, last name, salary, and job grade for all employees.

Relational Algebra Tree:

Relational Algebra Tree: Display the first name, last name, salary, and job grade for all employees.

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;

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the first name, last name, salary, and job grade for all employees - Duration

Rows:

Query visualization of Display the first name, last name, salary, and job grade for all employees - Rows

Cost:

Query visualization of Display the first name, last name, salary, and job grade for all employees - Cost

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

Previous SQL Exercise: Display name, department, city and state each employee.
Next SQL Exercise: Display all employees for departments 80 or 40.

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.