create_view.sql

USE UNI_DB;

CREATE OR REPLACE VIEW SCHEDULES
AS
EXPLAIN ANALYZE SELECT
	COURSE.COURSE_DISPLAY_SHORT_NAME,
	COURSE.COURSE_DISPLAY_FULL_NAME,
	COURSE.COURSE_DESCRIPTION,
	INSTRUCTOR.FIRST_NAME AS INSTRUCTOR_FIRST_NAME,
	INSTRUCTOR.LAST_NAME AS INSTRUCTOR_LAST_NAME,
	INSTRUCTOR.EMAIL AS INSTRUCTOR_EMAIL,
	CONCAT(ROOM.BUILDING,', ', ROOM.FLOOR, ', ', ROOM.NUMBER, ', ', ROOM.DISPLAY_NAME) AS ADDRESS,
	SCHEDULE.WEEK_DAY,
	LESSON.ID AS LESSON_ID,
	LESSON.START_TIME AS LESSON_START_TIME,
	LESSON.END_TIME AS LESSON_END_TIME
FROM SCHEDULE
JOIN COURSES COURSE
	ON SCHEDULE.COURSE_ID = COURSE.ID
JOIN INSTRUCTORS INSTRUCTOR
	ON SCHEDULE.INSTRUCTOR_ID = INSTRUCTOR.ID
JOIN ROOMS ROOM
	ON SCHEDULE.ROOM_ID = ROOM.ID
JOIN LESSONS_SCHEDULE LESSON
	ON SCHEDULE.LESSON_SCHEDULE_ID = LESSON.ID
;