World Cup Database - Build a World Cup Database - games table

Backend Development
1

Tell us what’s happening:
All tests are passing except the import to the games table. Results from SELECT query show 32 tables, but test will not pass. It completes successfully in under 20 seconds when running the script

Your code so far

`type or paste code here`

#! /bin/bash

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.

echo -e "$($PSQL "TRUNCATE games, teams;")"
echo -e "\nSuccessful Truncate!\n"

#Games Data Import

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  if [[ $WINNER != "winner" ]]
  then 
    #get team ID
    TEAM_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")
    #if not found
    if [[ -z $TEAM_ID ]]
    then 
       #Insert into teams
      INSERT_TEAM_ID=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
    if [[ $INSERT_TEAM_ID == "INSERT 0 1" ]]
    then 
      echo Inserted $WINNER into TEAMS!
    fi
    #get new id
    TEAM_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")
  fi
fi
done
#insert opponent for full list
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  if [[ $OPPONENT != "opponent" ]]
  then 
    #get team ID
    TEAM_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")
    #if not found
    if [[ -z $TEAM_ID ]]
    then 
       #Insert into teams
      INSERT_TEAM_ID=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
    if [[ $INSERT_TEAM_ID == "INSERT 0 1" ]]
    then 
      echo Inserted $OPPONENT into TEAMS!
    fi
    #get new id
    TEAM_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")
  fi
fi
done

echo -e "\nInserting into games\n"
# Inserting info into games file
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do


  if [[ $YEAR != "year" ]]
  then 
  #get game ID
  GAME_ID=$($PSQL "SELECT game_id FROM games WHERE round = '$ROUND' AND winner = '$WINNER' AND year = '$YEAR'")
  #if not found

  if [[ -z $GAME_ID ]]
  
  then 
  WINNER_ID="$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")"
  OPPONENT_ID="$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")" 
  #INsert rows
  INSERT_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner, opponent, winner_goals, opponent_goals, winner_id, opponent_id) VALUES('$YEAR', '$ROUND', '$WINNER', '$OPPONENT', '$WINNER_GOALS', '$OPPONENT_GOALS', '$WINNER_ID', '$OPPONENT_ID')")
  if [[ $INSERT_GAME_RESULT == 'INSERT 0 1' ]]
    then
    echo Inserted $ROUND Successfully!
    fi
  fi
fi
done

![Screenshot 2022-11-22 135034|690x448](upload://fnxUhpZPGJFAOioTRuec3d8GAKK.jpeg)

Your browser information:

User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36

Challenge: World Cup Database - Build a World Cup Database

Link to the challenge:

Screenshot 2022-11-22 135034
Screenshot 2022-11-22 1350341088×707 249 KB

2

Try checking specifically what columns it asks for the games table, might be that you are adding things it is not asking for or you are missing sth.

1 Like
3

thank you much! looks like i had two additional columns that were causing the issue.

1 Like