SQL quiz. 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.

Like

Login in to like
Comment

Login in to comment
Share

Tweet

SQL
Quiz

or Read more about SQL Quizzes

Follow CodeGalaxy

Mobile Beta

Send Feedback