PLEASE DO NOT ANSWER JUST ONE. ANSWER ALL AND PLEASE SHOW RESULTS. BEST ANSWER GETS THUMBS UP

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);

List the name of the division that has more than one employee whose salary is greater than her/his divisional average salary (use corelated subquery)

List the name of the employee that has the lowest salary in his division and list the total number of projects this employee is work on (use corelated subquery)

list the name of employee who do more projects than his/her divisional colleagues (hint : use group by did , empid, name having count(pid)> ( here is the correlated subquery that select count(pid) from es divisional colleagues workon record) )

list the name of divisions that have/sponsor project(s) employee 'chen' works on (hint on logic of code: select dname where did = did of the project (p) that chen works on).

(bonus) List the name of employee who work on all project (hint this means that there not exists a project the employee does not work on, Use NOT EXISTS, note, there is a similar query in chapter 8).