SQLProjects and Interview Preparationadvanced30 min read

SQL Best Practices and Final Project

Master production SQL practices, query optimization, schema migration safety, indexing strategies, and a complete student management database capstone.

In this lesson, you will learn

✓Review production SQL performance rules: avoid SELECT *, index foreign keys where appropriate, and use SARGable predicates.
✓Understand schema migration workflows and safe database changes in CI/CD pipelines.
✓Apply constraints, transactions, indexes, and query plans to build reliable database applications.
✓Design and query a small student management database as a capstone project.
✓Evaluate SQL code for correctness, security, maintainability, and performance.

Introduction to SQL Best Practices and Final Project

SQL knowledge becomes valuable when queries and schemas remain reliable as an application grows. Production SQL is not only about getting the correct result. It must also protect data, remain understandable, and perform acceptably as data volume and concurrent usage increase.

This final lesson brings together the most important ideas from the curriculum:

- **Correctness:** return the intended rows and handle NULL values and duplicate records deliberately.
- **Integrity:** use primary keys, foreign keys, UNIQUE constraints, CHECK constraints, and NOT NULL where appropriate.
- **Performance:** retrieve only required columns, filter effectively, and inspect query plans before making optimization decisions.
- **Security:** use parameterized queries and grant only the database permissions an application needs.
- **Maintainability:** choose clear names, consistent formatting, and understandable query structures.
- **Operational safety:** test schema changes, maintain backups and recovery procedures, and deploy versioned migrations.
- **Verification:** test normal cases, empty results, boundary values, invalid inputs, and realistic data volumes.

A query that works on ten rows may behave differently on ten million rows. Likewise, a migration that works on a development database may cause downtime or data loss if it is deployed carelessly to production.

The goal is not to follow rules mechanically. It is to understand why each practice exists and verify that it fits the database engine, workload, and application requirements.

Production SQL Rules

Use these rules as a review checklist for application and reporting queries.

**1. Select only the columns you need**

Prefer SELECT student_id, name over SELECT *. This communicates intent, can reduce data transfer, and prevents an application from accidentally depending on newly added columns.

**2. Filter as early as practical**

Use WHERE to restrict rows before returning them to the application. The optimizer may reorder operations internally, so do not assume the textual order alone determines performance.

**3. Make predicates index-friendly when possible**

A SARGable predicate allows the optimizer to use an appropriate index search condition. For example, filtering a timestamp with a range is often more index-friendly than applying a function to every timestamp.

**4. Index according to workload**

Indexes can improve reads, joins, and ordering, but they also consume storage and add work to INSERT, UPDATE, and DELETE operations. Do not create an index for every column.

**5. Join on meaningful keys**

Use declared primary and foreign keys to express relationships. Check whether a join unexpectedly multiplies rows because the relationship is one-to-many.

**6. Handle NULL intentionally**

Use IS NULL and IS NOT NULL to test for NULL. Comparisons such as column_name = NULL do not behave like ordinary equality comparisons.

**7. Use deterministic ordering when required**

ORDER BY created_at alone may not be deterministic when multiple rows have the same timestamp. Add a unique tie-breaker when stable ordering is required.

**8. Parameterize application queries**

Bind user-provided values as parameters. Do not construct SQL by concatenating untrusted input into the query text.

**9. Use transactions for related changes**

When multiple operations must succeed or fail together, group them in a transaction and handle errors correctly.

**10. Measure before optimizing**

Use realistic data, inspect query plans, and compare execution time and resource use. Avoid assuming that a query is slow or that an index will help without evidence.

Core Syntax & Practical Examples

These PostgreSQL examples illustrate common production patterns. They assume the tables and columns named in each example exist.

**Example 1: Select only required columns**

Instead of returning every column, request the fields needed by the application.

Select Only Required Columns

sql

1
2
3
4
5
6
7
8
9
-- Avoid returning unnecessary columns
SELECT *
FROM students;
 
-- Prefer an explicit column list
SELECT student_id, name, email
FROM students
WHERE is_active = TRUE
ORDER BY name, student_id;

Output

The output is illustrative and assumes two active students. Explicit columns make the query's requirements clear. The final output depends on the actual table data.

Use SARGable Date Filters

Suppose an application needs orders created during October 2026.

A function applied to a timestamp column may prevent an ordinary index on that column from being used efficiently. A half-open range is often a better starting point.

Filter Timestamps With a Range

sql

1
2
3
4
5
6
7
8
9
10
11
-- Often less index-friendly with a regular index on created_at
SELECT order_id, created_at
FROM orders
WHERE DATE(created_at) = DATE '2026-10-01';
 
-- Index-friendly range for October 2026
SELECT order_id, created_at
FROM orders
WHERE created_at >= TIMESTAMP '2026-10-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-11-01 00:00:00'
ORDER BY created_at, order_id;

Output

order_id | created_at ---------+--------------------- 101 | 2026-10-03 09:30:00 102 | 2026-10-15 14:10:00 103 | 2026-10-29 18:45:00

The output is illustrative. The half-open interval includes the start and excludes the next month's start, avoiding end-of-month timestamp precision problems. Actual index use depends on the query plan, indexes, statistics, and data distribution.

Inspect Query Plans

PostgreSQL provides EXPLAIN to show the plan the optimizer intends to use. EXPLAIN ANALYZE executes the query and reports observed execution information.

Use EXPLAIN ANALYZE carefully: the statement actually runs. For a SELECT this normally reads data, but for a modifying statement it can change data unless it is safely contained and rolled back.

Review a Query Plan

sql

1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY order_id;
 
-- Executes the SELECT and reports actual measurements
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY order_id;

Output

Illustrative plan shape: Index Scan using idx_orders_customer_id on orders Index Cond: (customer_id = 42) Actual plan may differ.

The displayed plan is illustrative, not a guaranteed result. PostgreSQL may choose a sequential scan, an index scan, or another plan depending on table size, statistics, available indexes, and estimated costs. Compare estimated and actual row counts and inspect buffer usage when investigating performance.

Indexing Rules

An index is a data structure that can help the database locate rows without scanning every row in a table. PostgreSQL commonly uses B-tree indexes for equality, range, and ordering operations.

**Consider an index when:**
- A column is frequently used in selective WHERE conditions.
- A column is used to join tables and the workload benefits from faster lookups.
- Queries repeatedly filter or order by a compatible combination of columns.
- A UNIQUE constraint is required. PostgreSQL creates a unique index to enforce it.

**Foreign-key reminder:** PostgreSQL automatically indexes the referenced primary key or unique key, but it does not automatically create an index on the referencing foreign-key columns. Indexing those columns can help joins and can reduce the cost of checking or changing referenced rows.

**Composite index order matters.** For an index on (customer_id, created_at), queries that constrain customer_id can often benefit. A query filtering only by created_at may not benefit equally from that index.

**Avoid over-indexing.** Every extra index requires storage and maintenance. Measure real workloads and periodically review whether indexes are used.

An index is a tool, not a guarantee. Always verify its usefulness using the query plan and representative data.

Create an Index for a Common Query

Suppose the application frequently fetches a customer's orders from newest to oldest. A composite index may help this access pattern.

Create and Evaluate a Composite Index

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);
 
SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
 
-- Inspect the plan after the index exists
EXPLAIN
SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Output

Illustrative result: order_id | created_at | total_amount ---------+---------------------+------------- 108 | 2026-10-20 11:30:00 | 1499.00 104 | 2026-10-12 16:00:00 | 899.00

The sample rows are illustrative. The index begins with customer_id because the query filters by that column, then stores created_at in descending order. The order_id tie-breaker makes the result deterministic, but the index may need to be adjusted if that ordering is important to performance. Confirm the actual plan before retaining the index.

Schema Migrations in CI/CD

A schema migration is a versioned change to the database structure or data. Examples include creating a table, adding a column, introducing a constraint, or transforming stored values.

A safer migration workflow is:

1. **Create a migration:** make a small, focused change and record it in version control.
2. **Review it:** check correctness, permissions, locking, data volume, rollback options, and compatibility with the application.
3. **Test it:** apply the migration to a disposable database with representative data. Test both the migration and application behavior.
4. **Check deployment compatibility:** if old and new application versions may run simultaneously, avoid a change that immediately breaks either version.
5. **Back up and prepare recovery:** use the organization's tested backup, restore, and incident procedures for high-risk changes.
6. **Deploy in the right order:** use expand-and-contract when a change must remain compatible across multiple application versions.
7. **Verify after deployment:** check migration status, application errors, constraints, query performance, and data correctness.

**Expand-and-contract example**

- Expand: add a new nullable column while the old application continues to work.
- Transition: deploy application code that writes the new column and, when needed, backfills existing rows in controlled batches.
- Verify: confirm the new column is populated and the application uses it correctly.
- Contract: only after compatibility and data checks, remove the old column in a later migration.

Do not assume every migration is automatically reversible. Dropping a column, deleting data, or rewriting values may require a recovery strategy rather than a simple down migration.

PostgreSQL supports transactional DDL for many common schema changes, but some operations have special transaction restrictions or significant locking implications. Review the exact command and PostgreSQL version before production deployment.

Example Migration

This example adds a field without immediately forcing every existing row to have a value. Application code and a backfill can be deployed before the column becomes mandatory.

In a real project, place each migration in the migration tool's versioned files rather than rerunning it manually without tracking.

Add, Backfill, and Validate a Column

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Migration 1: expand the schema
ALTER TABLE students
ADD COLUMN graduation_year INTEGER;
 
-- Backfill example: replace the illustrative value
-- with a value derived from trusted application or source data.
UPDATE students
SET graduation_year = 2027
WHERE graduation_year IS NULL
  AND is_active = TRUE;
 
-- Check the data before adding stricter rules
SELECT COUNT(*) AS missing_graduation_years
FROM students
WHERE is_active = TRUE
  AND graduation_year IS NULL;
 
-- Only apply stricter rules when the business requirement
-- and all existing data support them.
-- ALTER TABLE students
-- ALTER COLUMN graduation_year SET NOT NULL;

Output

missing_graduation_years ----------------------- 0

The count of zero is illustrative and is not guaranteed. The backfill value is only a demonstration; production values must come from the actual business rules and trusted data. The final NOT NULL migration is commented out because it is valid only if every row that must satisfy the constraint has been populated.

Transactions and Data Integrity

Transactions protect operations that must be treated as one logical unit. PostgreSQL uses BEGIN, COMMIT, and ROLLBACK to control a transaction.

For example, enrolling a student in a course may require inserting an enrollment record and updating a related counter. If the operations must succeed together, use a transaction and enforce data rules with constraints where possible.

Remember:
- A transaction does not replace primary keys, foreign keys, or CHECK constraints.
- Keep transactions as short as practical to reduce lock duration.
- Handle errors in the application and ensure failed work is rolled back.
- Use appropriate isolation and locking when concurrent operations could conflict.
- Do not assume that a read followed by a write is safe under every concurrency pattern.

Real-World Architecture & Best Practices

In a backend application, SQL usually sits between application services and a database. A robust flow looks like this:

1. The application validates request shape and business rules.
2. The database query uses bound parameters rather than concatenated input.
3. The database enforces persistent rules through constraints.
4. Related changes execute within an appropriate transaction.
5. Queries are reviewed using representative data and execution plans.
6. Migrations are versioned, tested, and deployed through the release process.
7. Logs and metrics help identify slow queries, failures, and connection pressure.

Avoid logging passwords, access tokens, or sensitive personal information. Use connection pooling appropriately, apply least-privilege database roles, and separate schema-changing permissions from ordinary application permissions.

**Production review checklist**

- Does the query return the correct rows?
- Are NULLs, duplicates, empty results, and boundary cases handled?
- Are only necessary columns returned?
- Are parameters safely bound?
- Are joins and aggregations logically correct?
- Are indexes supported by the actual query workload?
- Have query plans and performance been checked on representative data?
- Are schema changes compatible with application deployment?
- Are constraints, transaction boundaries, and permissions appropriate?
- Is there a tested recovery plan for risky changes?

These practices are complementary. For example, an index cannot fix a logically incorrect join, and a transaction cannot prevent SQL injection caused by unsafe string concatenation.

Final Project: Student Management Database

Build a small relational database for a learning platform. It stores students, courses, and enrollments.

**Requirements**
- Every student has a unique email address.
- Every course has a unique course code.
- An enrollment connects one student to one course.
- A student cannot enroll in the same course twice.
- Course capacity cannot be negative.
- Reports show enrollment totals and students who have not enrolled.
- Queries should be readable and should support common access patterns.

**Step 1: Create the schema**

Use primary keys for entity identity, foreign keys for relationships, and UNIQUE constraints to prevent duplicate business identifiers.

Create the Capstone Schema

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE TABLE students (
    student_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE courses (
    course_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    course_name VARCHAR(150) NOT NULL,
    capacity INTEGER NOT NULL CHECK (capacity >= 0)
);
 
CREATE TABLE enrollments (
    enrollment_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (student_id, course_id)
);
 
-- Useful indexes for common lookups and joins
CREATE INDEX idx_enrollments_course_id
ON enrollments (course_id);
 
CREATE INDEX idx_enrollments_student_enrolled
ON enrollments (student_id, enrolled_at DESC);

Output

CREATE TABLE CREATE TABLE CREATE TABLE CREATE INDEX CREATE INDEX

The messages illustrate successful execution in a clean PostgreSQL database where these table names do not already exist. PostgreSQL automatically creates indexes for primary keys and UNIQUE constraints. The additional enrollment indexes support likely join and lookup patterns, but should still be evaluated against the actual workload.

Seed the Capstone Database

Insert a small, consistent dataset so the reporting queries and their expected results can be checked against the same records.

Insert Sample Students, Courses, and Enrollments

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
INSERT INTO students (name, email)
VALUES
    ('Asha', 'asha@example.com'),
    ('Rahul', 'rahul@example.com'),
    ('Meera', 'meera@example.com');
 
INSERT INTO courses (course_code, course_name, capacity)
VALUES
    ('SQL101', 'SQL Fundamentals', 30),
    ('DB201', 'Database Design', 20);
 
INSERT INTO enrollments (student_id, course_id, enrolled_at)
SELECT s.student_id, c.course_id, v.enrolled_at
FROM (
    VALUES
        ('asha@example.com', 'SQL101', TIMESTAMPTZ '2026-10-01 09:00:00+00'),
        ('rahul@example.com', 'SQL101', TIMESTAMPTZ '2026-10-02 09:00:00+00'),
        ('asha@example.com', 'DB201', TIMESTAMPTZ '2026-10-03 09:00:00+00')
) AS v(email, course_code, enrolled_at)
JOIN students AS s ON s.email = v.email
JOIN courses AS c ON c.course_code = v.course_code;

Output

INSERT 0 3 INSERT 0 2 INSERT 0 3

Assuming the tables are empty before the inserts, the three students, two courses, and three enrollments are inserted. Running the seed data again will encounter uniqueness violations unless the data is reset or the seed script is deliberately made repeatable.

Capstone Query 1: Enrollment Counts

Use LEFT JOIN to include courses even when they have no enrollments. Count a column from the enrollment table rather than COUNT(*), because the unmatched row from a LEFT JOIN still appears in the result.

Count Enrollments Per Course

sql

1
2
3
4
5
6
7
8
9
10
SELECT
    c.course_code,
    c.course_name,
    COUNT(e.enrollment_id) AS enrollment_count,
    c.capacity
FROM courses AS c
LEFT JOIN enrollments AS e
    ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_code, c.course_name, c.capacity
ORDER BY c.course_code;

Output

This output follows the capstone seed data. SQL101 has two enrollments, while DB201 has one. A course with no enrollments would still appear with a count of zero.

Capstone Query 2: Students Without Enrollments

A LEFT JOIN can find students who do not have a matching enrollment. The condition in the WHERE clause tests for the absence of a matching enrollment row.

Find Students With No Enrollments

sql

1
2
3
4
5
6
SELECT s.student_id, s.name, s.email
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
WHERE e.enrollment_id IS NULL
ORDER BY s.student_id;

Output

student_id | name | email -----------+-------+------------------- 3 | Meera | meera@example.com

Meera has no enrollment in the sample dataset, so she is returned. This anti-join pattern is useful for finding records that lack a related row.

Capstone Query 3: Find Courses Near Capacity

A course may be considered near capacity when its enrollment count reaches at least 80% of its capacity. This is a reporting rule, not a database constraint.

A zero-capacity course requires deliberate handling because dividing by zero is invalid. NULLIF prevents that division, while the CASE expression makes the zero-capacity result explicit.

Calculate Course Capacity Utilization

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    c.course_code,
    c.course_name,
    COUNT(e.enrollment_id) AS enrollment_count,
    c.capacity,
    CASE
        WHEN c.capacity = 0 THEN NULL
        ELSE ROUND(
            100.0 * COUNT(e.enrollment_id) / NULLIF(c.capacity, 0),
            2
        )
    END AS utilization_percent
FROM courses AS c
LEFT JOIN enrollments AS e
    ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_code, c.course_name, c.capacity
ORDER BY c.course_code;

Output

course_code | course_name | enrollment_count | capacity | utilization_percent ------------+-------------------+------------------+----------+-------------------- DB201 | Database Design | 1 | 20 | 5.00 SQL101 | SQL Fundamentals | 2 | 30 | 6.67

The utilization values are calculated from the seed data. A zero-capacity course returns NULL for utilization because a percentage of a zero capacity is undefined. If the business needs a different representation, define it explicitly.

Capstone Query 4: Add an Enrollment Safely

The UNIQUE(student_id, course_id) constraint prevents duplicate enrollments even when two requests arrive concurrently. An application can use a transaction for a larger workflow and decide how to handle an existing enrollment.

This example uses PostgreSQL's ON CONFLICT clause to make an attempted duplicate enrollment harmless.

Prevent Duplicate Enrollment

sql

1
2
3
4
INSERT INTO enrollments (student_id, course_id)
VALUES (3, 1)
ON CONFLICT (student_id, course_id) DO NOTHING
RETURNING enrollment_id, student_id, course_id;

Output

If the enrollment is new: enrollment_id | student_id | course_id --------------+------------+---------- 4 | 3 | 1 If the student is already enrolled: (0 rows)

The enrollment ID shown is illustrative; PostgreSQL identity values depend on prior inserts and sequence state. RETURNING returns the inserted row. DO NOTHING returns no row when the unique conflict causes the insert to be skipped.

Capstone Review and Testing

Before marking the project complete, test more than the happy path.

**Schema tests**
- Insert a student with a duplicate email and confirm the UNIQUE constraint rejects it.
- Insert an enrollment referencing a nonexistent student and confirm the foreign key rejects it.
- Insert the same student-course pair twice and confirm the second insert is rejected or deliberately skipped using ON CONFLICT.
- Insert a negative course capacity and confirm the CHECK constraint rejects it.

**Query tests**
- Verify enrollment counts against known sample data.
- Confirm courses with no enrollments still appear in the count report.
- Confirm students with no enrollments appear in the anti-join report.
- Test empty tables and tables with many rows.

**Operational tests**
- Apply migrations to a fresh database and to a copy containing representative data.
- Check that application code remains compatible during deployment.
- Review permissions and ensure the application role cannot perform unnecessary administrative operations.
- Inspect plans for frequently executed queries.
- Document any assumptions about data, ordering, time zones, and business rules.

A capstone is complete when its design choices are explainable, its constraints are tested, and its queries return correct results—not merely when the SQL statements run without errors.

Practice Exercises With Solutions

Complete these exercises using the capstone tables. Try to write each query before reading its solution.

**Exercise 1:** List active students alphabetically, returning only their IDs and names.

**Exercise 2:** Find the number of enrollments for every course, including courses with zero enrollments.

**Exercise 3:** Find students who are not enrolled in any course.

**Exercise 4:** Write a query that finds duplicate email addresses in an existing students table. Explain why a UNIQUE constraint is preferable when duplicate emails are not allowed.

**Exercise 5:** Explain how you would investigate a slow query that filters orders by customer ID and sorts by creation time.

**Exercise 6:** Describe a safe migration strategy for replacing a column used by an application without breaking the currently deployed version.

**Exercise 7:** Explain why a foreign-key column may need its own index in PostgreSQL.

Solutions follow.

Practice Exercise Solutions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Exercise 1
SELECT student_id, name
FROM students
WHERE is_active = TRUE
ORDER BY name, student_id;
 
-- Exercise 2
SELECT c.course_code, COUNT(e.enrollment_id) AS enrollment_count
FROM courses AS c
LEFT JOIN enrollments AS e
    ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_code
ORDER BY c.course_code;
 
-- Exercise 3
SELECT s.student_id, s.name
FROM students AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments AS e
    WHERE e.student_id = s.student_id
)
ORDER BY s.student_id;
 
-- Exercise 4
SELECT LOWER(email) AS normalized_email, COUNT(*) AS occurrences
FROM students
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;
 
-- Exercise 5: start by inspecting the actual plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Output

The exercise outputs are based on the capstone seed data where applicable. Exercise 4 returns no rows because the seed data has unique emails; LOWER(email) is used to detect case-insensitive duplicates. If email comparisons must be case-insensitive by design, enforce that rule consistently with an appropriate unique expression index or another schema-level strategy. For Exercise 6, use an expand-and-contract migration: add the replacement, deploy compatible code, backfill and verify, then remove the old column in a later release. For Exercise 7, PostgreSQL does not automatically index the referencing side of a foreign key; an index may speed up joins and checks when referenced rows are changed.

SQL Interview Questions and Answers

**1. Why should you avoid SELECT * in production queries?**

It can return unnecessary data, increase transfer and processing costs, and couple application code to columns it does not need. Explicit columns communicate intent more clearly.

**2. What does SARGable mean?**

A predicate is SARGable when it can be used effectively as a search condition by an index access method. For example, a timestamp range is often more index-friendly than wrapping the indexed timestamp in a function.

**3. Does creating an index always make a query faster?**

No. An index consumes storage and must be maintained during writes. If a query reads a large portion of a table, a sequential scan may be cheaper. The execution plan and real workload determine whether an index helps.

**4. How do you investigate a slow PostgreSQL query?**

Reproduce it with representative parameters and data, inspect EXPLAIN, and use EXPLAIN ANALYZE and BUFFERS when safe. Compare estimated versus actual rows, look at scans, joins, sorting, buffer activity, and timing, then make one evidence-based change at a time.

**5. Why might a foreign key need an index?**

PostgreSQL ensures that the referenced key is indexed, but does not automatically index the referencing columns. An index on those columns can improve joins and help PostgreSQL find dependent rows when a referenced row is updated or deleted.

**6. What is a database migration?**

A versioned, repeatable change to a database schema or data. Migrations should be reviewed, tested, ordered, and tracked alongside application releases.

**7. What is the expand-and-contract migration pattern?**

First add the new structure while retaining compatibility, then deploy code that supports the transition, backfill and verify data, and finally remove the old structure in a later release.

**8. Why use a transaction?**

A transaction groups related changes so they can commit together or be rolled back together. Constraints and appropriate concurrency control are still needed to protect integrity.

**9. How can you prevent SQL injection?**

Use parameterized queries or prepared statements, avoid concatenating untrusted input into SQL, validate input where appropriate, and give the application database role only necessary permissions.

**10. How do you know whether a capstone database is production-ready?**

Verify data integrity, query correctness, security, migration safety, expected workload performance, error handling, monitoring, backups, and recovery procedures. Passing a few example queries is not sufficient.

Common Mistakes

Avoid these mistakes when applying SQL in real projects.

Common SQL Mistakes and Safer Alternatives

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Mistake 1: returning every column
SELECT *
FROM students;
 
-- Better: return only what the caller needs
SELECT student_id, name, email
FROM students;
 
-- Mistake 2: comparing with NULL using equality
SELECT student_id
FROM students
WHERE email = NULL;
 
-- Better
SELECT student_id
FROM students
WHERE email IS NULL;
 
-- Mistake 3: concatenating untrusted input into SQL
-- Do not build a query by concatenating a user-supplied value.
-- Use a parameterized query in the application instead.
 
-- Mistake 4: assuming a foreign key creates an index
-- Create a referencing-side index when workload analysis supports it.
CREATE INDEX idx_enrollments_student_id
ON enrollments (student_id);
 
-- Mistake 5: assuming an index will always be used
EXPLAIN
SELECT student_id, name
FROM students
WHERE is_active = TRUE;

Output

The corrected NULL query returns rows whose email is NULL. EXPLAIN displays the optimizer's planned operations. Index creation succeeds only if the index name does not already exist in the schema.

The examples demonstrate safer patterns. The final CREATE INDEX duplicates an index already created in the capstone setup, so run that statement only if the index is absent or use a deliberate migration that accounts for existing schema state. Index use is determined by the optimizer, not guaranteed by the existence of an index.

Lesson Summary

You have reached the end of the SQL curriculum.

You should now be able to:
- Write SELECT queries with filters, sorting, joins, grouping, and subqueries.
- Use aggregate functions, CTEs, and window functions to solve reporting and interview problems.
- Design relational tables and use keys, constraints, and appropriate data types.
- Use transactions and understand why concurrent changes require careful handling.
- Apply parameterized queries and least-privilege permissions to reduce security risks.
- Investigate performance using query plans and evidence-based indexing.
- Plan versioned schema migrations that minimize deployment risk.
- Build and test a small relational project from its schema through reporting queries.

**Final challenge:** Extend the student management database with assessments and marks. Add constraints for valid marks, write a query that calculates each student's average mark, and use a window function to rank students within each course. Document your assumptions, test boundary cases, and explain how you would optimize the query as the dataset grows.

Finishing this lesson is the start of applying SQL in real applications. Continue practicing with realistic data, reviewing execution plans, and explaining your design decisions clearly.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using SELECT * in frequently executed application queries

Why it happens: It is convenient during exploration, so developers sometimes leave it in production code.

Incorrect Approach

sql

SELECT *
FROM students
WHERE is_active = TRUE;

Correct Solution

sql

SELECT student_id, name, email
FROM students
WHERE is_active = TRUE;

Key takeaway: Explicit columns make query intent clear and reduce unnecessary data transfer. SELECT * can still be appropriate for some exploratory tasks, but application queries usually benefit from selecting only required fields.

#2 Wrapping an indexed timestamp column in a function without checking the plan

Why it happens: The function-based predicate can look shorter and easier to read.

Incorrect Approach

sql

SELECT order_id
FROM orders
WHERE DATE(created_at) = DATE '2026-10-01';

Correct Solution

sql

SELECT order_id
FROM orders
WHERE created_at >= TIMESTAMP '2026-10-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-10-02 00:00:00';

Key takeaway: A half-open range is often compatible with a regular B-tree index on created_at. Always confirm the actual plan and ensure the boundaries match the column's data type and time-zone requirements.

#3 Creating indexes without considering write costs

Why it happens: Indexes can improve some reads, making it tempting to add one for every filtered or joined column.

Incorrect Approach

sql

-- Adding indexes indiscriminately is not a workload strategy.
CREATE INDEX idx_students_name ON students (name);
CREATE INDEX idx_students_email ON students (email);
CREATE INDEX idx_students_active ON students (is_active);

Correct Solution

sql

-- Add an index to support a known access pattern,
-- then validate it with a representative execution plan.
CREATE INDEX idx_enrollments_student_enrolled
ON enrollments (student_id, enrolled_at DESC);

Key takeaway: The example index is appropriate only if the application frequently queries enrollments by student and recency. Indexes require storage and increase write maintenance, so evaluate their value using real workload evidence.

#4 Deploying an untested schema migration directly to production

Why it happens: A migration may appear simple on an empty development database but behave differently with production data, locks, and concurrent application versions.

Incorrect Approach

sql

-- Risky when existing rows contain NULL or the table is large.
ALTER TABLE students
ALTER COLUMN graduation_year SET NOT NULL;

Correct Solution

sql

-- First inspect existing data.
SELECT COUNT(*) AS missing_years
FROM students
WHERE graduation_year IS NULL;
 
-- Apply SET NOT NULL only after the business rule is
-- confirmed and all required rows have been populated.
-- Test locking and deployment behavior before production.

Key takeaway: Schema changes need version control, realistic testing, compatibility planning, and a recovery strategy. A zero missing-row count is necessary for this example, but it is not a complete deployment plan by itself.

#5 Assuming foreign keys automatically index the referencing columns in PostgreSQL

Why it happens: A foreign-key declaration validates the relationship, so developers may assume it also creates every useful access path.

Incorrect Approach

sql

CREATE TABLE enrollments (
    enrollment_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(student_id)
);

Correct Solution

sql

CREATE INDEX idx_enrollments_student_id
ON enrollments (student_id);

Key takeaway: PostgreSQL automatically indexes the referenced primary or unique key, but not the referencing foreign-key column. Add an index when joins, deletes, updates, and measured workload justify it. Check for an existing suitable index before creating another.

#6 Trusting sample outputs without checking the dataset

Why it happens: Lesson examples are sometimes written independently, causing expected results to refer to rows that were never inserted.

Incorrect Approach

sql

-- Claiming a student has an enrollment
-- without verifying the seed data.

Correct Solution

sql

SELECT s.student_id, s.name
FROM students AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments AS e
    WHERE e.student_id = s.student_id
);

Key takeaway: Keep seed data and expected outputs consistent. For the capstone in this lesson, the seed data contains three students and three enrollments, leaving Meera without an enrollment. In a real database, verify results by executing the query against the documented setup.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Technical and placement interview questions

Frequently tested interview questions with detailed, full answers (click to expand)

Q1:What does SARGable mean?

Answer:

Q2:Does creating an index always make a query faster?

Answer:

Q3:How do you investigate a slow PostgreSQL query?

Answer:

Q4:Why might a foreign key need an index?

Answer:

Q5:What is a database migration?

Answer:

A versioned, repeatable change to a database schema or data. Migrations should be reviewed, tested, ordered, and tracked alongside application releases.

Q6:What is the expand-and-contract migration pattern?

Answer:

First add the new structure while retaining compatibility, then deploy code that supports the transition, backfill and verify data, and finally remove the old structure in a later release.

Q7:Why use a transaction?

Answer:

A transaction groups related changes so they can commit together or be rolled back together. Constraints and appropriate concurrency control are still needed to protect integrity.

Q8:How can you prevent SQL injection?

Answer:

Use parameterized queries or prepared statements, avoid concatenating untrusted input into SQL, validate input where appropriate, and give the application database role only necessary permissions.

Q9:How do you know whether a capstone database is production-ready?

Answer:

Key takeaways

Write SQL that is correct, secure, readable, and appropriate for the workload.
Avoid SELECT * in application queries when only specific columns are needed.
Use EXPLAIN and, when appropriate, EXPLAIN ANALYZE to investigate query performance.
Create indexes based on actual query patterns and verify their write and storage costs.
Use constraints and transactions to protect data integrity.
Manage schema changes through versioned, reviewed, tested migrations.
A successful capstone combines a sensible schema, reliable queries, and documented design decisions.

Was this lesson helpful?

Found an error? Report an issue