The table is given above which has a big amount of data with the columns give below:

GOALDIF VGOAL year HOME VISITOR result SEASON HGOAL day FT month TIER TOTGOAL DIVISION

/*Q7 (2 pt)

For each pair ('Arsenal', team2), report the number of home-wins and away-wins

of Arsenal versus team2 and the number of home-wins and away-wins of team2 versus Arsenal

(all since 1980).

Order the results by the second number of away-wins in descending order.

Use view Wins.*/

/*Your query here*/

/*Sample result

Arsenal Manchester United 16 5 19 11

Arsenal Liverpool 14 8 20 11

...*/

/*Build two bar-charts, one visualizing the two home-wins columns, and the other visualizing the two away-wins columns.*/

/*Drop view Wins.*/

DROP VIEW Wins;

/*Q8 (2 pt)

Winning at home is easier than winning as visitor.

Nevertheless, some teams have won more games as a visitor than when at home.

Find the team in Germany that has more away-wins than home-wins in total.

Print the team name, number of home-wins, and number of away-wins.*/

/*Your query here*/

/*Sample result

Wacker Burghausen ... ...*/

/*Q9 (3 pt)

One of the beliefs many people have about Italian soccer teams is that they play much more defense than offense.

Catenaccio or The Chain is a tactical system in football with a strong emphasis on defence.

In Italian, catenaccio means "door-bolt", which implies a highly organised and effective backline defence

focused on nullifying opponents' attacks and preventing goal-scoring opportunities.

In this question we would like to see whether the number of goals in Italy is on average smaller than in England.

Find the average total goals per season in England and Italy since the 1970 season.

The results should be (season, england_avg, italy_avg) triples, ordered by season.

Hint.

Subquery 1: Find the average total goals per season in England.

Subquery 2: Find the average total goals per season in Italy

(there is no totgoal in table Italy. Take hgoal+vgoal).

Join the two subqueries on season. */

/*Your query here*/

--Build a line chart visualizing the results. What do you observe?

/*Sample result

1970 2.5290927021696255 2.1041666666666665

1971 2.592209072978304 2.0125

...*/

/*Q10 (3 pt)

Find the number of games in France and England in tier 1 for each goal difference.

Return (goaldif, france_games, eng_games) triples, ordered by the goal difference.

Normalize the number of games returned dividing by the total number of games for the country in tier 1,

e.g. 1.0*COUNT(*)/(select count(*) from france where tier=1) */

/*Your query here*/

/*Sample result

-8 0.000114 0.000063

-7 0.000114 0.000104

...*/

/*Visualize the results using a barchart.*/

ENGLAND I Enter a SQL expression to filter results (use Ctrl-Space) 123 day T:123 SEASON TII ABC HOME T: ABC VISITOR TS ABC FT :l 123 HGOAL :| 123 VGOAL TS 123 DIVISION TI 123 TIER T 123 TOTGOAL TII 123 GOALDIF T:l ABC result 6-2 7-3 2-5 2-1 1,888 Bolton Wanderers Everton 1,888 Bolton Wanderers Notts County 1,888 Bolton Wanderers Preston North End 1,888 Bolton Wanderers Stoke City 1,838 Bolton Wanderers West Bromwich Albion 1-2 1,888 Bolton Wanderers Wolverhampton Wanderers 2-1 1,888 Burnley 1,838 Burnley 1,838 Burnley 1,838 Burnley 1,888 Burnley 1,88 Burnley 1,888 Burnley 1,888 Burnley 1,838 Burnley 1,888 Burnley 1,888 Burnley 1,888 Derby CountyAccrington F.C. 1,838 Derby County Aston Villa 1,838 Derby County Blackburn Rovers 1,888 Derby County Bolton Wanderers 1,838 Derby County Burnley 10 1 H 43 1 H Accrington F.C. Aston Villa Blackburn Rovers 45 2-2 4 H -6 A 1-7 4-1 1-0 2-2 1-0 2-2 2-1 2-0 Wolverhampton Wanderers 0-4 Derby County Everton Notts County Preston North End Stoke City West Bromwich Albion 17 1 H 4 A 57 3 H 0-2 2-3 1-0 1 H ENGLAND I Enter a SQL expression to filter results (use Ctrl-Space) 123 day T:123 SEASON TII ABC HOME T: ABC VISITOR TS ABC FT :l 123 HGOAL :| 123 VGOAL TS 123 DIVISION TI 123 TIER T 123 TOTGOAL TII 123 GOALDIF T:l ABC result 6-2 7-3 2-5 2-1 1,888 Bolton Wanderers Everton 1,888 Bolton Wanderers Notts County 1,888 Bolton Wanderers Preston North End 1,888 Bolton Wanderers Stoke City 1,838 Bolton Wanderers West Bromwich Albion 1-2 1,888 Bolton Wanderers Wolverhampton Wanderers 2-1 1,888 Burnley 1,838 Burnley 1,838 Burnley 1,838 Burnley 1,888 Burnley 1,88 Burnley 1,888 Burnley 1,888 Burnley 1,838 Burnley 1,888 Burnley 1,888 Burnley 1,888 Derby CountyAccrington F.C. 1,838 Derby County Aston Villa 1,838 Derby County Blackburn Rovers 1,888 Derby County Bolton Wanderers 1,838 Derby County Burnley 10 1 H 43 1 H Accrington F.C. Aston Villa Blackburn Rovers 45 2-2 4 H -6 A 1-7 4-1 1-0 2-2 1-0 2-2 2-1 2-0 Wolverhampton Wanderers 0-4 Derby County Everton Notts County Preston North End Stoke City West Bromwich Albion 17 1 H 4 A 57 3 H 0-2 2-3 1-0 1 H