1. For the department that controls more projects than any other department, ret
ID: 3706689 • Letter: 1
Question
1. For the department that controls more projects than any other department, retrieve its department number.
2. 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
If you have any doubts, please give me comment...
-- 1. For the department that controls more projects than any other department, retrieve its department number.
SELECT Dnum
FROM PROJECT P
GROUP BY Dnum
HAVING COUNT(*)>=ALL(
SELECT COUNT(*)
FROM PROJECT P1
WHERE P.Dnum <> P1.Dnum
GROUP BY Dnum
);
-- 2. Find the name of employee whoe supervises more employees than any other supervisor.
SELECT E.Fname, E.Lname
FROM EMPLOYEE E
GROUP BY E.Super_ssn, E.Fname, E.Lname
HAVING COUNT(*) >= ALL(
SELECT COUNT(*)
FROM EMPLOYEE E1
WHERE E.Super_ssn <> E1.Super_ssn
GROUP BY Super_ssn
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.