View the step-by-step solution to:

List of name of division and total number of employee who spent more than 200 hours in total working on projects. List the name of employee who is...

Can you please help me this? I have attached the assignment and database?

1. List of name of division and total number of employee who spent more than 200 hours in total working on projects. 2. List the name of employee who is working on the project whose budget is below the divisional average project budget. 3. List the total number of division that has 3 employees 4. List the total number of projects engineering division manager works on 9. Use alter statement to add a Feld (Feldname: total_employee, datatype: number) into division table. Then use “select * from division” to show division table. 10. Use an update statement to update values of total_employee. (Refer to the sample of SQL note of the week). 12. Increase the budget of project by 10% if there are two or more employees working on it. 13. List the name of the project has more than one employee working on it with salary greater than her/his divisional average salary (use corelated subquery) 14. List the name of the employee that makes more money than his/her divisional manager 15. List the name employees who work on all projects. (Hint: there is a very similar example of in text, which uses NOT EXISTS. The logic is to Fnd an employee that there is NO project that this employee does NOT work on)
Background image of page 1
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), 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);
Background image of page 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);
Background image of page 2
Show entire document
Sign up to view the entire interaction

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question