SQL question

Question

I can’t answer the 7. question from this link https://www.testdome.com/d/sql-interview-questions/17

The following data definition defines an organization's employee hierarchy.

An employee is a manager if any other employee has their managerId set to the first employees id. An employee who is a manager may or may not also have a manager.

TABLE employees
id INTEGER NOT NULL PRIMARY KEY
managerId INTEGER REFERENCES employees(id)
name VARCHAR(30) NOT NULL

Can someone solve it? :)

Answer 1

SELECT name
FROM employees
WHERE (id NOT IN (SELECT managerId FROM employees WHERE (managerId IS NOT NULL)))

All tests pass here

Answer 2

Select emp.name from employees emp inner join employees mngr on emp.id = mngr.managerId

https://lornajane.net/posts/2012/sql-joining-a-table-to-itself

I started out writing a subquery myself but it did not work out.

The point is you a looking for ID's that a not present in the manager-id column.

Sorry I just google it. A join in my opnion is a relation between two tables. I was not aware I could use it for (learned something today) the same table.

A inner join looks for records that are in the first argument (id) and in the second argument (managerid)

Answer 3

There is a table employees

This table has three columns
ID : is the primary key
Manager ID : is the reference to the manager
Name : Name is the name of the employee

A manager is also an employee (they do not want to know but they are).
The manager is referenced by his "employees(id)".

TABLE employees //table name

id INTEGER NOT NULL PRIMARY KEY //identifier of employee

managerId INTEGER REFERENCES employees(id)
//the manager and his id

name VARCHAR(30) NOT NULL
//the name of the employee

Answer 4

Thank you!

Task: Write a query that selects the names of employees who are not managers.

I write it:
SELECT name FROM employees
WHERE managerId NOT IN (‘NULL’);

... but its pass 2 / fail 2

Pass: Example Case; Workers have managers;
Fail: No managers; Managers have managers;

I don’t know how I can continue to write the query... :/

Answer 5

Can someone please explain why the 'where managerid is not null' part is necessary?
Surely it would only be necessary if the id was ever 'null', but that isn't the case here. What difference does taking all the null values out make?

Or for that matter why the following does not work?

Select name from employees where id <> managerid;
Does this only compare the id of Mike to the managerid of Mike, for example.. is that why?

Answer 6

Drag and drop from the options below to create a table with three columns: ''id'' as a primary key, username and password of type varchar.

test (
id ,
username (30),
password varchar(20),
(id)
);
KEY varchar int CREATE TABLE GO PRIMARY KEY

Answer 7

WOW It’s new for me! :) I always learning something. Thank you for your answers!

I tried a lot of ways to use it in this example, but I didn’t make the correct 4/4 pass.

My 3/4 pass code was this:

SELECT name
FROM employees
WHERE (managerId NOT IN (SELECT id FROM employees WHERE (managerId <> ‘NULL’)))

I tried build in the NOT IN section second part the example from you, but I’m failed.

My task is that, I make a query what selects the names of employees who are NOT managers, including something what checking managers who has managers. I’m lost a little bit... :D

Answer 8

name of employees that are not managers

good result is mike

Using inner join this would give the correct answer

Select emp.name from employees emp inner join employees mngr on emp.id <> mngr.managerId

Inner join takes care of of null values but the test case compiler does not see that.

It can be done using subqueries

select e.name from employees e where e.id not in (select m.managerId from employees m where m.managereId is not null)

Today I learned to use an extra name for tables for clarification

select name from employees where id not in (select managerId from employees m where managerId is not null)

it also can be done without

select name from employees //select the employee name
select managerId from employees where managerId is not null //select all manager id's and skip the one that are null
The employees-id may not be in the list of managerId's

I really do not understand why we have to worry about the managers-id of the manager but if I leave out the not null part it is not returning a name.

(sorry used google to cheat
https://www.testdome.com/questions/sql/workers/423
have a look a the distinct key word also)

Answer 9

select name from employees where id not in(select managerId from employees where managerid is not null);

:)