Due: September 22, 2026, 11:59 pm - Practice Questions

Assignment 4

For all of these queries, follow strict SQL rules with respect to attributes in the GROUP BY clause.

Write a single query for each problem. Name is a single field with nameFirst and nameLast concatenated.

  1. Using only subqueries in the WHERE clause, find the name of all players who played for the Detroit Tigers and hit more than 40 home runs in a stint.
  2. Using only subqueries in the SELECT clause and predicates in the WHERE clause, produce the standings for the 2013 American League Central division. The standings should show a team's wins, losses, winning percentage (wins/(wins+losses)) and games behind. Let T1 be the team in first place and T2 be the team we are finding games behind GB. GB = (ABS(T1.W -T2.W) + ABS(T1.L - T2.L))/2. The standings should be in team_rank order.
  3. Using a natural join between two subqueries in the FROM clause and nothing else, find the franchise names for franchises that have won the same number of games as they have lost over their entire existence. Return the number of wins (or losses) with the franchise name.
  4. Using a subquery returning the maximum number of RBIs in a season, find the name of the player with the most RBIs in a season.
  5. Using a subquery in the HAVING clause, find the name of the player with the most RBIs in a career
  6. Find the names of players with the most HRs in a stint for each year in the 1990's (1990-1999). Include the number of RBIs and the year in the output. There should be at least 10 rows in the answer.
  7. Find the names of players with the most HRs in a season for each year in the 1990's (1990-1999). Include the number of HRs and the year in the output. There should be at least 10 rows in the answer.
  8. Using the UNION operator, find the total number of distinct players who have stolen 20 or more bases or won 10 or more games as a pitcher (not necessarily in the same season).
  9. Using the EXCEPT operator, find the total number of distinct players who have stolen 20 or more bases and not won 10 or more games as a pitcher (not necessarily in the same season).
  10. Using the INTERSECT operator, find the total number of distinct players who have stolen 20 or more bases and won 10 or more games as a pitcher (not necessarily in the same season).

Your assignment should be a text file meeting the following criteria