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.
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.
How many different game names are in the games table?
A game on three platforms has three rows. COUNT(DISTINCT Name) counts each name once.
How many rows have no year?
Use IS NULL. = NULL runs without an error but never matches anything.
What's the earliest year in the games table?
Which platform has the most releases in the games table?
Group by the name so all of a game's rows are added up together.
These three questions aren't part of the studio's request. They're more practice with averages and HAVING, using publishers.
Which publisher has the highest average global sales per release?
Answer question 7 again, counting only publishers with at least 20 releases. Which publisher has the highest average?
Group by publisher, and use HAVING to keep only the groups with 20 or more rows.
Questions 7 and 8 have different answers. Why is question 8's answer the better one?
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.
Which genre sells the fewest copies per release, on average?
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?
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?
You can subtract one average from another in the SELECT, then sort by the result.
WHERE picks the years before grouping. HAVING keeps the genres with enough releases after. A column like COUNT(*) needs an AS.
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?
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.