DB Lab 1 Discprepancies

q12.sql

Your Answer:

select distinct S1.time_slot_id, S2.time_slot_id
from time_slot as S1, time_slot as S2 
where S1.day = S2.day and S1.time_slot_id != S2.time_slot_id
and numrange((60*S1.start_hr+S1.start_min), 60*S1.end_hr+S1.end_min) 
&& numrange(60*S2.start_hr+S2.start_min, 60*S2.end_hr+S2.end_min)

Instructor Answer:

select  S1.time_slot_id as timeslot1, S2.time_slot_id as timeslot2
       from time_slot as S1, time_slot as S2
       where S1.time_slot_id <> S2.time_slot_id and
                   S1.day = S2.day and 
       numrange((60*S1.start_hr+S1.start_min), 60*S1.end_hr+S1.end_min) &&  
       numrange(60*S2.start_hr+S2.start_min, 60*S2.end_hr+S2.end_min)

q13.sql

Your Answer:

with clashes as
(select distinct S1.time_slot_id, S2.time_slot_id
from time_slot as S1, time_slot as S2 
where S1.day = S2.day and S1.time_slot_id != S2.time_slot_id
and numrange((60*S1.start_hr+S1.start_min), 60*S1.end_hr+S1.end_min) && numrange(60*S2.start_hr+S2.start_min, 60*S2.end_hr+S2.end_min))

select distinct T1.ID, T1.name from 
(takes natural join section natural join student) as T1, (takes natural join section natural join student) as T2 
where T1.ID = T2.ID and T1.year = T2.year and T1.semester = T2.semester and
((T1.time_slot_id, T2.time_slot_id) in (select * from clashes) or T1.time_slot_id = T2.time_slot_id);

Instructor Answer:

with clashingslots as (select  S1.time_slot_id as timeslot1, S2.time_slot_id as timeslot2
         from time_slot as S1, time_slot as S2
         Where S1.time_slot_id <> S2.time_slot_id and
                     S1.day = S2.day and 
   numrange((60*S1.start_hr+S1.start_min), 60*S1.end_hr+S1.end_min) &&  
  numrange(60*S2.start_hr+S2.start_min, 60*S2.end_hr+S2.end_min) ),
  takes_slot as 
         (select  ID, takes.year, takes.semester, takes.course_id, takes.sec_id, time_slot_id
          From takes, section
          Where takes.course_id = section.course_id and takes.sec_id = section.sec_id and 
                     takes.year = section.year and takes.semester = section.semester)
select  T1.ID
       from takes_slot as T1, takes_slot as T2, clashingslots as CS
       where T1.ID = T2.ID and T1.year = T2.year and T1.semester = T2.semester 
                 and T1.time_slot_id = CS.timeslot1 and T2.time_slot_id = CS.timeslot2
                 and not  (T1.course_id = T2.course_id and T1.sec_id = T2.sec_id)

q9.sql

Your Answer:

with stable as 
(select course_id, sec_id, year, semester, count(ID) as numstu from takes
group by course_id, sec_id, year, semester),
enrol(max_enroll) as 
(select max(numstu) from stable)
select course_id, sec_id, year, semester, numstu from stable, enrol
where numstu = enrol.max_enroll;

Instructor Answer:

with enrollment as 
         (select course_id, sec_id, year, semester, count(*) as number
          from  takes
          group by course_id, sec_id, year, semester),
      maxnumber as (
          select max(number) as maxnum
          from enrollment
      )
      select * 
      from enrollment, maxnumber 
      where number = maxnum