CSE 154

Section 16: 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.

Exploration Session This Week

React

Today, 4:30pm, MGH389

  • One HW Extra Credit Point Per Session Attended!
  • If you have a legitmate conflict, fill out the form on the exploration sessions page of the course website to watch the Panapto and take a short quiz for credit.

React is an industry favorite JavaScript library for building complex web applications with rapidly changing data.

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 or .csv 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

We've provided a few practice datasets for you to set up on your phpMyAdmin:

  • menu-data.csv is a CSV file saved from an Excel-like program, with a small collection of rows for items in a cafe menu.
  • setup-fooddata.sql contains SQL commands to create a table with nutritional data from the USDA food database.
  • setup-games.sql also contains SQL commands, creating the same table used in lecture yesterday.

Setting Things Up: Using phpMyAdmin

Both .sql and .csv file types can be imported easily into your phpMyAdmin with a few button clicks!

You can follow this demo video (no audio) for creating your first database on phpMyAdmin using the menu-data.csv file.

You can find another demo video for creating a gamesdb database with a single Games table using the setup-games.sql file (you can follow the same process for setup-fooddata.sql).

For more details, 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 menu table

In this part, you'll have a chance to practice SQL queries with a small cafe menu! If you have successfully imported the menu-data.csv file and renamed the table "TABLE 1" to menu, this table will have the following structure:

id name category subcategory price cost
1 Blueberry Scone Bakery Scones 3.50 0.75
2 Blueberry Scone (Vegan) Bakery Scones 3.50 0.85
3 Chocolate Chip Scone Bakery Scones 3.70 0.75
... ... ... ... ... ...
59 Bubble Tea Drinks Tea 4.00 5.50

SQL with menu: Query 1 (Prepping)

For our first query, let's first see what's on the menu.

What query would lists all of the menu item names with their price in the menu table?

Solution:


SELECT name, price
FROM menu;

SQL

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

SQL with menu: Query 2 (Coffee!)

Write a query that lists the name, cost, and price of all menu items that contain the string "Coffee" in their name. Order the results by price (in ascending order). 


+--------------------------------------+------+-------+
| name                                 | cost | price |
+--------------------------------------+------+-------+
| Brewed Coffee (Black)                | 0.65 | 1.25  |
| Brewed Coffee (With Cream)           | 0.85 | 1.50  |
| Brewed Coffee (With Cream and Sugar) | 1.05 | 2.00  |
| Coffee Cake                          | 1.35 | 3.50  |
| Coffee Cake (Vegan)                  | 1.55 | 3.50  |
+--------------------------------------+------+-------+
5 rows in set (0.01 sec)

Expected results

SQL with menu: Query 2 Solution

One solution is provided below:


SELECT name, cost, price
FROM menu
WHERE name LIKE "%Coffee%";

SQL

SQL with menu: Query 3 (On a Budget)

Write a query that lists the name, price, and category of all menu items that are sold for a price less than $2.00. Order the results by price in descending order, breaking ties by name alphabetically. 


+----------------------------+-------+----------+
| name                       | price | category |
+----------------------------+-------+----------+
| Brewed Coffee (With Cream) | 0.85  | Drinks   |
| Plain Toast                | 0.65  | Bakery   |
| Brewed Coffee (Black)      | 1.25  | Drinks   |
| ...                        | ...   | ...      |
| Iced Water                 | 1.55  | Drinks   |
+----------------------------+-------+----------+
8 rows in set (0.01 sec)

Expected results

SQL with menu: Query 3 Solution

One solution is provided below:

SELECT name, price, category
FROM menu
WHERE price < 2
ORDER BY price DESC, name;

SQL

SQL with menu: Query 4 (Where's the Profit?)

It looks like there's an item that costs the cafe more than it sells for! Can you write a query to find the name of the item?

SQL with menu: Query 4 Solution

SELECT name
FROM menu
WHERE price < cost

SQL

SQL with menu: Query 5 (Highest Price)

Write a query that lists the name, price, and cost of the most expensive menu item on the menu (by price).

SQL with menu: Query 5 Solution

One possible solution is provided below: 

SELECT name, price, cost
FROM menu
ORDER BY price DESC
LIMIT 1;

SQL

Part II: Using SQL with Video Game Data

In this part of the section, we will use the video game dataset provided (seen in Wednesday'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 I: Writing SQL Queries with the food_data table

Next, you'll use the smaller food_data 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 food_data 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 food_data: Query 1 (Prepping)

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

Solution:


SELECT description
FROM food_data;

SQL

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

SQL with food_data: Query 2 (Proteins)

Write a query that returns all of the food descriptions, grams of protein, and grams of carb for food_data 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 food_data: Query 2 Solution

One solution is provided below: 


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

SQL

SQL with food_data: 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 food_data: Query 3 Solution

One solution is provided below: 


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

SQL

SQL with food_data: 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 food_data;

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 food_data: 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 food_data: Query 4 Solution

One solution is provided below: 


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

SQL

SQL with food_data: 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 food_data: Query 5 Solution

One solution is provided below:


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

SQL

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

You can 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)

This database 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