Hello again! This is the last part of our course. In this part, you're going to learn what views are and how to use them.
Before we go on to explain views, let's see an example. A teaching center which offers courses of various kinds has the following tables in their database:
course - lists all courses in the center,lecturer - all lecturers who run courses,position - all job positions available for lecturers,job_history - information about the history of positions each lecturer took up.
Select all data about courses together with data about their lecturers.
Note that both tables have column named id. To make the query results readable, prefix all selected columns with the name of the table the column comes from. So columns which come from the table course should be prefixed with course_ prefix (for example: course_id), columns which come from the table lecturer should be prefixed with lecturer_ prefix (for example: lecturer_first_name).
Hint: You will have to use an appropriate JOIN.
Hint 2: To rename all the columns, you have to list all columns in the query. (Yes, it's a lot of typing.)Type
SELECT course.id AS course_id, name AS course_name, description AS course_description, first_edition AS course_first_edition, next_edition AS course_next_edition, lecturer_id AS course_lecturer_id, lecturer.id AS lecturer_id, first_name AS lecturer_first_name, last_name AS lecturer_last_name, started_work AS lecturer_started_work, ended_work AS lecturer_ended_work FROM course JOIN lecturer ON lecturer.id = lecturer_id;
