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);
:)