Databases SQL Reporting by Example Day 2: Advanced Selecting Student Schedule

mykolash
mykolash
12,955 Points

Have an idea how to improve building schedule

Hi there,

what do you think about improving a bit building schedule example, eh?

-- Generate a schedule for Rex Rios.

SELECT Periods.id AS '#',
       Periods.start_time AS "Period starts at", 
       Periods.duration || " minutes" AS "Period duration", 
       --, TIME(Periods.start_time, "+ Periods.duration") AS "Period ends at" 
       Classes.room_id AS "Room",
       Subjects.name AS "Subject",
       Teachers.last_name AS "Teacher"
FROM Periods
  INNER JOIN Classes ON Periods.id = Classes.period_id
  INNER JOIN Subjects ON Classes.subject_id = Subjects.id
  INNER JOIN Schedule ON Classes.id = Schedule.class_id
  INNER JOIN Students ON Schedule.student_id=Students.id
  INNER JOIN Teachers ON Classes.teacher_id=Teachers.id
WHERE Students.first_name="Rex" AND Students.last_name="Rios"
ORDER BY Periods.id ASC;

Link for the lesson

Ben Deitch

PS: Not sure how to improve column in commented row:

       --, TIME(Periods.start_time, "+ Periods.duration") AS "Period ends at" 

cause time format seems to be provided in a bit buggy manner - "9:05" instead of "09:05" and "1:15" instead of "13:15" - may it be done in purpose, in order to UPDATE Periods table? And yes, imho, we do need this column in case Rex Rios will count seconds to the end of the too boring period - you know, all we did once! ;-)

2 Answers

Steven Parker
Steven Parker
203,443 Points

That's a nice enhancement. Good job. :+1:

And it's a bit of a mess to look at, but this will give you the ending time in the same format as the start time:

       LTRIM(STRFTIME('%H:%M',
                      SUBSTR('0'||Periods.start_time, -5),
                      '+'||Periods.duration||' minutes',
                      CASE WHEN
                        CAST(STRFTIME('%H',
                                      SUBSTR('0'||Periods.start_time, -5),
                                      '+'||Periods.duration||' minutes')
                             AS INTEGER) > 12
                        THEN '-12 hours' ELSE '+0 hours' END
                     ), '0')  AS "Period ends at"
mykolash
mykolash
12,955 Points

Cool-l-n-magic!

mykolash
mykolash
12,955 Points

So, final request seems to look like:

-- Generate a schedule for Rex Rios.

SELECT Periods.id AS '#',
       Periods.start_time AS "Period starts at", 
       Periods.duration || " minutes" AS "Period duration", 
       LTRIM(STRFTIME('%H:%M',
                      SUBSTR('0'||Periods.start_time, -5),
                      '+'||Periods.duration||' minutes',
                      CASE WHEN
                        CAST(STRFTIME('%H',
                                      SUBSTR('0'||Periods.start_time, -5),
                                      '+'||Periods.duration||' minutes')
                             AS INTEGER) > 12
                        THEN '-12 hours' ELSE '+0 hours' END
                     ), '0')  AS "Period ends at",
       Classes.room_id AS "Room",
       Subjects.name AS "Subject",
       Teachers.last_name AS "Teacher"
FROM Periods
  INNER JOIN Classes ON Periods.id = Classes.period_id
  INNER JOIN Subjects ON Classes.subject_id = Subjects.id
  INNER JOIN Schedule ON Classes.id = Schedule.class_id
  INNER JOIN Students ON Schedule.student_id=Students.id
  INNER JOIN Teachers ON Classes.teacher_id=Teachers.id
WHERE Students.first_name="Rex" AND Students.last_name="Rios"
ORDER BY Periods.id ASC;