Using the schema below:

STUDENT(Sid, Sname, Gender, Age, EntranceYear, GPA)

DEPT(Did, Dname)

PROF(Pid, Pname, Did, Birth_Date)

COURSE(Cno, Did, Cname)

SECTION(Sectno, Did, Cno, Pid)

ENROLL(Eid, Sid, Cno, Sectno, Grade)

Which SQL query retrieves department names that have fewer than 5 professors?

Question

Using the schema below:

STUDENT(Sid, Sname, Gender, Age, EntranceYear, GPA)

DEPT(Did, Dname)

PROF(Pid, Pname, Did, Birth_Date)

COURSE(Cno, Did, Cname)

SECTION(Sectno, Did, Cno, Pid)

ENROLL(Eid, Sid, Cno, Sectno, Grade)

Which SQL query retrieves department names that have fewer than 5 professors?

Answer

SELECT Dname   
 FROM DEPT   
 WHERE Did IN (SELECT Did FROM PROF GROUP BY Did HAVING COUNT(*) < 5);

Answer

SELECT Dname   
 FROM DEPT   
 WHERE Did NOT IN (SELECT Did FROM PROF GROUP BY Did HAVING COUNT(*) >= 5);

Answer

SELECT Dname   
 FROM DEPT   
 WHERE Did IN (SELECT Did FROM STUDENT GROUP BY Did HAVING COUNT(*) < 5);

Answer

SELECT Dname   
 FROM DEPT   
 WHERE Did IN (SELECT Did FROM PROF GROUP BY Did HAVING COUNT(*) > 5);

Crowdly