## Pratice easy, medium and hard questions on cricket dataset.SQL Practice: CricketDB - Codeverra

Codeverra · SQL Practice

SQL Practice Set
CricketDB

12 questions on a cricket dataset - players, matches, and ball-by-ball performances. Easy to hard. Every question has a one-line approach, a clean solution, and alternatives where they exist.

Dataset Schema

players        -- 15 rows
  id              SERIAL PRIMARY KEY
  name            VARCHAR
  role            VARCHAR        -- 'Batsman' | 'Bowler' | 'All-rounder' | 'Wicket-keeper'
  batting_style   VARCHAR        -- 'Right-hand' | 'Left-hand'
  bowling_style   VARCHAR        -- NULL for batsmen and wicket-keepers
  is_active       BOOLEAN

matches        -- 8 rows
  id              SERIAL PRIMARY KEY
  opponent        VARCHAR
  venue           VARCHAR
  format          VARCHAR        -- 'Test' | 'ODI' | 'T20'
  match_date      DATE
  result          VARCHAR        -- 'Won' | 'Lost' | 'Draw'

performances   -- 30 rows  -  one per player per match
  id              SERIAL PRIMARY KEY
  match_id        INT → matches.id
  player_id       INT → players.id
  runs            INT
  wickets         INT
  catches         INT

Easy - single table, basic clauses

Medium - JOINs, GROUP BY, HAVING

Hard - window functions, CTEs, subqueries

Easy

List all active players and their roles, sorted alphabetically

SELECT · WHERE · ORDER BY

You're building a squad page. You need every currently active player's name and role - nothing from retired players, sorted so it's easy to scan.

Approach

Filter is_active = TRUE on the players table and sort by name. Single table, no JOIN needed.

SELECT
    name,
    role,
    batting_style
FROM   players
WHERE  is_active = TRUE
ORDER BY name;

What to observe

11 rows - the 4 retired players (Dhoni, Sachin, Kumble, Dhawan) are excluded. is_active is a BOOLEAN column - comparing it to TRUE is the same as just writing WHERE is_active, which also works in PostgreSQL.

Easy

How many players are in each role?

GROUP BY · COUNT · ORDER BY

The selection committee wants a quick squad breakdown - how many batsmen, bowlers, all-rounders, and wicket-keepers are in the 15-man squad.

Approach

Group the players table by role and count rows per group. No JOIN, no WHERE - aggregate the whole table.

SELECT
    role,
    COUNT(*) AS player_count
FROM   players
GROUP BY role
ORDER BY player_count DESC;

What to observe

Batsmen: 7 · Bowlers: 4 · All-rounders: 3 · Wicket-keepers: 1. This includes both active and retired players - add WHERE is_active = TRUE before GROUP BY if you want only current players.

Easy

Show all players who have a bowling style recorded - use COALESCE for the rest

IS NULL · COALESCE · WHERE

You need a bowling roster. Pure batsmen and the wicket-keeper have NULL in bowling_style. Display "N/A" for them instead of a blank, and only show players who actually bowl.

Approach

Use WHERE bowling_style IS NOT NULL for the bowling roster. For the full squad with "N/A" fallback, remove the WHERE and wrap the column in COALESCE(bowling_style, 'N/A').

-- Bowlers only
SELECT name, role, bowling_style
FROM   players
WHERE  bowling_style IS NOT NULL
ORDER BY bowling_style, name;

-- Full squad with 'N/A' for non-bowlers
SELECT
    name,
    role,
    COALESCE(bowling_style, 'N/A') AS bowling_style
FROM   players
ORDER BY role, name;

What to observe

Never write WHERE bowling_style = NULL - that always returns zero rows. NULL comparisons require IS NULL / IS NOT NULL. COALESCE returns the first non-NULL argument, so COALESCE(bowling_style, 'N/A') gives 'N/A' only when bowling_style is NULL.

Easy

India's win/loss record - how many matches won, lost, and drawn per format?

GROUP BY · COUNT · FILTER

A journalist wants India's record broken down by format - Tests, ODIs, and T20s separately, with wins, losses, and draws in each.

Approach

Group matches by format, then use COUNT(*) FILTER (WHERE result = '...') to count each outcome within the same row. Avoids writing three separate queries.

SELECT
    format,
    COUNT(*)                                       AS played,
    COUNT(*) FILTER (WHERE result = 'Won')        AS wins,
    COUNT(*) FILTER (WHERE result = 'Lost')       AS losses,
    COUNT(*) FILTER (WHERE result = 'Draw')       AS draws
FROM   matches
GROUP BY format
ORDER BY format;

Alternative - CASE WHEN (works in all databases)

Replace each FILTER with SUM(CASE WHEN result = 'Won' THEN 1 ELSE 0 END). FILTER is PostgreSQL-specific and cleaner; CASE WHEN is standard SQL that works everywhere including MySQL and SQLite.

What to observe

ODI: 2 played, 2 wins · T20: 3 played, 2 wins, 1 loss · Test: 3 played, 2 wins, 1 loss. This dataset has no draws - all draw counts will be 0, which is correct.

Medium

Show each player's career totals - runs, wickets, and matches played

JOIN · GROUP BY · SUM · COUNT

You need a career statistics page - one row per player showing their aggregate numbers across all matches they appeared in.

Approach

JOIN performances → players, then GROUP BY player to aggregate. Always include the player's id in GROUP BY alongside name - two players could theoretically share a name, and the id prevents incorrect grouping.

SELECT
    pl.name,
    pl.role,
    COUNT(*)            AS matches_played,
    SUM(p.runs)         AS total_runs,
    SUM(p.wickets)      AS total_wickets,
    SUM(p.catches)      AS total_catches,
    MAX(p.runs)         AS highest_score,
    ROUND(AVG(p.runs), 1) AS batting_avg
FROM   performances  AS p
JOIN   players       AS pl  ON pl.id = p.player_id
GROUP BY pl.id, pl.name, pl.role
ORDER BY total_runs DESC;

What to observe

Kohli tops the run chart at 388 across 6 appearances. Only 13 players appear - 4 in the squad (Dhoni, Sachin, Kumble, Dhawan) were never selected for these 8 matches and have no rows in performances. An INNER JOIN only returns players who played.

Medium

Find players who never took a single wicket in any match

LEFT JOIN · anti-join · IS NULL · NOT EXISTS

The bowling coach wants a list of players in the squad who contributed zero wickets across all recorded matches - pure batsmen who were selected but never bowled.

Approach

Two valid patterns: (1) LEFT JOIN from players to performances and filter on IS NULL (players with no performances at all), or (2) JOIN then HAVING to find players who played but had total wickets = 0.

-- Approach A: Players in squad but never selected (no performances)
SELECT pl.name, pl.role
FROM   players      AS pl
LEFT JOIN performances AS p ON p.player_id = pl.id
WHERE  p.id IS NULL
ORDER BY pl.name;

-- Approach B: Players who played but took 0 wickets
SELECT
    pl.name,
    pl.role,
    SUM(p.wickets) AS total_wickets
FROM   performances  AS p
JOIN   players       AS pl  ON pl.id = p.player_id
GROUP BY pl.id, pl.name, pl.role
HAVING  SUM(p.wickets) = 0
ORDER BY pl.name;

What to observe

Approach A returns 4 players never selected at all (Dhoni, Sachin, Kumble, Dhawan). Approach B returns players who did play but bowled zero wickets - Kohli, Rohit, KL Rahul, Shreyas, Suryakumar. These are different questions despite sounding similar. Read carefully which one you actually need.

Medium

Which all-rounders contributed both runs AND wickets in the same match?

JOIN · WHERE with AND · multiple conditions

The team analyst wants to identify matches where all-rounders delivered on both fronts - not just with the bat or ball, but both in the same game.

Approach

Three-table JOIN (performances → players → matches), then filter with WHERE pl.role = 'All-rounder' AND p.runs > 0 AND p.wickets > 0. All conditions go in WHERE - no aggregation needed because we want individual match rows.

SELECT
    pl.name,
    m.opponent,
    m.format,
    m.result,
    p.runs,
    p.wickets
FROM   performances  AS p
JOIN   players       AS pl  ON pl.id = p.player_id
JOIN   matches       AS m   ON m.id  = p.match_id
WHERE  pl.role    = 'All-rounder'
  AND  p.runs     > 0
  AND  p.wickets  > 0
ORDER BY p.runs DESC;

What to observe

Jadeja appears 3 times - he consistently contributed with both bat and ball. Ashwin shows up twice. Pandya twice. This is the kind of query coaches use for form analysis - identifying players whose match impact is genuinely two-dimensional.

Medium

Players with over 50 runs AND over 3 wickets - the true all-rounders

JOIN · GROUP BY · HAVING with multiple conditions

You want to rank players by genuine all-round contribution - not just role label, but actual numbers. Show only players who cleared both the batting and bowling thresholds.

Approach

Aggregate total runs and wickets per player with GROUP BY, then use HAVING to filter on both aggregates simultaneously. WHERE runs before grouping - HAVING filters after. You can't use WHERE here because SUM(runs) doesn't exist until after aggregation.

SELECT
    pl.name,
    pl.role,
    SUM(p.runs)    AS total_runs,
    SUM(p.wickets) AS total_wickets
FROM   performances  AS p
JOIN   players       AS pl  ON pl.id = p.player_id
GROUP BY pl.id, pl.name, pl.role
HAVING  SUM(p.runs)    > 50
   AND  SUM(p.wickets) > 3
ORDER BY total_runs DESC;

What to observe

Jadeja (165 runs, 7 wickets) and Ashwin (48 runs, 10 wickets) clear both bars - though Ashwin is close on runs. Pandya (62 runs, 3 wickets) falls just short on wickets if you use > 3 strictly. Try changing the thresholds to see who enters and exits the list.

Hard

Top scorer in each match - without removing other players from the result

ROW_NUMBER · OVER · PARTITION BY · subquery

For every match, identify who scored the most runs. The catch: you want to see all players in each match, but flag who the top scorer was. A GROUP BY would collapse the rows - you need a window function.

Approach

Use ROW_NUMBER() OVER (PARTITION BY match_id ORDER BY runs DESC) to rank players within each match. Then wrap it in a subquery (or CTE) and filter on rn = 1 to get only the top scorer per match.

-- Full ranking (all players visible, with their rank per match)
SELECT
    pl.name,
    m.opponent,
    m.format,
    p.runs,
    ROW_NUMBER() OVER (
        PARTITION BY p.match_id
        ORDER BY p.runs DESC
    ) AS rank_in_match
FROM   performances  AS p
JOIN   players       AS pl  ON pl.id = p.player_id
JOIN   matches       AS m   ON m.id  = p.match_id
ORDER BY m.match_date, rank_in_match;

-- Top scorer only per match
SELECT name, opponent, format, runs
FROM (
    SELECT
        pl.name, m.opponent, m.format, p.runs,
        ROW_NUMBER() OVER (
            PARTITION BY p.match_id
            ORDER BY p.runs DESC
        ) AS rn
    FROM   performances  AS p
    JOIN   players       AS pl  ON pl.id = p.player_id
    JOIN   matches       AS m   ON m.id  = p.match_id
) AS ranked
WHERE rn = 1
ORDER BY runs DESC;

Alternative - RANK() instead of ROW_NUMBER()

If two players score the same runs in a match, ROW_NUMBER picks one arbitrarily. Use RANK() instead - tied players both get rn = 1 and both appear in the filtered result. Use ROW_NUMBER when you want exactly one row per group regardless of ties; RANK when ties should both show.

What to observe

Kohli's 121 against England (Test, Ahmedabad) is the highest individual score. You cannot do this with a plain GROUP BY - MAX(runs) gives you the number but not the player's name without a second join. Window functions solve this cleanly by operating across rows without collapsing them.

Hard

Kohli's running career total - cumulative runs match by match

SUM OVER · PARTITION BY · ORDER BY · running total

Show Kohli's runs in each match chronologically, and alongside each match show his cumulative career total at that point - the running total that grows with every game.

Approach

Filter performances to Kohli only, then use SUM(runs) OVER (ORDER BY match_date). The PARTITION BY is omitted here - since we've already filtered to one player, the window covers all his rows, accumulating in date order.

SELECT
    m.match_date,
    m.opponent,
    m.format,
    m.result,
    p.runs                                           AS runs_this_match,
    SUM(p.runs) OVER (
        ORDER BY m.match_date
    )                                                AS career_total
FROM   performances  AS p
JOIN   players       AS pl  ON pl.id = p.player_id
JOIN   matches       AS m   ON m.id  = p.match_id
WHERE  pl.name = 'Virat Kohli'
ORDER BY m.match_date;

Extension - all players at once using PARTITION BY

Remove the WHERE clause and add PARTITION BY pl.id inside the OVER - the running total then resets per player. Without PARTITION BY on multi-player data, the total would accumulate across all players combined, which is wrong.

What to observe

The final row shows career_total = 388 (Kohli's total across 6 matches in this dataset). Key insight: SUM() OVER keeps every row intact while adding the accumulated value. A plain GROUP BY + SUM would give you one total row, not a row-by-row progression.

Hard

Players who scored above the average score across all performances

CTE · subquery · AVG · correlated comparison

Find players whose career total runs exceed the average career total across all players who appeared in matches - a measure of who's punching above the squad average.

Approach

Two-step problem: first calculate total runs per player, then find the average of those totals. Use a CTE to name the first step cleanly, then compare each player's total against the average in the outer query.

WITH player_totals AS (
    SELECT
        pl.id,
        pl.name,
        pl.role,
        SUM(p.runs) AS total_runs
    FROM   performances  AS p
    JOIN   players       AS pl  ON pl.id = p.player_id
    GROUP BY pl.id, pl.name, pl.role
)
SELECT
    name,
    role,
    total_runs,
    ROUND(AVG(total_runs) OVER (), 1) AS squad_avg
FROM   player_totals
WHERE  total_runs > (SELECT AVG(total_runs) FROM player_totals)
ORDER BY total_runs DESC;

Alternative - scalar subquery inline (no CTE)

You can compute the average inline: HAVING SUM(p.runs) > (SELECT AVG(sub.total) FROM (SELECT SUM(runs) AS total FROM performances GROUP BY player_id) sub). This works but is harder to read - the CTE version names each step clearly and is easier to debug.

What to observe

AVG() OVER () with an empty OVER clause computes the average across the entire result - a window function trick that lets you show the squad average alongside each player's number without a second query or a self-join.

Hard

Full player report card with CASE WHEN performance tier

CTE · multiple aggregates · CASE WHEN classification · ROUND

Build a one-page squad report: every player's matches, runs, wickets, batting average, highest score, and a performance tier label (Star / Solid / Support) based on their numbers.

Approach

Use a CTE to compute all raw aggregates in one place, then apply CASE WHEN in the outer SELECT to assign the tier. Separating aggregation from classification makes both steps readable and individually testable.

WITH stats AS (
    SELECT
        pl.id,
        pl.name,
        pl.role,
        pl.is_active,
        COUNT(*)              AS matches_played,
        SUM(p.runs)           AS total_runs,
        SUM(p.wickets)        AS total_wickets,
        MAX(p.runs)           AS highest_score,
        ROUND(AVG(p.runs), 1) AS batting_avg
    FROM   performances  AS p
    JOIN   players       AS pl  ON pl.id = p.player_id
    GROUP BY pl.id, pl.name, pl.role, pl.is_active
)
SELECT
    name,
    role,
    CASE WHEN is_active THEN 'Active' ELSE 'Retired' END   AS status,
    matches_played,
    total_runs,
    total_wickets,
    highest_score,
    batting_avg,
    CASE
        WHEN total_runs >= 200 OR  total_wickets >= 8   THEN '⭐ Star'
        WHEN total_runs >= 80  OR  total_wickets >= 4   THEN '✅ Solid'
        ELSE                                                     '🔧 Support'
    END                                                       AS tier
FROM   stats
ORDER BY total_runs DESC;

What to observe

Kohli (388 runs) and Rohit (243) are clear Stars by runs. Bumrah (11 wickets) and Ashwin (10 wickets) earn Star by the bowling route. The CASE WHEN checks conditions top to bottom - first match wins. Adjust the thresholds and rerun to see the tier boundaries shift.

Concepts Covered

Run these 12 questions in order - each builds on what came before.

EasySELECT, WHERE, IS NULL, COALESCE, GROUP BY, COUNT, FILTER

MediumINNER JOIN, LEFT JOIN, anti-join, GROUP BY + HAVING, multi-condition filters

HardROW_NUMBER, RANK, SUM OVER, PARTITION BY, CTEs, CASE WHEN classification

Downloadcricketdb_setup.sql ↓

SQL Practice SetCricketDB

Concepts Covered

SQL Practice Set
CricketDB