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

Posted by Vanessa Van Gilder
Vanessa Van Gilder
7,034 Points

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  
 ;
Jake Klein
Jake Klein
13,262 Points

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.

Vanessa Van Gilder
Vanessa Van Gilder
7,034 Points

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

6 Answers

Nicolas Montoya
Nicolas Montoya
10,592 Points
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.

Paul Brubaker
Paul Brubaker
14,290 Points
Paul Brubaker
Paul Brubaker
14,290 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;
Ololade Oluseye
Ololade Oluseye
2,602 Points
Ololade Oluseye
Ololade Oluseye
2,602 Points

-- Generate a schedule for Rex Rios.

SELECT FIRST_NAME, LAST_NAME, NAME, START_TIME, DURATION FROM SCHEDULE

JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID

JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID

JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID

JOIN ROOMS ON CLASSES.ROOM_ID = ROOMS.ID

JOIN PERIODS ON CLASSES.PERIOD_ID = PERIODS.ID

WHERE FIRST_NAME = "Rex" AND LAST_NAME = "Rios"

ORDER BY START_TIME ASC

Kareem Jeiroudi
Kareem Jeiroudi
14,984 Points
Kareem Jeiroudi
Kareem Jeiroudi
14,984 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;
Aric Longo
Aric Longo
1,778 Points
Aric Longo
Aric Longo
1,778 Points

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!

Sumiya Malik
Sumiya Malik
3,976 Points
Sumiya Malik
Sumiya Malik
3,976 Points

I did mine like this, Made it more detailed adding start time and duration too

-- Generate a schedule for Rex Rios.
SELECT Periods.ID Period_ID,Classes.ID Class,Subjects.ID Subject_ID, Subjects.Name,  Periods.Start_Time, Periods.Duration
FROM Classes JOIN Periods
ON Periods.ID = Classes.Period_ID
JOIN Subjects
ON Classes.Subject_ID= Subjects.ID
JOIN Rooms
ON Rooms.ID= Classes.Room_ID
JOIN Schedule
ON Schedule.Class_ID = Classes.ID
JOIN Students
ON Schedule.Student_ID= Students.ID
Where Students.First_Name='Rex' AND Students.Last_Name='Rios'
Order By Period_ID