Overview

This lab will follows up on this week's introduction to SQL, a language that allows you to quickly retrieve information about large datasets. In today's exercises, you will get practice writing SQL queries using a few example databases. Before getting started writing SQL, you will upload data from our provided smaller datasets, including nutritional stats from the USDA food database, as well as data for different video games and their lifetime sales.

Exercise 1: Getting MySQL Set Up on C9 and Uploading the Data

Cloud 9 conviently has MySQL pre-installed for you to use. All you have to do is start it on the c9 environment and then run our provided setup.sql file. This file will load the lab's data, populating three new tables: FoodData, Games, and Sales (Games and Sales both represent video game data and are related, as opposed to FoodData). The slides below provide a walkthrough to help you get cloud 9 set up with SQL.

Exercise 1 (Continued)

The following steps will help you get started with using MySQL on Cloud 9:

• Download the setup.sql file and then save it in a directory called sqllab in Cloud 9.
• Open the "Window" menu item and select "New Terminal" from the dropdown. A new window should popup with a label like "bash - 'ubuntu@yourusername'" (shown below).

Exercise 1 (Continued)

• Next, you'll then want to start mysql using two commands: first type mysql-ctl start on the terminal and press enter, and then type mysql-ctl cli and press enter. You should see something like the following:

Exercise 1 (Continued)

• Finally, you'll need to type use c9 on the terminal to use the provided cloud 9 database, and then type source sqllab/setup.sql (or source path/to/setup.sql if you saved the file elsewhere) to load the lab data. Loading the data may take a few seconds.

• If it works you will see a bunch of Query OK, 1 row affected... messages.
• Leave this terminal open. You will be running your own sql queries here next.
• Now you're ready to start the rest of the lab! If you ran into any problems in this process (e.g., ran into any error messages on the terminal) you can ask your TA for help or you may also find this short overview helpful.

Exercise 2: Our First Query

Now you're ready to do the really cool stuff! First, we'll use the smaller FoodData table to fetch some data about food nutrition info found from the USDA nutrition database. Each food listed has data for a 100g serving, which makes it useful to compare nutrition information between different foods.

The following table (called a schema) shows the different types of data (as columns) in the FoodData table for each food item (row), where protein and carbs are given in grams:

FoodData

Exercise 2 (Continued)

For your first query, let's first see what foods we have available. To refresh your memory from yesterday's lecture, we'll give this one to you. Type the following SQL code in your terminal (you can open a new terminal and run mysql-ctl cli if you closed it earlier):

SELECT description FROM FoodData;

This command will list all of the food descriptions in the FoodData table. You should see a number of rows populate your terminal after executing this command, followed by a result row count of 2417 rows. If you run into an error at this point, it may be a result of unsuccessfully setting up the databases in Exercise 1 in which case, you may want to go back and read through your command syntax.

Exercise 3 Part I: SQL Basics with FoodData

Recall that the WHERE clause lets us specify criteria in our query to filter out results.

Show all food descriptions and their calorie counts from FoodData that have fewer than 20 calories per 100g (remember that all data is given for 100g in FoodData).

Exercise 3 Part II: SQL Basics with FoodData

Write a query to return all food descriptions and calorie counts from FoodData that have more than 500 calories. Order the results in ascending order of calorie counts.

Exercise 3 Part III: SQL Basics with FoodData

Write a query to return the descriptions , calorie count, grams of protein, and grams of carbohydrates for foods that have more grams of protein than carbohydrates. Order the results by alphabetical order.

Exercise 3 Part IV: SQL Basics with FoodData

Write a query to return the descriptions and calorie counts of foods that have the word 'apple' in them. Order the results in ascending order by calorie count (the calorie counts are stored as strings, so you will have to use the CAST function to turn them into DECIMALs).

Exercise 4: Using SQL with Video Game Sales Data

In this part of the lab, you will use the video game datasets provided, represented in the Games and Sales tables, each which have 3000 rows of video game data.

Games

Sales

Exercise 4 Part I

Write a SQL query that returns the names of all games that were developed by Nintendo.

Exercise 4 Part II

Write a SQL query that returns a list of all platform types in the Games table (e.g., Wii).

Exercise 4 Part III

Write a SQL query that returns the names of the video games that were released in the earliest year recoded in the Games table.

Exercise 4 Part IV

Write a SQL query that returns the names and release year of the video games that have the word "Pokemon" in their name.

Exercise 4 Part V

Write a SQL query that returns the largest amount of lifetime sales (in US dollars) from one video game in North America. How does this number copare to the largest sales from one game in Japan?

Exercise 4 Part VI

Write a SQL query that returns the sum of all global sales from in the Sales database.

Exercise 4 Part VII

Write a SQL query that returns the average of all global sales from in the Sales database.

Exercise 4 Part VIII

Write a SQL query that returns the average of all global sales from in the Sales database.