Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

mykolash
mykolash
12,955 Points
Posted by 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
229,732 Points
Steven Parker
Steven Parker
229,732 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
mykolash
mykolash
12,955 Points

Cool-l-n-magic!

mykolash
mykolash
12,955 Points
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;