You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
42 lines
2.1 KiB
Bash
42 lines
2.1 KiB
Bash
#! /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 + 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 FULL JOIN teams ON games.winner_id = teams.team_id WHERE year=2018 AND round='Final'")"
|
|
|
|
echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
|
|
echo -e "$($PSQL "(SELECT name FROM games INNER JOIN teams ON games.winner_id = teams.team_id WHERE year=2014 AND round='Eighth-Final') UNION (SELECT name FROM games FULL JOIN teams ON games.opponent_id = teams.team_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 -e "$($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 -e "$($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 -e "$($PSQL "SELECT name FROM teams WHERE name LIKE 'Co%'")"
|