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

Stamos Bolovinos
Stamos Bolovinos
4,320 Points

How to sort on start_time?

I don't agree to the solution of the teacher. I remember from my school time, what a schedule should look like. First each class has a number, so I used the pk of the classes table. Then a schedule contains the class name and the teacher. Of course it needs to have the start time, and room number, so we know when and where to go. And the schedule of course needs to be sorted by time.

The problem I'm facing is that the periods table stores the START_TIME as text.

I can't figure out how to sort on this column.

I tried this:

SELECT
  c.id 'Class ID',
  su.name 'Class Name', 
  su.grade 'Grade',
  t.first_name || ' ' || t.last_name 'Teacher',
  CASE WHEN LENGTH(p.start_time) = 5 THEN TIME(p.start_time)
    ELSE TIME('0' || p.start_time) END 'Start Time',
  r.id 'Room Number' 
FROM classes c
INNER JOIN schedule sc ON c.id = sc.class_id
INNER JOIN students st ON sc.student_id = st.id
INNER JOIN teachers t ON c.teacher_id = t.id
INNER JOIN subjects su ON c.subject_id = su.id
INNER JOIN periods p ON c.period_id = p.id
INNER JOIN rooms r ON c.room_id = r.id
WHERE st.first_name = 'Rex' AND st.last_name = 'Rios'
ORDER BY 'Start Time'

But it delivers unexpected results in the sorting of the 'Start Time' column. I know the result would still be incorrect, as AM and PM information is also missing.

But still I would expect from my query to get times sorted as

  • 01:15:00
  • 02:05:00
  • 09:05:00
  • 09:55:00
  • 10:44:00
  • 11:35:00
  • 12:25:00

How to solve this?

2 Answers

Playground is limited to certain extend - e.g. not supporting regex and advanced functions.

Edit: Correct me if I am wrong someone.

I wouldn't say that's the problem. The problem is non-standard data. According to the SQLite manual, date-times should be in any of 3 particular formats:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
  • REAL as Julian day numbers
  • INTEGER as Unix Time

The data in the periods table follows none of those standards. If it followed any of them, the data could be sorted and our time operating functions would work as designed.

In practice, it'd be better to update the table with correctly formatted data. However, in this exercise, ordering by periods.id suffices.

You will not be able to sort by START_TIME this particular time. START_TIME value is stored as String and as far as I am aware SQL Playground has limited casting ability (in RL situation we would just TO_DATE it).

Nevertheless, in PERIODS table each period has it's ID assigned according to START_TIME which means you will be good to go as long as you order on ID.

Side note: INNER JOIN can be simply replaced with JOIN, they are equal. Of course in complex queries it will make everything clearer (especially when you have heaps of LEFT / RIGHT JOINS). Also don't bother using FIRST and LAST_NAME, just go with Rex's ID in WHERE.

Apart from that - excellent work Sir! :)

Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Thanks for the answer! So what do you mean with 'limited casting ability' - is this a limitation of only the Playground or general of SQLite?