w3resource

SQL Subquery Exercises: Display a set of rows to find all departments that do actually have one or more employees assigned to them

SQL SUBQUERY: Exercise-26 with Solution

From the following table, write a SQL query to find all those departments where at least one employee is employed. Return department name.

Sample table: employees


Sample table: departments


Sample Solution:

SELECT  department_name 
FROM departments 
WHERE department_id IN 
(SELECT DISTINCT(department_id) 
FROM employees);

Sample Output:

department_name
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting

Pictorial Presentation:

SQL Subqueries: Display a set of rows to find all departments that do actually have one or more employees assigned to them.

Practice Online


Query Visualization:

Duration:

Query visualization of Display a set of rows to find all departments that do actually have one or more employees assigned to them - Duration

Rows:

Query visualization of Display a set of rows to find all departments that do actually have one or more employees assigned to them - Rows

Cost:

Query visualization of Display a set of rows to find all departments that do actually have one or more employees assigned to them - Cost

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

Previous: Write a query to display the employee id, name ( first name and last name ), SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.
Next: From the following tables, write a SQL query to find those employees who work in departments located at 'United Kingdom'. Return first name.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

Ref: https://bit.ly/3cayyhJ