• Division (DID, dname, managerID)

  Employee (empID, name, salary, DID)

  Project (PID, pname, budget, DID)

  Workon (PID, EmpID, hours)

  Formulate the following queries. You must copy each query question in your submission even you do not have an answer to it.

  1. Create a view called "high_cost_Division" that shows the ID and the name of divisions that have less than 6 employees but total salary is over $300,000. The view also include the each division's headcount and total salary. Show the code of view create statement and use select statement to show the contents of the view.

  2. Create a view named as 'letgo_list' that has the name of employee and his/her salary, who don't work on any project but salary is over company's average. Also show employee's division name. Show the code of view create statement and use select statement to show the contents of the view.

  3. Show the ID, name and division name of the employee whose salary is higher than ‘Justin ‘ and 'Sam'. (hint; use subquery)

  4. Show the name of each employee in marketing division whose salary is above his/her divisional average and and works on more than one projects. Also show the total number of projects she/he works on, as well as the total hours he/she spent on the project(s).

  5. List the name of project that ‘Chen’ works on but 'Sam' does not work on. (hint: use two inner select statements /sub-queries, use key word 'IN' and 'NOT IN').

  6. List the name and budget of the project if its budget is over average budget and has more than 3 people working on it, Also show the average budget in query result.

  7 List the total number of project 'Larry' does not work on (must use subquery for this query)

  8. List the name of employee who work on a project sponsored by accounting division but does not work on a project sponsored by engineering division ( refer to the hint in query (5)).

  9. List the name of the project that has most people working in (hint use a subquery at HAVING clause).

  10. List the total number of employee whose salary is over company's average salary (subquery) for each division, including division name.

  11. List the name of manager (note a manager is an employee whose empid is IN division table as managerID) who works on project with 'Brian' (use subquery to find PID of project Brian works on)

  12. (a) List the name of employees who don't work on project "web development" (b) Also write an explanation on why using JOIN of employee, workon and project tables does not work for this query).

  13. List the name of employee who work on more projects than Grace.

  14. List name of project whose budget is SECOND lowest, list its budget and highest budget, as well as the difference between highest and 2nd highest budgets (hint, refer to the video lecture).

  15. List the name of project that 'chen' works on but not from chen's division. (hint/pseudocode: find ID of project that is IN (list of ID of project chen works on returned by a subquery) AND this project's DID NOT IN (list of DID of chen's returned by a subquery)

  16. (Bonus) List the name of employee who make highest salary in his/her division (hint: use correlated subquery) .