SQL Assignment 3 • For each employee having a dependent, retrieve the employee’s

Question

SQL Assignment 3 • For each employee having a dependent, retrieve the employee’s name along with his/her dependent name(s). • For each department, list its name along with the names of projects the department controls. • For each department, lists its name, manager’s name, and the names of projects it controls. • For each employee, list his/her first name, last name, and his/her department manager’s name. • For the department that controls more projects than any other department, retrieve its department number. • Find the name of employee who supervises more employees than any other supervisor

Figure 3 One possible database state for the COMPANY relational database schema EMPLOYEE Bdate B Smith 123456789 1965-01-09 231 Fondren, Houston, TX M 30000 John Frankin T Wong 333445555 1955-12-08 Alicia rS Wallace 987654321 1941 06-20 291 Benry BelaeX43000 890665555 455555 638 Voss. Houston, TX M 40000 888665555 JZelaya 999887777 1968-01-19 3321 Castle TXF25000 RKNorayan 000884444 1002091e HmT M 38000 20445598 loyceA Engish 453453453 1972-0731 5631 Rice Hoason. TXF 25000 AhmadV Jabbar982987987 1969-03-29 980 Dutas Houstion TXM 25000 9876543 amo E IBorg 888665555 1932-11-10, 450st nMouton. TX M 55000 NULL 445555E DE PARTMENT DEPT LOCATIONS Drame Dumber 338455 9826543

Explanation / Answer

1)  each employee having a dependent, retrieve the employee’s name along with his/her dependent name(s)

select concat(e.Fname, ', ', e.Lname) as ENAME, d.Dependent_name from employee e inner join dependent d on e.Ssn = d.Essn;

here e and d are aliases for employee and dependent table respectively.

2) For each department, list its name along with the names of projects the department controls.

select d.Dname, p.Pname from department d inner join project p on d.Dnumber = p.Dnum;

here d and p are aliases for department and project table respectively.

3)  For each department, lists its name, manager’s name, and the names of projects it controls.

select d.Dname, concat(e.Fname, ', ', e.Lname) as Mgr_Name, p.Pname from department d inner join employee e on d.Mgr_ssn = e.Ssn inner join project p on d.Dnumber = p.Dnum;

here d, e and p are aliases for department, employee and project table respectively.

5) For the department that controls more projects than any other department, retrieve its department number.

select Dnum FROM project GROUP BY Dnum ORDER BY Dnum DESC LIMIT 1;

In Oracle:

select Dnum FROM project GROUP BY Dnum ORDER BY Dnum DESC where ROWNUM <=1;

In SQL Server/ MS Access:

select TOP 1 Dnum FROM project GROUP BY Dnum ORDER BY Dnum DESC;

6) Find the name of employee who supervises more employees than any other supervisor

select concat(Fname, ', ', Lname) from Employee where Super_ssn = (select Super_ssn FROM Employee GROUP BY Super_ssn ORDER BY Super_ssn DESC LIMIT 1);

In Oracle:

select Fname, Lname from Employee where Super_ssn in (select Super_ssn FROM Employee GROUP BY Super_ssn ORDER BY Super_ssn DESC where ROWNUM <=1);

In Sql Server/ MS Access: select Fname, Lname from Employee where Super_ssn in (select TOP 1 Super_ssn FROM Employee GROUP BY Super_ssn ORDER BY Super_ssn DESC);

If you have any doubts you may ask me in the comments section..

Thanks and Happy Chegging :)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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