1. Learn
  2. /
  3. Courses
  4. /
  5. Database Design

Exercise

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.

Instructions 1/2

undefined XP
    1
    2
  • Create a view called top_artists_2017 with artist from artist_title.
  • To only return the highest scoring artists of 2017, join the views top_15_2017 and artist_title on reviewid.
  • Output top_artists_2017.