sql-warmup.md

SQL (Pre) Warm-up

1. Is MySQL running?
2. Are you able to log-in to mysql on the terminal?
3. Remember to copy/paste as you test your SQL from VSCode into the MySQL console.

SQL Warm-up

1. Create a new database named games
2. Create a new table called titles

• Have a primary ID column
• Have a string for the game_title
• Have a string for the ESRB rating
• Have a date column for the release date (hint: look for MySQL datetime columns)
• Create an index on the primary key
• (Bonus) Create an index to speed up sorting by the release date (Hint: look for MySQL index types)

1. Insert your favorite games
2. Write a query to return your favorite games ordered by their ESRB rating
3. Write a query to return your favorite games ordered by release date.
4. Write a query to return your favorite games that have not yet been released

STOP

Part II

In order to gather user ratings for games, you need to create a users table. (In part III we will create a many:many table to track user ratings of our games)

1. Create a new table users

• What do we always create when we make a table in SQL?
• Create a string column to hold their email
• Create a string column to hold their password
• Create a date column to track their age (for calculating games they can play according to ESRB ratings)
• Create a true/false column to track if we have authorized/approved their email (to prevent SPAM)

1. Insert some users (some that are approved, others that are not approved)
   • Write a query to get approved users.
   • Write a query to get all users older than 18
   • Write a query to get all users younger than 12
2. Write a query to update a user that hasn't yet been approved. Set their approval status to true.

STOP

Part III

Now, we want to be able to give users the ability to rate their favorite games. Each user can rate many games, and each game will have many ratings.

1. Create a table users_to_games_ratings

• Create our favorite default column
• Create a foreign key that references users.id
• Create a foreign key that references titles.id
• Create an int / double column that holds the rating

1. Insert some ratings for different users of your favorite games
2. Write a query to get the title of both the game, and the username as well as their ratings.