Creating a view from other views
Views can be created from queries that include other views. This is useful when you have a complex schema, potentially due to normalization, because it helps reduce the JOINS needed. The biggest concern is keeping track of dependencies, specifically how any modifying or dropping of a view may affect other views.
In the next few exercises, we'll continue using the Pitchfork reviews data. There are two views of interest in this exercise. top_15_2017 holds the top 15 highest scored reviews published in 2017 with columns reviewid,title, and score. artist_title returns a list of all reviewed titles and their respective artists with columns reviewid, title, and artist. From these views, we want to create a new view that gets the highest scoring artists of 2017.
This exercise is part of the course
Database Design
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create a view with the top artists in 2017
___ ___ top_artists_2017 ___
-- with only one column holding the artist field
SELECT artist_title.___ FROM ___
INNER JOIN ___
ON ___.___ = ___.___;
-- Output the new view
SELECT * FROM ___;