SQLProjects and Interview Preparationadvanced30 min read

Building a Student Management Database

Build an end-to-end PostgreSQL student management database with normalized tables, constraints, sample data, enrollment tracking, GPA calculations, and analytical queries.

In this lesson, you will learn

✓Design a normalized schema for students, courses, enrollments, and grades
✓Define primary keys, foreign keys, unique constraints, check constraints, and appropriate data types
✓Seed sample test data while preserving valid foreign key relationships
✓Write analytical queries calculating student GPAs and course enrollment statistics
✓Use joins, aggregate functions, conditional expressions, and common table expressions to answer reporting questions
✓Understand how NULL grades, retakes, and students without enrollments affect analytical queries

Introduction to the Student Management Database

A student management database stores information about students, the courses offered by an institution, and the courses students register for.

In this project, we will build a small but realistic relational database using PostgreSQL.

Our database will answer questions such as:
- Which courses has a student enrolled in?
- How many students are enrolled in each course?
- Which students have completed a course?
- What is each student's GPA based on completed, graded enrollments?
- Which courses currently have no enrollments?
- Which students have not registered for any courses?

## The data model

We will use four main tables:

1. students — one row per student.
2. courses — one row per course.
3. enrollments — one row per student's registration in a course.
4. grades — the grading scale used to convert letter grades into grade points.

The enrollments table connects students and courses. This is necessary because one student can enroll in multiple courses, and one course can have multiple students.

The grades table stores the institution's grading scale separately. This avoids repeating the grade-point mapping in every enrollment record.

## Relationship overview

- One student can have many enrollments.
- One course can have many enrollments.
- Each enrollment belongs to exactly one student and one course.
- Each enrollment may have a final letter grade. An ungraded enrollment has no final grade yet.

This is a many-to-many relationship between students and courses, implemented through the enrollments table.

We will use a simplified grading policy:
- A+ = 4.0
- A = 4.0
- B = 3.0
- C = 2.0
- D = 1.0
- F = 0.0

For this project, GPA is the average of grade points for completed enrollments with a recorded grade. All courses have equal weight. Real institutions may use credit-weighted GPA, different grading scales, and specific policies for withdrawals and retakes.

Designing the Database Schema

Start by creating the tables in dependency order. A referenced table must exist before its foreign key can be created.

We use:
- BIGINT GENERATED ALWAYS AS IDENTITY for automatically generated identifiers.
- PRIMARY KEY to uniquely identify each row.
- NOT NULL for required values.
- UNIQUE to prevent duplicate student emails and course codes.
- CHECK constraints to restrict values to valid ranges.
- FOREIGN KEY constraints to preserve relationships.

The enrollment table uses a composite unique constraint on (student_id, course_id). This prevents the same student from registering for the same course more than once in this simplified project.

If an institution allows a student to retake a course, the model must be extended to represent separate attempts rather than removing this constraint without further design.

Create the Student Management 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
30
31
32
33
34
35
36
37
38
39
40
CREATE TABLE students (
    student_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    date_of_birth DATE,
    admission_date DATE NOT NULL DEFAULT CURRENT_DATE
);
 
CREATE TABLE courses (
    course_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    course_name VARCHAR(150) NOT NULL,
    credits INTEGER NOT NULL CHECK (credits BETWEEN 1 AND 6)
);
 
CREATE TABLE grades (
    letter_grade VARCHAR(2) PRIMARY KEY,
    grade_points NUMERIC(3, 2) NOT NULL
        CHECK (grade_points BETWEEN 0 AND 4),
    CHECK (letter_grade IN ('A+', 'A', 'B', 'C', 'D', 'F'))
);
 
CREATE TABLE enrollments (
    enrollment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id BIGINT NOT NULL
        REFERENCES students(student_id),
    course_id BIGINT NOT NULL
        REFERENCES courses(course_id),
    enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'enrolled'
        CHECK (status IN ('enrolled', 'completed', 'withdrawn')),
    final_grade VARCHAR(2)
        REFERENCES grades(letter_grade),
    UNIQUE (student_id, course_id),
    CHECK (
        (status = 'completed' AND final_grade IS NOT NULL)
        OR
        (status IN ('enrolled', 'withdrawn') AND final_grade IS NULL)
    )
);

Output

CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE

PostgreSQL creates four related tables. The foreign keys prevent enrollments from referring to nonexistent students, courses, or grades. The status and grade constraints enforce the simplified policy that only completed enrollments have final grades.

Understanding the Relationships

The schema separates facts according to what they describe.

## Why not store everything in one table?

Imagine a table containing student name, email, course name, course code, and grade. If a student enrolls in five courses, the student's name and email would be repeated five times.

That duplication creates problems:
- Updating a student's email requires changing multiple rows.
- Deleting one course enrollment could accidentally remove the only stored copy of student information.
- Inconsistent values could appear in different rows.

Our design stores student information once in students, course information once in courses, and registration information in enrollments.

## Why use a separate grades table?

The grades table is a small reference table. It defines which letter grades are accepted and how each grade maps to points.

The foreign key from enrollments.final_grade ensures that a grade must exist in the approved grading scale.

## Why use credits?

The courses table contains credits because courses may carry different academic weights. Our initial GPA report will use a simple average to keep the calculation clear. Later, we will also calculate a credit-weighted GPA.

## Important business rule

This schema treats each student-course pair as a single enrollment. If a student must retake a course, a production system would normally introduce a separate attempt number or term and update the uniqueness rule accordingly.

Inspect the Foreign-Key Relationships

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
   AND tc.constraint_schema = kcu.constraint_schema
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
   AND ccu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.constraint_schema = 'public'
  AND tc.table_name IN ('enrollments')
ORDER BY kcu.column_name;

Output

The query reads PostgreSQL's information schema to inspect the foreign keys on enrollments. Exact constraint metadata depends on the schema and database in which the query runs.

Adding the Grading Scale and Sample Data

Next, insert the grading scale, courses, students, and enrollments.

Insert parent records before child records:
1. Insert grades, courses, and students.
2. Insert enrollments that refer to those records.

The sample data includes:
- Students who have completed courses.
- A student who has no enrollments.
- A student with an enrollment that has not yet been graded.
- A course with no enrollments.

These cases help test whether analytical queries handle missing relationships correctly.

The SQL below uses explicit column lists. This makes inserts easier to understand and less fragile if the table's column order changes.

Seed the Database

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
INSERT INTO grades (letter_grade, grade_points) VALUES
    ('A+', 4.00),
    ('A',  4.00),
    ('B',  3.00),
    ('C',  2.00),
    ('D',  1.00),
    ('F',  0.00);
 
INSERT INTO students
    (full_name, email, date_of_birth, admission_date)
VALUES
    ('Aarav Sharma', 'aarav@example.com', '2005-04-12', '2024-07-01'),
    ('Diya Verma',   'diya@example.com',  '2004-11-03', '2024-07-01'),
    ('Kabir Singh',  'kabir@example.com', '2005-08-19', '2024-07-01'),
    ('Meera Gupta',  'meera@example.com', '2004-02-25', '2024-07-01');
 
INSERT INTO courses (course_code, course_name, credits) VALUES
    ('CS101', 'Introduction to Programming', 4),
    ('DB201', 'Database Systems', 3),
    ('OS301', 'Operating Systems', 4),
    ('NW101', 'Computer Networks', 3);
 
INSERT INTO enrollments
    (student_id, course_id, enrollment_date, status, final_grade)
VALUES
    (1, 1, '2024-08-01', 'completed', 'A'),
    (1, 2, '2024-08-02', 'completed', 'B'),
    (2, 1, '2024-08-01', 'completed', 'A+'),
    (2, 3, '2024-08-03', 'completed', 'B'),
    (3, 2, '2024-08-04', 'completed', 'C'),
    (3, 3, '2024-08-04', 'enrolled',  NULL);

Output

INSERT 0 6 INSERT 0 4 INSERT 0 4 INSERT 0 6

The inserted rows use IDs 1 through 4 for students and courses because the tables are newly created and the inserts run in the displayed order. In an existing database, identity values may differ, so application code should not rely on these hard-coded IDs.

Verifying the Sample Data

Before writing reports, verify that the expected records exist and that the relationships are correct.

The following query joins enrollments to students and courses. It also joins grades to show the grade-point value when a final grade exists.

Because enrollment status can be enrolled, completed, or withdrawn, the report includes status as well as grade. A NULL grade for an active enrollment is expected and does not necessarily indicate an error.

View Student Enrollments

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    s.full_name,
    c.course_code,
    c.course_name,
    e.status,
    e.final_grade,
    g.grade_points
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
LEFT JOIN grades AS g
    ON g.letter_grade = e.final_grade
ORDER BY s.full_name, c.course_code;

Output

The INNER JOIN operations find the student and course associated with each enrollment. The LEFT JOIN to grades preserves an enrollment even when final_grade is NULL. PostgreSQL may display NULL values using its configured output format.

Calculating Student GPA

GPA stands for Grade Point Average. Before calculating it, define exactly which records count.

For this project:
- Only completed enrollments count.
- Only enrollments with a recorded grade count.
- Each course has equal weight in the simple GPA.
- A student with no completed, graded courses has a NULL GPA because no average can be calculated.

The AVG aggregate ignores NULL values. However, we explicitly filter to completed enrollments so that an in-progress enrollment cannot affect the calculation.

We use LEFT JOIN from students so that students without any completed courses still appear in the report.

Calculate Simple GPA for Every Student

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    s.student_id,
    s.full_name,
    ROUND(AVG(g.grade_points), 2) AS gpa,
    COUNT(e.enrollment_id) AS graded_courses
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
   AND e.status = 'completed'
LEFT JOIN grades AS g
    ON g.letter_grade = e.final_grade
GROUP BY s.student_id, s.full_name
ORDER BY s.student_id;

Output

student_id | full_name | gpa | graded_courses -----------+--------------+------+--------------- 1 | Aarav Sharma | 3.50 | 2 2 | Diya Verma | 3.50 | 2 3 | Kabir Singh | 2.00 | 1 4 | Meera Gupta | NULL | 0

Aarav's GPA is (4.00 + 3.00) / 2 = 3.50. Diya's is (4.00 + 3.00) / 2 = 3.50. Kabir's completed graded enrollment contributes 2.00; his in-progress enrollment is excluded. Meera remains in the result because of LEFT JOIN, but has no GPA yet. COUNT(e.enrollment_id) counts only matched completed enrollments because the status filter is in the JOIN condition.

Calculating Credit-Weighted GPA

A simple average gives every course the same influence, regardless of credit value.

Many institutions use a credit-weighted GPA:

Credit-weighted GPA = SUM(grade points × course credits) / SUM(course credits)

For example, a 4-credit course contributes more to GPA than a 1-credit course.

We can calculate this by joining enrollments to courses and grades, then multiplying each grade-point value by the course's credit value.

We should still include only completed enrollments with a grade. If there are no qualifying courses, the result should remain NULL rather than inventing a GPA of zero.

Credit-Weighted GPA

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
    s.student_id,
    s.full_name,
    ROUND(
        SUM(g.grade_points * c.credits)::numeric
        / NULLIF(SUM(c.credits), 0),
        2
    ) AS weighted_gpa,
    SUM(c.credits) AS completed_credits
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
   AND e.status = 'completed'
LEFT JOIN courses AS c
    ON c.course_id = e.course_id
LEFT JOIN grades AS g
    ON g.letter_grade = e.final_grade
GROUP BY s.student_id, s.full_name
ORDER BY s.student_id;

Output

Aarav's weighted GPA is (4.00 × 4 + 3.00 × 3) / 7 = 3.57. Diya's is (4.00 × 4 + 3.00 × 4) / 8 = 3.50. NULLIF protects the denominator from division by zero. This is a simplified institutional policy; official GPA rules must follow the institution's regulations.

Analyzing Course Enrollment

A student management system should also report demand for each course.

A common mistake is to use INNER JOIN when the report must include courses with zero enrollments. INNER JOIN would remove those courses entirely.

Instead, start with courses and use LEFT JOIN to enrollments. COUNT(e.enrollment_id) counts actual matching enrollment rows. Do not use COUNT(*) for this purpose: with LEFT JOIN, a course with no matches still produces one output row, and COUNT(*) would count that row as one.

Count Students Registered in Each Course

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    c.course_code,
    c.course_name,
    COUNT(e.enrollment_id) AS total_enrollments,
    COUNT(*) FILTER (
        WHERE e.status = 'completed'
    ) AS completed_enrollments,
    COUNT(*) FILTER (
        WHERE e.status = 'enrolled'
    ) AS active_enrollments
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
ORDER BY c.course_code;

Output

course_code | course_name | total_enrollments | completed_enrollments | active_enrollments ------------+------------------------------+-------------------+-----------------------+------------------ CS101 | Introduction to Programming | 2 | 2 | 0 DB201 | Database Systems | 2 | 2 | 0 NW101 | Computer Networks | 0 | 0 | 0 OS301 | Operating Systems | 2 | 1 | 1

The LEFT JOIN keeps Computer Networks in the report even though it has no enrollments. COUNT(e.enrollment_id) returns zero for it. PostgreSQL's FILTER clause counts rows matching a condition for each aggregate. For courses without enrollments, the FILTER counts are zero.

Finding Students Without Enrollments

Administrators may need to find students who have never registered for a course.

A LEFT JOIN followed by a NULL check can identify these students. Another common approach is NOT EXISTS.

NOT EXISTS is especially clear when the question is whether any related row exists. It avoids accidentally multiplying student rows if the relationship later changes or the query includes additional joins.

Find Students Who Have Never Enrolled

sql

1
2
3
4
5
6
7
8
9
10
11
SELECT
    s.student_id,
    s.full_name,
    s.email
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;

Output

student_id | full_name | email -----------+------------+------------------- 4 | Meera Gupta | meera@example.com

The correlated subquery checks for an enrollment belonging to each student. Meera has no matching enrollment, so NOT EXISTS is true for her. The query returns only students with no enrollment records at all, not students who have only withdrawn enrollments.

Finding the Highest-GPA Students

A common report is a ranking of students by GPA.

First, calculate each student's GPA in a common table expression (CTE). Then filter the result to students with a GPA and rank them using DENSE_RANK.

DENSE_RANK assigns the same rank to equal GPAs without leaving gaps in the next rank. Students with no GPA are excluded because NULL does not represent a calculated academic average.

Rank Students by GPA

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH student_gpas AS (
    SELECT
        s.student_id,
        s.full_name,
        AVG(g.grade_points) AS gpa
    FROM students AS s
    JOIN enrollments AS e
        ON e.student_id = s.student_id
       AND e.status = 'completed'
    JOIN grades AS g
        ON g.letter_grade = e.final_grade
    GROUP BY s.student_id, s.full_name
)
SELECT
    student_id,
    full_name,
    ROUND(gpa, 2) AS gpa,
    DENSE_RANK() OVER (ORDER BY gpa DESC) AS gpa_rank
FROM student_gpas
ORDER BY gpa_rank, student_id;

Output

student_id | full_name | gpa | gpa_rank -----------+--------------+------+--------- 1 | Aarav Sharma | 3.50 | 1 2 | Diya Verma | 3.50 | 1 3 | Kabir Singh | 2.00 | 2

Aarav and Diya share rank 1 because both have a GPA of 3.50. Kabir receives rank 2. Meera is absent because the CTE uses INNER JOIN and she has no completed graded enrollments.

Transactions and Data Integrity

A transaction groups related changes into a single unit of work.

For example, registering a student for a course may involve inserting an enrollment and recording related payment or audit information in a larger application. If one required step fails, the application may need to roll back the entire operation.

PostgreSQL supports BEGIN, COMMIT, and ROLLBACK.

- BEGIN starts a transaction.
- COMMIT makes the transaction's changes permanent.
- ROLLBACK discards the transaction's uncommitted changes.

Foreign keys and constraints are still important inside transactions. A transaction does not make invalid data valid; it provides a way to apply a group of changes atomically.

Register a Student Safely

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
BEGIN;
 
INSERT INTO enrollments
    (student_id, course_id, status, final_grade)
VALUES
    (4, 1, 'enrolled', NULL);
 
-- Check the inserted record before committing.
SELECT
    enrollment_id,
    student_id,
    course_id,
    status
FROM enrollments
WHERE student_id = 4
  AND course_id = 1;
 
COMMIT;

Output

The example assumes the previous sample data has been inserted and no additional enrollment rows exist. The displayed enrollment_id is illustrative because identity values depend on the database's sequence state. In application code, validate inputs and commit only after the required operations succeed.

Indexes and Query Performance

As the database grows, reports and lookups may need indexes.

PostgreSQL automatically creates indexes for primary keys and unique constraints. Foreign key declarations do not automatically create an index on the referencing columns.

In our schema:
- students.email has a unique index.
- courses.course_code has a unique index.
- enrollments has a unique index on (student_id, course_id).
- The composite index on (student_id, course_id) can help lookups by student_id because student_id is the leading column.
- A separate index on enrollments(course_id) can help course-based enrollment reports.

Do not create indexes blindly. Indexes consume storage and add work to INSERT, UPDATE, and DELETE operations. Measure query behavior using realistic data.

Create and Inspect Useful Indexes

sql

1
2
3
4
5
6
7
8
9
10
CREATE INDEX idx_enrollments_course_id
    ON enrollments(course_id);
 
EXPLAIN
SELECT
    student_id,
    course_id,
    status
FROM enrollments
WHERE course_id = 3;

Output

CREATE INDEX QUERY PLAN ------------------------------------------------ [PostgreSQL displays the selected execution plan]

EXPLAIN displays the plan PostgreSQL chooses. The exact plan depends on table size, statistics, configuration, and data distribution. On a very small table, PostgreSQL may prefer a sequential scan even when an index exists. EXPLAIN ANALYZE actually runs the query and reports measured execution details.

Exercises: Extend the Project

Complete these exercises using the database you created.

## Exercise 1: Student course list
Display every student's name and the courses they are enrolled in. Include students who have no enrollments.

Hint: Start from students and use LEFT JOIN.

## Exercise 2: Course demand
Return the course code, course name, and number of students currently enrolled. Include courses with zero active enrollments.

Hint: Count enrollments where status = 'enrolled'.

## Exercise 3: Students above a GPA threshold
Find students whose simple GPA is at least 3.5. Include only students who have a calculated GPA.

Hint: Use GROUP BY and HAVING, or calculate GPA in a CTE and filter it.

## Exercise 4: Completed credits
Calculate the number of completed credits for each student. Students without completed courses should remain in the report.

Hint: Use LEFT JOIN and conditional aggregation. Be careful to return zero rather than NULL when the intended meaning is no completed credits.

## Exercise 5: Data integrity
Try inserting an enrollment for a nonexistent student or course. Observe the foreign-key error.

Hint: Use a transaction and ROLLBACK if you are experimenting with changes you do not want to keep.

## Exercise 6: Improve the model
Design a way to support course retakes across academic terms without overwriting a student's earlier grade.

Hint: Consider academic_terms and a separate attempt identifier or a uniqueness rule involving term_id.

Exercise Solutions

Compare your queries with these reference solutions.

## Solution 1: All students and their courses

The LEFT JOIN keeps students without enrollments. Those students have NULL course columns.

## Solution 2: Active enrollments by course

The active-status condition belongs in the JOIN condition. If placed in WHERE, courses with no active enrollments would be removed.

## Solution 3: Students with GPA of at least 3.5

HAVING filters groups after aggregation. AVG ignores NULL values, but the completed-enrollment filter is still required by our policy.

## Solution 4: Completed credits

COALESCE changes a NULL total to zero for students with no completed courses.

Exercise Reference Queries

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Solution 1: Every student and their courses
SELECT
    s.full_name,
    c.course_code,
    c.course_name
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
LEFT JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.full_name, c.course_code;
 
-- Solution 2: Active enrollments by course
SELECT
    c.course_code,
    c.course_name,
    COUNT(e.enrollment_id) AS active_enrollments
FROM courses AS c
LEFT JOIN enrollments AS e
    ON e.course_id = c.course_id
   AND e.status = 'enrolled'
GROUP BY c.course_id, c.course_code, c.course_name
ORDER BY c.course_code;
 
-- Solution 3: Students with simple GPA >= 3.5
SELECT
    s.student_id,
    s.full_name,
    ROUND(AVG(g.grade_points), 2) AS gpa
FROM students AS s
JOIN enrollments AS e
    ON e.student_id = s.student_id
   AND e.status = 'completed'
JOIN grades AS g
    ON g.letter_grade = e.final_grade
GROUP BY s.student_id, s.full_name
HAVING AVG(g.grade_points) >= 3.5
ORDER BY gpa DESC, s.full_name;
 
-- Solution 4: Completed credits for every student
SELECT
    s.student_id,
    s.full_name,
    COALESCE(
        SUM(c.credits) FILTER (
            WHERE e.status = 'completed'
        ),
        0
    ) AS completed_credits
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
LEFT JOIN courses AS c
    ON c.course_id = e.course_id
GROUP BY s.student_id, s.full_name
ORDER BY s.student_id;

Output

The output shown is for Exercises 2, 3, and 4 after the sample data is inserted and before the optional transaction example is committed. Exercise 1's output depends on the current enrollment records. If the transaction example is committed, Meera's course list and active-enrollment counts will change.

Knowledge Check

Test your understanding before moving on.

1. Why is the enrollments table needed?
A. To duplicate all student data
B. To connect students and courses and store registration-specific information
C. To replace primary keys
D. To store only course names

2. What does a foreign key help enforce?
A. Query formatting
B. Referential integrity
C. Automatic GPA calculation
D. Index removal

3. Why use LEFT JOIN in the GPA report?
A. To remove students without grades
B. To include students who have no completed graded enrollments
C. To make every GPA equal to zero
D. To prevent sorting

4. What is the difference between COUNT(*) and COUNT(e.enrollment_id) after a LEFT JOIN?
A. They always return the same result
B. COUNT(*) counts the output row for an unmatched parent, while COUNT(e.enrollment_id) counts only non-NULL enrollment IDs
C. COUNT(e.enrollment_id) counts every table in the database
D. COUNT(*) ignores NULL values

5. Which expression calculates a credit-weighted GPA?
A. AVG(credits)
B. SUM(grade_points * credits) / SUM(credits)
C. COUNT(grade_points)
D. MAX(credits)

6. Why should you inspect a query plan?
A. To guarantee that an index is always used
B. To understand how PostgreSQL intends to execute a query
C. To change foreign keys automatically
D. To bypass constraints

7. What is the main purpose of a transaction?
A. To group changes into a unit that can be committed or rolled back
B. To make every query faster
C. To remove all constraints
D. To replace database backups

8. Why should a production system explicitly define its GPA policy?
A. Every institution calculates GPA identically
B. Grade weighting, retakes, withdrawals, and missing grades can affect the result
C. GPA does not require grades
D. SQL cannot calculate averages

Answer Key

text

1
2
3
4
5
6
7
8
1. B — Connects students and courses.
2. B — Enforces referential integrity.
3. B — Preserves students with no qualifying grades.
4. B — Counts a real enrollment ID rather than the unmatched output row.
5. B — Uses grade points weighted by course credits.
6. B — Shows the chosen execution strategy.
7. A — Provides commit and rollback semantics.
8. B — GPA policies differ across institutions.

Review the corresponding sections if any answer is unclear. Understanding the reason behind each answer is more useful than memorizing the letter.

SQL Interview Questions and Answers

## 1. How would you model a many-to-many relationship?

Use a junction table such as enrollments. It stores foreign keys to students and courses and can also store attributes specific to the relationship, such as registration date and status.

## 2. What is normalization, and why is it useful here?

Normalization organizes data into related tables to reduce unnecessary duplication and avoid update, insertion, and deletion anomalies. Student details belong in students, course details belong in courses, and registration details belong in enrollments.

## 3. Why use foreign keys?

Foreign keys prevent child rows from referencing nonexistent parent rows. For example, an enrollment cannot reference a student ID that does not exist in students.

## 4. How would you calculate GPA in SQL?

Join enrollments to grades, filter to the enrollment statuses allowed by the grading policy, and use AVG(grade_points). For a credit-weighted GPA, divide SUM(grade_points * credits) by SUM(credits).

## 5. How do you include students with no courses in a report?

Start with students and LEFT JOIN enrollments. Put conditions that determine which enrollments qualify in the JOIN condition if students without qualifying enrollments must remain visible.

## 6. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after aggregate calculations.

## 7. Why might a query not use an index?

The table may be small, many rows may match the filter, or the optimizer may estimate a sequential scan to be cheaper. Statistics, selectivity, and query shape affect the plan.

## 8. Why is a NULL GPA different from a GPA of zero?

NULL means no GPA can be calculated from the qualifying records. A GPA of zero is a calculated result that could arise from graded courses with zero grade points.

## 9. How would you support course retakes?

Represent separate attempts, typically using an attempt identifier and term. Then define whether GPA includes all attempts, only the latest attempt, or another institution-specific selection rule.

## 10. How would you prevent duplicate registrations?

Use a database uniqueness constraint for the business key, such as (student_id, course_id) in this simplified model. If registrations are allowed across terms, include the term or attempt in the model and uniqueness rule.

## 11. What is the purpose of a transaction during registration?

It allows related database changes to succeed or fail together. If a required operation fails before commit, the application can roll back the uncommitted work.

## 12. How should an application query this database securely?

Use parameterized queries for user-supplied values, validate inputs, grant the application only the permissions it needs, and avoid exposing database credentials to clients.

Common Mistakes and How to Avoid Them

## Mistake 1: Putting all data into one table

This repeats student and course details and makes updates difficult. Use separate entities and a junction table.

## Mistake 2: Using INNER JOIN when unmatched rows must remain visible

An INNER JOIN removes students or courses without matching rows. Use LEFT JOIN when the report must preserve them.

## Mistake 3: Counting the wrong expression

After a LEFT JOIN, COUNT(*) can count an unmatched parent row. Count the nullable child primary key when counting matching child records.

## Mistake 4: Counting in-progress courses in GPA

An enrollment without a final grade should not affect the average under this project's policy. Filter to completed, graded enrollments.

## Mistake 5: Treating NULL GPA as zero

No graded courses does not mean a GPA of zero. Keep the result NULL unless the reporting requirement explicitly specifies a different presentation.

## Mistake 6: Forgetting course credits

A simple GPA treats every course equally. If the institution uses credit weighting, multiply each grade-point value by the course's credits.

## Mistake 7: Relying only on application validation

Application checks are useful, but database constraints protect integrity even if a request comes from another application or a concurrent request bypasses a prior check.

## Mistake 8: Assuming a foreign key automatically creates an index on the referencing column

PostgreSQL automatically indexes primary keys and unique constraints, but it does not automatically create an index on every foreign-key column. Add indexes based on query and maintenance needs.

## Mistake 9: Assuming the example IDs always start at one

Identity values depend on the table's sequence state. Use RETURNING or query the inserted records rather than assuming IDs in production code.

## Mistake 10: Ignoring retake and term policies

The simplified unique constraint permits only one enrollment per student-course pair. A real academic system should explicitly model terms, attempts, and the GPA rules that apply to them.

Correctly Count Matching Enrollments

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Incorrect when zero-enrollment courses must show zero:
SELECT
    c.course_code,
    COUNT(*) 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;
 
-- Correct:
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;

Output

For a course with no enrollments: Incorrect query: enrollment_count = 1 Correct query: enrollment_count = 0

A LEFT JOIN produces one result row for an unmatched course, with NULL values for enrollment columns. COUNT(*) counts that result row; COUNT(e.enrollment_id) ignores the NULL enrollment ID.

Project Completion Checklist

You have built the foundation of a relational student management database.

Before considering the project complete, verify that you can:
- Create the four tables in dependency order.
- Explain every primary key, foreign key, and important constraint.
- Insert valid sample data and identify invalid relationships.
- Retrieve enrollment details with joins.
- Calculate simple and credit-weighted GPAs.
- Include students and courses with no matching records.
- Use GROUP BY, HAVING, CTEs, and window functions for reports.
- Explain the effect of NULL values on aggregates.
- Use transactions for related changes.
- Inspect execution plans and add indexes based on measured needs.
- Describe how the model would change to support academic terms and course retakes.

This project is a foundation, not a complete university information system. A production design would usually add academic terms, departments, instructors, course sections, attendance, assessments, and an explicit institutional grading policy.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Storing students, courses, and enrollments in one large denormalized table.

Why it happens: A single table may seem simpler initially, but it duplicates student and course information and can cause update, insertion, and deletion anomalies.

Incorrect Approach

sql

-- A denormalized design repeats student and course details
CREATE TABLE student_course_data (
    student_name VARCHAR(100),
    student_email VARCHAR(255),
    course_name VARCHAR(150),
    final_grade VARCHAR(2)
);

Correct Solution

sql

-- Keep entities separate and connect them through enrollments
-- See the students, courses, grades, and enrollments
-- CREATE TABLE statements in the schema section.

Key takeaway: Use separate tables for separate entities. Store registration-specific information in the junction table and enforce relationships with foreign keys.

#2 Using COUNT(*) to count enrollments after a LEFT JOIN.

Why it happens: A LEFT JOIN returns a row for a course even when no enrollment matches, so COUNT(*) counts the unmatched output row.

Incorrect Approach

sql

SELECT
    c.course_code,
    COUNT(*) 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;

Correct Solution

sql

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;

Key takeaway: COUNT(e.enrollment_id) counts only non-NULL enrollment IDs, returning zero for courses with no matching enrollments.

#3 Including in-progress enrollments in GPA calculations.

Why it happens: A student may be registered for a course before a final grade is available. Treating missing grades as zero would incorrectly lower the GPA.

Incorrect Approach

sql

-- Does not explicitly enforce the completed-course policy
SELECT
    e.student_id,
    AVG(g.grade_points) AS gpa
FROM enrollments AS e
LEFT JOIN grades AS g
    ON g.letter_grade = e.final_grade
GROUP BY e.student_id;

Correct Solution

sql

SELECT
    e.student_id,
    AVG(g.grade_points) AS gpa
FROM enrollments AS e
JOIN grades AS g
    ON g.letter_grade = e.final_grade
WHERE e.status = 'completed'
GROUP BY e.student_id;

Key takeaway: The corrected query calculates GPA only from completed enrollments with a valid recorded grade. Use a LEFT JOIN from students when the report must also include students who have no GPA yet.

#4 Assuming a GPA formula is universal and ignoring retakes or credit weighting.

Why it happens: Institutions may use different grade-point mappings, course weights, repeat-course policies, and treatment of withdrawals.

Incorrect Approach

sql

-- Simple GPA may not match an institution's official policy
SELECT AVG(g.grade_points) AS gpa
FROM enrollments AS e
JOIN grades AS g
    ON g.letter_grade = e.final_grade
WHERE e.student_id = 1
  AND e.status = 'completed';

Correct Solution

sql

-- Credit-weighted GPA for the project's simplified policy
SELECT
    SUM(g.grade_points * c.credits)
        / NULLIF(SUM(c.credits), 0) AS weighted_gpa
FROM enrollments AS e
JOIN courses AS c
    ON c.course_id = e.course_id
JOIN grades AS g
    ON g.letter_grade = e.final_grade
WHERE e.student_id = 1
  AND e.status = 'completed';

Key takeaway: Choose the GPA policy explicitly before implementing reports. The weighted example is appropriate only when the institution defines GPA using course credits and the selected enrollments.

#5 Assuming a foreign-key declaration automatically creates an index on the referencing column.

Why it happens: Primary keys and unique constraints create indexes, but PostgreSQL does not automatically index every foreign-key column.

Incorrect Approach

sql

-- The foreign key ensures integrity, but does not
-- automatically create an index on enrollments.course_id.
CREATE TABLE enrollments_example (
    enrollment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_id BIGINT NOT NULL REFERENCES courses(course_id)
);

Correct Solution

sql

CREATE INDEX idx_enrollments_course_id
    ON enrollments(course_id);

Key takeaway: Create an index on the referencing column when query patterns or parent-row updates and deletes justify it. Verify the benefit using realistic workloads and execution plans.

Ready for hands-on challenges?

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

Practice Arena →

Key takeaways

A well-designed student database separates students, courses, and enrollments into related tables instead of duplicating their information.
Primary keys identify records, foreign keys enforce relationships, and constraints protect data integrity.
An enrollment represents a student's registration in a course and can store course-specific information such as the final grade.
GPA calculations require an explicit grading scale and a defined policy for missing grades and repeated courses.
LEFT JOIN preserves students or courses that have no related records, while INNER JOIN returns only matching records.
Indexes should support real query patterns and be evaluated with realistic data and EXPLAIN ANALYZE.

Was this lesson helpful?

Found an error? Report an issue