imagine i have 3 table employee detail, project details and Works on(where it's set that which employee will work on which project). The question is To show the average salary of all the employees work on those project which are having more than three employees.
create table Employee
(
empid varchar(5) primary key,
ename varchar(20),
address varchar(50),
phone bigint(10),
job varchar(10),
salary varchar(10)
);
insert into Employee values ('e101','Ankita','Delhi',9987654321,'Manager','12000');
insert into Employee values ('e102','Rahul','Banglore',9219849816,'Developer','80000');
insert into Employee values ('e103','Aditya','Noida',9517536842,'Chef','16000');
insert into Employee values ('e104','Rachna','Mumbai',7539561682,'Teacher','45000');
insert into Employee values ('e105','Karan','Delhi',8529517463,'Driver','18000');
create table Project
(
project_no varchar(5) primary key,
pname varchar(20),
no_of_hours int(3)
);
Insert into Project values('p101', 'Data Handling', 5);
Insert into Project values('p102', 'Game Develop', 3);
Insert into Project values('p103', 'New Recipe', 6);
Insert into Project values('p104', 'Question Paper', 9);
Insert into Project values('p105', 'Ride to Shimla', 12);
create table Works_on
(
empid varchar(5),
project_no varchar(5),
start_date date,
end_date date,
manager_name varchar(20),
primary key(empid, project_no),
foreign key(empid) references Employee(empid),
foreign key(project_no) references Project(project_no)
);
Insert into Works_on value('e101', 'p101', '2021-02-24', '2021-03-25', 'Anshika');
Insert into Works_on value('e102', 'p101', '2021-03-4', '2021-04-2', 'Anshika');
Insert into Works_on value('e103', 'p102', '2021-02-4', '2021-03-5', 'Roshan');
Insert into Works_on value('e104', 'p101', '2021-05-20', '2021-05-29', 'Anshika');
Insert into Works_on value('e105', 'p103', '2021-02-4', '2021-02-5', 'Dharmesh');
Insert into Works_on value('e103', 'p101', '2021-02-4', '2021-02-5', 'Anshika');
Insert into Works_on value('e105', 'p104', '2021-02-4', '2021-02-5', 'Ritika');
Insert into Works_on value('e105', 'p105', '2021-02-4', '2021-02-5', 'Sid');
select w.project_no project_number,
count(w.project_no) number_of_employees,
avg(e.salary) average_salary
from
Employee e, Project p, Works_on w
where
w.project_no= p.project_no
and w.empid=e.empid
group by w.project_no
having(count(w.project_no))>3;