Given table: <pre><code>CREATE TABLE employee (id

Given table:

CREATE TABLE employee
(id integer NOT NULL,
department_id integer,
chief_id integer,
name varchar(100));

Which query will print all employees who do not have chief working in the same department.

SELECT a.* FROM employee a LEFT JOIN employee b ON (b.id = a.chief_id and b.department_id = a.department_id) WHERE b.id IS NULL
SELECT id FROM employee a1 WHERE chief_id IS NULL AND (SELECT department_id FROM employee WHERE id=a1.chief_id)=department_id;
SELECT id FROM employee a1 WHERE a1.chief_id IS NULL OR a1.chief_id NOT IN (SELECT id FROM employee WHERE department_id=a1.department_id);

Explanation

Query should print all employees:
- who either do not have chief at all
- or whose chief works in department different from employee's one
Choices with sub-queries will not print those employees who do not have chief at all.

Подобається

Ввійдіть щоб вподобати
Комментувати

Ввійдіть щоб прокоментувати
Поширити

Tweet

Тести з
SQL

Приєднуйся і вивчай SQL

або Дізнайся більше про Тести з SQL онлайн

Слідкуй за CodeGalaxy

Мобільний додаток Beta

Зворотній Зв’язок