-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathqueries.sh
61 lines (43 loc) · 3.05 KB
/
queries.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
#! /bin/bash
# PSQL="psql --username=freecodecamp --dbname=worldcup --no-align --tuples-only -c"
PSQL="psql --username=postgres --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 winner FROM games WHERE round='Final' AND year=2018")"
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 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(winner) FROM games ORDER BY winner ASC")"
echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "SELECT year, winner FROM games WHERE round='Final' ORDER BY year ASC")"
echo -e "\nList of teams that start with 'Co':"
echo "$($PSQL "SELECT name FROM teams WHERE name LIKE 'Co%'")"
# # Follwoing uses join with foreign keys.
# echo -e "\nWinner of the 2018 tournament team name:"
# echo "$($PSQL "SELECT name FROM teams INNER JOIN games ON teams.team_id = games.winner_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 games INNER JOIN teams ON teams.team_id = games.winner_id OR teams.team_id = games.opponent_id WHERE round='Eighth-Final' AND year='2014' 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")"
# 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 DISTINCT(name) FROM teams INNER JOIN games ON teams.team_id = games.winner_id OR teams.team_id = games.opponent_id WHERE name LIKE 'Co%'")"
# #