Reference no: EM131035666
(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
|
What approach to decision-making
: What approach to decision-making would characterize a VBM organization and how does corporate culture play into this? What should the role of the CFO be in an organization that practices VBM fully? Has VBM generally been successful for those organiza..
|
|
Management of healthcare systems
: Define HR and the role they play within an organization, with the employee, and the employer. What are a few strategies used to motivate employees? Describe what you would do if one of your employee’s performances was below the standard.
|
|
Designing a social networking website
: Your employer has tasked you with designing a social networking website. You are free to design either a blog, a micro-blog, or a wiki.
|
|
Patients is there any challenges to standardization
: While standardization has certainly helped to maintain and improve the quality of care given to patients is there any challenges to standardization? Do these standards depending on the HMO?
|
|
Combination of join and subqury or correlated
: 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).
|
|
Evaluated in consideration for an award
: Why is it important to provide detailed instructions to bidders regarding how their proposals will be handled and evaluated in consideration for an award?
|
|
Formulate the problem as integer linear programming model
: A personnel manager must schedule the police force in such a way as to satisfy the staffing requirements shown in the table below TIME Minimum number of officers required Midnight. determine how many officers should work each shift in order to minimi..
|
|
Provide a explanation of all hoosier burger major subsystems
: Explain which of the four approaches to system development found on pages 20-24 you believe should be used by Hoosier Burger. Support your explanation with examples from your own experiences and/or scholarly sources.
|
|
Equation to find harley unlevered beta
: UNLEVERED BETA Harley Motors has $10 million in assets, which were financed with $2 million of debt and $8 million in equity. Harley's beta is currently 1 2, and its tax rate is 40%. Use the Hamada equation to find Harley's unlevered beta, bU.
|