Multi-Table Cricket Query Builder

What You'll Build

In this exercise you will build a normalised five-table IPL database and write a suite of join queries that answer real analytics questions across it. You will create teams, venues, players, matches, and deliveries tables, seed them with realistic data, then join them in increasingly sophisticated ways, inner joins, outer joins, self joins, and multi-table chains, to produce the kind of insights a cricket analyst genuinely needs.

By the end you will have a single SQL file that turns five separate tables into rich, combined results: full scorecards, players who have not yet batted, captain hierarchies, and venue-level breakdowns. This mirrors exactly how production reporting works, where normalised storage is recombined on demand through joins to answer whatever question the moment requires.

The focus is on choosing the right join for each question and getting condition placement exactly right. You will decide inner versus outer at each link, avoid the NULL trap, refine a self join into clean pairs, and chain four tables without losing rows. Predict each query's result before running, then verify, so you trust your joins rather than merely hoping they are correct.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the analytics team assembling the broadcast's end-of-day statistics package. Just as they compile separate panels, top scorers, team totals, venue records, and trend graphs, from the same ball-by-ball data, you build separate grouped queries from one deliveries table. Just as each panel must be accurate before it airs to millions, each query must be verified before it feeds the dashboard. Just as the package tells the day's story through numbers, your query layer turns raw deliveries into the season's narrative. Just as a wrong figure on air is caught by cross-checking panels against each other, team totals summing to the tournament total, your queries can be validated against one another for consistency. Just as the package is rebuilt fresh from the feed each day, every dashboard number is derived from raw rows at query time, never hand-edited. This reveals why disciplined aggregation is the backbone of every stats broadcast.

Prerequisites

A working PostgreSQL setup with the psql client, as established in the Module 1 exercise, ready to run SQL files against a local database.
Completion of this module's lessons on inner joins, outer joins, self joins, cross joins, and multi-table joins, all applied directly below.
Comfort with WHERE, GROUP BY, ORDER BY, and aggregates from Modules 1 and 2, since several join queries also group and summarise results.
A text editor to maintain a single joins_lab.sql file so the whole schema and query suite stays reproducible and easy to rerun.

Setup & Project Structure

You will create a fresh database and a normalised schema of five related tables that mirror a real cricket data model. The schema script defines primary and foreign keys to establish the relationships your joins will traverse, and a single queries file holds every analytical query. As always, keeping both in rerunnable scripts makes the entire lab reproducible from scratch.

Analogy🏏Cricket

🏏 Think of it like cricket: Building the deliveries table with season, match_date, and dismissal columns is like laying out a season-long scorebook designed for every question you will ask. Just as a good scorebook records not only runs but the date and manner of each dismissal so any summary can be drawn later, your enriched table carries the extra columns every aggregation and date query will need. Just as a scorer keeps the blank scorebook template so next season starts identically, your schema lives in a rerunnable script. Just as all the tournament's summary graphics are produced from that one book, every dashboard panel lives in one queries file you can run end to end. Just as reopening the book regenerates any figure on demand, rerunning the file regenerates every number at once. This reveals why a well-designed table and scripted panels make a dashboard reproducible.

This normalised design is deliberately split the way a production database would be: each entity in its own table, connected by keys, with no duplicated facts. That separation is exactly what makes joins necessary, so the schema gives you authentic material to practise on rather than a single flat table that would never need a join in the first place.

bash

# Fresh database for the joins lab.
dropdb ipl_joins 2>/dev/null; createdb ipl_joins
mkdir -p ipl_joins_lab && cd ipl_joins_lab
touch schema.sql      # five related tables, seeded
touch joins_lab.sql   # the analytical join queries

psql ipl_joins -f schema.sql
psql ipl_joins -f joins_lab.sql

# Quick check that all tables loaded:
psql ipl_joins -c "\dt"            # lists the 5 tables
psql ipl_joins -c "SELECT COUNT(*) FROM deliveries;"   # expect 8

Step 1 — Foundation

Step one builds the normalised schema. Five tables, teams, venues, players, matches, and deliveries, are linked by foreign keys: players reference their team, deliveries reference a player, a match, and carry runs, and matches reference a venue. A captain_id on players self-references the table so you can practise a self join. This is a realistic, fully relational cricket model.

Seeding includes deliberate edge cases: a player who has faced no deliveries, so outer joins have something to preserve, and a clear captain hierarchy for the self join. Defining every key and constraint up front means your joins traverse genuine relationships and your results are trustworthy. Run this schema once to establish the reproducible foundation for every query that follows.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the groundstaff and scorers preparing before the season opener. Just as they set up the pitch, boundary, and a fresh scorebook with columns for every stat to be recorded, the schema lays out typed columns for runs, dates, and dismissals. Just as a complete scorebook lets every later statistic be computed accurately, a complete table lets every dashboard panel aggregate correctly. Just as the same setup repeats reliably each match, rerunning the schema rebuilds identical data. Just as the scorers agree before the first ball how a wide or a no-ball will be recorded, the schema fixes types and NULL conventions up front so every later aggregate interprets the data identically. Just as an incomplete scorebook column ruins every statistic that depends on it, a mistyped or missing column undermines every panel built on the table. This shows why a thorough foundation makes every later summary dependable.

sql

-- schema.sql : run with  psql ipl_joins -f schema.sql
DROP TABLE IF EXISTS deliveries, matches, players, venues, teams CASCADE;

CREATE TABLE teams (
    team_id   INTEGER PRIMARY KEY,
    team_name TEXT NOT NULL
);
CREATE TABLE venues (
    venue_id   INTEGER PRIMARY KEY,
    venue_name TEXT NOT NULL,
    city       TEXT
);
CREATE TABLE players (
    player_id   INTEGER PRIMARY KEY,
    player_name TEXT NOT NULL,
    team_id     INTEGER REFERENCES teams(team_id),
    captain_id  INTEGER REFERENCES players(player_id)   -- self reference
);
CREATE TABLE matches (
    match_id   INTEGER PRIMARY KEY,
    venue_id   INTEGER REFERENCES venues(venue_id),
    match_date DATE
);
CREATE TABLE deliveries (
    delivery_id INTEGER PRIMARY KEY,
    match_id    INTEGER REFERENCES matches(match_id),
    player_id   INTEGER REFERENCES players(player_id),
    runs_scored INTEGER NOT NULL CHECK (runs_scored >= 0)
);

INSERT INTO teams VALUES (1,'Mumbai Indians'),(2,'Royal Challengers'),(3,'Chennai Super Kings');
INSERT INTO venues VALUES (10,'Wankhede','Mumbai'),(11,'Chinnaswamy','Bengaluru'),(12,'Chepauk','Chennai');
INSERT INTO players VALUES
    (1,'Rohit Sharma',     1, NULL),   -- captain of MI
    (2,'Suryakumar Yadav', 1, 1),
    (3,'Virat Kohli',      2, NULL),   -- captain of RCB
    (4,'Faf du Plessis',   2, 3),
    (5,'MS Dhoni',         3, NULL),   -- captain of CSK
    (6,'Ruturaj Gaikwad',  3, 5),
    (7,'Dewald Brevis',    1, 1);      -- has faced NO deliveries
INSERT INTO matches VALUES (100,10,DATE '2024-04-01'),(101,11,DATE '2024-04-05'),(102,12,DATE '2024-04-09');
INSERT INTO deliveries VALUES
    (1000,100,1,72),(1001,100,2,61),(1002,101,3,45),
    (1003,101,4,33),(1004,102,5,18),(1005,102,6,58),
    (1006,100,1,40),(1007,101,3,67);
-- Dewald Brevis (player 7) appears in no deliveries row.

SELECT COUNT(*) AS deliveries_loaded FROM deliveries;   -- expect 8

Step 2 — Core Logic

Now you write the core join queries. You will build a full scorecard by chaining deliveries to players, teams, and venues; list every player including non-batters with a LEFT JOIN; and find players who have not batted using the anti-join pattern. Each query targets a specific join skill, and you should predict its exact rows before running to confirm your reasoning.

These queries put the module's concepts to work on real questions. The scorecard demonstrates a clean multi-table inner-join chain, the full-roster query shows outer-join completeness, and the anti-join surfaces missing data. Save them in joins_lab.sql and check that Dewald Brevis, the non-batter, appears in the LEFT JOIN and the anti-join but not the inner-join scorecard.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the broadcast analyst slicing the same day's play three ways for three on-screen panels. Just as they break the data down by team for one graphic, by player for another, and by venue for a third, each query groups the same table on a different column. Just as every panel must rank its entries clearly, you order each by its key aggregate. Just as the panels share one underlying scorecard, your queries share one deliveries table. Just as a panel showing only three teams instantly warns the analyst the feed is incomplete, eyeballing each query's row counts against known facts catches bugs early. Just as the director insists each graphic answers exactly one question cleanly, each query should group on exactly the dimension its panel needs. This reveals why a single clean dataset can power many distinct summary views.

sql

-- joins_lab.sql : run with  psql ipl_joins -f joins_lab.sql
-- Predict each result before running, then verify.

-- Q1. Full scorecard: every delivery with player, team, venue (INNER chain).
SELECT d.delivery_id,
       p.player_name,
       t.team_name,
       v.venue_name,
       d.runs_scored
FROM deliveries AS d
INNER JOIN players AS p ON d.player_id = p.player_id
INNER JOIN teams   AS t ON p.team_id   = t.team_id
INNER JOIN matches AS m ON d.match_id  = m.match_id
INNER JOIN venues  AS v ON m.venue_id  = v.venue_id
ORDER BY d.delivery_id;

-- Q2. Every player with their total runs, including non-batters (LEFT JOIN).
SELECT p.player_name,
       COALESCE(SUM(d.runs_scored), 0) AS total_runs
FROM players AS p
LEFT JOIN deliveries AS d ON p.player_id = d.player_id
GROUP BY p.player_name
ORDER BY total_runs DESC, p.player_name ASC;
-- Dewald Brevis shows 0, not NULL.

-- Q3. Players who have NOT batted at all (anti-join).
SELECT p.player_name
FROM players AS p
LEFT JOIN deliveries AS d ON p.player_id = d.player_id
WHERE d.player_id IS NULL;
-- Returns: Dewald Brevis.

Step 3 — Integration & Enhancement

Step three brings in self joins and richer multi-table analytics. You will list each player beside their captain with a self join, build a per-venue scoring summary across the full chain, and rank teams by total runs using joins plus aggregation. These integrate everything in the module, demonstrating how joins and the earlier grouping skills combine into genuine analytical queries.

Watch the details that make these correct: a LEFT self join so captains themselves are not dropped, proper grouping after the joins, and deterministic ordering on every leaderboard. Saving these alongside the core queries gives you a complete, reproducible join lab that demonstrates real command of combining normalised tables into insight.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the analyst adding the advanced graphics that follow the basic scoreboard. Just as they show only players who met a minimum-innings cut for an awards panel and chart scoring momentum month by month, you apply HAVING thresholds and DATE_TRUNC trends. Just as these refined panels demand more care than a raw total, they require correct filter placement and NULL handling. Just as the full package tells a richer story, your integrated queries deliver a complete dashboard. Just as the momentum chart would mislead viewers if rain-abandoned fixtures were charted as scoring dips, your trend query must decide explicitly how missing and NULL data appear. Just as the awards cut-off is printed on the graphic so viewers trust the ranking, the HAVING threshold belongs visibly in the query, not buried in app code. This shows why combining the module's tools yields professional-grade reporting.

sql

-- joins_lab.sql (continued)

-- Q4. Each player beside their captain (LEFT self join keeps captains).
SELECT p.player_name        AS player,
       c.player_name        AS captain
FROM players AS p
LEFT JOIN players AS c ON p.captain_id = c.player_id
ORDER BY p.player_id;
-- Captains (Rohit, Virat, Dhoni) show NULL captain.

-- Q5. Per-venue scoring summary (multi-table chain + GROUP BY).
SELECT v.venue_name,
       v.city,
       COUNT(*)                  AS innings,
       SUM(d.runs_scored)        AS total_runs,
       ROUND(AVG(d.runs_scored),1) AS avg_runs
FROM deliveries AS d
INNER JOIN matches AS m ON d.match_id = m.match_id
INNER JOIN venues  AS v ON m.venue_id = v.venue_id
GROUP BY v.venue_name, v.city
ORDER BY total_runs DESC, v.venue_name ASC;

-- Q6. Team leaderboard by total runs (joins + aggregation).
SELECT t.team_name,
       COUNT(DISTINCT p.player_id) AS batters_used,
       COALESCE(SUM(d.runs_scored), 0) AS total_runs
FROM teams AS t
LEFT JOIN players    AS p ON t.team_id  = p.team_id
LEFT JOIN deliveries AS d ON p.player_id = d.player_id
GROUP BY t.team_name
ORDER BY total_runs DESC, t.team_name ASC;

Step 4 — Testing & Verification

Finally, rebuild and run everything, then check the queries against the expected results below. Because all logic lives in scripts, you can regenerate the whole lab in seconds and confirm your joins return exactly the right rows, preserving non-batters where intended and dropping them where intended, rather than depending on luck or run order.

Analogy🏏Cricket

🏏 Think of it like cricket: Rebuilding the whole dashboard and checking each panel is like reprinting the entire season summary from the scorebook and confirming every graphic matches the record. Just as a broadcaster would not trust a figure that only appears if statements are run in a lucky order, you rebuild from scratch so no result depends on the sequence you happened to type. Just as the scorer reruns the totals and cross-checks the Orange Cap, run rates, and dismissal counts against the book, you check each panel against the expected results below. Just as the season book lets them regenerate every graphic in minutes, your scripted logic regenerates the entire dashboard data layer in seconds. Just as matching every panel to the record proves the summary is trustworthy, matching outputs confirms your figures are reproducible and correct. This reveals why a clean full rebuild is the real proof of a dashboard's integrity.

bash

# Rebuild and run end to end.
dropdb ipl_joins && createdb ipl_joins
psql ipl_joins -f schema.sql
psql ipl_joins -f joins_lab.sql

# EXPECTED KEY RESULTS (verify a few):
# schema -> deliveries_loaded = 8
# Q1 (scorecard): 8 rows, Dewald Brevis NOT present (he has no deliveries).
# Q2 (all players + runs): 7 players; Dewald Brevis total_runs = 0.
#     Rohit 112, Virat 112, Ruturaj 58, Surya 61... (verify ordering by running)
# Q3 (anti-join): exactly 1 row -> Dewald Brevis.
# Q4 (self join): 7 rows; Rohit, Virat, Dhoni show captain = NULL.
# Q5 (per venue): Wankhede total = 173 (72+61+40), etc.
# Q6 (team leaderboard): Mumbai Indians includes Brevis (0) in batters? 
#     batters_used counts DISTINCT player_id with a delivery; verify.

# Spot-check the anti-join count:
psql ipl_joins -c \
"SELECT COUNT(*) FROM players p LEFT JOIN deliveries d ON p.player_id=d.player_id WHERE d.player_id IS NULL;"
# Expected: 1 (Dewald Brevis)

Warning: A frequent mistake in Q2 and Q6 is adding a WHERE condition on the deliveries columns, like WHERE d.runs_scored > 0, to a LEFT JOIN. That rejects the NULL-padded non-batter rows and silently turns the outer join into an inner join, dropping Dewald Brevis. Keep non-batter conditions out of WHERE, or put them in the ON clause, to preserve every player.

Extension Challenge: Add a Q7 that generates every unique fixture between teams using a self cross join on the teams table with a strict inequality (a.team_id < b.team_id), then, as a stretch, LEFT JOIN each fixture to a results table you design so unplayed fixtures still appear. This combines cross joins, refinement, and outer joins into one scheduling query.

A normalised five-table schema linked by primary and foreign keys is recombined on demand through joins, exactly as production reporting works.
An inner-join chain across deliveries, players, teams, matches, and venues builds a full scorecard, dropping any entity without a match like a non-batter.
A LEFT JOIN with COALESCE lists every player with their runs, showing zero for non-batters, while the IS NULL anti-join isolates who has not batted.
A LEFT self join relates players to their captains while preserving the captains themselves, whose self-reference is NULL.
Combining multi-table joins with GROUP BY produces per-venue summaries and team leaderboards, uniting this module with earlier aggregation skills.
Condition placement is decisive: a WHERE filter on the optional table re-drops preserved rows, so keep non-batter conditions in ON or omit them.