Get Started

Querying the star schema

The novel genre hasn't been selling as well as your company predicted. To help remedy this, you've been tasked to run some analytics on the novel genre to find which areas the Sales team should target. To begin, you want to look at the total amount of sales made in each state from books in the novel genre.

Luckily, you've just finished setting up a data warehouse with the following star schema:

The tables from this schema have been loaded. Note that you should not use aliases in FROM and JOIN statements.

This is a part of the course

“Database Design”

View Course

Exercise instructions

  • Select state from the appropriate table and the total sales_amount.
  • Complete the JOIN on book_id.
  • Complete the JOIN to connect the dim_store_star table
  • Conditionally select for books with the genre novel.
  • Group the results by state.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Output each state and their total sales_amount
SELECT ___.___, ___(___)
FROM ___
	-- Join to get book information
    JOIN ___ ON ___.___ = ___.___
	-- Join to get store information
    JOIN ___ ON ___.___ = ___.___
-- Get all books with in the novel genre
WHERE  
    ___.___ = 'novel'
-- Group results by state
GROUP BY
    ___.___;
Edit and Run Code

This exercise is part of the course

Database Design

BeginnerSkill Level
4.6+
55 reviews

Learn to design databases in SQL to process, store, and organize data in a more efficient way.

In this chapter, you will take your data modeling skills to the next level. You'll learn to implement star and snowflake schemas, recognize the importance of normalization and see how to normalize databases to different extents.

Exercise 1: Star and snowflake schemaExercise 2: Running from star to snowflakeExercise 3: Adding foreign keysExercise 4: Extending the book dimensionExercise 5: Normalized and denormalized databasesExercise 6: Querying the star schema
Exercise 7: Querying the snowflake schemaExercise 8: Updating countriesExercise 9: Extending the snowflake schemaExercise 10: Normal formsExercise 11: Converting to 1NFExercise 12: Converting to 2NFExercise 13: Converting to 3NF

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free