Project Introduction

Chinook Database

Introduction

In this project, you will query the Chinook Database. The Chinook Database holds information about a music store. For this project, you will be assisting the Chinook team with understanding the media in their store, their customers and employees, and their invoice information. To assist you in the queries ahead, the schema for the Chinook Database is provided below. You can see the columns that link tables together via the arrows.

Project Submission Details

Project Requirements

Presentations

You are now working on the portion of the project you will need to submit to a reviewer.

Your presentation should include:

• Four slides
• One visualization per slide
• A 1-2 sentence explanation of each slide
• The SQL query is used to create the data used in the visualization.

Project Submission Details

Project Requirements

Presentations

You are now working on the portion of the project you will need to submit to a reviewer.

Your presentation should include:

• Four slides
• One visualization per slide
• A 1-2 sentence explanation of each slide
• The SQL query is used to create the data used in the visualization.

Instructions

Step 1: Get the Chinook database up and running.

You can run the DB Browser database on your own machine (See the Set Up the DB Browser Database page) or you can use the provided workspace (see the Chinook Postgres SQL Workspace page).

Step 2: Complete the SQL Question Sets

This series of questions will confirm that you have mastered the main concepts taught throughout the SQL lessons. These questions will not be “graded” by a reviewer and are intended to help you self-assess.

Step 3: Create the Presentation

There isn’t a ‘right answer’ for this portion of the project. The project is designed to allow you to be creative in the questions you ask and then write a SQL query to pull the data needed to answer your question successfully.

You will use the pulled data to build a visual (bar chart, histogram, or another plot) that answers your question and present that information in a slide deck using PowerPoint, Google Slides or your favorite presentation software. See more information about what is required and how to submit the project on the Project Submission Details page.

Project Submission Details

Project Requirements

Presentations

You are now working on the portion of the project you will need to submit to a reviewer.

Your presentation should include:

• Four slides
• One visualization per slide
• A 1-2 sentence explanation of each slide
• The SQL query is used to create the data used in the visualization.
  

  Queries

  Please include a text file that includes each of the queries used to create the visualizations.

  • All SQL queries run without errors and produce the intended results.
  • Each SQL query needs to include one or more explicit join(s). The query’s JOIN or JOINs should be necessary to answer the question. If a question does not require a JOIN, please change the question to be one that does.
  • Each SQL query needs to include one or more aggregation(s). This could be a COUNT, AVG, SUM, or other aggregation.
  • The project includes at least four unique SQL queries.Example Queries in Plain Text

    <span class="token">/</span><span class="token">*</span> Query <span class="token">1</span> <span class="token">-</span> query used <span class="token">for</span> <span class="token">first</span> insight <span class="token">/</span> <span class="token">SELECT</span> t1<span class="token">.</span>col<span class="token">,</span> <span class="token">COUNT</span><span class="token">(</span>ct<span class="token">)</span> <span class="token">FROM</span> table1 t1
    <span class="token">JOIN</span> table2 t2
    <span class="token">ON</span> t1<span class="token">.</span>col <span class="token">=</span> t2<span class="token">.</span>col
    <span class="token">GROUP</span> <span class="token">BY</span> t1<span class="token">.</span>col<span class="token">;</span> 
    <span class="token">/</span><span class="token">*</span> Query <span class="token">2</span> <span class="token">-</span> query used <span class="token">for</span> <span class="token">second</span> insight <span class="token">/</span> <span class="token">SELECT</span> t1<span class="token">.</span>col<span class="token">,</span> <span class="token">COUNT</span><span class="token">(</span>ct<span class="token">)</span> <span class="token">FROM</span> table1 t1
    <span class="token">JOIN</span> table2 t2
    <span class="token">ON</span> t1<span class="token">.</span>col <span class="token">=</span> t2<span class="token">.</span>col
    <span class="token">GROUP</span> <span class="token">BY</span> t1<span class="token">.</span>col<span class="token">;</span>

    Visualizations We suggest using a spreadsheet application, such as Excel or Google Sheets, to create your visualizations. However, you’re welcome to use whatever tool you’d like. Your visualizations could be any that you learned about in the previous lesson. Below is one example, and a link has been provided to an example slide ( uploaded in the files)

  • You should have four slides similar to the example above, but the questions you ask are up to you, and all four of your final submitted queries should contain a JOIN and AGGREGATION. Look at the RUBRIC to verify you have met all of the necessities for this submission.Submitting Your ProjectOnce you have completed the presentation and created the text file, put your text file and presentation in a folder and zip it and submit the zipped folder for your project.In order to review your presentation, you will need to save your slides as a PDF. You can do this from within a presentation tool by selecting File > Download as > PDF Document.
    

    Project: Music SQL Database

    SQL Queries

    Success Criteria	Specifications
    The student can write error-free SQL queries.	All SQL queries run without errors and produce the intended results.
    The student can use joins correctly in SQL queries.	Each SQL query needs to include one or more explicit join. The JOIN or JOINs should be necessary to the query. If a question does not require a JOIN please change the question to be one that does.Example: SELECT * FROM Album JOIN Track on Track.AlbumID = Album.AlbumID
    The student can use aggregations correctly in SQL queries.	Each SQL query needs to include one or more aggregation. This could be a COUNT, AVG, SUM, or other aggregation.
    Student can answer multiple questions by using SQL	The student has used at least 4 unique SQL queries in their submission.

    Presentation

    Success Criteria	Specifications
    The student’s slides are organized well and are easy to read and understand.	Each slide should have an appropriate title and the visualization descriptions should be free of significant factual, spelling and grammar mistakes.
    The student can create data visualizations that provide useful information.	All visualizations should make logical sense and provide accurate information about the indicated area.
    The student can format data visualizations clearly and make good use of labeling.	All visualizations include a title and axis labels, have a legend where applicable, and are easily understood.Every visualization should have chart title x axis title x axis labels y axis title y axis labels

    Submission Phase

    Success Criteria	Specifications
    The student has uploaded all files necessary for review.	A PDF report has been uploaded and a .txt file with the queries has been uploaded in a single zipped folder file

Project: SQL Project

To submit your project, please do the following:

• Review your project against the project Rubric. Reviewers will use this to evaluate your work.
• Create your slides with whatever presentation software you’d like (PowerPoint).

In order to review your presentation, you will need to save your slides as a .pdf file. You can do this from within Google Slides by selecting File > Download as > PDF Document.

• Create a separate text file with each of the SQL queries used to create the visualizations.
• Save the presentation as a PDF and the SQL queries in a text file(.txt) in the same folder.
  • Zip (compress) the folder and submit this zipped folder with both files in it.

  

  • Submit the zipped file.