Find the update anomaly – Databases Practice Problem

Repeated data drifts out of step. Find where it already has.

What to build

A query that finds every course stored with more than one title.

Requirements

Group the rows by course.
Count how many different titles each course has.
Return only the courses with more than one title, sorted by course.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE enroll_info (
  student_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL,
  course_title TEXT NOT NULL,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);

Output

Columns course and titles, in order of course. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

The table may be empty.

Example

When a test starts with:

INSERT INTO enroll_info VALUES (1, 'Rahim', 'CSE101', 'Programming', 'A'), (2, 'Nadia', 'CSE101', 'Structured Programming', 'A+'), (2, 'Nadia', 'MAT101', 'Calculus', 'A');

your SQL should print:

course  titles
------  ------
CSE101  2
(1 row)

Why it works

In a well-designed table a course's title is stored once. Here it is repeated in every enrollment, so an update to only some rows leaves two titles, which this query exposes.

Examples

Example 1 · Input

CREATE TABLE enroll_info (
  student_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL,
  course_title TEXT NOT NULL,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES (1, 'Rahim', 'CSE101', 'Programming', 'A'), (2, 'Nadia', 'CSE101', 'Structured Programming', 'A+'), (2, 'Nadia', 'MAT101', 'Calculus', 'A');

Expected output

course  titles
------  ------
CSE101  2
(1 row)

Example 2 · Input

CREATE TABLE enroll_info (
  student_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL,
  course_title TEXT NOT NULL,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES (1, 'A', 'X', 'T', 'B');

Expected output

course  titles
------  ------
(0 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

Group by `course` and count the different titles with `COUNT(DISTINCT course_title)`.

Hint 2

A healthy course has exactly one title, so keep the groups with more than one.

Solution

Show one correct solution (try it yourself first)

SELECT course, COUNT(DISTINCT course_title) AS titles
FROM enroll_info
GROUP BY course
HAVING COUNT(DISTINCT course_title) > 1
ORDER BY course;

Lesson: Normalization →