SQL subqueries on employee Database: List the department where there are no employees

Last update on May 30 2022 07:17:12 (UTC/GMT +8 hours)

SQL subqueries on employee Database: Exercise-77 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

77. From the following table, write a SQL query to find those departments where no employee works. Return department ID.

Sample table: employees

Sample table: department

Sample Solution:

SELECT b.dep_id,
       count(a.dep_id)
FROM department b
LEFT OUTER JOIN employees a ON a.dep_id=b.dep_id
GROUP BY b.dep_id
HAVING count(a.dep_id) = 0;

Sample Output:

 dep_id | count
--------+-------
   4001 |     0
(1 row)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Structure of employee Database:

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

Previous: From the following table, write a SQL query to count the number of employees who work as a manager. Return number of employees.
Next: SQL Exercises, Practice, Solution

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