👑 Premium Sign in Sign up

Language lab · Beginner → Data

🗄️ SQL

Concept → trace → write code → run tests → explain why → improve → mini-project. Start in the browser if you like; set up your own computer when you are ready.

🧪 Runs in your browserOfficial download & docs ↗

📖 Topics with real code (15)

Every verified output below was produced by running the code with SQLite 3.45.1 on 5 Nov 2026. Open a topic to edit the code and run it right here in your browser.

🌱 Beginner (4)

Create a table and insert rows

CREATE TABLE defines columns and their types (INTEGER, TEXT, REAL). PRIMARY KEY makes each row unique. INSERT adds rows.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, class INTEGER, city TEXT);
INSERT INTO students (name, class, city) VALUES ('Asha', 8, 'Hyderabad'), ('Ravi', 9, 'Vijayawada'), ('Meena', 8, 'Warangal'), ('Imran', 10, 'Hyderabad');
SELECT * FROM students;

✅ Verified output

id | name | class | city
1 | Asha | 8 | Hyderabad
2 | Ravi | 9 | Vijayawada
3 | Meena | 8 | Warangal
4 | Imran | 10 | Hyderabad
SELECT, WHERE and boolean logic

WHERE keeps only matching rows. Combine conditions with AND, OR and NOT; IN tests a list, BETWEEN a range, LIKE a text pattern.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, class INTEGER, city TEXT, marks INTEGER);
INSERT INTO students (name, class, city, marks) VALUES ('Asha', 8, 'Hyderabad', 88), ('Ravi', 9, 'Vijayawada', 95), ('Meena', 8, 'Warangal', 72), ('Imran', 10, 'Hyderabad', 64), ('Priya', 9, 'Guntur', 81);
SELECT name, marks FROM students WHERE class = 8 AND marks >= 80;
SELECT name FROM students WHERE city IN ('Hyderabad', 'Guntur') OR marks > 90;
SELECT name, marks FROM students WHERE marks BETWEEN 70 AND 90 AND name LIKE '%a%';

✅ Verified output

name | marks
Asha | 88

name
Asha
Ravi
Imran
Priya

name | marks
Asha | 88
Meena | 72
Priya | 81
ORDER BY, LIMIT and calculated columns

ORDER BY sorts (ASC or DESC); LIMIT keeps the first rows. You can calculate new columns and name them with AS.

CREATE TABLE students (name TEXT, marks INTEGER, max_marks INTEGER);
INSERT INTO students VALUES ('Asha', 88, 100), ('Ravi', 95, 100), ('Meena', 72, 100), ('Imran', 64, 100);
SELECT name, marks, ROUND(100.0 * marks / max_marks, 1) AS percent, marks >= 75 AS distinction FROM students ORDER BY marks DESC LIMIT 3;

✅ Verified output

name | marks | percent | distinction
Ravi | 95 | 95.0 | 1
Asha | 88 | 88.0 | 1
Meena | 72 | 72.0 | 0
Aggregates: COUNT, SUM, AVG, MIN, MAX

Aggregate functions turn many rows into one value. COUNT(*) counts rows; COUNT(col) skips NULLs.

CREATE TABLE results (name TEXT, subject TEXT, marks INTEGER);
INSERT INTO results VALUES ('Asha', 'Maths', 88), ('Asha', 'Science', 92), ('Ravi', 'Maths', 95), ('Ravi', 'Science', NULL), ('Meena', 'Maths', 72);
SELECT COUNT(*) AS rows_, COUNT(marks) AS with_marks, SUM(marks) AS total, ROUND(AVG(marks), 2) AS average, MIN(marks) AS lowest, MAX(marks) AS highest FROM results;

✅ Verified output

rows_ | with_marks | total | average | lowest | highest
5 | 4 | 347 | 86.75 | 72 | 95

🌿 Intermediate (5)

GROUP BY and HAVING

GROUP BY makes one row per group; HAVING filters groups (WHERE filters rows before grouping).

CREATE TABLE results (name TEXT, subject TEXT, marks INTEGER);
INSERT INTO results VALUES ('Asha', 'Maths', 88), ('Asha', 'Science', 92), ('Ravi', 'Maths', 95), ('Ravi', 'Science', 70), ('Meena', 'Maths', 72), ('Meena', 'Science', 66);
SELECT subject, COUNT(*) AS students, ROUND(AVG(marks), 1) AS average FROM results GROUP BY subject;
SELECT name, SUM(marks) AS total FROM results GROUP BY name HAVING SUM(marks) > 150 ORDER BY total DESC;

✅ Verified output

subject | students | average
Maths | 3 | 85.0
Science | 3 | 76.0

name | total
Asha | 180
Ravi | 165
INNER JOIN and LEFT JOIN

JOIN combines tables on matching keys. INNER JOIN keeps only matches; LEFT JOIN keeps every row from the left table and fills NULL when there is no match.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE loans (book_id INTEGER, member_id INTEGER, returned TEXT);
INSERT INTO members VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena');
INSERT INTO books VALUES (1, 'Wings of Fire'), (2, 'Malgudi Days'), (3, 'Gitanjali');
INSERT INTO loans VALUES (1, 1, '2026-10-10'), (2, 1, NULL), (3, 2, NULL);
SELECT m.name, b.title, COALESCE(l.returned, 'not returned') AS status FROM loans l JOIN members m ON m.id = l.member_id JOIN books b ON b.id = l.book_id ORDER BY m.name;
SELECT m.name, COUNT(l.book_id) AS loans FROM members m LEFT JOIN loans l ON l.member_id = m.id GROUP BY m.name ORDER BY m.name;

✅ Verified output

name | title | status
Asha | Wings of Fire | 2026-10-10
Asha | Malgudi Days | not returned
Ravi | Gitanjali | not returned

name | loans
Asha | 2
Meena | 0
Ravi | 1
Subqueries

A query inside another query. Use it in WHERE (compare with a calculated value), with IN, or with EXISTS.

CREATE TABLE students (name TEXT, class INTEGER, marks INTEGER);
INSERT INTO students VALUES ('Asha', 8, 88), ('Ravi', 9, 95), ('Meena', 8, 72), ('Imran', 9, 64), ('Priya', 8, 81);
SELECT name, marks FROM students WHERE marks > (SELECT AVG(marks) FROM students);
SELECT name, class, marks FROM students s WHERE marks = (SELECT MAX(marks) FROM students t WHERE t.class = s.class) ORDER BY class;

✅ Verified output

name | marks
Asha | 88
Ravi | 95
Priya | 81

name | class | marks
Asha | 8 | 88
Ravi | 9 | 95
UPDATE, DELETE and constraints

UPDATE changes rows, DELETE removes them: always use WHERE. NOT NULL, UNIQUE and CHECK constraints reject bad data.

CREATE TABLE wallets (owner TEXT UNIQUE NOT NULL, paise INTEGER CHECK (paise >= 0));
INSERT INTO wallets VALUES ('Asha', 50000), ('Ravi', 20000), ('Old', 0);
UPDATE wallets SET paise = paise + 5000 WHERE owner = 'Ravi';
DELETE FROM wallets WHERE paise = 0;
SELECT * FROM wallets;
SELECT changes() AS rows_changed_by_last_statement;

✅ Verified output

owner | paise
Asha | 50000
Ravi | 25000

rows_changed_by_last_statement
1
CASE expressions

CASE adds if/else logic inside a query, for example to turn marks into grades or to count conditionally.

CREATE TABLE students (name TEXT, marks INTEGER);
INSERT INTO students VALUES ('Asha', 88), ('Ravi', 95), ('Meena', 72), ('Imran', 30);
SELECT name, marks, CASE WHEN marks >= 90 THEN 'A+' WHEN marks >= 75 THEN 'A' WHEN marks >= 35 THEN 'Pass' ELSE 'Fail' END AS grade FROM students;
SELECT SUM(CASE WHEN marks >= 35 THEN 1 ELSE 0 END) AS passed, SUM(CASE WHEN marks < 35 THEN 1 ELSE 0 END) AS failed FROM students;

✅ Verified output

name | marks | grade
Asha | 88 | A
Ravi | 95 | A+
Meena | 72 | Pass
Imran | 30 | Fail

passed | failed
3 | 1

🌳 Advanced (6)

Common table expressions (WITH) and recursion

WITH names a sub-result so a query reads top to bottom. WITH RECURSIVE builds rows step by step: here, numbers 1 to 10 and a Fibonacci table.

WITH class_avg AS (SELECT 8 AS class, 80.3 AS avg UNION ALL SELECT 9, 79.5)
SELECT class, avg, CASE WHEN avg > 80 THEN 'above 80' ELSE 'below 80' END AS band FROM class_avg;
WITH RECURSIVE fib(n, a, b) AS (SELECT 1, 0, 1 UNION ALL SELECT n + 1, b, a + b FROM fib WHERE n < 10)
SELECT n, a AS fibonacci FROM fib;

✅ Verified output

class | avg | band
8 | 80.3 | above 80
9 | 79.5 | below 80

n | fibonacci
1 | 0
2 | 1
3 | 1
4 | 2
5 | 3
6 | 5
7 | 8
8 | 13
9 | 21
10 | 34
Window functions

Window functions calculate across related rows without collapsing them: RANK within each class, a running total, the difference from the class average.

CREATE TABLE students (name TEXT, class INTEGER, marks INTEGER);
INSERT INTO students VALUES ('Asha', 8, 88), ('Ravi', 9, 95), ('Meena', 8, 72), ('Imran', 9, 64), ('Priya', 8, 81);
SELECT name, class, marks,
       RANK() OVER (PARTITION BY class ORDER BY marks DESC) AS rank_in_class,
       SUM(marks) OVER (ORDER BY name) AS running_total,
       ROUND(marks - AVG(marks) OVER (PARTITION BY class), 1) AS vs_class_avg
FROM students ORDER BY class, rank_in_class;

✅ Verified output

name | class | marks | rank_in_class | running_total | vs_class_avg
Asha | 8 | 88 | 1 | 88 | 7.7
Priya | 8 | 81 | 2 | 305 | 0.7
Meena | 8 | 72 | 3 | 224 | -8.3
Ravi | 9 | 95 | 1 | 400 | 15.5
Imran | 9 | 64 | 2 | 152 | -15.5
Transactions

BEGIN starts a transaction; COMMIT saves every change together; ROLLBACK undoes them all. Money moves either fully or not at all.

CREATE TABLE wallets (owner TEXT PRIMARY KEY, paise INTEGER CHECK (paise >= 0));
INSERT INTO wallets VALUES ('Asha', 50000), ('Ravi', 20000);
BEGIN;
UPDATE wallets SET paise = paise - 10000 WHERE owner = 'Asha';
UPDATE wallets SET paise = paise + 10000 WHERE owner = 'Ravi';
COMMIT;
BEGIN;
UPDATE wallets SET paise = paise + 99999 WHERE owner = 'Asha';
ROLLBACK;
SELECT owner, paise, (SELECT SUM(paise) FROM wallets) AS total FROM wallets;

✅ Verified output

owner | paise | total
Asha | 40000 | 70000
Ravi | 30000 | 70000
Indexes and query plans

An index lets the database find rows without scanning the whole table. EXPLAIN QUERY PLAN shows SCAN (every row) before and SEARCH ... USING INDEX after.

CREATE TABLE results (id INTEGER PRIMARY KEY, roll TEXT, marks INTEGER);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 2000) INSERT INTO results (roll, marks) SELECT 'R' || i, i % 101 FROM n;
EXPLAIN QUERY PLAN SELECT * FROM results WHERE roll = 'R42';
CREATE INDEX idx_results_roll ON results(roll);
EXPLAIN QUERY PLAN SELECT * FROM results WHERE roll = 'R42';
SELECT * FROM results WHERE roll = 'R42';

✅ Verified output

id | parent | notused | detail
2 | 0 | 0 | SCAN results

id | parent | notused | detail
3 | 0 | 0 | SEARCH results USING INDEX idx_results_roll (roll=?)

id | roll | marks
42 | R42 | 42
Views and normalisation

Normalised tables avoid repeating data; a VIEW saves a query so it can be used like a table.

CREATE TABLE classes (id INTEGER PRIMARY KEY, name TEXT, teacher TEXT);
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES classes(id), marks INTEGER);
INSERT INTO classes VALUES (1, '8A', 'Mrs Rao'), (2, '9B', 'Mr Khan');
INSERT INTO students (name, class_id, marks) VALUES ('Asha', 1, 88), ('Ravi', 2, 95), ('Meena', 1, 72);
CREATE VIEW report AS SELECT c.name AS class, c.teacher, s.name AS student, s.marks FROM students s JOIN classes c ON c.id = s.class_id;
SELECT * FROM report ORDER BY class, marks DESC;
SELECT class, teacher, COUNT(*) AS size FROM report GROUP BY class;

✅ Verified output

class | teacher | student | marks
8A | Mrs Rao | Asha | 88
8A | Mrs Rao | Meena | 72
9B | Mr Khan | Ravi | 95

class | teacher | size
8A | Mrs Rao | 2
9B | Mr Khan | 1
JSON functions

Modern SQL engines store and query JSON. json_extract reads a field; json_each turns a JSON array into rows.

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);
INSERT INTO events (payload) VALUES ('{"user":"Asha","action":"login","tags":["web","mobile"]}'), ('{"user":"Ravi","action":"quiz","score":9}');
SELECT id, json_extract(payload, '$.user') AS user, json_extract(payload, '$.action') AS action, json_extract(payload, '$.score') AS score FROM events;
SELECT e.id, t.value AS tag FROM events e, json_each(json_extract(e.payload, '$.tags')) t;

✅ Verified output

id | user | action | score
1 | Asha | login | NULL
2 | Ravi | quiz | 9

id | tag
1 | web
1 | mobile

🧪 Browser workspace

Runs in a sandbox inside your browser with a time limit: no install needed, and your code never reaches our server.

Loading the workspace…

🛠️ Set up and verify

  1. Install
    winget install SQLite.SQLite
    brew install sqlite
    sudo apt install sqlite3
  2. Editor: Visual Studio Code with SQLTools (+ SQLite driver).
  3. Create hello.sql
    SELECT 'Hello, GayaGnan!' AS greeting;
  4. Run it and check the version
    sqlite3 :memory: < hello.sql
sqlite3 --version
  5. Health check: paste what your terminal printed.

Install one language at a time. These commands only install or print versions; nothing here deletes files. Versions change: check the official page and note the version you installed.

🧭 The 14-lab path

  • 00 · Setup and verify — install the runtime and editor, run hello world, print the version, fix PATH
  • 01 · Syntax and trace — follow each line and the value of every variable
  • 02 · Logic — conditionals and decision trees
  • 03 · Loops — trace tables and loop challenges
  • 04 · Functions — break problems down and test each piece
  • 05 · Data structures — lists, maps, stacks, queues and trees you can see 🧪 SQL Joins Detective
  • 06 · Debugging — bug cases with a hint ladder
  • 07 · Algorithms — search, sort and Big-O comparison 🧪 Index Tuning Game
  • 08 · Files and data — read and write fixture files
  • 09 · API or database — talk to a mock API or a sandbox database 🧪 Transactions & Consistency
  • 10 · Testing — write tests and read failing assertions
  • 11 · Security — secure coding and dependency hygiene for this language
  • 12 · Project — one portfolio mini-project
  • 13 · Assessment — quiz, coding test, rubric and an explain-your-answer prompt

📚 Modules

  1. SELECT
  2. filters
  3. ORDER BY
  4. aggregates
  5. GROUP BY and HAVING
  6. joins
  7. subqueries
  8. CTEs
  9. window functions
  10. insert, update, delete (sandbox)
  11. constraints
  12. normalisation
  13. indexes
  14. transactions
  15. EXPLAIN plans
  16. data quality

🏗️ Mini-projects

  • School records schema
  • Synthetic bank transaction report
  • Claims status analytics
  • Star schema and dashboard query

Projects use synthetic data only.