Assignment 2

Due: 11:59 on 9/8/2026

Note! All column names in your output must be meaningful!

For this assignment, you can ignore the possibility of ties.

For this assignment, follow strict SQL for attributes in the GROUP BY clause.

In a single SQL query:

  1. Find the number of times each league won the world series (WSWin = 'Y'). Output the league id (lgid), the number of times the league won the World Series and the last year the team from that league won the World Series.
  2. Find the league ID of the of the league that has won the most World Series.
  3. Find number of times each franchise has won the World Series. Output the franchId and the total number of World Series wins for each franchId with at least 4 World Series wins.
  4. Find the number of years since the last time a franchise won the World Series. Output the franchid and the total number of years for each franchId with at least 4 World Series wins. You can use 2026 as the current year (e.g., the team that won in 2025 would be 1 year from their last win).
  5. A team's winning percentage is the number of wins (team_W) divided by the number of games (team_G). Find the 5 worst winning percentages for a franchise after 2020 (the winning percentage is over all of the years). Output the franchise id and the winning percentage.
  6. Find the franchId which has had the most teamIds. Output the franchId and the number of teamIds.
  7. Find the 5 longest lived franchises. NOTE! For this query, we will assume that all franchises have played in all years between their first year and their last year. Output the franchId and the number of years they have existed.
  8. Find the lgid, divid and yearid of the division with the most teams that finished with a losing record (team_l > team_w). Output the league id, the division id, the yearid and the number of teams.
  9. Find the league id, division id and year id of teh division with the lowest winning percentage for the team that finished first (DivWin = 'Y'). Output the league id, the division id, the year id and their winning percentage.
  10. For many years, American League (lgid = 'AL') teams never played National League (lgid = 'NL') teams during the regular season. Write a query to determine the year interleague play started. NOTE! Our database does not have this information, but we can make a good guess, which works in this case. Output the year.

Upload the assignment on canvas.

Your assignment should be a text file meeting the following criteria