Part B Readings Chapter 2, 7.Formulate the following queries HERE IS CODE ( SHOW
ID: 3601852 • Letter: P
Question
Part B
Readings Chapter 2, 7.Formulate the following queries
HERE IS CODE ( SHOW RESULTS PLEASE OF TABLES)
drop table workon;
drop table employee;
drop table project;
drop table division;
create table division
(did integer,
dname varchar (25),
managerID integer,
constraint division_did_pk primary key (did)
);
create table employee
(empID integer,
name varchar(30),
salary float,
did integer,
constraint employee_empid_pk primary key (empid),
constraint employee_did_fk foreign key (did) references division(did)
);
create table project
(pid integer,
pname varchar(25),
budget float,
did integer,
constraint project_pid_pk primary key (pid),
constraint project_did_fk foreign key (did) references division(did)
);
create table workon
(pid integer references project(pid),
empID integer references employee(empID),
hours integer,
constraint workon_pk primary key (pid, empID)
);
/* loading the data into the database */
insert into division
values (1,'engineering', 2);
insert into division
values (2,'marketing', 1);
insert into division
values (3,'human resource', 3);
insert into division
values (4,'Research and development', 5);
insert into division
values (5,'accounting', 4);
insert into project
values (1, 'DB development', 8000, 2);
insert into project
values (2, 'network development', 6000, 2);
insert into project
values (3, 'Web development', 5000, 3);
insert into project
values (4, 'Wireless development', 5000, 1);
insert into project
values (5, 'security system', 6000, 4);
insert into project
values (6, 'system development', 7000, 1);
insert into employee
values (1,'kevin', 32000,2);
insert into employee
values (2,'joan', 42000,1);
insert into employee
values (3,'brian', 37000,3);
insert into employee
values (4,'larry', 82000,5);
insert into employee
values (5,'harry', 92000,4);
insert into employee
values (6,'peter', 45000,2);
insert into employee
values (7,'peter', 68000,3);
insert into employee
values (8,'smith', 39000,4);
insert into employee
values (9,'chen', 71000,1);
insert into employee
values (10,'kim', 46000,5);
insert into employee
values (11,'smith', 46000,1);
insert into workon
values (3,1,30);
insert into workon
values (2,3,40);
insert into workon
values (5,4,30);
insert into workon
values (6,6,60);
insert into workon
values (4,3,70);
insert into workon
values (2,4,45);
insert into workon
values (5,3,90);
insert into workon
values (3,3,100);
insert into workon
values (6,8,30);
insert into workon
values (4,4,30);
insert into workon
values (5,8,30);
insert into workon
values (6,7,30);
insert into workon
values (6,9,40);
insert into workon
values (5,9,50);
insert into workon
values (4,6,45);
insert into workon
values (2,7,30);
insert into workon
values (2,8,30);
insert into workon
values (2,9,30);
insert into workon
values (1,9,30);
insert into workon
values (1,8,30);
insert into workon
values (1,7,30);
insert into workon
values (1,5,30);
insert into workon
values (1,6,30);
insert into workon
values (2,6,30);
a. List the total number of project 'chen' works on (must use subquery)
b. List the total number of project and total budget for each division, including division name (use outer join)
c. List the name of the employee who work on one or more projects with budget over $5000.
d. List the total number of employee whose salary is over company's average salary (subquery)
e. List the name of manager (note a manager is an employee whose empid is in division table as managerID) who work on project (use subquery)
f. List the name of employee who don't workon on project 1 (PID =1). (note if you use join, you will generate a wrong result. Use subquery may create correct result. Check out the difference).
Explanation / Answer
a.select count(pid) as total_number_of_project
from where pid in (select pid,name
from workon w
inner join employee e
on w.empID=e.empID
where e.name='chen')a
group by pid
b.select count(pid)as total_number_of_project,sum(budget) as total_budget,dname
from project p
outer join division d
on p.did=d.did
group by dname
c.select name from employee e,project p
inner join
(select empid from workon
having count(pid)>1
group by empid)a
on a.empid=e.empid
where p.budget>'$5000'
d.select count(empid)
from employee
where salary >(select avg(salary) from employee)