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 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 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?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework