When running the tests, I am getting this error: SUBTASKS 1.1 “before all” hook for “:1 “worldcup” database should exist”
My insert_data.sh inserts the data correctly and my queries.sh gives the expected output.
I’ve tried deleting and recreating the container and importing my .sql and .sh files, to no avail.
Using the time command on insert_data.sh, the script takes about 1m20s.
I’ve read on another topic that this command would help, but it did nothing for me: sed ‘s/20000/50000/’ -i /home/codeally/project/.freeCodeCamp/package.json
insert_data.sh
#! /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.
GAMES=games.csv
GET_ID () {
team_id=$($PSQL "select team_id from teams where name='$1';")
if [[ -z $team_id ]]
then
result=$($PSQL "insert into teams(name) values('$1');")
team_id=$($PSQL "select team_id from teams where name='$1';")
fi
echo $team_id
}
echo $($PSQL "truncate games, teams;")
cat $GAMES | while IFS="," read -r year round winner opponent winner_goals opponent_goals
do
if [[ $year != year ]]
then
winner_id=$(GET_ID "$winner")
opponent_id=$(GET_ID "$opponent")
echo $winner_id
echo $opponent_id
echo $($PSQL "insert into games(year, round, winner_id, opponent_id, winner_goals, opponent_goals) values($year, '$round', $winner_id, $opponent_id, $winner_goals, $opponent_goals);")
fi
done
queries.sh
#! /bin/bash
PSQL="psql --username=freecodecamp --dbname=worldcup --no-align --tuples-only -c"
# Do not change code above this line. Use the PSQL variable above to query your database.
echo -e "\nTotal number of goals in all games from winning teams:"
echo "$($PSQL "SELECT SUM(winner_goals) FROM games")"
echo -e "\nTotal number of goals in all games from both teams combined:"
echo "$($PSQL "select sum(winner_goals + opponent_goals) from games;")"
echo -e "\nAverage number of goals in all games from the winning teams:"
echo "$($PSQL "select avg(winner_goals) from games;")"
echo -e "\nAverage number of goals in all games from the winning teams rounded to two decimal places:"
echo "$($PSQL "select round(avg(winner_goals), 2) from games;")"
echo -e "\nAverage number of goals in all games from both teams:"
echo "$($PSQL "select avg(winner_goals + opponent_goals) from games;")"
echo -e "\nMost goals scored in a single game by one team:"
echo "$($PSQL "select max(winner_goals) from games;")"
echo -e "\nNumber of games where the winning team scored more than two goals:"
echo "$($PSQL "select count(*) from games where winner_goals > 2;")"
echo -e "\nWinner of the 2018 tournament team name:"
echo "$($PSQL "select name from games inner join teams on games.winner_id = teams.team_id where round='Final' and year=2018;")"
echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL "select name from teams where team_id in (select winner_id from games where year=2014 and round='Eighth-Final') or team_id in (select opponent_id from games where year=2014 and round='Eighth-Final') order by name;")"
echo -e "\nList of unique winning team names in the whole data set:"
echo "$($PSQL "select distinct(name) from games inner join teams on games.winner_id = teams.team_id order by name;")"
echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "select year, name from games inner join teams on games.winner_id = teams.team_id where round = 'Final' order by year;")"
echo -e "\nList of teams that start with 'Co':"
echo "$($PSQL "select name from teams where name like 'Co%';")"