Using the COMPANY database from Fundamentals of Database Systems 6th Edition, wr

Question

Using the COMPANY database from Fundamentals of Database Systems 6th Edition, write SQL query that provides the data requested:

a) For all employees of the company, return their last name, their salary, the salary of their immediate supervisor, and the salary of the highest paid employee in their department. Order your results in non-ascending order by employee salary.

b) For all departments, list the department name, the first name of the department's manager, the total number of projects that the department controls, and the total number of hours employees work on all projects controlled by that department. Order your results by the total number of hours employees work on all projects controlled by a department.

c) List all employees who have one or two dependents. Include the employee's first and last name, the number of dependents they have, the total number of hours they work, and their immediate supervisors first and last name.

The company datbase is as follows:

Employee{Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_Ssn, Dno}

Department{Dname, Dnumber, Mgr_ssn, Mgr_start_date}

Dept_Locations{Dnumber, Dlocation}

Project{Pname, Pnumber, Plocation, Dnum}

Works_On{Essn, Pno, Hours}

Dependent{Essn, Dependent_Name Sex, Bdate, Relationship}

Explanation / Answer

a)   SELECT E.Lname, E.Salary “Salary”, S.Salary “supervisor salary”, from Employee E, Employee S

      where E. Super_Ssn=M. Ssn

     order by E.salary desc;

b)  select Dname, Fname, count(Pnumber), sum(Hours)

     from Employee, Department,

     where Employee.Dno= Department. Dno

     and Department. Mgr_ssn= Employee. Ssn

     and Department. Dno= Project. Dnum

     and Works_On. Essn= Employee. Ssn

     and Works_On. Pno= Project. Pnumber

     and Project. Dnum= Employee. Dno

     order by sum(Hours);

c)  Select E.Fname, E.Lname, count(*) “No_of_dependents”, sum(Hours), S. Fname , S.Lname

     from Employee E, Employee S, Dependent, Works_On

     where E.ssn=Dependent.Essn

     and count(*)>1

     and E.ssn =Works_On.Essn

     and E. Super_Ssn=M. Ssn;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.