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 Tree:
Visual Presentation:
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
Query Visualization:
Duration:
Rows:
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/joins-hr/sql-joins-hr-exercise-4.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics