How can I solve this exercise with just one SQL query?-CodePudding

I'm struggling to find a solution to this exercise. I really don't understand how to implement "the total number of lectures provided in October 2014" with " select the code of the classroom" within one query (I attach the photo and the database tables):

Unfortunately I cannot attach my pics directly, sorry for the links and other errors that I could have made. I attach also the code that I wrote, just for clarity, but I already know that it's wrong:

SELECT L0.RoomID,
       COUNT(*),
       MAX(AttendingStudent#)
FROM   LECTURE L0
WHERE  L0.RoomID IN(
  SELECT L1.RoomID
  FROM   CLASSROOM C, LECTURE L1
  WHERE  C.RoomID = L1.RoomID 
  AND C.Video_Kit = 'yes' 
  AND L1.Date >= 01/01/2014 
  AND Date <= 31/12/2014
  GROUP BY L1.RoomID
  HAVING COUNT(DISTINCT L1.CCode) >= 20
)
AND Date >= 01/10/2014 
AND Date <= 31/10/2014
GROUP BY L0.RoomID, L0.Date, L0.StartHour

CodePudding user response：

You have most of the parts but you just need to put them together in the correct way.

It may be easier to think about if you break the question down into multiple parts:

For the classrooms having video kit ... select the code of the classroom

SELECT roomid
FROM   classroom
WHERE  video_kit = 'yes'

and where at least 20 different courses have been held in the whole of 2014

SELECT roomid
FROM   classroom
WHERE  video_kit = 'yes'
AND    roomid IN (
         SELECT roomid
         FROM   lecture
         WHERE  "Date" >= DATE '2014-01-01'
         AND    "Date" <  DATE '2015-01-01'
         GROUP BY roomid
         HAVING COUNT(DISTINCT ccode) >= 20
       )

select ... the total number of lectures provided in October 2014

SELECT roomid,
       ( SELECT COUNT(*)
         FROM   lecture l
         WHERE  c.roomid = l.roomid
         AND    "Date" >= DATE '2014-10-01'
         AND    "Date" <  DATE '2014-11-01'
       ) AS num_lectures
FROM   classroom c
WHERE  video_kit = 'yes'
AND    roomid IN (
         SELECT roomid
         FROM   lecture
         WHERE  "Date" >= DATE '2014-01-01'
         AND    "Date" <  DATE '2015-01-01'
         GROUP BY roomid
         HAVING COUNT(DISTINCT ccode) >= 20
       )

select ... the maximum number of students who participated in such lectures.

SELECT roomid,
       ( SELECT COUNT(*)
         FROM   lecture l
         WHERE  c.roomid = l.roomid
         AND    "Date" >= DATE '2014-10-01'
         AND    "Date" <  DATE '2014-11-01'
       ) AS num_lectures,
       ( SELECT MAX(AttendingStudent#)
         FROM   lecture l
         WHERE  c.roomid = l.roomid
         AND    "Date" >= DATE '2014-10-01'
         AND    "Date" <  DATE '2014-11-01'
       ) AS max_attending_students
FROM   classroom c
WHERE  video_kit = 'yes'
AND    roomid IN (
         SELECT roomid
         FROM   lecture
         WHERE  "Date" >= DATE '2014-01-01'
         AND    "Date" <  DATE '2015-01-01'
         GROUP BY roomid
         HAVING COUNT(DISTINCT ccode) >= 20
       )

Now, there are more efficient ways to write the last two parts using a join condition but, since this is a homework question I will let you explore that and come up with your own better solution.