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

Here's my version of making the schedule. Feedback appreciated.

I'm fairly new to SQL so any feedback is appreciated!

SELECT PERIOD_ID AS Period, ROOM_ID AS 'Room #', START_TIME AS 'Starting Time', NAME AS 'Class'
  FROM STUDENTS AS ST
    JOIN SCHEDULE AS SC 
      ON ST.ID = SC.STUDENT_ID
    JOIN CLASSES AS C
      ON SC.CLASS_ID = C.ID
    JOIN PERIODS AS P
      ON C.PERIOD_ID = P.ID
    JOIN SUBJECTS AS SU
      ON SU.ID = C.SUBJECT_ID
  WHERE ST.FIRST_NAME = 'Rex' AND ST.LAST_NAME = 'Rios'
  ORDER BY Period  
 ;

Good work! Just be careful about how you alias your columns. Aliasing Room_ID as Room # could cause issues if you were going to export this data for use in another program, or even reference this query in another SQL statement. Generally it's best practice to avoid whitespace or special characters in column alias names. For example, instead of Room #, consider Room_Number.

Thanks, Jake! I hadn't thought of that--good point!

4 Answers

Nicolas Montoya
Nicolas Montoya
10,592 Points

This is great. Much more helpful than the example used in the video! I am sure it is easy for you, but to go even further you could add the teacher name as well. Also I like how easy to read the code is.

Kareem Jeiroudi
Kareem Jeiroudi
14,982 Points

I find yours a better schedule than the video's. Here have a look at mine as well. I also tried to have a nice output as I'd like to see my schedule as a student:

SELECT PER.ID Period, CL.ID "Class ID", SUB.NAME "Subject Name",
  PER.START_TIME "Start Time", PER.DURATION "Duration", ROOM_ID "Room"
FROM CLASSES CL
 JOIN SCHEDULE SCH ON CL.ID = SCH.CLASS_ID
 JOIN STUDENTS ST ON SCH.STUDENT_ID = ST.ID
 JOIN SUBJECTS SUB ON CL.SUBJECT_ID = SUB.ID
 JOIN PERIODS PER ON CL.PERIOD_ID = PER.ID
 JOIN ROOMS RO ON CL.ROOM_ID = RO.ID
WHERE ST.FIRST_NAME = 'Rex' AND ST.LAST_NAME = 'Rios'
ORDER BY PER.ID;

Its funny how everyone has different scripts but the same answers haha. I did mine a bit differently as well but still got the detailed schedule with period #, room # and subject name and I kept the class ID.

SELECT classes.id, period_id, room_id, name FROM classes
JOIN schedule ON classes.id = schedule.class_id
JOIN students ON schedule.student_id = students.id
JOIN subjects ON classes.subject_id = subjects.id
WHERE schedule.student_id = 10399
ORDER BY period_id ASC

Hope this helps someone, somehow!

Paul Brubaker
Paul Brubaker
9,925 Points

These are all great! Here is my version. I tried to make a schedule that would be easy for an anxious student to understand on their first day. I am a little surprised no one included the teachers' names. That seems like something students would really like to know.

SELECT SUBJECTS.NAME AS subject, ROOMS.ID AS room, TEACHERS.FIRST_NAME || " " || TEACHERS.LAST_NAME AS teacher,
  PERIODS.ID as period, PERIODS.START_TIME AS begins
FROM STUDENTS
JOIN SCHEDULE ON SCHEDULE.STUDENT_ID = STUDENTS.ID
JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
JOIN TEACHERS ON TEACHERS.ID = CLASSES.TEACHER_ID, 
  PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID, 
  ROOMS ON ROOMS.ID = CLASSES.ROOM_ID, 
  SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE STUDENTS.FIRST_NAME || " " || STUDENTS.LAST_NAME = "Rex Rios"
ORDER BY PERIODS.ID;