Queries.sh file in build a world cup DB project can't pass

1

Hello ,
I have problem passing the last test case in the build a world cup database project , the file queries.sh in which I wrote code to extract some info about the database can’t pass the test although I got the output exactly as it is in the output.txt file , I don’t know where the problem is in , is it from the virtual machine or what ? , I provided the code I used for the file
if any one could help ??

#! /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)+sum(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 round(avg(winner_goals)+avg(opponent_goals),16) 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(winner_goals) from games where winner_goals>2")"

echo -e "\nWinner of the q2018 tournament team name:"
echo "$($PSQL "select name from teams inner join games on teams.team_id = games.winner_id where year=2018 and round='Final'")"

echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL "select distinct name from teams inner join games on teams.team_id=games.winner_id or teams.team_id=games.opponent_id 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 teams inner join games on teams.team_id = games.winner_id order by name asc")"

echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "select year, name from teams inner join games ON teams.team_id = games.winner_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%'")"
2

Could you share your insert_data.sh file as well?

3

thanks , here is the insert_data.sh file

#! /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 $($PSQL "truncate games,teams" )
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
if [[ $WINNER != winner  ]]
then
# select team_id from the teams table for winner  
TEAM_ID=$($PSQL "select team_id from teams where name='$WINNER'")
TEAM_ID_2=$($PSQL "select team_id from teams where name='$OPPONENT'")
# if not found 
  if [[ -z $TEAM_ID ]]
  then
  # insert this winner team into the teams table
  INSERT_TEAM_RESULT=$($PSQL "insert into teams(name) values('$WINNER')")
  # if insert is done , echo the team name
    if [[ $INSERT_TEAM_RESULT == "INSERT 0 1" ]]
    then
    echo inserted into teams , $WINNER
    fi
  fi
fi
# for opponent teams , if not found
if [[ -z $TEAM_ID_2 ]]
then
if [[ $OPPONENT != opponent ]]
then
# insert this opponent team into the teams table
INSERT_TEAM_RESULT_2=$($PSQL "insert into teams(name) values('$OPPONENT')")
# if insert is done , echo the opponent 
    if [[ $INSERT_TEAM_RESULT_2 == "INSERT 0 1" ]]
    then
    echo inserted into teams , $OPPONENT
    fi
fi
fi
done
# for the games table 
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do 
WINNER_ID=$($PSQL "select team_id from teams where name='$WINNER'")
OPPONENT_ID=$($PSQL "select team_id from teams where name='$OPPONENT'")
INSERT_GAME_RESULT=$($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)")
if [[ $INSERT_GAME_RESULT == "INSERT 0 1" ]]
then
echo "inserted into games , $YEAR $ROUND $WINNER"
fi
done
4

run the code then pipe the output into a file
then use the diff command to compare it against the expected output (it should be in the files in the project)

2 Likes
5

thank you , Solved !! .

1 Like
6

mabrook! (Egyptian here too)

1 Like
7

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.