(Copy each question including its number, even you DO NOT have an answer for it, readings chapters 2,7,8)
Formulate the following queries: (note, for some queries, you may need to use the combination of join and subqury or correlated subquery, together with group by and having clauses).
If your last name's initial is from A to K, you MUST answer questions 1, 3,5,7. If your last name's initial is from L to Z you MUST answer questions 2,4,6,8.You must number and copy each question even you do not have answer No credit will be given to your work if you do following this rule.
1. Create a view named as EmpInfo that shows the information of each employee and the total number of project he/she participate. Note, for the employee who do not participate any project, the count is 0.
2. Create a view named as EmpInfo that shows the information of each employee and the total project working hours he/she has. Note, for the employee who do not participate any project, the project working hour is 0.
3. List the name of employee who is working on the project whose budget is below the division average project budget.
4. List the name of project that some employee(s) who is/are working on it make less than divisional average salary.
5. List the total number of division that has 3 employees
6. List the total number of project that has 2 employees working on it
7. List the total number of projects accounting division manager works on.
8. List the total number of projects that 'joan' does not work on.
Everyone answer the following questions:
9. Create a table VEHICLES (VNo, model, year, DID). This table is to store information about vehicles each division has. A division can have several vehicles. (Refer to the LoadDb file for SQL Create statement. Pay attention to the forign key definition). Show the CREATE statement.
10 Use INSERT statement to load 3-5 rows into VEHICLE table. Show the rows using Select statement.
12. List the total number of employees who have more than 100 project working hours in total.
13. List the name of the division that has more than one employee whose salary is greater than her/his divisional average salary (use corelated subquery)
14. List the name of the employee that has the lowest salary in his division and list the total number of projects this employee is work on (use corelated subquery)
15. list the name of divisions that have/sponsor project(s) employee 'chen' works on.
16. (Bonus) list the name of employee who do more projects than his/her divisional colleagues (hint : use group by did , empid, name having count(pid)> ( here is the correlated subquery that select count(pid) from e's divisional colleagues workon record) )
Attachment:- loadDB.sql