Please help with these sql queries drop table workon; drop table employee; drop
ID: 3602571 • Letter: P
Question
Please help with these sql queries
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 total number of project 'chen' works on (must use subquery)
List the total number of project and total budget for each division, including division name (use outer join)
List the name of the employee who work on one or more projects with budget over $5000.
List the total number of employee whose salary is over company's average salary (subquery)
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)
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
Hi,
Kindly find the queries along with their results! Let me know if you have any doubts.
1. mysql> Select count(*) from workon where empID in (select empID from employee where name="chen");
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
2.
Note: MySQL doesn't support outer joins, so we have mocked an outer join by combining the results from inner, left and right join!
mysql> select count(*), sum(budget),dname from (select pid, budget, dname, division.did from division left join project on project.did=division.did where ((project.did) is null) union select pid, budget, dname, division.did from division right join project on project.did=division.did where ((division.did) is null) union select pid, budget, dname, division.did from division inner join project on project.did=division.did) as joint_db group by did;
+----------+-------------+--------------------------+
| count(*) | sum(budget) | dname |
+----------+-------------+--------------------------+
| 2 | 12000 | engineering |
| 2 | 14000 | marketing |
| 1 | 5000 | human resource |
| 1 | 6000 | Research and development |
| 1 | NULL | accounting |
+----------+-------------+--------------------------+
5 rows in set (0.00 sec)
3. mysql> select name from employee where empID in (select distinct empID from workon where pid in (select distinct pid from project where budget>5000));
+-------+
| name |
+-------+
| brian |
| larry |
| harry |
| peter |
| peter |
| smith |
| chen |
+-------+
7 rows in set (0.00 sec)
4. mysql> select count(*) from employee where salary> (select avg(salary) from employee);
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
5. mysql> select name from employee where empID in (select managerID from division where managerID in (Select empID from workon));
+-------+
| name |
+-------+
| kevin |
| brian |
| larry |
| harry |
+-------+
6. mysql> select name from employee where empID not in (select empID from workon where pid=1);
+-------+
| name |
+-------+
| kevin |
| joan |
| brian |
| larry |
| kim |
| smith |
+-------+
6 rows in set (0.00 sec)