w3resource
SQL exercises

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. Write a query in SQL to display all departments including those where does not have any employee.

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)


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: 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.
Next: Write a query in SQL to display the first and last name and salary for those employees who earn less than the employee earn whose number is 182.

What is the difficulty level of this exercise?



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