<jupyter_start><jupyter_text>1. What was the total spent on salaries by each team, each year?<jupyter_code>query = """
SELECT teamID, team_ID, SUM(salary)
FROM salaries
GROUP BY teamID, yearID
"""
df_salaries = pd.read_sql_query(query, conn)
df_salaries<jupyter_output><empty_output><jupyter_text>2. What is the first and last year played for each player? Hint: Create a new table from 'Fielding.csv'.<jupyter_code>query = """
SELECT DISTINCT playerID,
        MIN(yearID) OVER (PARTITION BY playerID) AS "First Year",
        MAX(yearID) OVER (PARTITION BY playerID) AS "Last Year"
FROM fielding
GROUP BY playerID, yearID
"""
df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>3. Who has played the most all star games?<jupyter_code>query = """
SELECT playerID, COUNT(ID)
FROM allstarfull
GROUP BY playerID
ORDER BY COUNT(ID) DESC
"""
df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>4. Which school has generated the most distinct players? Hint: Create new table from 'CollegePlaying.csv'.<jupyter_code>query = """
SELECT name_full AS "School", COUNT(DISTINCT playerID) AS "Num of Players"
FROM collegeplaying
JOIN schools
ON schools.schoolID = collegeplaying.schoolID
GROUP BY name_full
ORDER BY COUNT(playerID) DESC
"""
df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>5. Which players have the longest career? Assume that the debut and finalGame columns comprise the start and end, respectively, of a player's career. Hint: Create a new table from 'Master.csv'. Also note that strings can be converted to dates using the DATE function and can then be subtracted from each other yielding their difference in days.<jupyter_code>query = """
SELECT playerID, DATE(finalgame_date) - DATE(debut_date) AS "Career (years)"
FROM people
GROUP BY playerID
ORDER BY (DATE(finalgame_date) - DATE(debut_date)) DESC
"""
df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>6. What is the distribution of debut months? Hint: Look at the DATE and EXTRACT functions.<jupyter_code>query = """
SELECT STRFTIME('%m', debut) AS Month, COUNT(*)
FROM people
GROUP BY Month

"""
df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output><jupyter_text>7. What is the effect of table join order on mean salary for the players listed in the main (master) table? Hint: Perform two different queries, one that joins on playerID in the salary table and other that joins on the same column in the master table. You will have to use left joins for each since right joins are not currently supported with SQLalchemy.<jupyter_code># Only keep those who have salary info in salaries
query = """
SELECT people.playerID, people.nameGiven, people.nameFirst, people.nameLast, AVG(salary)
FROM salaries
LEFT JOIN people
ON salaries.playerID = people.playerID
GROUP BY people.playerID

"""
df = pd.read_sql_query(query, conn)
df
# Keep all the players in people (even without salary info)
query = """
SELECT people.playerID, people.nameGiven, people.nameFirst, people.nameLast, AVG(salary)
FROM people
LEFT JOIN salaries
ON salaries.playerID = people.playerID
GROUP BY people.playerID

"""
df = pd.read_sql_query(query, conn)
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08996008596868284, 'fraction_numerical': 0.0021492170709241634, 'mean_word_length': 5.8589473684210525, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '338050', 'license': 'MIT', 'n_tokens_mistral': 1058, 'n_tokens_neox': 1013, 'n_words': 374, 'path': 'SQL practice (project_3_starter)/baseball.ipynb', 'repo_name': 'katiehuang1221/onl_ds5_project_3', 'size': '3257'}