Answered You can hire a professional tutor to get the answer.
drop drop drop drop table table table table workon; employee; project; division; create table division (did integer, dname varchar (25), managerID
Formulate the following queries. For each query, copy the question, show the code, then show the result. Note, once the syntax of your code is correct, the code always generates some result. But the result may not be correct if the LOGIC of the code is incorrect. Some please double check your code and even manually check the result by checking the data in related table(s).
These must work in Oracle. Database for oracle is attached.
1 List the name of employee and total hours he works on the project (use outer join so that employees who don't work on project will be also listed with zero project count )
2 List the name of project that ‘chen’ does not work on (request: must use NOT EXISTS)
3. List the total number of projects whose budget is above the average project budget.
4. List the name of project that has more than two employee working on it
5. List the name of the division that has more than 2 employees who work on project.
6. List the name of the managers who do not work on any projects. (note, the manager is the employee whose ID is in division table)
7. List the name of project and the name of its sponsoring division that has more budget average budget of projects.
8. List the name(s) of employees who is/are working on some project(s) but not on the project "Web development"
9. List the name of division whose average salary is higher than company's average salary
10 List the name of the division that has at least one employee who works on a project that has budget over 4000 (use subquery)
11. List the name of project whose budget is higher than project “security system”.
12 List the name of the employee whose salary is below the company average but the total working hours of project is over 100 (subquery)
13.(bonus) List the name of employee who works on a project that is sponsored by his own division (i.e., the project's DID = employee's DID, use corelated subquery) .
