CSE 154

Section 8: Practice with MySQL

Section Agenda

In this week's section, we'll practice writing SQL queries for several example databases. As we saw in lecture yesterday, we can use phpMyAdmin (which comes with MAMP) to create a database storing different tables (e.g. games database with a single table called Games).

For general query practice with a few practice tables, remember that you can you can also use the CSE 154 Query Tester, which is a tool to practice more queries.

Section Goals

By the end of this section, you should be able to:

  • Identify the difference between a database, table, row (record), column (attribute), and query.
  • Set up a new database with tables on phpMyAdmin with a provided .sql file.
  • Write SQL queries to filter out specific data from a table using SELECT, FROM, WHERE, ORDER BY, and LIMIT, as well as modifers to WHERE (AND, OR, LIKE, etc.)

Setting Things Up: Populating a Database with Practice Datasets

In this section, we'll continue to use setup-games.sql (from Friday) as well as a new dataset setup-fooddata.sql which includes nutritional data from the USDA food database.

If you set these up on your own computer, make sure to follow the phpMyAdmin tutorial (you can find alternative directions for using MySQL on a command line here).

Part I: Writing SQL Queries with the FoodData table

Now we'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:

id description protein carb
9427 Abiyuch, raw 1.5 17.6
9002 Acerola juice, raw 0.40 4.80
... ... ... ...

SQL with FoodData: Query 1 (Prepping)

For our first query, let's first see what foods we have available.

What query would lists all of the food descriptions in the FoodData table?

Solution:


SELECT description
FROM FoodData;

SQL

You should see a 2371 rows returned after executing this command in your MySQL console.

SQL with FoodData: Query 2 (Proteins)

Write a query that returns all of the food descriptions, grams of protein, and grams of carb for FoodData items that have more grams protein than carbs per 100g (remember that all data is given for 100g in this dataset). Your result should be in whatever order as defined by the table (no explicit ordering). 


+-----------------------+---------+------+
| description           | protein | carb |
+-----------------------+---------+------+
| Butter, salted        |    0.85 | 0.06 |
| Butter oil, anhydrous |    0.28 | 0.00 |
| Cheese, blue          |   21.40 | 2.34 |
| Cheese, brick         |   23.24 | 2.79 |
| Cheese, brie          |   20.75 | 0.45 |
| ...                   |     ... |  ... |
+-----------------------+---------+------+
191 rows in set (0.01 sec)

Expected results (first 8 rows)

SQL with FoodData: Query 2 Solution

One solution is provided below: 


SELECT description, protein, carb
FROM FoodData
WHERE protein > carb;

SQL

SQL with FoodData: Query 3 (Cooked Proteins)

Write a query to return the descriptions, grams of protein, and grams of carbohydrates for foods that have more grams of protein than carbohydrates and which have the word 'cooked' in their description. Order the results by alphabetical order. 


+----------------------------------------------------------+---------+-------+
| description                                              | protein | carb  |
+----------------------------------------------------------+---------+-------+
| Asparagus, frozen, cooked, boiled, drained, with salt    |    2.95 |  1.92 |
| Asparagus, frozen, cooked, boiled, drained, without salt |    2.95 |  1.92 |
| Bamboo shoots, cooked, boiled, drained, with salt        |    1.53 |  1.52 |
| Broccoli raab, cooked                                    |    3.83 |  3.12 |
| Cowpeas, leafy tips, cooked, boiled, drained, with salt  |    4.67 |  2.80 |
| ...                                                      |     ... |   ... |
+----------------------------------------------------------+---------+-------+
18 rows in set (0.01 sec)

Expected results (first 5 rows)

SQL with FoodData: Query 3 Solution

One solution is provided below: 


SELECT description, protein, carb
FROM FoodData
WHERE protein > carb AND description LIKE '%cooked%'
ORDER BY description;

SQL

SQL with FoodData: Query 4 (Cooked Proteins, with Percent Protein)

Next, modify your answer from Query 3 to also include a new column containing the grams of protein rounded to the nearest whole integer. Omit the protein and carb columns in this query. You can use an alias to add a new named column to the result table.

For example, to create an alias for a new column named my_alias for adding protein and carb grams per record, we could do:

SELECT (protein + carb) as my_alias FROM FoodData;

You can use the ROUND(x, y) function in MySQL which truncates x to have exacty y decimal places. In this case, round to 0 decimal places. Refer to the slide below for the expected output results.

SQL with FoodData: Query 4 Expected Results


+----------------------------------------------------------+-----------------+
| description                                              | percent_protein |
+----------------------------------------------------------+-----------------+
| Asparagus, frozen, cooked, boiled, drained, with salt    |               3 |
| Asparagus, frozen, cooked, boiled, drained, without salt |               3 |
| Bamboo shoots, cooked, boiled, drained, with salt        |               2 |
| Broccoli raab, cooked                                    |               4 |
| Cowpeas, leafy tips, cooked, boiled, drained, with salt  |               5 |
| ...                                                      |             ... |
+----------------------------------------------------------+-----------------+
12 rows in set (0.01 sec)

Expected results (first 5 rows)

SQL with FoodData: Query 4 Solution

One solution is provided below: 


SELECT description, ROUND(protein) as percent_protein
FROM FoodData
WHERE protein > carb AND description LIKE '%cooked%'
ORDER BY description;

SQL

SQL with FoodData: Query 5 (An Apple a Day Keeps the Doctor Away?)

Write a query to return the descriptions of foods that have the word 'apple' in them. Order the results in descending order by description. 


+-------------------------------------------------------------------------+
| description                                                             |
+-------------------------------------------------------------------------+
| Sugar-apples, (sweetsop), raw                                           |
| Ruby Red grapefruit juice blend (grapefruit, grape, apple), OCEAN SPRAY |
| Pineapple, raw, traditional varieties                                   |
| Pineapple, raw, extra sweet variety                                     |
| Pineapple, raw, all varieties                                           |
| ...                                                                     |
+-------------------------------------------------------------------------+
75 rows in set (0.00 sec)

Expected results (first 5 rows)

SQL with FoodData: Query 5 Solution

One solution is provided below:


SELECT description
FROM FoodData
WHERE description LIKE '%apple%'
ORDER BY description DESC;

SQL

Part II: Using SQL with Video Game Data

In this part of the section, we will use the video game dataset provided (identical to the one introduced in Friday's lecture). This dataset has a table called Games which we will write queries with. There are approximately 3000 rows of video game data in this table.

id name platform release_year genre publisher developer rating
1 Pokemon Red/Blue GB 1996 Role-Playing Nintendo Game Freak E
2 Spyro Reignited Trilogy PS4 2018 Platform Activision Toys for Bob E
3 Universal Paperclips PC 2017 World Domination Frank Lantz Frank Lantz E
... ... ... ... ... ... ... ...

SQL with Games: Query 1 (Nintendo Games)

Write a SQL query that returns the names of all games that were developed by Nintendo (your result should not have duplicates).


+---------------------------+
| name                      |
+---------------------------+
| Mario Kart Wii            |
| New Super Mario Bros.     |
| New Super Mario Bros. Wii |
| Mario Kart DS             |
| Wii Fit                   |
| ...                       |
+---------------------------+
63 rows in set (0.01 sec)

Expected results (first 5 rows)

SQL with Games: Query 1 Solution

One solution is provided below:


SELECT DISTINCT name
FROM Games
WHERE developer = 'Nintendo';

SQL

SQL with Games: Query 2 (All Game Platforms)

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


+----------+
| platform |
+----------+
| GB       |
| PS4      |
| PC       |
| NES      |
| PS       |
| Wii      |
| ...      |
+----------+
26 rows in set (0.01 sec)

Expected results (first 6 rows)

SQL with Games: Query 2 Solution

One solution is provided below:


SELECT DISTINCT platform
FROM Games;

SQL

SQL with Games: Query 3 (Pioneers of an Industry)

Write a SQL query that returns the names and release year of 20 of the video games released earliest as determined by their release_year in the Games table. 


+-----------------------------+--------------+
| name                        | release_year |
+-----------------------------+--------------+
| Combat                      |         1977 |
| Space Invaders              |         1978 |
| Defender                    |         1980 |
| Asteroids                   |         1980 |
| Missile Command             |         1980 |
| ...                         | ...          |
+-----------------------------+--------------+
20 rows in set (0.01 sec)

Expected results (first 5 rows)

SQL with Games: Query 3 Solution

One solution is provided below:


SELECT name, release_year
FROM Games
ORDER BY release_year
LIMIT 20;

SQL

SQL with Games: Query 4 (Bringing Back the Purple Dragon)

Write a SQL query that returns the name, platform and release year of all games with the word 'Spyro' in their title and which don't include 'Skylanders' in their title. Hint: Use NOT to negate a boolean expression in MySQL. 


+----------------------------+----------+--------------+
| name                       | platform | release_year |
+----------------------------+----------+--------------+
| Spyro Reignited Trilogy    | PS4      | 2018         |
| Spyro the Dragon           | PS       | 1998         |
| Spyro: Year of the Dragon  | PS       | 2000         |
| Spyro 2: Ripto's Rage      | PS       | 1999         |
| Spyro: Season of Ice       | GBA      | 2001         |
| ...                        | ...      | ...          |
+----------------------------+----------+--------------+
8 rows in set (0.01 sec)

Expected results (first 5 rows)

SQL with Games: Query 4 Solution

One solution is provided below:


SELECT name, platform, release_year
FROM Games
WHERE name LIKE '%Spyro%'
AND NOT (name LIKE '%Skylanders%');

SQL

SQL with Games: Query 5 (Retro Puzzle Games)

Note: This is a challenge query! We'll review ways to approach the solution in upcoming lectures, but feel free to see how far you can get using what we've covered (and the cheatsheet)!

Write a SQL query that returns the name and release_year of the Puzzle games released in the earliest year for Puzzle games in the Games table. How does the result for Puzzle games compare to Sports games?


+-----------------------------+--------------+
| name                        | release_year |
+-----------------------------+--------------+
| Ms. Pac-Man                 | 1981         |
+-----------------------------+--------------+
1 rows in set (0.02 sec)

Expected results

SQL with Games: Query 5 Solution

One solution is provided below (you can nest SELECT to return specific values like MIN):


SELECT g1.name, g1.release_year 
FROM Games g1 
WHERE g1.genre = 'puzzle' 
AND g1.release_year = (SELECT MIN(g2.release_year)
                       FROM Games g2 
                       WHERE g2.genre = 'puzzle');

SQL

SQL with Games: Query 6 (Average Release Year)

Write a SQL query that returns the average release_year of games in the Games table. Use the ROUND function to round the result to the nearest integer and rename the column with an alias avg_release_year. 


+------------------+
| avg_release_year |
+------------------+
|             2005 |
+------------------+
1 row in set (0.01 sec)

Expected result

SQL with Games: Query 6 Solution

One solution is provided below:


SELECT ROUND(AVG(release_year)) as avg_release_year
FROM Games;

SQL

Part III: More Practice (Using the CSE 154 Query Tester Tool)

Next, we will use the CSE 154 Query Tester tool to practice writing queries for a few databases. The query tester is located here.

world Database (by Sylvia Tashev, Stefanie Hatcher)

The first database we will practice writing SQL queries with includes tables containing information about countries, cities, and languages (note that this database isn't up to date, but still is useful for practicing queries). The schema for the three tables are on the next three slides below.

countries Table (world Database)

(Other columns: independence_year, region, surface_area, life_expectancy, gnp_old, local_name, government_form, capital, code2, head_of_state)
code name continent population gnp ...
AFG Afghanistan Asia 22720000 5976.0 ...
NLD Netherlands Europe 15864000 371362.0 ...
..................

cities Table (world Database)

id name country_code district population
3793New YorkUSANew York8008278
1Los AngelesUSACalifornia3694820
...............

languages Table (world Database)

country_codelanguageofficialpercentage
AFGPashtoT52.4
NLDDutchT95.6
............

SQL with world: Query 1 (Official Languages)

List all of the official languages spoken around the world in reverse alphabetical order. 


+------------+
| language   |
+------------+
| Zulu       |
| Xhosa      |
| Wolof      |
| Vietnamese |
| Uzbek      |
| ...        |
+------------+
102 rows in set (0.02 sec)

Expected results (first 5 rows)

Query 1: Solution

One solution is provided below:


SELECT DISTINCT language
FROM languages
WHERE official='T'
ORDER BY language DESC;

SQL

SQL with world: Query 2 (Republics)

What countries have the word Republic as part of their name? 


+---------------------------------------+
| name                                  |
+---------------------------------------+
| Dominican Republic                    |
| Central African Republic              |
| Congo, The Democratic Republic of the |
| Czech Republic                        |
+---------------------------------------+
4 rows in set (0.00 sec)

Expected results

Query 2: Solution

One solution is provided below:


SELECT name
FROM countries
WHERE name LIKE '%Republic%';

SQL

SQL with world: Query 3 (Names of Countries Not in Africa)

List the names of all countries that are not in Africa. 


+--------------------------+
| name                     |
+--------------------------+
| Afghanistan              |
| Netherlands              |
| Netherlands Antilles     |
| Albania                  |
| American Samoa           |
| ...                      |
+--------------------------+
181 rows in set (0.00 sec)

Expected results (first 5 rows)

Query 3: Solution

Two solutions are provided below. Note that the first uses != and the second uses <> for the inequality operators. 

          
SELECT name
FROM countries
WHERE continent != 'Africa';

SQL

  
SELECT name
FROM countries
WHERE continent <> 'Africa';

SQL

SQL with world: Query 4 (Low GNP)

Which countries have a GNP between $10,000 AND $50,000? Write a query that returns the name and gnp of these countries. 

  
+------------------------+----------+
| name                   | gnp      |
+------------------------+----------+
| Algeria                | 49982.00 |
| United Arab Emirates   | 37966.00 |
| Bangladesh             | 32852.00 |
| Brunei                 | 11705.00 |
| Bulgaria               | 12178.00 |
...
+------------------------+----------+
36 rows in set (0.00 sec)

Expected results (first 5 rows)

Query 4: Solution


SELECT name, gnp
FROM countries
WHERE gnp BETWEEN 10000 AND 50000;

SQL

You can also use:


SELECT name, gnp
FROM countries
WHERE gnp < 50000
AND gnp > 10000;

SQL

SQL with world: Query 5 (Dense Populations)

Which countries have the densest population (population divided by surface area)? Write a query that returns the name, population, surface_area, and population_density of all countries, ordered by population densities (descending). 

  
+------------+------------+--------------+--------------------+
| name       | population | surface_area | population_density |
+------------+------------+--------------+--------------------+
| Macao      |     473000 |        18.00 |       26277.777778 |
| Monaco     |      34000 |         1.50 |       22666.666667 |
| Hong Kong  |    6782000 |      1075.00 |        6308.837209 |
| Singapore  |    3567000 |       618.00 |        5771.844660 |
| Gibraltar  |      25000 |         6.00 |        4166.666667 |
| ...        |        ... |          ... |                ... |
+------------+------------+--------------+--------------------+
239 rows in set (0.00 sec)

Expected results (first 5 rows)

Query 5: Solution

          
SELECT name, population, surface_area,
   population / surface_area as population_density
FROM countries
ORDER BY population_density DESC;

SQL

SQL with world: Query 6 (English-Speaking Countries)

List the country_codes for all countries where English is spoken as an official language. 

  
+--------------+
| country_code |
+--------------+
| AIA          | 
| ASM          |
| ATG          | 
| BLZ          |
| BMU          |
| ...          |
+--------------+
44 rows in set (0.02 sec)

Expected results (first 5 rows)

Query 6: Solution

          
SELECT country_code
FROM languages
WHERE language = 'English' AND official = 'T';

SQL