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

Обратная Связь