SQL Interview Questions_part 3-1

 

7. Workers

[Problem]

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

An employee is a manager if any other employee has their managerId set to this employee's id. That means John is a manager if at least one other employee has their managerId set to John's id.

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

 

[My Answer]

#first_try
select name 
from employees
where managerId is null;
 
 
#Answer
select name 
from employees
where id not in
(select managerId from employees where managerId is not null);

Modify the provided SQLite create table statement so that:

• Only users from the users table can exist within users_roles.
• Only roles from the roles table can exist within users_roles.
• A user can only have a specific role once.

#first_try
CREATE TABLE users_roles (
  userId INTEGER NOT NULL,
  roleId INTEGER NOT NULL,
  FOREIGN KEY (userId) REFERENCES users (id),
  FOREIGN KEY (roleId) REFERENCES roles (id));
 
 
#Answer
CREATE TABLE users_roles (
  userId INTEGER NOT NULL,
  roleId INTEGER NOT NULL,
  FOREIGN KEY (userId) REFERENCES users (id),
  FOREIGN KEY (roleId) REFERENCES roles (id),
  UNIQUE (userId, roleId));