w3resource

SQL JOINS on HR Database: Display all departments including those where does not have any employee

SQL JOINS on HR Database: Exercise-6 with Solution

6. From the following table, write a SQL query to find all departments including those without any employee. Return first name, last name, department ID, department name.

Sample table: departments


Sample table: employees


Sample Solution:

SELECT E.first_name, E.last_name, D.department_id, D.department_name 
 FROM employees E 
   RIGHT OUTER JOIN departments D
     ON E.department_id = D.department_id;

Sample Output:

first_name  |  last_name  | department_id |   department_name    
-------------+-------------+---------------+----------------------
 Steven      | King        |            90 | Executive
 Neena       | Kochhar     |            90 | Executive
 Lex         | De Haan     |            90 | Executive
 Alexander   | Hunold      |            60 | IT
 Bruce       | Ernst       |            60 | IT
 David       | Austin      |            60 | IT
 Valli       | Pataballa   |            60 | IT
 Diana       | Lorentz     |            60 | IT
 Nancy       | Greenberg   |           100 | Finance
 Daniel      | Faviet      |           100 | Finance
 John        | Chen        |           100 | Finance
 Ismael      | Sciarra     |           100 | Finance
 Jose Manuel | Urman       |           100 | Finance
 Luis        | Popp        |           100 | Finance
 Den         | Raphaely    |            30 | Purchasing
 Alexander   | Khoo        |            30 | Purchasing
 Shelli      | Baida       |            30 | Purchasing
 Sigal       | Tobias      |            30 | Purchasing
 Guy         | Himuro      |            30 | Purchasing
 Karen       | Colmenares  |            30 | Purchasing
 Matthew     | Weiss       |            50 | Shipping
 Adam        | Fripp       |            50 | Shipping
 Payam       | Kaufling    |            50 | Shipping
 Shanta      | Vollman     |            50 | Shipping
 Kevin       | Mourgos     |            50 | Shipping
 Julia       | Nayer       |            50 | Shipping
 Irene       | Mikkilineni |            50 | Shipping
 James       | Landry      |            50 | Shipping
 Steven      | Markle      |            50 | Shipping
 Laura       | Bissot      |            50 | Shipping
 Mozhe       | Atkinson    |            50 | Shipping
 James       | Marlow      |            50 | Shipping
 TJ          | Olson       |            50 | Shipping
 Jason       | Mallin      |            50 | Shipping
 Michael     | Rogers      |            50 | Shipping
 Ki          | Gee         |            50 | Shipping
 Hazel       | Philtanker  |            50 | Shipping
 Renske      | Ladwig      |            50 | Shipping
 Stephen     | Stiles      |            50 | Shipping
 John        | Seo         |            50 | Shipping
 Joshua      | Patel       |            50 | Shipping
 Trenna      | Rajs        |            50 | Shipping
 Curtis      | Davies      |            50 | Shipping
 Randall     | Matos       |            50 | Shipping
 Peter       | Vargas      |            50 | Shipping
 John        | Russell     |            80 | Sales
 Karen       | Partners    |            80 | Sales
 Alberto     | Errazuriz   |            80 | Sales
 Gerald      | Cambrault   |            80 | Sales
 Eleni       | Zlotkey     |            80 | Sales
 Peter       | Tucker      |            80 | Sales
 David       | Bernstein   |            80 | Sales
 Peter       | Hall        |            80 | Sales
 Christopher | Olsen       |            80 | Sales
 Nanette     | Cambrault   |            80 | Sales
 Oliver      | Tuvault     |            80 | Sales
 Janette     | King        |            80 | Sales
 Patrick     | Sully       |            80 | Sales
 Allan       | McEwen      |            80 | Sales
 Lindsey     | Smith       |            80 | Sales
 Louise      | Doran       |            80 | Sales
 Sarath      | Sewall      |            80 | Sales
 Clara       | Vishney     |            80 | Sales
 Danielle    | Greene      |            80 | Sales
 Mattea      | Marvins     |            80 | Sales
 David       | Lee         |            80 | Sales
 Sundar      | Ande        |            80 | Sales
 Amit        | Banda       |            80 | Sales
 Lisa        | Ozer        |            80 | Sales
 Harrison    | Bloom       |            80 | Sales
 Tayler      | Fox         |            80 | Sales
 William     | Smith       |            80 | Sales
 Elizabeth   | Bates       |            80 | Sales
 Sundita     | Kumar       |            80 | Sales
 Ellen       | Abel        |            80 | Sales
 Alyssa      | Hutton      |            80 | Sales
 Jonathon    | Taylor      |            80 | Sales
 Jack        | Livingston  |            80 | Sales
 Charles     | Johnson     |            80 | Sales
 Winston     | Taylor      |            50 | Shipping
 Jean        | Fleaur      |            50 | Shipping
 Martha      | Sullivan    |            50 | Shipping
 Girard      | Geoni       |            50 | Shipping
 Nandita     | Sarchand    |            50 | Shipping
 Alexis      | Bull        |            50 | Shipping
 Julia       | Dellinger   |            50 | Shipping
 Anthony     | Cabrio      |            50 | Shipping
 Kelly       | Chung       |            50 | Shipping
 Jennifer    | Dilly       |            50 | Shipping
 Timothy     | Gates       |            50 | Shipping
 Randall     | Perkins     |            50 | Shipping
 Sarah       | Bell        |            50 | Shipping
 Britney     | Everett     |            50 | Shipping
 Samuel      | McCain      |            50 | Shipping
 Vance       | Jones       |            50 | Shipping
 Alana       | Walsh       |            50 | Shipping
 Kevin       | Feeney      |            50 | Shipping
 Donald      | OConnell    |            50 | Shipping
 Douglas     | Grant       |            50 | Shipping
 Jennifer    | Whalen      |            10 | Administration
 Michael     | Hartstein   |            20 | Marketing
 Pat         | Fay         |            20 | Marketing
 Susan       | Mavris      |            40 | Human Resources
 Hermann     | Baer        |            70 | Public Relations
 Shelley     | Higgins     |           110 | Accounting
 William     | Gietz       |           110 | Accounting
             |             |           200 | Operations
             |             |           130 | Corporate Tax
             |             |           160 | Benefits
             |             |           270 | Payroll
             |             |           260 | Recruiting
             |             |           120 | Treasury
             |             |           250 | Retail Sales
             |             |           210 | IT Support
             |             |           230 | IT Helpdesk
             |             |           190 | Contracting
             |             |           170 | Manufacturing
             |             |           240 | Government Sales
             |             |           150 | Shareholder Services
             |             |           180 | Construction
             |             |           220 | NOC
             |             |           140 | Control And Credit
(122 rows)

Relational Algebra Expression:

Relational Algebra Expression: Display all departments including those where does not have any employee.

Relational Algebra Tree:

Relational Algebra Tree: Display all departments including those where does not have any employee.

Pictorial Presentation:

SQL Exercises: Display all departments including those where does not have any employee

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display all departments including those where does not have any employee - Duration

Rows:

Query visualization of Display all departments including those where does not have any employee - Rows

Cost:

Query visualization of Display all departments including those where does not have any employee - Cost

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

Previous: 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.
Next: From the following table, write a SQL query to find those employees who earn less than the employee of ID 182. Return first name, last name and salary.

What is the difficulty level of this exercise?