Unit 04 · Practice

Practice

Every question on this page uses the games table from this unit's lessons, not Northwind. The Tables panel shows its columns.

Type your query in the box and click Run, or press Ctrl + Enter (Cmd + Enter on a Mac). Run as many queries as you want on the way to an answer. When the answer is a count of rows, don't count them by hand. The total is shown under the results table.

Part 1 is six questions about what's in the games table. Part 2 is three questions about publishers. Part 3 is six questions working through the game studio's request.

Everything you run on this page is recorded and goes into your submission.

Part 1 · Getting to know the table

Before you answer anything for anybody, find out what you have. Run whatever queries you need, then type your answer. For a number, type only the number.

1

How many different game names are in the games table?

⌘/Ctrl + Enter

A game on three platforms has three rows. COUNT(DISTINCT Name) counts each name once.

2

How many rows have no year?

⌘/Ctrl + Enter

Use IS NULL. = NULL runs without an error but never matches anything.

3

What's the earliest year in the games table?

⌘/Ctrl + Enter
4
Show the total global sales for each year from 2010 through 2015, earliest year first.
⌘/Ctrl + Enter
5

Which platform has the most releases in the games table?

⌘/Ctrl + Enter
6
Show the ten best-selling games, with each game's sales on every platform added together. Show the name and the total global sales, highest first.
⌘/Ctrl + Enter

Group by the name so all of a game's rows are added up together.

Part 2 · Publishers

These three questions aren't part of the studio's request. They're more practice with averages and HAVING, using publishers.

7

Which publisher has the highest average global sales per release?

⌘/Ctrl + Enter
8

Answer question 7 again, counting only publishers with at least 20 releases. Which publisher has the highest average?

⌘/Ctrl + Enter

Group by publisher, and use HAVING to keep only the groups with 20 or more rows.

9

Questions 7 and 8 have different answers. Why is question 8's answer the better one?

⌘/Ctrl + Enter
Part 3 · The studio's request

Back to the game studio's request from the lessons:

We're trying to decide what our next big game should be. Can you look at the sales data and tell us what sells? We'd especially like to know which genres sell best.

The lessons looked at what counts as a game and how sales changed over time. The questions below take the rest of it, one piece at a time.

10
For each genre, show the genre, the number of releases, the total global sales, and the average global sales per release. Put the genre with the highest total first.
⌘/Ctrl + Enter
11

Which genre sells the fewest copies per release, on average?

⌘/Ctrl + Enter
12

A coworker looks at your results from question 10 and says: "Action has the highest total sales by far. Action is the obvious pick." What's the problem with that reasoning?

13

The studio also wants to know whether what sells best depends on the region. For each genre, compare its average sales per release in North America with its average in Japan. Which genre sells much better per release in North America than in Japan, with the biggest gap between the two?

⌘/Ctrl + Enter

You can subtract one average from another in the SELECT, then sort by the result.

14
Here's one table you might show the studio about genres. Build it, and clean it up so it's ready to present. Use only releases from 2013 through 2015, the last three full years in the games table, and only genres with at least 50 releases in those years. Show the genre, the number of releases, and the average global sales per release, with the highest average first. Give the columns clear names. You can include other numbers if you think the studio would want them.
⌘/Ctrl + Enter

WHERE picks the years before grouping. HAVING keeps the genres with enough releases after. A column like COUNT(*) needs an AS.

15

Shooter comes first in your table from question 14, at about 1.39 million copies per release. You're showing that table to the studio. What should you tell them along with Shooter's number?

⌘/Ctrl + Enter
Submit

When you're done, download your submission file and upload it to this unit's practice assignment in Canvas. It carries a summary of your progress and the record of every attempt you made on this page. You can submit as many times as you like; the last one counts.

Attempts are saved in this browser. To keep going on another computer, download your submission here and resume from it there.