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 ;
Nicolas Montoya10,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 Jeiroudi14,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 Brubaker9,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;