Tell us what’s happening:
I completed all the tasks but only the last task keeps telling me that I have not finished my queries.sh correctly…
Did I overlook something? I think the output of these queries is the same as in exepcted_output.txt …
Another question:
Average number of goals in all games from both teams.
Shouldnt that actually be something like this? (my math is not that good maybe)
(AVG(winner_goals) + AVG(opponent_goals)) / 2
Your code so far
#! /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 AVG(winner_goals) + AVG(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(game_id) FROM games WHERE winner_goals > 2”)”
echo -e “\nWinner of the 2018 tournament team name:”
echo “$($PSQL “SELECT name FROM teams WHERE team_id = (SELECT winner_id FROM games WHERE round = ‘Final’ AND year = 2018)”)”
echo -e “\nList of teams who played in the 2014 ‘Eighth-Final’ round:”
echo “$($PSQL “SELECT name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id WHERE round=‘Eighth-Final’ AND year=2014 UNION SELECT name FROM games LEFT JOIN teams ON games.opponent_id = teams.team_id WHERE round=‘Eighth-Final’ AND year=2014 ORDER BY name ASC”)”
echo -e “\nList of unique winning team names in the whole data set:”
echo “$($PSQL “SELECT DISTINCT name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id ORDER BY name ASC”)”
echo -e “\nYear and team name of all the champions:”
echo “$($PSQL “SELECT year, name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id WHERE round=‘Final’”)”
echo -e “\nList of teams that start with ‘Co’:”
echo “$($PSQL “SELECT name FROM teams WHERE Name LIKE ‘Co%’”)”
Your browser information:
User Agent is: Mozilla/5.0 (X11; Linux x86_64; rv:105.0) Gecko/20100101 Firefox/105.0
Challenge: World Cup Database - Build a World Cup Database
Link to the challenge: