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.
Deze oefening maakt deel uit van de cursus
Database Design
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
-- 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 ___;