load file 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, empID integer, hours integer, constraint workon_pk primary key (pid, empID), constraint workon_pid_fk foreign key (pid) references project(pid), constraint workon_empid_fk foreign key (empID) references employee(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 employee values (12,'joan', 48000,1); insert into employee values (13,'kim', 49000,2); insert into employee values (14,'austin', 46000,1); insert into employee values (15,'sam', 52000,5); insert into employee values (16,'Justin', 62000,2); insert into employee values (17,'Nacy', 52000,1); insert into employee values (18,'Marilyn', 52000,5); insert into employee values (19,'Kristie', 52000,1); insert into employee values (20,'John', 52000,3); insert into employee values (21,'Alex', 69000,1); insert into employee values (22,'Phil', 72000,2); insert into employee values (23,'Steve', 74000,4); insert into employee values (24,'Jenna', 69000,1); insert into employee values (25,'Alan', 62000,2); insert into employee values (26,'Julia', 69000,4); insert into employee values (27,'Sandra', 72000,4); insert into employee values (28,'Joe', 74000,4); insert into employee values (29,'karl', 69000,5); insert into employee values (30,'grace', 62000,4); 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 (1,8,30); insert into workon values (2,9,30); insert into workon values (1,9,30); insert into workon values (2,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); insert into workon values (2,12,30); insert into workon values (3,13,30); insert into workon values (4,14,20); insert into workon values (4,15,40); insert into workon values (2,19,30); insert into workon values (1,19,30); insert into workon values (5,18,30); insert into workon values (3,17,30); insert into workon values (4,25,30); insert into workon values (3,16,30); insert into workon values (2,16,30); insert into workon values (2,22,30); insert into workon values (3,23,30); insert into workon values (4,24,20); insert into workon values (6,25,40); insert into workon values (3,21,40); insert into workon values (4,26,20); insert into workon values (4,27,40); insert into workon values (2,27,30); insert into workon values (1,26,30); insert into workon values (5,26,30); insert into workon values (3,26,30); insert into workon values (4,28,30); insert into workon values (3,28,30); insert into workon values (2,29,30); insert into workon values (2,30,30); insert into workon values (3,30,30); insert into workon values (4,21,20); insert into workon values (6,22,40); insert into workon values (1,30,40);insert into workon values (3,13,30); insert into workon values (4,14,20); insert into workon values (4,15,40); insert into workon values (2,19,30); insert into workon values (1,19,30); insert into workon values (5,18,30); insert into workon values (3,17,30); insert into workon values (4,25,30); insert into workon values (3,16,30); insert into workon values (2,16,30); insert into workon values (2,22,30); insert into workon values (3,23,30); insert into workon values (4,24,20); insert into workon values (6,25,40); insert into workon values (3,21,40); insert into workon values (4,26,20); insert into workon values (4,27,40); insert into workon values (2,27,30); insert into workon values (1,26,30); insert into workon values (5,26,30); insert into workon values (3,26,30); insert into workon values (4,28,30); insert into workon values (3,28,30); insert into workon values (2,29,30); insert into workon values (2,30,30); insert into workon values (3,30,30); insert into workon values (4,21,20); insert into workon values (6,22,40); insert into workon values (1,30,40); insert into workon values (3,9,10); insert into workon values (4,9,20);

Part A a1. Use CREATE statement to create a table Client (AcctNo, ClientName, phone). Note AcctNo is primary key and you must define a constraint for this primary key in CREATE statement. Use Char for the data type of phone. Show the statement.

a2. Use INSERT statement to add two client records (insert two clients with different account number, make up your own data for clients). Show the INSERT statements and use select statement to show the contents of Client table.

a3. Use ALTER statements to add a foreign key ClientAcct, into the Project table, which has values of the AcctNo of Client . So that table Client has a 1:M relationship with table Project. Note, you need to use TWO ALTER statements, one for adding ClientAcct into Project table; one for adding foreign key constraint named as project_ClientAcct_fk into Project table. Show the ALTER statements.

a4 use an update statement to assign a client account number to the project(s) with the name that has word 'system' . Write another update statement to assign another client account number to the rest of the projects. Show result.

a5. Test the foriegn key constraint by inserting a new project with wrong client account number. Show the result.

a6. User ALTER statement to Add an attribute Project_Count into Employee table (data type to be integer, refer to the data type used for Workon table (hours) in loadDB file). Show result.

a7. Use UPDATE statement to fill the value of Project_count of each employee record in Employee table. Namely, add the count of total number of projects an employee works on into Project_count in Employee table for each employee. Hint: you need a subquery in Update statement as follows Update _________ Set _____ = (select count (pid) ...... ) Show the contents of Employee after update.

a8. Create a table Promotion_list (EMPID, Name, Salary, DivisionName).

a9. Load Promotion_list with the information of employees who make less than company average and work on at least 2 projects. (Hint use INSERT INTO SELECT statement ). Show the code and result.

Part B

b1 Create a view named as Promotion_candidates that lists empid, name, salary, did of employees who work on more than 2 projects and salary is below their divisional average.

b2. Increase the budget of a project by 5% if there is a manager working on it .

b3. List the name of employee who does not work on any project sponsored by his/her own division.

b4. List the name of project with the budget higher than ALL projects from 'human resource' division.

b5. List the name of employee who work on more projects than employee 'Alan, Show three columns in result: name of employee, project count of employee, Alan's project count.

b6. List The name of division that has employee(s) who work on other division's project. (correlated subquery)

b7. List the name of project that enginnering division committed more man-hours than marketing division.

b8 (bonus). List the name of employee who works ONLY with his/her divisional colleagues on project(s). (Hint, namely, the employee (e) works on project, but there NOT EXISTS a project (p) that (e) works on (... p.pid = w.pid and w.empid = e.empid...) and another employee (ee) also works on but they are from different divisions (e.did <> ee.did.) .