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)
I recreated the database and that helped.
now the problem is with queries.sh 1.1:11
codeally@3411150ee09f:~/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.8125000000000000
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:
Algeria
Argentina
Belgium
Brazil
Chile
Colombia
Costa Rica
France
Germany
Greece
Mexico
Netherlands
Nigeria
Switzerland
United States
Uruguay
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:
2018|France
2014|Germany
List of teams that start with 'Co':
Colombia
Do you know how to use the diff command? You can pipe your results into a file then compare that file against the expected output to see what value is not exactly as expected.
ohhh sorry i never noticed that there were a file named expected_output.txt… thanks
I changed the order of France and Germany. Now diff command is not showing any differences but still the test is unchecked??
thats right, they are two just when I copied that i missed the second name, but there are two names and after diff it is showing no differences any more.
okay, at this point save all your files outside (the sql dump the script or other stuff you created)
Then look at the pinned post to see if any of the troubleshooting steps help
if still stuck, I think you should create a new topic with a different title as the original issue this topic was opened for is already fixed. (hopefully someone with the correct experience will help then)
Hello Yaniani55,
Yes, what helped me was to save the files outside and resetting the project and copying back the code. But I did not know what exactly the problem was.
