+ 3

SQL 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? :)

18th Jan 2018, 8:24 PM
András Soós
András Soós - avatar
9 odpowiedzi
+ 4
SELECT name FROM employees WHERE (id NOT IN (SELECT managerId FROM employees WHERE (managerId IS NOT NULL))) All tests pass here
12th Feb 2018, 9:07 AM
Елена Рагель
Елена Рагель - avatar
+ 3
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)
18th Jan 2018, 9:59 PM
sneeze
sneeze - avatar
+ 2
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
18th Jan 2018, 8:50 PM
sneeze
sneeze - avatar
+ 1
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... :/
18th Jan 2018, 9:31 PM
András Soós
András Soós - avatar
+ 1
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?
14th Apr 2018, 4:36 PM
Tom
+ 1
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
6th Oct 2018, 12:01 PM
VIDYASAGAR JHA
VIDYASAGAR JHA - avatar
0
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
19th Jan 2018, 10:27 AM
András Soós
András Soós - avatar
0
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)
23rd Jan 2018, 10:19 PM
sneeze
sneeze - avatar
0
select name from employees where id not in(select managerId from employees where managerid is not null); :)
12th Feb 2018, 6:14 AM
Prakhar Rastogi
Prakhar Rastogi - avatar