I'm sorry if you don't like baseball, you will get more flexibility on the next project

CREATE TABLE Statements

  1. Use the Date Model (from class) to write 4 CREATE TABLE statements in Notepad++ (or notepad). If you were absent, search the internet for "SQL CREATE TABLE Statements" and you'll get a lot of info
    1. Stadiums Table
    2. Seat Sections Table
    3. Teams Table
    4. Games Table
  2. Have me check your CREATE TABLE statements
  3. Run the CREATE TABLE statements one at a time in APEX SQL Workshop

INSERT Statements

  1. Roll the die to get your division
    1. NL East
    2. AL East
    3. NL Central
    4. AL Central
    5. NL West
    6. AL West
  2. You each have five teams in your division
  3. If you were absent, search the internet for "SQL INSERT Statements" and you'll get a lot of info
  4. Write the INSERT statements to add the data into your database, all of the data should be on your team's website, which can be reached through MLB.com
    1. Add team info to TEAMS table
    2. Add stadium info to STADIUM table
    3. Look at the seating map for your stadium, add 6 different sections' info into the Seat Sections table, you don't have to do the whole stadium
    4. Add 10 games into your table, these should be between only teams in your division, use real data from teams schedule. You should have a variety of teams, not all the same home team, even if they are your favorite.

SHOW ME YOUR WORK FOR GRADING BEFORE YOU GO ON

UPDATE & DELETE Statements

  1. If you were absent, search the internet for "SQL UPDATE statements" and "SQL DELETE statements"
  2. Run the following code, if it doesn't work, check to make sure you have the games table and that you didnt call it game on accident
CREATE TABLE copy_games
AS (SELECT * FROM games)
  1. Now we are going to change the scores run the following. If it doesn't work, it's probably because you used different column names for the home and away scores, you can modify it to match your table
UPDATE copy_games
SET away_score = 5,
home_score = 6
 
  1. Check to make sure it worked
SELECT *
FROM copy_games
  1. Now if you go look, you'll see that it changed the score for every game, that's because we didn't use a WHERE. We are now going to wipe out the scores for the games
UPDATE copy_games
SET away_score = NULL,
home_score = NULL
  1. Now we are going to UPDATE with a WHERE, change the 96 to one of your own game_numbers in the WHERE, you will also have to change the column names if you didn't use game_number
UPDATE copy_games
SET home_score = 3,
away_score = 2
WHERE game_number = 96
  1. Check your work
SELECT *
FROM copy_games
  1. DELETE works a lot like UPDATE, delete one of your games, again be sure to use a where, otherwise you will delete all of your rows
DELETE FROM copy_games
WHERE home_score IS NULL
  1. Check one more time to make sure you deleted all but one row in your copy_games table
SELECT *
FROM copy_games

SHOW ME YOUR WORK FOR GRADING