w3resource

SQL Exercise: List the employee who are not working under a manager

SQL employee Database: Exercise-26 with Solution

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

26. From the following table, write a SQL query identify the employees who do not report to a manager. Return employee name, job name.

Sample table: employees


Pictorial Presentation:

SQL exercises on employee Database: List the employee name, and job_name who are not working under a manager

Sample Solution:

SELECT e.emp_name,
       e.job_name
FROM employees e
WHERE manager_id IS NULL;

Sample Output:

 emp_name     | job_name
--------------+--------------
  KAYLING     | PRESIDENT

Explanation:

The said query in SQL that retrieves a list of all employees who do not have a manager, along with their name and job position from the 'employees' table.

The WHERE clause that filters the results so that, it only includes the employees who do not have a value in the "manager_id" column, which indicates whether or not the "manager_id" column is null.

Relational Algebra Expression:

Relational Algebra Expression: List the employee name, and job_name who are not working under a manager.

Relational Algebra Tree:

Relational Algebra Tree: List the employee name, and job_name who are not working under a manager.

Practice Online


Sample Database: employee

employee database structure

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

Previous SQL Exercise: List the employees who joined before 1 April 1991.
Next SQL Exercise: List all the employees joined on 1st may 91.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

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

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook