1. For each department, lists its name, manager\'s name, and the names of projec
ID: 3705894 • Letter: 1
Question
1. For each department, lists its name, manager's name, and the names of projects it controls.
2. For the department that controls more projects than any other department, retrieve its department number.
3. Find the name of employee whoe supervises more employees than any other supervisor.
G Google . T G Home l Chegg.com xC Chegg Study | Guided SolutxG data model Google Search x D xG how do i take a screenshot x + Cwww.cse.hcmut.edu.vn/ttqnguyet/CSDL/EbookDB.pdf One possible database state for the COMPANY relational database schema. igure 3.6 2 of 48 Ax EMPLOYEE Fname Minit Lname Sa Super ssn Dno B | sme, I : 23456789 | 1965-01-0? | 731 Fondron, Houston, TX | M | 30000 33445555 1955-12-08 638 Voss, Houston, TX M40000 J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring TX F | 25000 nnitor S Wallaco 987654321 1941-06-20 291 Berry, Bellaire, TXF 43000 Ramesh K Narayan 68884444 1962015 975 Fire Oak, Humble, TX M 38000 oyce A English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 James E Br 88866555 1937-1110 450 Stone, Houston TXM 55000 Franidin T 5555 S 21 4 5555 4 DEPARTMENT DEPT LOCATIONS 3334455!5 987654321 888665555 1988-05 22 1995-01-01 1981-06-19 WORKS ON Phumber Plooation Deum 123456789 123450709 32.5 1200 2 20.0 10.0 10.0 10 10.0 10.0 10 Stafford 20 Houston 30 Stafford 453453453 DEPENDENT 333445555 S Bdate 999887777 30 1986-04-05 Daughter 10 10.0 10 350 3445555 333445555 Thoodore 333445555 oy M 1983-10-25 Son F 1958-05.03 Spouse M 1942-02-28Spouse M 1988-01-04 Son F 1988-12-30Daughter F 1967-05-05 Spouse Abner 30 20.0 123456789 Michaal 123456789 Alce 23456789 Elizabeth 888665555 20 NULLExplanation / Answer
Solution:
Note: The query is given in SQL.
Query:
1)
SELECT Dname, Fname, Pname FROM EMPLOYEE INNER JOIN WORKS_ON ON EMPLOYEE.Ssn= WORKS_ON.Essn INNER JOIN PROJECT WORKS_ON.Pno= PROJECT.Pno. WHERE Mgr_Ssn = Essn
2)
SELECT Dnumber FROM DEPARTMENT INNER JOIN DEPT_LOCATIONS ON DEPARTMENT.Dnumber= DEPT_LOCATIONS .DnumberINNER JOIN PROJECT DEPT_LOCATIONS .Dlocation= PROJECT.Plocation WHERE COUNT(Pno) AS X MAX(X)
3)
SELECT Ssn FROM EMPLOYEE INNER JOIN WORKS_ON ON EMPLOYEE.Ssn= WORKS_ON.Essn WHERE COUNT(Essn) AS X MAX(X) GROUP BY MgrSsn
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.