How to select something from sql with an or condition and a count?

Question

Tables:

• STUDENT (SNUM: INTEGER, SNAME: STRING, MAJOR: STRING)
• CLASS (CNAME: STRING, MEETS_AT: STRING, ROOM: STRING, FID: INTEGER)
• ENROLLED (SNUM: INTEGER, CNAME: STRING)

Task:

Find the names of all classes that either meet in room R128 or have five or more students enrolled.

How would you write the SQL statement to include both the above conditions? I was able to do it in two SQL statements:

SELECT CNAME
FROM   ENROLLMENT
GROUP BY CNAME
HAVING COUNT(CNAME) > 5

SELECT CNAME
FROM CLASS
WHERE ROOM = 'R128'

But how can I merge these into one statement?

Answer 1

SELECT CNAME
FROM   ENROLLMENT
GROUP BY CNAME
HAVING COUNT(CNAME) > 5 
UNION
SELECT CNAME
FROM CLASS
WΗΕRΕ RΟΟΜ = 'R128'

This way you can merge the 2 different data together

Answer 2

You can add a condition containing aggregation to the HAVING Clause together with an OR operator :

ЅΕLΕCΤ e.CΝΑΜΕ
  FRΟΜ CLASS c
  JOIN ENROLLMENT e
    ON e.CΝΑΜΕ = c.CΝΑΜΕ
 GRΟՍΡ ΒY e.CΝΑΜΕ
HAVING COUNT(e.CΝΑΜΕ) >= 5 
    OR SUM(CASE WHEN c.ROOM='R128' THEN 1 ELSE 0 END)>0

Answer 3

I would write this with direct filtering in the where clause:

select cname
from class c
where
    room = 'R128'
    or (select count(*) from enrolled e where e.cname = c.cname) > 5