SQL subqueries on employee Database: List the name of the department where number of employees is equal to the number of characters in the department name

SQL subqueries on employee Database: Exercise-41 with Solution

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

41. From the following tables, write a SQL query to find those departments where the number of employees is equal to the number of characters in the department name. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT *
FROM department d
WHERE length(dep_name) IN
    (SELECT count(*)
     FROM employees e
     WHERE e.dep_id = d.dep_id );

OR

SELECT d.dep_name,
       count(*)
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_name
HAVING count(*) = LENGTH (d.dep_name);

Sample Output:

 dep_id | dep_name | dep_location
--------+----------+--------------
   2001 | AUDIT    | MELBOURNE
(1 row)

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 find those employees whose net pay is more than any other employee receive. Return employee name, salary, and commission.
Next: From the following tables, write a SQL query to find those departments where the highest number of employees works. Return department name.