Exercise 0 (0 points). Run the code cell below to load the data, which is a SQLite3 database containing results and fixtures of various soccer...
This question has been answered
Question

Can someone please look at this problem and Check my SQL script. I'm in a Jupyter Notebook running SQLlite3 on Python 3.6. These were uploaded in reverse order. Thanks,


Screen Shot 2019-11-05 at 9.30.17 PM.pngScreen Shot 2019-11-05 at 9.29.57 PM.pngScreen Shot 2019-11-05 at 9.29.39 PM.pngScreen Shot 2019-11-05 at 9.29.15 PM.png

Image transcriptions

In [16] : query_top10_away = ". SELECT soccer_results . away_team AS team, ROUND ( AVG ( soccer_results . away_score), 3) AS ave_goals FROM soccer_results LEFT JOIN SELECT away_team, COUNT ( away_team) AS AwayGames FROM soccer_results GROUP BY away_team ) AS tblAwayCounts ON soccer_results . away_team = tblAwayCounts . away_team WHERE (soccer_results . date >= "2000-01-01 00:00:00.0") AND (soccer_results . neutral= 'FALSE) AND AwayGames>30 GROUP BY soccer_results . away_team ORDER BY ROUND (AVG ( soccer_results . away_score) , 3) DESC LIMIT 10 # Write your query here! print (query_top10_away )

Out [ 19 ] : date home_team away_team home_score away_score tournament city country neutral 0 1994-01-02 Barbados Grenada 0 0 Friendly Bridgetown Barbados FALSE 1 1994-01-02 Ghana Egypt 2 Friendly Accra Ghana FALSE 2 1994-01-05 Mali Burkina Faso Friendly Bamako Mali FALSE 3 1994-01-09 Mauritania Mali 3 Friendly Nouakchott Mauritania FALSE 4 1994-01-11 Thailand Nigeria Friendly Bangkok Thailand FALSE Each row of this dataframe is a game, which is played between a "home team" (column home_team) and an "away team" (away_team). The number of goals scored by each team appears in the home_score and away_score columns, respectively. Exercise 1 (1 point): Write an SQL query find the ten (10) teams that have the highest average away-scores since the year 2000. Your query should satisfy the following criteria: . It should return two columns: team: The name of the team - ave_goals: The team's average number of goals in "away" games. An "away game" is one in which the team's name appars in away_team and the game takes place at a "non-neutral site" (neutral value equals FALSE). . It should only include teams that have played at least 30 away matches. . It should round the average goals value (ave_goals) to three decimal places. . It should only return the top 10 teams in descending order by average away-goals. . It should only consider games played since 2000 (including the year 2000). Store your query string as the variable, query_top10_away, below. The test cell will run this query string against the input dataframe, df, defined above and return the result in a dataframe named offensive_teams. (See the test cell.)

Exercise 0 (0 points). Run the code cell below to load the data, which is a SQLite3 database containing results and fixtures of various soccer matches that have been played around the globe since 1980. Observe that the code loads all rows from the table, soccer_results, contained in the database file, prob0 . db. You do not need to do anything for this problem other than run the next two code cells and familiarize yourself with the resulting dataframe, which is stored in the variable df. In [18] : import sqlite3 as db import pandas as pd from datetime import datetime from collections import defaultdict disk_engine = db. connect ( ' file: prob0 . db?mode=ro', uri=True) def load_data( ) : df = pd. read_sql_query ( "SELECT * FROM soccer_results", disk_engine) return df In [19] : # Test: Exercise 0 (exposed) df = load_data ( ) assert df . shape [0] == 22851, "Row counts do not match. Try loading the data again" assert df . shape [1] == 9, "You don't have all the columns. Try loading the data again" print ( "\n (Passed! ) " ) df . head ( ) ( Passed! )

Answered by Expert Tutors

m ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor ne

sus ante, dapibus a molestie consequat, u

ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibu
Step-by-step explanation
m risu

iscing elit. Nam lacinias, dictupulv,cefacilisis. Pellentesque dapxitsipiscing elametsum dolor sit amet,suslsusictriceslestie consfficac, dictum,sus ante, dametonec aliquet. Lorisusltipiscing elit,amet, consectesum risus ante, dapibus a molestie consequat, ultricng elsus ante, dapibus a mollestie conslnctum vitae odio. Donec aDonecitnDonec aliqxlrisusltpulvinar tortor nec facilllenltitur laoreesulconglllone

The student who asked this found it Helpful
Overall rating 100%
Get unstuck

445,696 students got unstuck by Course
Hero in the last week

step by step solutions

Our Expert Tutors provide step by step solutions to help you excel in your courses