w3resource

SQL Exercise: Display all employees for departments 80 or 40

SQL JOINS on HR Database: Exercise-4 with Solution

4. From the following tables, write a SQL query to find all those employees who work in department ID 80 or 40. Return first name, last name, department number and department name.

Sample table: departments

Sample table: employees

Sample Solution:

-- Selecting specific columns (E.first_name, E.last_name, E.department_id, D.department_name) from the 'employees' table, aliased as 'E', and the 'departments' table, aliased as 'D'
SELECT E.first_name, E.last_name, E.department_id, D.department_name 

-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'departments' table (aliased as 'D') based on the 'department_id' column
FROM employees E 

JOIN departments D 
  ON E.department_id = D.department_id 

-- Additional condition to filter rows where the 'department_id' is either 80 or 40
AND E.department_id IN (80, 40)

-- Ordering the result set based on the 'last_name' column
ORDER BY E.last_name;

Sample Output:

first_name	last_name	department_id	department_name
Ellen		Abel		80		Sales
Sundar		Ande		80		Sales
Amit		Banda		80		Sales
Elizabeth	Bates		80		Sales
David		Bernstein	80		Sales
Harrison	Bloom		80		Sales
Nanette		Cambrault	80		Sales
Gerald		Cambrault	80		Sales
Louise		Doran		80		Sales
Alberto		Errazuriz	80		Sales
Tayler		Fox		80		Sales
Danielle	Greene		80		Sales
Peter		Hall		80		Sales
Alyssa		Hutton		80		Sales
Charles		Johnson		80		Sales
Janette		King		80		Sales
Sundita		Kumar		80		Sales
David		Lee		80		Sales
Jack		Livingston	80		Sales
Mattea		Marvins		80		Sales
Susan		Mavris		40		Human Resources
Allan		McEwen		80		Sales
Christopher	Olsen		80		Sales
Lisa		Ozer		80		Sales
Karen		Partners	80		Sales
John		Russell		80		Sales
Sarath		Sewall		80		Sales
Lindsey		Smith		80		Sales
William		Smith		80		Sales
Patrick		Sully		80		Sales
Jonathon	Taylor		80		Sales
Peter		Tucker		80		Sales
Oliver		Tuvault		80		Sales
Clara		Vishney		80		Sales
Eleni		Zlotkey		80		Sales

Code Explantion:

The said query in SQL that selects the first name, last name, department ID, and department name of employees who work in either department 80 or department 40, and orders the result set by last name.
The JOIN clause joins the employees table with the departments table using the department_id column, which is common to both tables.
The ORDER BY clause sorts the result set by last_name.

Relational Algebra Expression:

Relational Algebra Expression: Display the first name, last name, department number and department name, for all employees for departments 80 or 40.

Relational Algebra Tree:

Relational Algebra Tree: Display the first name, last name, department number and department name, for all employees for departments 80 or 40.

Visual Presentation:

SQL Exercises: Display the first name, last name, department number and department name, for all employees for departments 80 or 40

Alternative Solutions:

Using INNER JOIN with WHERE Clause:


SELECT E.first_name, E.last_name, E.department_id, D.department_name
FROM employees E
JOIN departments D ON E.department_id = D.department_id
WHERE E.department_id IN (80, 40)
ORDER BY E.last_name;

Using ANSI-92 JOIN Syntax with AND Condition:


SELECT E.first_name, E.last_name, E.department_id, D.department_name
FROM employees E
JOIN departments D ON E.department_id = D.department_id AND E.department_id IN (80, 40)
ORDER BY E.last_name;

Using Subquery in the WHERE Clause:


SELECT first_name, last_name, department_id,
       (SELECT department_name FROM departments WHERE department_id = E.department_id) AS department_name
FROM employees E
WHERE department_id IN (80, 40)
ORDER BY last_name;

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the first name, last name, department number and department name, for all employees for departments 80 or 40 - Duration

Rows:

Query visualization of Display the first name, last name, department number and department name, for all employees for departments 80 or 40 - Rows

Cost:

Query visualization of Display the first name, last name, department number and department name, for all employees for departments 80 or 40 - Cost

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

Previous SQL Exercise: List all employees names, salaries, and job grades.
Next SQL Exercise: Employees who contain a letter z to their first name.

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.