SQL for Development Data

Back to Code Studio

How the live SQL works. Your first Run downloads SQLite (compiled to WebAssembly by the sql.js project, under 1 MB) and loads three small tables: households (240 rows), districts (10 rows) and survey (16 rows). Every query runs on your own machine, and nothing you type is sent anywhere. Reload the page to get the tables back as they started.

Module 1 of 9

Your first query

SQL (Structured Query Language) is how you ask questions of data held in tables. Most government and NGO systems keep their records in a database: a scheme's beneficiary register, a survey platform's server, a district MIS. SQL is the common language for reading them.

This course runs SQLite, a small database engine that lives in a single file and here runs inside your browser. The core of SQL is shared across databases, so what you learn transfers to PostgreSQL, MySQL and SQL Server. Where SQLite behaves differently, the course says so in a box marked Other databases.

Three tables

households has 240 households across 10 districts, with caste, the head's gender and education, household size, monthly per-capita expenditure (in rupees), land, and Yes/No columns for a toilet, a bank account, self-help group membership and a cash transfer. districts has one row per district with its state, region, programme phase and field team. survey is a 16-person sample from three districts. Illustrative data All three tables are invented for teaching. The district names are real places, but no number in these tables describes them.

Click Run. The star means every column; LIMIT 5 keeps the first five rows.

SELECT * FROM households LIMIT 5;

Name the columns you want instead of using the star. Each column is separated by a comma, and the statement ends with a semicolon.

SELECT hh_id, district, caste, monthly_pc_exp
FROM households
LIMIT 8;

Which version of SQLite is this? Ask it.

SELECT sqlite_version();

Other databases. LIMIT works in SQLite, PostgreSQL and MySQL. SQL Server writes SELECT TOP 5 * instead, and the SQL standard form is FETCH FIRST 5 ROWS ONLY.

Try it: in the second cell, add hh_size and land_acres to the column list and run again. Then change the table to districts and select every column.

Module 2 of 9

Filter with WHERE, sort with ORDER BY

WHERE keeps the rows that meet a condition. Text values go in single quotes and must match exactly, including capital letters.

SELECT hh_id, district, caste, monthly_pc_exp
FROM households
WHERE district = 'Gaya' AND area = 'Rural';

Combine conditions with AND and OR, use IN for a list of values and BETWEEN for a range (both ends included). Brackets make the order of a mixed AND/OR explicit.

SELECT hh_id, district, caste, head_gender, monthly_pc_exp
FROM households
WHERE caste IN ('SC', 'ST')
  AND head_gender = 'Female'
  AND monthly_pc_exp BETWEEN 1000 AND 3000;

Sort and keep the top rows

ORDER BY sorts, ascending by default; add DESC for largest first. With LIMIT this gives you the poorest or richest households in a few words.

SELECT hh_id, district, caste, hh_size, monthly_pc_exp
FROM households
ORDER BY monthly_pc_exp ASC
LIMIT 10;

LIKE matches patterns: % stands for any run of characters. Here, every district whose name starts with P.

SELECT * FROM districts WHERE district LIKE 'P%';

Other databases. SQLite's LIKE ignores case for English letters, so 'p%' also matches Patna. PostgreSQL's LIKE is case-sensitive; it has ILIKE for the case-insensitive version.

Try it: change the third query to list the ten highest-spending households, then add WHERE area = 'Rural' before the ORDER BY.

Module 3 of 9

Aggregates, GROUP BY and HAVING

Aggregate functions collapse many rows into one number: COUNT, SUM, AVG, MIN, MAX. AS gives the result a name, and ROUND(x, 0) trims the decimals.

SELECT COUNT(*)                     AS households,
       ROUND(AVG(monthly_pc_exp), 0) AS mean_pc_exp,
       MIN(monthly_pc_exp)           AS lowest,
       MAX(monthly_pc_exp)           AS highest
FROM households;

One row per group

GROUP BY runs the aggregate separately for each value of a column. This is disaggregation, the same split you would make in a pivot table.

SELECT caste,
       COUNT(*)                      AS households,
       ROUND(AVG(monthly_pc_exp), 0) AS mean_pc_exp
FROM households
GROUP BY caste
ORDER BY mean_pc_exp DESC;

In this invented table, SC households average 2,356 rupees and General households 3,771. The overall mean of 3,401 in the first query hides that gap.

A share is an average of a 0/1 column. In SQLite a comparison such as has_toilet = 'Yes' returns 1 or 0, so its average is the proportion of households with a toilet.

SELECT district,
       COUNT(*) AS households,
       ROUND(100.0 * AVG(has_toilet = 'Yes'), 1)       AS pct_toilet,
       ROUND(100.0 * AVG(has_bank_account = 'Yes'), 1) AS pct_bank
FROM households
GROUP BY district
ORDER BY pct_toilet;

Other databases. PostgreSQL will not average a true/false value directly. Write AVG(CASE WHEN has_toilet = 'Yes' THEN 1.0 ELSE 0 END), which works everywhere, including here. Also watch integer division: in PostgreSQL and SQL Server 7 / 2 is 3. Multiplying by 100.0 first keeps the decimals.

Filter groups with HAVING

WHERE filters rows before grouping; HAVING filters the groups after. This query groups by district and caste, then keeps only the cells with at least 5 households, a habit worth keeping before you report any small-group average.

SELECT district, caste,
       COUNT(*) AS households,
       ROUND(AVG(monthly_pc_exp), 0) AS mean_pc_exp
FROM households
WHERE area = 'Rural'
GROUP BY district, caste
HAVING COUNT(*) >= 5
ORDER BY district, mean_pc_exp;

Try it: in the second query, change caste to head_gender in both places and run again. In the last query, raise the threshold to 8 and see which cells drop out.

Module 4 of 9

Recoding with CASE

CASE turns values into categories. SQL checks each WHEN in order and stops at the first one that is true, so put the narrowest band first or order the cut-offs from low to high.

SELECT hh_id, monthly_pc_exp,
       CASE
         WHEN monthly_pc_exp < 1500 THEN '1 below 1,500'
         WHEN monthly_pc_exp < 3000 THEN '2 1,500 to 2,999'
         WHEN monthly_pc_exp < 6000 THEN '3 3,000 to 5,999'
         ELSE '4 6,000 and above'
       END AS exp_band
FROM households
LIMIT 10;

The bands are invented for teaching. In real work, cut-offs come from a stated source, such as a poverty line for a named year and sector. The number at the start of each label makes the bands sort in order.

Group by the new category

You can group by the CASE expression itself. SQLite also lets you group by the alias, exp_band, which saves repeating it.

SELECT CASE
         WHEN monthly_pc_exp < 1500 THEN '1 below 1,500'
         WHEN monthly_pc_exp < 3000 THEN '2 1,500 to 2,999'
         WHEN monthly_pc_exp < 6000 THEN '3 3,000 to 5,999'
         ELSE '4 6,000 and above'
       END AS exp_band,
       COUNT(*) AS households,
       ROUND(100.0 * AVG(received_transfer = 'Yes'), 1) AS pct_transfer
FROM households
GROUP BY exp_band
ORDER BY exp_band;

Other databases. Grouping by an alias works in SQLite, PostgreSQL and MySQL. SQL Server and Oracle reject it; repeat the whole CASE in the GROUP BY, or compute it in a CTE (module 6).

Recode land into the agricultural census classes

The Agriculture Census groups operational holdings into five size classes: marginal (below 1 hectare), small (1 to 2), semi-medium (2 to 4), medium (4 to 10) and large (10 and above), as the Ministry of Agriculture and Farmers Welfare set out in a PIB release of 5 February 2019. The table records land in acres, and one acre is about 0.405 hectares, so the query converts first. The last three classes are merged because so few households here hold that much.

SELECT CASE
         WHEN land_acres = 0 THEN '0 landless'
         WHEN land_acres * 0.4047 < 1 THEN '1 marginal (under 1 ha)'
         WHEN land_acres * 0.4047 < 2 THEN '2 small (1 to 2 ha)'
         ELSE '3 larger (2 ha and above)'
       END AS land_class,
       COUNT(*) AS households
FROM households
WHERE area = 'Rural'
GROUP BY land_class
ORDER BY land_class;

Try it: add a fourth column to the second query, ROUND(100.0 * AVG(caste IN ('SC','ST')), 1) AS pct_sc_st, and run again. Then move the first cut-off from 1,500 to 2,000 in all three places you need to.

Module 5 of 9

Joining households to districts

The households table knows each household's district but not its state or field team. Those live in districts, one row per district. A join brings them together on the column they share.

SELECT h.hh_id, h.district, d.state, d.field_team, h.monthly_pc_exp
FROM households AS h
JOIN districts AS d ON h.district = d.district
LIMIT 8;

h and d are short names (aliases) for the tables, so h.district means the district column of households. Once joined, you can group by any column from either table.

SELECT d.state,
       COUNT(*) AS households,
       ROUND(AVG(h.monthly_pc_exp), 0) AS mean_pc_exp,
       ROUND(100.0 * AVG(h.has_toilet = 'Yes'), 1) AS pct_toilet
FROM households AS h
JOIN districts AS d ON h.district = d.district
GROUP BY d.state
ORDER BY mean_pc_exp DESC;

Inner join against left join

A plain JOIN is an inner join: it keeps only rows that find a match on both sides. A left join keeps every row of the left-hand table, and fills the right-hand columns with NULL where there is no match.

Every household's district appears in districts, so the joins above lose nothing. The 16-person survey is different: it covers only three districts. Count survey respondents per district with an inner join first.

SELECT d.district, d.state, COUNT(s.id) AS respondents
FROM districts AS d
JOIN survey AS s ON s.district = d.district
GROUP BY d.district, d.state
ORDER BY d.district;

Three rows: the inner join has silently dropped the seven districts the survey never reached. Now the same query with LEFT JOIN.

SELECT d.district, d.state, COUNT(s.id) AS respondents
FROM districts AS d
LEFT JOIN survey AS s ON s.district = d.district
GROUP BY d.district, d.state
ORDER BY respondents DESC, d.district;

All ten districts appear, and the seven without a respondent show 0. For a coverage report that difference is the whole point: an inner join would make the gaps disappear.

Count the right thing. COUNT(s.id) counts only rows where the survey id is not NULL, which is why unmatched districts show 0. COUNT(*) counts rows, and would show 1 for each of them, because the left join keeps one row of NULLs for every unmatched district. Change it and run again to see.

Try it: add d.programme_phase to the last query (in the SELECT and the GROUP BY). Then, in the state query, group by d.field_team instead.

Module 6 of 9

Subqueries and CTEs

A subquery is a query inside another query. This one finds households spending more than the overall mean: the inner query computes the mean once, and the outer query uses it as a number.

SELECT COUNT(*) AS above_mean
FROM households
WHERE monthly_pc_exp > (SELECT AVG(monthly_pc_exp) FROM households);

A subquery can also stand in for a table. Here the inner query builds district means, and the outer query keeps the districts above 3,000.

SELECT *
FROM (
  SELECT district, ROUND(AVG(monthly_pc_exp), 0) AS mean_pc_exp
  FROM households
  GROUP BY district
) AS dm
WHERE mean_pc_exp > 3000
ORDER BY mean_pc_exp DESC;

WITH: name each step

Nested brackets get hard to read after two levels. A common table expression (CTE), written with WITH, names each step and lets the next step use it, top to bottom. This query compares each district's mean with its state's mean.

WITH district_means AS (
  SELECT district, AVG(monthly_pc_exp) AS d_mean, COUNT(*) AS n
  FROM households
  GROUP BY district
),
state_means AS (
  SELECT d.state, AVG(h.monthly_pc_exp) AS s_mean
  FROM households AS h
  JOIN districts AS d ON h.district = d.district
  GROUP BY d.state
)
SELECT dm.district, d.state, dm.n,
       ROUND(dm.d_mean, 0) AS district_mean,
       ROUND(sm.s_mean, 0) AS state_mean,
       ROUND(dm.d_mean - sm.s_mean, 0) AS gap
FROM district_means AS dm
JOIN districts AS d ON dm.district = d.district
JOIN state_means AS sm ON sm.state = d.state
ORDER BY d.state, gap DESC;

Each CTE can be run on its own while you build the query: copy the inside of district_means into a fresh SELECT to check it before you rely on it. That habit catches most join mistakes early.

Try it: in the CTE query, add WHERE area = 'Rural' to both CTEs, so the comparison is rural households only. Remember that the second CTE needs h.area.

Module 7 of 9

Window functions: rank and compare within groups

GROUP BY collapses each group to one row. A window function computes across a group but keeps every row. The group is set by OVER (PARTITION BY ...). SQLite has supported window functions since version 3.25 (2018); the version check in module 1 shows the one running here.

Each household against its district mean

SELECT hh_id, district, monthly_pc_exp,
       ROUND(AVG(monthly_pc_exp) OVER (PARTITION BY district), 0) AS district_mean,
       ROUND(monthly_pc_exp - AVG(monthly_pc_exp) OVER (PARTITION BY district), 0) AS gap
FROM households
ORDER BY district, hh_id
LIMIT 12;

Number the rows within each group

ROW_NUMBER() numbers rows 1, 2, 3 within each partition, in the order you give. Wrap it in a CTE and keep numbers 1 to 3 to get the three lowest-spending households in every district: a common first step when drawing a list for a field visit.

WITH ranked AS (
  SELECT hh_id, district, caste, monthly_pc_exp,
         ROW_NUMBER() OVER (PARTITION BY district ORDER BY monthly_pc_exp) AS rn
  FROM households
)
SELECT district, rn, hh_id, caste, monthly_pc_exp
FROM ranked
WHERE rn <= 3
ORDER BY district, rn;

Look at Indore: households 59 and 62 both spend 1,640 but get numbers 1 and 2. ROW_NUMBER() breaks ties in an order you did not choose, and it can change between runs or databases. Add a second sort key, ORDER BY monthly_pc_exp, hh_id, to make the list repeatable.

RANK and ties

RANK() gives tied values the same rank and then skips; ROW_NUMBER() never ties. Here the districts are ranked by the share of households with a bank account, computed in a CTE first.

WITH d AS (
  SELECT district,
         ROUND(100.0 * AVG(has_bank_account = 'Yes'), 1) AS pct_bank
  FROM households
  GROUP BY district
)
SELECT district, pct_bank,
       RANK()       OVER (ORDER BY pct_bank DESC) AS rnk,
       ROW_NUMBER() OVER (ORDER BY pct_bank DESC) AS row_num
FROM d
ORDER BY rnk, district;

Barmer, Kozhikode, Patna and Rewa all have 91.7 per cent, so all four get rank 1 and the next district, Gaya, gets rank 5. DENSE_RANK() does the same without skipping; add it as a third column and compare.

Try it: in the ROW_NUMBER query, add DESC after monthly_pc_exp inside OVER (...) to list the three highest spenders instead. Then partition by caste.

Module 8 of 9

Missing values: NULL

NULL means unknown or not recorded. SQL keeps it apart from zero and from an empty string, and it follows its own rules. The three teaching tables have no missing values, so this cell makes a small table that does: a follow-up visit log where some visits recorded no expenditure. The table is invented.

DROP TABLE IF EXISTS followup;
CREATE TEMP TABLE followup (hh_id INTEGER, visit_month TEXT, pc_exp_followup INTEGER);
INSERT INTO followup VALUES
  (1, '2026-06', 3400),
  (2, '2026-06', NULL),
  (3, '2026-06', 2900),
  (4, '2026-06', NULL),
  (5, '2026-06', 0);
SELECT * FROM followup;

Counting and averaging skip NULL

SELECT COUNT(*)               AS visits,
       COUNT(pc_exp_followup) AS recorded,
       AVG(pc_exp_followup)   AS mean_recorded,
       SUM(pc_exp_followup) / COUNT(*) AS mean_if_missing_were_zero
FROM followup;

AVG divides by the 3 recorded values and leaves out the 2 visits with nothing recorded. That is usually what you want, but say so when you report it: "mean of 3 households with a recorded value". Treating a missing value as zero, as the last column does, pulls the mean down without any evidence that those households spent nothing.

Test for NULL with IS NULL

Any comparison with NULL gives NULL, which WHERE treats as false. So = NULL matches nothing. Run this and compare the two counts.

SELECT
  (SELECT COUNT(*) FROM followup WHERE pc_exp_followup = NULL)  AS with_equals,
  (SELECT COUNT(*) FROM followup WHERE pc_exp_followup IS NULL) AS with_is_null;

COALESCE and NULLIF

COALESCE(a, b) returns the first value that is not NULL, which is useful for labels. NULLIF(x, 0) turns a zero into NULL, which protects a division from a zero denominator.

SELECT hh_id,
       COALESCE(CAST(pc_exp_followup AS TEXT), 'not recorded') AS shown,
       ROUND(1000.0 / NULLIF(pc_exp_followup, 0), 3) AS per_1000
FROM followup;

Other databases. SQLite returns NULL when you divide by zero. PostgreSQL and SQL Server stop with an error instead, which is why NULLIF(x, 0) is worth writing every time.

Left joins create NULLs too, as module 5 showed. To find households in the follow-up log that match nothing in households, or the other way round, join and keep the rows where the right-hand key IS NULL. The next module uses exactly that.

Try it: add a row with INSERT INTO followup VALUES (6, '2026-06', NULL); at the top of the second cell, run it, and check which counts change.

Module 9 of 9

Working with an MIS export

Scheme and programme MIS systems hold records as rows: one per beneficiary, per payment, per visit or per month. When you download an export, you get a table, and the questions you ask of it are the ones in this course: how many, where, how much, who is missing, which rows repeat.

The cell below builds a small export from the households table. Every value in it is invented, including the amount and the month, and its columns are chosen for teaching: they do not copy any real portal's layout. Run it first; it reports how many rows it made (93).

-- Build an invented MIS-style export from the households table.
-- Run this cell first; the table lasts until you reload the page.
DROP TABLE IF EXISTS mis_payments;
CREATE TEMP TABLE mis_payments (
  payment_id   INTEGER,
  beneficiary_id INTEGER,  -- the household's id in the programme register
  district     TEXT,
  month        TEXT,       -- YYYY-MM, stored as text
  amount       REAL,
  status       TEXT
);
-- One credited payment for most households that reported a transfer
INSERT INTO mis_payments
SELECT hh_id * 10, hh_id, district, '2026-04', 500, 'Credited'
FROM households
WHERE received_transfer = 'Yes' AND hh_id % 9 <> 0;
-- A few failed payments, and one row entered twice
INSERT INTO mis_payments
SELECT hh_id * 10 + 1, hh_id, district, '2026-04', 500, 'Failed'
FROM households
WHERE received_transfer = 'No' AND hh_id % 13 = 0;
INSERT INTO mis_payments
SELECT payment_id, beneficiary_id, district, month, amount, status
FROM mis_payments
WHERE beneficiary_id = (SELECT MIN(beneficiary_id) FROM mis_payments);
SELECT COUNT(*) AS rows_in_export FROM mis_payments;

Check for duplicates before you count anything

A payment entered twice inflates every total built on it. Group by the id and keep the groups that appear more than once.

SELECT payment_id, beneficiary_id, COUNT(*) AS copies
FROM mis_payments
GROUP BY payment_id, beneficiary_id
HAVING COUNT(*) > 1;

One payment, number 30 for beneficiary 3, appears twice.

Totals by district and status

Count distinct beneficiaries as well as rows, so a duplicate cannot pass as an extra person.

SELECT district, status,
       COUNT(*) AS payment_rows,
       COUNT(DISTINCT beneficiary_id) AS beneficiaries,
       SUM(amount) AS amount
FROM mis_payments
GROUP BY district, status
ORDER BY district, status;

Rewa shows 11 credited rows but 10 beneficiaries, and 5,500 rupees where the true figure is 5,000. That is the duplicate from the last query. Drop it with SELECT DISTINCT in a CTE, or sum over distinct payment ids, before any total leaves your hands.

Reconcile the export with the survey

Households that told the survey they received a transfer, but have no credited payment in the export, are the cases to follow up. A left join from the survey side, keeping rows where the MIS side is NULL, finds them. This pattern is often called an anti-join.

SELECT h.district, COUNT(*) AS reported_but_not_in_mis
FROM households AS h
LEFT JOIN mis_payments AS m
  ON m.beneficiary_id = h.hh_id AND m.status = 'Credited'
WHERE h.received_transfer = 'Yes'
  AND m.beneficiary_id IS NULL
GROUP BY h.district
ORDER BY reported_but_not_in_mis DESC;

A gap here has several possible causes, and the query cannot tell them apart: the survey answer may be wrong, the payment may sit in a month outside the export, or the household may be missing from the register. The query tells you where to look.

Real MIS data is personal data. A beneficiary export usually carries names, phone numbers, bank details or ID numbers. Work with the fewest columns you need, replace identifiers with a study id before analysis, and check your obligations under the Digital Personal Data Protection Act, 2023. The Data Protection & the DPDP Act deck covers them.

Try it: change the reconciliation to count households that said No to a transfer but do have a credited payment. You need to flip the survey condition and change IS NULL to IS NOT NULL.

Where next

→