Section 15 - POST and SQL

Section 15: POST and SQL

Section Goals

By the end of this section, you should know how to:

Create POST endpoints
Use PHPMyAdmin to create databases and tables
Use SQL to query certain information from a table

Final Project Proposal Feedback

We have released the feedback on the proposal/wireframe you submitted for the final project. Please make sure you review the feedback and reach out to the course staff if you have any questions. We are able to support you on this final project more than we are able to on a HW assignment. There are many resources available in this class:

Post questions on Piazza
Come to the WPL
Go to Melissa's Office Hours
Email/talk to any of the TAs

POST Walkthrough

Let's look at POST as "GET, with some extra steps". Here are steps you can follow to turn a GET request into a POST request:

npm install multer
Add the multer package to handle form-data.
const multer = require("multer");
Add the following middleware to handle various kinds of POST request:
app.use(express.urlencoded({extended: true}));
app.use(express.json());
app.use(multer().none());
Change app.get to app.post
Change req.params or req.query to req.body
Finally, check that the required POST parameters are passed.

Exercise 1: Updating the typing test to use POST

Recall from last Tuesday's section that we wrote an endpoint for a typing test in which clients could submit high scores to our servers. We used GET, but POST would be far more appropriate, since we weren't actually GETting any info from the server.

Open up your local solution to the exercise, or the solution JS, and edit it so that the final endpoint uses POST. You can use Postman to test out your endpoint, though be sure to copy over the resources/scores.json file from that section.

POST involves a bit more setup, so utilize the previous slides as reference. Since the endpoint used to have URL parameters, change them into body parameters and change the endpoint to highscore/send.

Solution

SQL Overview

Databases are large collections of data
Tables are contained within databases. Tables have columns, each corresponding to a name and a type (for example, an "age" column with type "INT"). Tables store individual records into each row.
There are many data types in SQL. We will go over some of these on Wednesday.
SQL communicates with a database to retrieve, add, or modify information stored within. We will be using the MySQL language. Most SQL variants are quite similar.
You can review syntax with this SQL Cheatsheet

MAMP and PHPMyAdmin

MAMP is a tool for hosting a local webserver, like we do using nodemon. The main reason we are installing it is that it comes with a MySQL server that we can access through a program called PHPMyAdmin. It will host the database, which we can access later from our Node webservice.

Follow this guide to get MAMP set up on your machine:

See the slide below for an important Windows fix and how to verify you did things right.

Windows Fix

There is a major bug in MAMP MySQL that may cause yours to fail.

Open MAMP and after it starts up, "Stop" the server.
Open C:/MAMP/conf/mysql/my.ini in a text editor.
Somewhere around line 50, comment out this line with a #
#log-bin=mysql-bin
Start MAMP and see if the MySQL light turns green after a bit.
If that doesn't work, go to C:\MAMP\db\mysql and delete all of the mysql-bin.* while the server is off.
To verify it is all working, go to the MAMP start page and visit "tools > phpMyAdmin"

PHPMyAdmin Demo: Creating a database

Databases are collections of tables. Usually your webservice will acess one database, and then use one or more tables from it to get and store information.

On the left side of the screen is a list of databases.

Click the "New" button, enter the database name games, and hit "create".

You should also set up a database called cafe.

PHPMyAdmin Demo: Importing a table with a SQL file

SQL files simply contain SQL code. They can create tables, add values into those tables, delete tables, select data, and more. You don't need to understand the code inside to use them.

To import a SQL file, select a database on the left, then go to the "import" tab at the top. Upload the SQL file and hit "Go" at the bottom of the page.

You may get a bunch of errors. They may be errors in the SQL code, but usually they don't mean much, and you can just dismiss them. Expand the database on the left to see if the new table(s) have been added successfully.

Using this process, import the Games table into the games database using setup-games.sql.

PHPMyAdmin Demo: Importing a table with a CSV file

This is similar to importing with a SQL file, but you need to set a few options.

After adding the CSV file, make sure "CSV" is the selected file format, and provide a table name. Depending on your version of MAMP, you may also need to check a box that says to treat the first row of the file as column names.

Import menu-data.csv into the cafe database with the table name "menu".

You can actually use these to import Excell spreadsheets into a database. Just open Excell or Google Sheets, make a table, and then download/save it as a CSV file. Try it out with this spreadsheet

PHPMyAdmin Demo: Running a SQL Query

You can run SQL queries on your databases in PHPMyAdmin!

Select a database on the left, then go to the SQL tab on top. There you should be able to write a SQL query and submit it to the database to be processed.

Enter the following query in the games database to select the names of all of the games. Hit the "Go" button in the bottom-right to submit it.

SELECT name FROM Games;

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

New Keywords: SELECT FROM

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

What query would list 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 PHPMyAdmin

SQL with `menu`: Query 2 (On a Budget)

New Keywords: WHERE, ORDER BY

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 2 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 3 (Coffee!)

New Keywords: LIKE

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 3 Solution

One solution is provided below:


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

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)

New Keywords: LIMIT

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. 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)

New Keywords: DISTINCT

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 (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 2 Solution

One solution is provided below:


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

SQL

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

New Keywords: AND/OR/NOT

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         |
            | ...                        | ...      | ...          |
            +----------------------------+----------+--------------+
            8 rows in set (0.01 sec)

Expected results (first 5 rows)

SQL with `Games`: Query 3 Solution

One solution is provided below:


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

SQL

Challenge Problems

The following two problems contain material that will not necessarily be covered in this course. Try them to challenge yourself and learn new things, but do not feel nervous if you cannot do them.

SQL with `Games`: Query 4 (Average Release Year)

New Keywords: AVG, ROUND, AS

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 4 Solution

One solution is provided below:


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

SQL

SQL with `Games`: Query 5 (Retro Puzzle Games)

New Concepts: Subqueries, MIN

Note: This is very Difficult! You can see how far you can get with what we have taught, or look into the above links for resources.

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. We also want to differentiate between the two tables, so we label them g1 and g2.):


            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