Views
2. Views - Introduction
Revision

Instruction

Well done! That was quite a lot of typing, right?

Well, this is where views come in handy. In SQL, a view is a "remembered" query. If we repeatedly perform the same complicated query, we have to write it from scratch every time. When we create a view, the database can remember the query for us. All we need to remember is the name of the view.

In the teaching center database, there is some historical data - for instance, there are some courses which are no longer run (their next_edition field is NULL). So, each time we want to retrieve some information for only those courses which are actually run, we have to write:

SELECT * FROM course 
WHERE next_edition IS NOT NULL;

A view could make our life easier. Take a look:

CREATE VIEW current_course AS 
SELECT * FROM course 
WHERE next_edition IS NOT NULL;

As you can see, we provided the keywords CREATE VIEW, followed by the name of the view and the keyword AS. And that's it. A view will be created with the query we specify afterwards.

Exercise

Alright. Create the view course_lecturer joining courses and lecturers as in the previous exercise. To save you some typing, we have put the query in the template.

Stuck? Here's a hint!

Type

CREATE VIEW course_lecturer AS 
SELECT 
course.id AS course_id,
course.name AS course_name,
course.description AS course_description,
course.first_edition AS course_first_edition,
course.next_edition AS course_next_edition,
course.lecturer_id AS course_lecturer_id,
lecturer.id AS lecturer_id,
lecturer.first_name AS lecturer_first_name,
lecturer.last_name AS lecturer_last_name,
lecturer.started_work AS lecturer_started_work,
lecturer.ended_work AS lecturer_ended_work
FROM course JOIN lecturer ON course.lecturer_id = lecturer.id;

Console

Code editor

Result

TableConsole