#! /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
echo $($PSQL "TRUNCATE teams, games")
cat games_test.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
# Eliminating Title line
if [[ $YEAR != "year" ]]
# get team_id
OPOONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
# if not found
if [[ -z $OPPONENT_ID ]]
then
# insert team
INSERT_OPPONENT_TEAM=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
if [[ INSERT_OPPONENT_TEAM == "INSERT 0 1" ]]
then
echo Inserted into teams, $OPPONENT
fi
# new winner team_id
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
fi
# get team_id
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
# if not found
if [[ -z $WINNER_ID ]]
then
# insert team
INSERT_WINNER_TEAM=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
if [[ INSERT_WINNER_TEAM == "INSERT 0 1" ]]
then
echo Inserted into teams, $WINNER
fi
# new team_id
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
fi
LOG_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner, opponent, winner_id, opponent_id, winner_goals, opponent_goals") VALUES($YEAR, '$ROUND', '$WINNER', 'OPPONENT', $WINNER_ID, $OPPONENT_ID, $WINNER_GOALS, $OPPONENT_GOALS)"
if [[ $LOG_GAME_RESULT == "INSERT 0 1" ]]
then
echo Inserted, $YEAR $ROUND $WINNER $OPPONENT $WINNER_ID $OPPONENT_ID $WINNER_GOALS $OPPONENT_GOALS
fi
fi
done
odeally@d04c40c6043a:~/project$ ./insert_data.sh
TRUNCATE TABLE
./insert_data.sh: line 48: syntax error near unexpected token `('
./insert_data.sh: line 48: `LOG_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner, opponent, winner_id, opponent_id, winner_goals, opponent_goals") VALUES($YEAR, '$ROUND', '$WINNER', 'OPPONENT', $WINNER_ID, $OPPONENT_ID, $WINNER_GOALS, $OPPONENT_GOALS)"'
codeally@d04c40c6043a:~/project$
Hi
the ones with single quotes are VARCHAR and the other ones without sinle quotes are INT.and sorry I missed to type the $ sign with second one.
regards
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
this should be
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
Hi,
sorry I could check it now that i could connect with codeally.
here is what psql prompt shows:
(24 rows)
(END)
there is END at the end which I dont know how to exit from it as after that I cant run it any further.
and this is the error
/insert_data.sh: line 18: get: command not found
ERROR: column "winner" of relation "games" does not exist
LINE 1: INSERT INTO games(year, round, winner, opponent, winner_goal...
^
/insert_data.sh: line 18: get: command not found
ERROR: column "winner" of relation "games" does not exist
LINE 1: INSERT INTO games(year, round, winner, opponent, winner_id, ...
^
./insert_data.sh: line 18: get: command not found
ERROR: column "winner" of relation "games" does not exist
LINE 1: INSERT INTO games(year, round, winner, opponent, winner_id, ...
^
codeally@466adf0b8792:~/project$
yes thats right, i made chages and the script works but i am not getting green checks for:
When you run your insert_data.sh script, it should add each unique team to the teams table. There should be 24 rows
When you run your insert_data.sh script, it should insert a row for each line in the games.csv file (other than the top line of the file). There should be 32 rows. Each row should have every column filled in with the appropriate info. Make sure to add the correct IDâs from the teams table (you cannot hard-code the values)
the code is :
#! /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
echo $($PSQL "TRUNCATE teams, games")
cat games_test.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
# Eliminating Title line
if [[ $YEAR != "year" ]]
then
# echo $YEAR $ROUND $WINNER $OPPONENT $WINNER_GOALS $OPPONENT_GOALS
get team_id
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
# if not found
if [[ -z $OPPONENT_ID ]]
then
# insert team
INSERT_OPPONENT_TEAM=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
if [[ INSERT_OPPONENT_TEAM == "INSERT 0 1" ]]
then
echo Inserted into teams, $OPPONENT
fi
# new opp team_id
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
fi
# get team_id
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
# if not found
if [[ -z $WINNER_ID ]]
then
# insert team
INSERT_WINNER_TEAM=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
if [[ INSERT_WINNER_TEAM == "INSERT 0 1" ]]
then
echo Inserted into teams, $WINNER
fi
# new team_id
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
fi
LOG_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals, team_id) VALUES($YEAR, '$ROUND', $WINNER_ID, $OPPONENT_ID, $WINNER_GOALS, $OPPONENT_GOALS, $WINNER_ID)")
if [[ $LOG_GAME_RESULT == "INSERT 0 1" ]]
then
echo Inserted, $YEAR $ROUND $WINNER $OPPONENT $WINNER_ID $OPPONENT_ID $WINNER_GOALS $OPPONENT_GOALS $WINNER_ID
fi
fi
done
Iâve edited your code for readability. When you enter a code block into a forum post, please precede it with a separate line of three backticks and follow it with a separate line of three backticks to make it easier to read.
You can also use the âpreformatted textâ tool in the editor (</>) to add backticks around text.
script is working fine but these last three are not passing:
When you run your insert_data.sh script, it should add each unique team to the teams table. There should be 24 rows
When you run your insert_data.sh script, it should insert a row for each line in the games.csv file (other than the top line of the file). There should be 32 rows. Each row should have every column filled in with the appropriate info. Make sure to add the correct IDâs from the teams table (you cannot hard-code the values)
You should correctly complete the queries in the queries.sh file. Fill in each empty echo command to get the output of what is suggested with the command above it. Only use a single line like the first query. The output should match what is in the expected_output.txt file
codeally@466adf0b8792:~/project$ ./queries.sh
Total number of goals in all games from winning teams:
68
Total number of goals in all games from both teams combined:
90
Average number of goals in all games from the winning teams:
2.1250000000000000
Average number of goals in all games from the winning teams rounded to two decimal places:
2.13
Average number of goals in all games from both teams:
2.81250000000000000000
Most goals scored in a single game by one team:
7
Number of games where the winning team scored more than two goals:
6
Winner of the 2018 tournament team name:
France
List of teams who played in the 2014 'Eighth-Final' round:
Argentina
Belgium
Brazil
Colombia
Costa Rica
France
Germany
Netherlands
List of unique winning team names in the whole data set:
Argentina
Belgium
Brazil
Colombia
Costa Rica
Croatia
England
France
Germany
Netherlands
Russia
Sweden
Uruguay
Year and team name of all the champions:
2014|Germany
2018|France
List of teams that start with 'Co':
Colombia
Costa Rica
but I am not passing the test for the last three tasks.
yeah so this part you have to work out the issues one by one.
For eg. for the teams table, try to figure out what went wrong. (count the rows, see if there are any missing teams or duplicate teams etc)
