Setting Up PostgreSQL & First Queries

What You'll Build

In this hands-on exercise you will install PostgreSQL, create your own IPL cricket database, and load it with real-looking match data. You will then run a graded sequence of queries that exercise everything from Module 1: selecting columns, filtering with WHERE, and ranking results with ORDER BY and LIMIT, all against data you populated yourself.

By the end you will have a working local database and a query file that answers concrete cricket questions, such as the top run scorers and which batsmen stayed not out. This is the foundation every later module builds on, so getting a clean, reproducible setup now pays off through the entire course rather than fighting environment issues later.

The goal is fluency, not just correctness. You will type each query yourself, predict its output before running it, and compare against the expected result. That deliberate loop, predict then verify, is how query intuition is built, and it is far more valuable than copying answers, because it trains you to reason about what the engine will actually return.

Prerequisites

• A computer running Windows, macOS, or Linux with permission to install software and roughly 300 MB of free disk space available.
• Basic comfort with a terminal or command prompt, since you will run a few commands to start the database and open its shell.
• Completion of Module 1 reading lessons on SELECT, WHERE, and ORDER BY, whose concepts every exercise query below directly applies.
• A plain text editor to save your queries in a .sql file so your work is reproducible and easy to rerun from scratch.

Setup & Project Structure

You will install the PostgreSQL server and its command-line client, psql. The server is the engine that stores data and runs queries; psql is the interactive shell you type SQL into. We will keep all work in a single project folder containing one schema file that creates and seeds tables, and one queries file holding the exercise answers.

Keeping setup in a script rather than typing it once interactively means you can drop and rebuild the database at any time with a single command. This reproducibility is a core data-engineering habit: anyone, including future you, can recreate the exact same database state from the files, making debugging and collaboration vastly easier than relying on a fragile, hand-built database.

# 1. Install PostgreSQL (choose your platform).

# macOS (Homebrew):
brew install postgresql@16
brew services start postgresql@16

# Ubuntu / Debian Linux:
sudo apt update
sudo apt install -y postgresql postgresql-client
sudo service postgresql start

# Windows: download the installer from postgresql.org and run it,
# then use the bundled "SQL Shell (psql)" application.

# 2. Create a project folder and files.
mkdir ipl_sql_lab
cd ipl_sql_lab
touch schema.sql        # creates and seeds tables
touch exercises.sql     # your query answers

# 3. Create a fresh database for this lab.
createdb ipl_lab

# 4. Open the interactive shell connected to that database.
psql ipl_lab
# You should see a prompt like:  ipl_lab=#
# Type \q to quit, \dt to list tables, \d players to describe a table.

Step 1 — Foundation

Step one builds the schema: a single deliveries table rich enough to practise every Module 1 concept. It includes numeric columns for runs, text columns for names and venues, and a nullable dismissal column so you can practise IS NULL filtering. Defining clear types and a primary key now gives the engine the structure it needs to validate every row you insert.

You will save this in schema.sql and run it once. The CREATE TABLE statement declares the structure, and the INSERT statements seed enough rows to make query results meaningful. Running the whole file at once demonstrates the reproducible workflow: the database state comes entirely from a script you can rerun anytime, not from manual one-off typing.

-- schema.sql : run with  psql ipl_lab -f schema.sql

-- Start clean so the script is fully reproducible.
DROP TABLE IF EXISTS deliveries;

CREATE TABLE deliveries (
    delivery_id  INTEGER PRIMARY KEY,
    batsman      TEXT    NOT NULL,
    bowler       TEXT,
    team_name    TEXT    NOT NULL,
    venue        TEXT    NOT NULL,
    runs_scored  INTEGER NOT NULL CHECK (runs_scored >= 0),
    strike_rate  NUMERIC,
    dismissal    TEXT          -- NULL means the batsman was not out
);

INSERT INTO deliveries
    (delivery_id, batsman, bowler, team_name, venue, runs_scored, strike_rate, dismissal) VALUES
    (1,  'Rohit Sharma',     'Bumrah',  'Mumbai Indians',      'Wankhede',    72, 142.0, NULL),
    (2,  'Virat Kohli',      'Chahar',  'Royal Challengers',   'Chinnaswamy', 45, 128.5, 'caught'),
    (3,  'MS Dhoni',         'Boult',   'Chennai Super Kings', 'Chepauk',     18, 150.0, 'bowled'),
    (4,  'Suryakumar Yadav', 'Rashid',  'Mumbai Indians',      'Wankhede',    61, 168.5, NULL),
    (5,  'Rishabh Pant',     'Bumrah',  'Delhi Capitals',      'Kotla',       89, 151.0, 'run out'),
    (6,  'Shubman Gill',     'Siraj',   'Gujarat Titans',      'Motera',      57, 129.4, 'lbw'),
    (7,  'Virat Kohli',      'Bumrah',  'Royal Challengers',   'Wankhede',    33, 110.0, 'caught'),
    (8,  'Rohit Sharma',     'Chahal',  'Mumbai Indians',      'Chinnaswamy', 12,  85.0, 'bowled'),
    (9,  'KL Rahul',         'Boult',   'Lucknow Super Giants','Lucknow',     74, 133.9, NULL),
    (10, 'Hardik Pandya',    'Rashid',  'Mumbai Indians',      'Motera',      46, 164.2, 'caught');

-- Quick sanity check after loading.
SELECT COUNT(*) AS total_rows FROM deliveries;

Step 2 — Core Logic

Now you write the core exercise queries that apply SELECT, WHERE, and ORDER BY. Each is a concrete cricket question with a single correct result against the seeded data. Save these in exercises.sql and run them one at a time, predicting the output first. The comments state each task so your file doubles as a self-contained, gradeable worksheet.

These queries deliberately revisit every Module 1 idea: explicit column selection, comparison and pattern filters, NULL handling, and ranked top-N results. Working through them in sequence cements how the clauses combine in a single statement, which is exactly the muscle memory you need before moving on to aggregations and joins in the modules that follow.

-- exercises.sql : run with  psql ipl_lab -f exercises.sql
-- Predict each result BEFORE running, then verify.

-- Q1. List every batsman and their runs, highest first.
SELECT batsman, runs_scored
FROM deliveries
ORDER BY runs_scored DESC, delivery_id ASC;

-- Q2. Show only innings of more than 50 runs.
SELECT batsman, runs_scored
FROM deliveries
WHERE runs_scored > 50
ORDER BY runs_scored DESC;

-- Q3. Find all not-out innings (dismissal is missing).
SELECT batsman, runs_scored, venue
FROM deliveries
WHERE dismissal IS NULL;

-- Q4. Innings by Mumbai Indians OR Delhi Capitals scoring at least 45.
SELECT batsman, team_name, runs_scored
FROM deliveries
WHERE (team_name = 'Mumbai Indians' OR team_name = 'Delhi Capitals')
  AND runs_scored >= 45
ORDER BY runs_scored DESC, delivery_id ASC;

-- Q5. Any innings played at a venue whose name starts with 'W'.
SELECT batsman, venue, runs_scored
FROM deliveries
WHERE venue LIKE 'W%';

-- Q6. The single top-scoring innings (top-N with a tie-breaker).
SELECT batsman, runs_scored, venue
FROM deliveries
ORDER BY runs_scored DESC, delivery_id ASC
LIMIT 1;

Step 3 — Integration & Enhancement

Step three combines clauses and adds computed columns to answer richer questions. You will alias derived values, build a clean leaderboard with a stable tie-breaker, and use DISTINCT to list unique venues. These queries integrate everything from Module 1 into single statements, mirroring how real reporting queries layer filtering, computation, ordering, and limiting all at once.

Pay attention to the integer-division guard and the deterministic ORDER BY here, because these are the exact details that separate a query that looks right from one that is right. Saving these enhanced queries alongside the earlier ones gives you a complete, reproducible worksheet that demonstrates genuine command of the module's material.

-- exercises.sql (continued)

-- Q7. Leaderboard: name, runs, and runs-per-over-style rate,
--     forcing decimal division to avoid integer truncation.
SELECT batsman,
       runs_scored,
       ROUND(runs_scored * 1.0 / 6, 2) AS runs_per_six_balls
FROM deliveries
ORDER BY runs_scored DESC, delivery_id ASC;

-- Q8. Unique list of venues used in the dataset.
SELECT DISTINCT venue
FROM deliveries
ORDER BY venue ASC;

-- Q9. Top 3 strike rates among innings of at least 40 runs,
--     relabelled for a clean report.
SELECT batsman          AS player,
       runs_scored      AS runs,
       strike_rate      AS sr
FROM deliveries
WHERE runs_scored >= 40
ORDER BY strike_rate DESC, delivery_id ASC
LIMIT 3;

-- Q10. Page 2 of the full leaderboard, 3 rows per page.
SELECT batsman, runs_scored
FROM deliveries
ORDER BY runs_scored DESC, delivery_id ASC
LIMIT 3 OFFSET 3;

Step 4 — Testing & Verification

Finally you verify your work by running both files end to end and checking the outputs against the expected results below. Because everything lives in scripts, you can drop the database, recreate it, and rerun in seconds, confirming your setup is genuinely reproducible rather than dependent on lucky manual steps you cannot repeat.

# Rebuild and run everything from scratch to prove reproducibility.
dropdb ipl_lab && createdb ipl_lab
psql ipl_lab -f schema.sql
psql ipl_lab -f exercises.sql

# EXPECTED KEY RESULTS (verify a few):
# schema.sql  -> total_rows = 10
# Q2 (>50 runs) returns 5 rows: Pant 89, Rohit 72, KL Rahul 74, Surya 61, Gill 57
#                (ordered: Pant 89, KL Rahul 74, Rohit 72, Surya 61, Gill 57)
# Q3 (not out) returns 3 rows: Rohit (72), Surya (61), KL Rahul (74)
# Q6 (top innings) -> Rishabh Pant, 89, Kotla
# Q8 (distinct venues) -> Chepauk, Chinnaswamy, Kotla, Lucknow, Motera, Wankhede
# Q10 (page 2) -> rows ranked 4th-6th: KL Rahul 74? recompute by running it.

# Tip: pipe a single query's output to verify row counts:
psql ipl_lab -c "SELECT COUNT(*) FROM deliveries WHERE runs_scored > 50;"
# Expected: 5

• Installing the PostgreSQL server plus the psql client gives you a local engine to run every query, the foundation for the whole course.
• Keeping schema and queries in .sql files makes your database fully reproducible, so you can drop and rebuild identical state in seconds.
• Defining clear column types, NOT NULL, and a CHECK constraint up front lets the engine reject invalid data the moment you insert it.
• Predicting each query's result before running it, then verifying, builds genuine query intuition far faster than copying answers.
• A single statement can combine SELECT, WHERE, ORDER BY, LIMIT, and computed aliases, mirroring how real reporting queries are built.
• Forcing decimal division and adding a unique tie-breaker in ORDER BY are the small details that make queries correct, not just plausible.