What would be the query for the employee who worked for all the department. here department and employee have many to many cardinality.
The tables are:
CREATE TABLE employees
(
employee_id int NOT NULL CONSTRAINT pk_employees PRIMARY KEY,
employee_name nvarchar(128) NOT NULL CONSTRAINT uk_employees_employee_name UNIQUE
);
CREATE TABLE departments
(
department_id int NOT NULL PRIMARY KEY,
department_name nvarchar(128) NOT NULL CONSTRAINT uk_departments_department_name UNIQUE
);
CREATE TABLE department_employees
(
department_id int NOT NULL CONSTRAINT fk_department_employees_departments REFERENCES departments(department_id),
employee_id int NOT NULL CONSTRAINT fk_departement_employees_employees REFERENCES employees(employee_id),
CONSTRAINT pk_deparment_employees PRIMARY KEY (department_id, employee_id)
)
Sample data:
INSERT INTO employees
VALUES (1, 'John Doe'), (2, 'Jane Doe'), (3, 'William Doe'), (4, 'Margaret Doe')
INSERT INTO departments
VALUES (1, 'Accounting'), (2, 'Humman Resources'), (3, 'Marketing')
INSERT INTO department_employees
VALUES
(1, 1), (2, 1), (3, 1),
(2, 2), (2, 3),
(3, 3), (3, 4)
Expected results:
+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
| 1 | John Doe |
+-------------+---------------+
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…