Homework 4 — Football League Database

In this assignment you will complete a Flask web application by writing SQL queries against a European Football League database hosted on Supabase. Every question requires at least one JOIN — that is the whole point of this assignment. Deadline: Friday, May 1, 11:59 pm.

Overview¶

You will edit a single file (queries.py) and fill in the body of each query function. A local Flask dashboard shows you in real time whether each query is correct. You will practice:

• INNER JOIN — matching rows between two tables

• LEFT JOIN — keeping rows from the left table even when there is no match

• Joining the same table twice using aliases (e.g., home club and away club from one matches row)

• Multi-table joins — chaining three or four tables together

• Self-join — joining a table to itself

• Subqueries inside a JOIN — using a derived table in the FROM clause

What You Will Do¶

1. Accept the GitHub Classroom assignment using the link provided on Moodle.

2. Clone the repository to your machine (or open it in coder.cs.calvin.edu).

3. Create a Python virtual environment and install dependencies.

4. Copy the provided .env file (posted on Moodle) into the project root.

5. Run python app.py to start the Flask dashboard.

6. Open queries.py and read the docstring for each function — it tells you exactly what to return.

7. Write your SQL query inside the function, run the app, and refresh the dashboard to see your score.

8. Repeat until all cards are green.

9. Commit and push: git add ., git commit -m "Complete hw4", git push. If you are using VS Code Git integration, just make sure all your changes are committed and pushed to GitHub.

Files You Should Care About¶

Database Schema¶

The database contains five tables describing a fictional European football league system.

leagues¶

Stores information about football leagues.

clubs¶

Represents football clubs. Each club belongs to one league.

players¶

Represents individual players. club_id is nullable — a NULL value means the player is a free agent.

matches¶

Each row is one match between two clubs.

goals¶

One row per goal scored across all matches.

Entity-Relationship Diagram¶

Setup Instructions¶

1. Accept the Assignment¶

Click the GitHub Classroom link posted on Moodle. This creates a private copy of the starter repository under your GitHub account.

2. Clone the Repository¶

git clone <your-repo-url>
cd hw4-football-<your-username>

3. Create a Virtual Environment¶

python -m venv .venv
source .venv/bin/activate      # macOS / Linux
.venv\Scripts\activate         # Windows

4. Install Dependencies¶

pip install -r requirements.txt

5. Configure the .env File¶

Download the .env file from Moodle and place it in the project root. It contains the Supabase connection credentials and should look like this:

SUPABASE_HOST=...
SUPABASE_PORT=6543
SUPABASE_DB=postgres
SUPABASE_USER=...
SUPABASE_PASSWORD=...

Running the App¶

python app.py

Then open your browser and go to http://localhost:5000. If port 5000 is busy, try:

python app.py --port 5001

How the Webpage Works¶

The dashboard shows one card per question. Each card has one of four states:

The page also shows your running point total. Refresh the page after saving queries.py to update your score.

How to Write Queries¶

Open queries.py. Each function looks like this:

def q1():
    """
    List the name and position of every player who currently belongs to a club.
    Include the club's name. Order by player name (ascending).

    Returns columns: player_name, position, club_name
    Points: 10
    """
    return """

    """

Replace the empty string with your SQL query:

def q1():
    """..."""
    return """
        SELECT p.name AS player_name, p.position, c.name AS club_name
        FROM players p
        JOIN clubs c ON p.club_id = c.club_id
        ORDER BY p.name;
    """

Exploring the Data¶

Before writing a query, it helps to look at the raw data directly. The database is remote (hosted on Supabase), so you need to connect using the credentials in your .env file.

Option A — psql in the terminal¶

Open your .env file and note the five values. Then run:

psql -h <SUPABASE_HOST> \
     -p <SUPABASE_PORT> \
     -U <SUPABASE_USER> \
     -d <SUPABASE_DB>

Replace each <...> with the corresponding value from your .env. When prompted for a password, paste the value of SUPABASE_PASSWORD.

Once connected, you will see a postgres=> prompt. Some useful commands:

-- List all tables
\dt

-- Inspect a table's columns
\d players

-- Browse data
SELECT * FROM leagues;
SELECT * FROM clubs ORDER BY founded_year;
SELECT * FROM players LIMIT 10;
SELECT * FROM matches ORDER BY match_date;
SELECT * FROM goals LIMIT 20;

-- Exit
\q

Option B — VS Code SQLTools extension¶

Install the SQLTools extension and the SQLTools PostgreSQL/Cockroach Driver. Create a new connection using the values from your .env file. You can then browse tables and run queries directly inside VS Code.

Understanding what the data actually looks like will save you a lot of time when writing and debugging your queries.

Rules and Constraints¶

Questions¶

Q1 · Players and Their Clubs — 10 pts¶

List the name and position of every player who is currently signed to a club. Include the club’s name. Order by player name (ascending).

Returns: player_name, position, club_name

Q2 · Clubs and Their Leagues — 10 pts¶

For each club, show the club name and the name of the league it belongs to. Order by league name (ascending), then club name (ascending).

Returns: club_name, league_name

Q3 · Players from Historic Clubs — 10 pts¶

Show the name and nationality of every player who plays for a club founded before 1900. Also include the club name and the year it was founded. Order by founded year (ascending), then player name (ascending).

Returns: player_name, nationality, club_name, founded_year

Q4 · Premier League Squad — 10 pts¶

List the name, position, and shirt number of all players registered to a club that plays in the 'Premier League'. Order by shirt number (ascending).

Returns: player_name, position, shirt_number

Q5 · Goal Scorers — 10 pts¶

For every goal recorded, show the scorer’s full name and the minute the goal was scored. Order by minute (ascending), then player name (ascending).

Returns: player_name, minute

Q6 · All Players, Including Free Agents — 10 pts¶

List every player in the database along with their club name. For players who are not signed to any club, display 'Free Agent' instead of a club name. Order by player name (ascending).

Returns: player_name, club_name

Q7 · Clubs with No Players — 10 pts¶

List the names of all clubs that currently have no players registered. Order by club name (ascending).

Returns: club_name

Q8 · Brazilian Players — 10 pts¶

List all players whose nationality is 'Brazilian', whether or not they are currently signed to a club. Show their club name, or 'Free Agent' if they are unattached. Order by player name (ascending).

Returns: player_name, club_name

Q9 · Match Fixtures — 10 pts¶

For each match, show the home club name, the away club name, and the match date. Order by match date (ascending).

Returns: home_club, away_club, match_date

Q10 · Matches and Their League — 10 pts¶

For each match, show the match date and the name of the league the match belongs to. Order by match date (ascending).

Returns: match_date, league_name

Q11 · Goals with Club Context — 15 pts¶

For every goal scored, show the scorer’s name, their current club name, and the minute the goal was scored. Include only players who are currently signed to a club. Order by minute (ascending), then player name (ascending).

Returns: player_name, club_name, minute

Q12 · Players, Clubs, and Leagues — 15 pts¶

List each player’s name, their club name, and the name of the league their club competes in. Order by league name (ascending), then club name (ascending), then player name (ascending).

Returns: player_name, club_name, league_name

Q13 · Full Match Summary — 15 pts¶

For each match, show the home club name, away club name, the score formatted as 'X - Y' (home goals dash away goals), and the league name. Order by match date (ascending).

Returns: home_club, away_club, score, league_name

Q14 · Goals in a Specific Country — 15 pts¶

List all goals scored by players whose club competes in a league based in 'Spain'. Show the player name, their club name, and the minute of the goal. Order by club name (ascending), then minute (ascending).

Returns: player_name, club_name, minute

Q15 · Clubs That Have Never Played at Home — 15 pts¶

List the names of all clubs that have never appeared as the home team in any match. Order by club name (ascending).

Returns: club_name

Q16 · Match Results — 15 pts¶

For each match in the 'Premier League', show the home club name, away club name, and the result from the home team’s perspective: 'Home Win', 'Away Win', or 'Draw'. Order by match date (ascending).

Returns: home_club, away_club, result

Q17 · Own Goals — 15 pts¶

List all own goals (is_own_goal = true), showing the name of the player who accidentally scored it, their current club name, and the league the club competes in. Order by league name (ascending), then player name (ascending).

Returns: player_name, club_name, league_name

Q18 · Players Who Have Scored — 15 pts¶

Show the name and club name of every player who has scored at least one goal (including own goals). Each player should appear only once. Do not use aggregate functions. Order by player name (ascending).

Returns: player_name, club_name

Q19 · Clubs Sharing a City — 15 pts¶

Find all pairs of clubs that are based in the same city. Show both club names and the city they share. Each pair should appear only once — if Arsenal and Chelsea both appear, show (Arsenal, Chelsea, London) not both (Arsenal, Chelsea) and (Chelsea, Arsenal). Order by city (ascending), then the first club name (ascending).

Returns: club1_name, club2_name, city

Q20 · Players at Active Clubs — 15 pts¶

Show the name, position, and club name of every player who belongs to a club that has appeared in at least one match (as either home or away). Use a subquery inside the JOIN to find the set of relevant club IDs, rather than a WHERE ... IN (...) clause. Order by club name (ascending), then player name (ascending).

Returns: player_name, position, club_name

Submission¶

When all cards are green (or you have done your best), submit via Git:

git add queries.py
git commit -m "Complete hw4"
git push

Only queries.py is graded. The autograder runs on GitHub Actions within a few minutes of your push — check the Actions tab on your repository for results.

Troubleshooting¶

The app won’t start. Make sure your virtual environment is activated and you ran pip install -r requirements.txt. Check that your .env file is in the project root.

I get a connection error. Verify the .env file is present and contains the correct DATABASE_URL. Do not modify the URL.

My query runs in SQLTools but shows ❌ in the dashboard. The result is likely correct SQL but returns the wrong rows, wrong column names, or wrong ordering. Re-read the docstring — column aliases and ORDER BY must match exactly.

My query uses the right logic but the column name is wrong. Use AS to rename columns: p.name AS player_name. The exact alias names are listed in the “Returns” line of each docstring.

I keep getting ⚠️ Query Error. The error message from PostgreSQL is shown directly on the card. For more detail, check the terminal where app.py is running.

Q9 / Q13 — I can only get one club name, not both. You need to join clubs twice using different aliases. See the hint in Q9 for the pattern.

Q19 — My result has duplicate pairs. Make sure your self-join condition includes c1.club_id < c2.club_id to enforce a canonical ordering within each pair.

Rubric¶

Each question is graded on five criteria:

1. Execution — the query runs without errors (required to earn any points)

2. Result set — the returned rows match exactly

3. Column names — aliases match what the docstring specifies

4. Ordering — rows appear in the specified order

5. Constraint compliance — no aggregate functions used

Partial credit is not awarded per question — each query is either fully correct or not.