Practice questions: subqueries, schema design and the capstone project
Subqueries, schema design and project22 min readLesson 3 of 3
These ten questions cover subqueries, schema design and transactions. After them is the capstone project: a library database with a schema, seed data, a query set and a delivery checklist. Answer first, then compare with the worked material.
3.1.1 Q1 What is a subquery, and where can it appear?
Answer (English): A subquery is a complete SELECT inside another statement. It can sit in WHERE as a value list or EXISTS test, in FROM as a derived table, in the select list as a scalar value, or in HAVING as part of a group comparison. Its position decides the expected shape. Parentheses are required and the inner query has no trailing semicolon.
Hinglish: Subquery doosre statement ke andar chalti SELECT hai. Woh WHERE, FROM, select list ya HAVING mein aa sakti hai, aur jagah se shape decide hoti hai: list, table ya scalar value. Parentheses chahiye, andar semicolon nahi.
3.1.2 Q2 What does a scalar subquery require, and how do SQLite and PostgreSQL differ when it returns several rows?
Answer (English): A scalar subquery should return one row and one column because a single value is expected. SQLite takes the first row when several come back. PostgreSQL raises an error because the standard allows at most one row. Make the single row explicit with an aggregate or with ORDER BY and LIMIT 1.
Hinglish: Scalar subquery ek row aur ek column lauti hai. SQLite kai rows par pehli row le leta hai, PostgreSQL error deta hai. Aggregate ya ORDER BY ... LIMIT 1 se single row explicit banao.
3.1.3 Q3 Why does NOT IN return nothing when the subquery contains NULL, and what is the fix?
Answer (English): For a row outside the list, the comparison against the NULL entry is UNKNOWN, so the row does not pass. NOT EXISTS asks whether a matching row exists and has no NULL comparison. Filtering NULLs out of the subquery with IS NOT NULL is the other fix.
Hinglish: List se bahar wali row ke liye NULL se comparison UNKNOWN banta hai, isliye row pass nahi hoti. NOT EXISTS NULL-safe hai, ya subquery mein IS NOT NULL filter lagao.
SELECT name FROM students
WHERE mentor_id NOT IN (SELECT mentor_id FROM students)
ORDER BY name;
SELECT name FROM students
WHERE NOT EXISTS (
SELECT 1 FROM students m WHERE m.mentor_id = students.mentor_id
)
ORDER BY name;Output
(0 rows) name ------------ Aarav Sharma Dev Patel Meera Iyer
3.1.4 Q4 When is a correlated subquery better than a join?
Answer (English): A correlated subquery reads a column from the outer row and runs once per outer row. It reads well for existence tests such as "students who have at least one enrollment". When the outer query needs columns from both tables, a join is clearer and easier for the planner to optimise. Planners often rewrite either form, so choose by readability and check the plan.
Hinglish: Correlated subquery outer row ka column padhti hai aur har row ke liye chalti hai. Existence test ke liye clear hoti hai; dono tables ke columns chahiye to join behtar hai. Planners aksar dono ko rewrite karte hain, isliye readability se chuno.
3.1.5 Q5 What is a CTE, and what makes it easier than a derived table?
Answer (English): A CTE is a named subquery declared with WITH before the main statement. The name turns nesting into a top-to-bottom pipeline, and several CTEs can be joined like tables. A derived table does the same work inside FROM, where the name is buried. Cost depends on the engine, so treat the CTE as a readability tool and check large queries with the plan.
Hinglish: CTE WITH se named subquery hoti hai jo main statement se pehle aati hai. Naam se nested query pipeline ban jaati hai aur kai CTEs join ho sakti hain. Derived table bhi same kaam karti hai par FROM ke andar dab jaati hai. CTE ko readability tool maano aur plan check karo.
3.1.6 Q6 Define 1NF, 2NF and 3NF with one example of each violation.
Answer (English): 1NF wants atomic values, so a comma-separated phone list in one cell violates it. 2NF wants every non-key column to depend on the whole composite key, so a student name stored in an enrollment violates it. 3NF wants no dependency between non-key columns, so a course title stored next to course_code in enrollments violates it. Each violation creates insert, update or delete anomalies.
Hinglish: 1NF atomic values chahiye, isliye cell mein phone list violation hai. 2NF mein non-key column poore composite key par depend kare, isliye enrollment mein student name violation hai. 3NF mein non-key columns ke beech dependency nahi honi chahiye, jaise enrollment mein course title.
3.1.7 Q7 When does the planner ignore an index?
Answer (English): It skips an index when the column is wrapped in a function, when a LIKE pattern starts with a wildcard, when the index is not selective, when statistics are stale, or when a scan is cheaper on a small table. An index on (city, joined_on) also cannot serve a query that filters only joined_on. Check the plan instead of guessing.
Hinglish: Index tab ignore hota hai jab column par function ho, LIKE wildcard se shuru ho, index selective na ho, statistics purane hon, ya chhoti table par scan sasta ho. (city, joined_on) wala index sirf joined_on wali query ko serve nahi karta. Plan dekho.
3.1.8 Q8 What does ROLLBACK do, and which ACID property does it demonstrate?
Answer (English): ROLLBACK discards every change made since BEGIN or since the named savepoint and restores the previous state. That is atomicity: the transaction applies completely or not at all. Consistency is enforced at commit, isolation keeps uncommitted work hidden, and durability keeps a committed version alive after a crash.
Hinglish: ROLLBACK BEGIN ya savepoint ke baad ke saare changes discard karke pehli state lauta deta hai. Yeh atomicity dikhata hai: transaction poora lage ya bilkul na lage. Consistency commit par, isolation uncommitted work chhupane se, aur durability crash ke baad data bachne se aati hai.
3.1.9 Q9 Walk through designing a schema for a small lending system.
Answer (English): Start with the nouns: books, members, loans. A loan links one book to one member, so it has two foreign keys, its own key, and the borrow, due and return dates. Keep title and email in their own tables with UNIQUE rules on ISBN and email. Add CHECK rules for copies and fines, then test each rule with a deliberately invalid insert.
Hinglish: Nouns se shuru karo: books, members, loans. Loan ek book ko ek member se jodta hai, isliye uske do foreign keys, apna key aur dates hote hain. Title aur email apni tables mein rakho, ISBN aur email par UNIQUE lagao, copies aur fine par CHECK lagao aur galat inserts se test karo.
3.1.10 Q10 How do you prove a schema works before shipping it?
Answer (English): Run three check sets. Constraint checks attempt invalid inserts and confirm each fails. Query checks run the application's real questions against hand-worked answers. Plan checks use EXPLAIN on heavy queries and confirm the expected index is used. Then run a transaction, roll it back and confirm nothing changed.
Hinglish: Teen check sets chalao. Constraint checks mein galat inserts fail hone chahiye. Query checks mein application ke asli sawal hand-worked answer se match karo. Plan checks mein EXPLAIN se index use confirm karo. Phir transaction chalakar rollback karo aur check karo kuch nahi badla.
3.2 Capstone project: a small library database
The capstone uses a separate library schema, which also proves the design rules on a fresh project. Requirements: books have one ISBN, a title, an author, a category and a copy count; members have a name, an email and a join date; a loan records which book went to which member, when it went out, when it is due and when it came back. A book can be out on several copies at once, and a member can hold several books. Fines are non-negative and recorded at return.
The design choices to defend in review: the loan row is the relationship, so its grain is one borrow event. returned_on is NULL while the book is out, which needs no extra boolean. isbn and email get UNIQUE rules because they identify a real book and a real person. CHECK (due_on >= borrowed_on) keeps a bad date out before a report has to trust it.
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS members;
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
isbn TEXT NOT NULL UNIQUE,
category TEXT NOT NULL,
copies INTEGER NOT NULL CHECK (copies >= 0)
);
CREATE TABLE members (
member_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
joined_on TEXT NOT NULL
);
CREATE TABLE loans (
loan_id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(book_id),
member_id INTEGER NOT NULL REFERENCES members(member_id),
borrowed_on TEXT NOT NULL,
due_on TEXT NOT NULL,
returned_on TEXT,
fine INTEGER NOT NULL DEFAULT 0 CHECK (fine >= 0),
CHECK (due_on >= borrowed_on)
);
INSERT INTO books (book_id, title, author, isbn, category, copies) VALUES
(1, 'SQL in Practice', 'R. Iyer', '9781000000001', 'databases', 3),
(2, 'Python Start', 'A. Das', '9781000000002', 'programming', 2),
(3, 'Data Structures', 'M. Nair', '9781000000003', 'programming', 1),
(4, 'Computer Networks', 'K. Bose', '9781000000004', 'networking', 2),
(5, 'Machine Learning', 'S. Rao', '9781000000005', 'ai', 1),
(6, 'Operating Systems', 'P. Menon', '9781000000006', 'systems', 1);
INSERT INTO members (member_id, name, email, joined_on) VALUES
(1, 'Aarav Sharma', 'aarav@library.example', '2026-01-12'),
(2, 'Meera Iyer', 'meera@library.example', '2026-01-18'),
(3, 'Sana Khan', 'sana@library.example', '2026-02-09'),
(4, 'Dev Patel', 'dev@library.example', '2026-03-22'),
(5, 'Zoya Ali', 'zoya@library.example', '2026-04-02');
INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on, returned_on, fine) VALUES
(1, 1, 1, '2026-03-10', '2026-03-24', '2026-03-20', 0),
(2, 2, 2, '2026-03-15', '2026-03-29', '2026-04-02', 30),
(3, 1, 3, '2026-03-18', '2026-04-01', NULL, 0),
(4, 5, 2, '2026-03-25', '2026-04-08', NULL, 0),
(5, 3, 1, '2026-04-01', '2026-04-15', NULL, 0),
(6, 4, 4, '2026-04-02', '2026-04-16', NULL, 0);3.3 Capstone query set
The query set proves the schema answers the library's real questions. Run these in order and check the answers. Active loans: four rows ordered by due date. Overdue on 5 April 2026: the one loan due on 1 April with no return. Members with no loans: Zoya Ali. Books never borrowed: Operating Systems. Most borrowed book: SQL in Practice with two loans. Recorded fines: one fine of 30 on the two returned loans, so the average fine is 15.
SELECT b.title, m.name, l.due_on
FROM loans l
JOIN books b ON b.book_id = l.book_id
JOIN members m ON m.member_id = l.member_id
WHERE l.returned_on IS NULL
ORDER BY l.due_on;Output
title | name | due_on ------------------+--------------+----------- SQL in Practice | Sana Khan | 2026-04-01 Machine Learning | Meera Iyer | 2026-04-08 Data Structures | Aarav Sharma | 2026-04-15 Computer Networks | Dev Patel | 2026-04-16
SELECT l.loan_id, b.title, m.name, l.due_on
FROM loans l
JOIN books b ON b.book_id = l.book_id
JOIN members m ON m.member_id = l.member_id
WHERE l.returned_on IS NULL AND l.due_on < '2026-04-05'
ORDER BY l.due_on;
SELECT SUM(fine) AS total_fines, AVG(fine) AS average_fine
FROM loans
WHERE returned_on IS NOT NULL;Output
loan_id | title | name | due_on --------+-----------------+-----------+----------- 3 | SQL in Practice | Sana Khan | 2026-04-01 total_fines | average_fine ------------+------------- 30 | 15
SELECT m.name
FROM members m
LEFT JOIN loans l ON l.member_id = m.member_id
WHERE l.member_id IS NULL;Output
name -------- Zoya Ali
SELECT b.title
FROM books b
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.book_id);Output
title ----------------- Operating Systems
SELECT b.title, COUNT(*) AS loans
FROM loans l
JOIN books b ON b.book_id = l.book_id
GROUP BY b.book_id, b.title
ORDER BY loans DESC, b.title
LIMIT 1;Output
title | loans ----------------+------ SQL in Practice | 2
3.4 Project notes: indexes, transactions and delivery
Indexes to add and defend: loans(book_id) for a book's history, loans(member_id) for a member's loans, and a composite loans(returned_on, due_on) for the overdue report, which filters both. Do not index members.email separately; the UNIQUE constraint already creates an index. Write one EXPLAIN check per report.
Transactions: a single UPDATE that sets returned_on and fine is atomic on its own, but a real return also adjusts copy counts and maybe a waitlist, so wrap those statements in BEGIN and COMMIT with a ROLLBACK on failure. The project notes should include that skeleton and one rollback test.
Delivery checklist: DDL script, seed script, six queries with expected output, a note on each design decision, constraint tests and plan checks. The project is done when the query set returns the stated rows, every invalid insert fails, and the rollback leaves the database unchanged.
3.5 Quiz pointer
The module quiz covers the whole module, including subqueries, normalization and transactions. Take it after reading the capstone queries, then rerun any query you missed. Keep the project files; a manager may ask you to defend each schema choice.