Readings:chapters 2,7,8 solution

$14.99

Category:

Description

If your last name’s initial is from A to K, you MUST answer questions 2, 4, 6, 8. If your last name’s initial is from L to Z you MUST answer questions 1,3,5,7. You must number and copy each question even you do not have answer. No credit will be given to your work if you do not following this rule. You MUST do the work independently. 

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

SELECT division.dname, Count(employee.empID) AS CountOfempID, Sum(workon.hours) AS SumOfhours

FROM (division INNER JOIN employee ON division.did = employee.did) INNER JOIN workon ON (employee.empid = workon.empid) AND (employee.empID = workon.empID)

GROUP BY division.dname HAVING (((Sum(workon.hours))>200));

  1. List name of employee who make more salary than company’s average salary and also list his/her division name.

SELECT employee.empID, employee.name, division.dname

FROM division INNER JOIN employee ON division.did = employee.did

WHERE (((employee.salary)>(select avg(salary) from employee)));

  1. List the name of employee who is working on the project whose budget is below the divisional average project budget.

SELECT employee.name

FROM employee INNER JOIN project ON employee.did = project.did

WHERE project.budget<(SELECT Avg(budget) FROM project);

  1. List the name of project that some employee(s) who is/are working on it make less than divisional average salary.

SELECT project.pname, employee.name

FROM (division INNER JOIN employee ON division.did = employee.did) INNER JOIN project ON division.did = project.did

WHERE (((employee.salary)<(select avg(salary) from employee)));

  1. List the total number of division that has 3 employees

Select count (DID) as totalDivision from Project, Employee where Employee.DID=Project.DID having (count(division.DID=3));

  1.  List the total number of project that has 2 employees working on it

Select count(PID) as totalProject from Workon, Employee where Employee.EmpID=Workon.EmpID having (count(Workon.EmpID=2));

  1. List the total number of projects engineering division manager works on.

Select PName from Project, Workon, Employee where Employee.EmpID= Workon.EmpID AND Workon.PID= Project.PID  AND division.dName= ‘ engineering ‘;

  1. List the total number of projects that ‘chen’ does not work on.

Select PName from Project, Workon, Employee where Employee.EmpID= Workon.EmpID AND Workon.PID= Project.PID  AND Employee.Name!= ‘chen’;

Everyone answer the following questions INDEPENDENTLY:

  1. Use alter statement to add a field (fieldname: total_employee, datatype: number) into division table. Then use “select * from division” to show division table.

ALTER TABLE division
ADD total_employee number;

select * from division;

division
did dname managerID total_employee
1 engineering 2
2 marketing 1
3 human resource 3
4 Research and development 5
5 accounting 4

 

10 Use an update statement to update values of total_employee.  (Refer to the sample of SQL note of the week).

UPDATE division set total_employee=5 where managerid=2;

  1. Increase the budget of project by 10% if there are two or more employees working on it.

SELECT Count(Workon.PID) AS totalProject, [project].[budget]+[project].[budget]*0.1 AS [Increased Budget]

FROM Employee, project INNER JOIN Workon ON (project.pid = Workon.pid) AND (project.pid = Workon.pid)

WHERE (((Employee.EmpID)=[Workon].[EmpID]))

GROUP BY [project].[budget]+[project].[budget]*0.1

HAVING (((Count([Workon].[EmpID]=2))<>False));

  1. 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)

SELECT project.pname, division.did, employee.salary

FROM (division INNER JOIN employee ON division.did = employee.did) INNER JOIN project ON division.did = project.did

GROUP BY project.pname, division.did, employee.salary

HAVING (([Employee].[Salary]>(Select avg(Salary) from Employee)) AND ((Count(employee.empID))>1));

  1. List the name of the employee that makes more money than his/her divisional manager

SELECT employee.name, division.managerID, employee.empID

FROM division INNER JOIN employee ON division.did = employee.did

WHERE (((employee.salary)<(select avg(salary) from employee)));

  1. 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 find an employee that there is NO project that this employee does NOT work on)

SELECT NAME from EMPLOYEE WHERE NOT EXISTS (SELECT did FROM division WHERE NOT EXISTS ( SELECT * FROM project WHERE did=did) );