SQL JOINS on HR Database: Display the first name, last name, department number and department name, for 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:

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;

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

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following table, write a SQL query to find the first name, last name, salary, and job grade for all employees.
Next: From the following tables, write a SQL query to find those employees whose first name contains a letter ‘z’. Return first name, last name, department, city, and state province.