Write an SQL Query for the following question if an employee

Write an SQL Query for the following question:

if an employee works on every project, then retrieve the name of the employee

Solution

-------------------------------------------------------------

--This solution has it own data for better understanding

--------------------------------------------------------------
--Creating table for projects
CREATE TABLE projects
(
Project_id int primary key,
Project_name varchar(50)

);

insert into projects values( 101,\'DB design\')
insert into projects values( 102,\'UI design\')
insert into projects values( 103,\'testing\')
insert into projects values( 104,\'support\')

select * from projects -- total 4 projects runnings
-------------------------------------------------------------
--Creating table for employees
CREATE TABLE Employees
(
Emp_id int primary key,
Emp_name varchar(50)

);
insert into Employees values( 1   ,\'Syed Abbas\')
insert into Employees values( 2   ,\'Kim Abercrombie\')
insert into Employees values( 3   ,\'Hazem Abolrous\')
insert into Employees values( 4   ,\'Pilar Ackerman\')
insert into Employees values( 5   ,\'Jay Adams\')

select * from Employees
-------------------------------------------------------------
--Creating table for projectMapping
create table projectMapping
(
emp_id int not null,
project_id int not null
);

insert into projectMapping values (1,101)
insert into projectMapping values (1,102)
insert into projectMapping values (1,103)
insert into projectMapping values (1,104)
insert into projectMapping values (2,103)
insert into projectMapping values (3,101)
insert into projectMapping values (3,102)
insert into projectMapping values (3,103)
insert into projectMapping values (3,104)
insert into projectMapping values (4,101)
insert into projectMapping values (4,102)
insert into projectMapping values (5,101)
insert into projectMapping values (5,103)

select * from projectmapping --so employees 1,3 are in all projects
-------------------------------------------------------------
select distinct a.emp_id, a.emp_name from employees a
inner join projectMapping b on a.emp_id = b.emp_id
group by a.emp_id,a.emp_name
having count(project_id) = (select count(distinct project_id) from projects)
-------------------------------------------------------------
--result set
emp_id   emp_name
1   Syed Abbas
3   Hazem Abolrous
-------------------------------------------------------------