w3resource
SQL exercises

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. Write a query in SQL to display the first name, last name, department number and department name, for all employees for departments 80 or 40.

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

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: Write a query in SQL to display the first name, last name, salary, and job grade for all employees.
Next: Write a query in SQL to display those employees who contain a letter z to their first name and also display their last name, department, city, and state province.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming