Move the course facts into their own table

Take the first step of normalizing a table: give a repeated fact its own home.

What to build

A courses table filled from the big table, with each course stored once.

Requirements

Create courses with code as primary key, a required title and a teacher.
Fill it with each different course from enroll_info.
Show every column of courses, in order of code.

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,
  teacher TEXT,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);

Output

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

Constraints

The facts about each course are the same in every row where it appears, and teacher may be NULL.

Example

When a test starts with:

INSERT INTO enroll_info VALUES (1, 'Rahim', 'CSE101', 'Programming', 'Dr. Alam', 'A'), (2, 'Nadia', 'CSE101', 'Programming', 'Dr. Alam', 'A+'), (2, 'Nadia', 'MAT101', 'Calculus', 'Ms. Sultana', 'A');

your SQL should print:

code    title        teacher
------  -----------  -----------
CSE101  Programming  Dr. Alam
MAT101  Calculus     Ms. Sultana
(2 rows)

Why it works

course → course_title, teacher is a dependency on part of the key, so those facts belong in a table keyed by the course. DISTINCT collapses the repeated copies into one row per course.

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,
  teacher TEXT,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES (1, 'Rahim', 'CSE101', 'Programming', 'Dr. Alam', 'A'), (2, 'Nadia', 'CSE101', 'Programming', 'Dr. Alam', 'A+'), (2, 'Nadia', 'MAT101', 'Calculus', 'Ms. Sultana', 'A');

Expected output

code    title        teacher
------  -----------  -----------
CSE101  Programming  Dr. Alam
MAT101  Calculus     Ms. Sultana
(2 rows)

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,
  teacher TEXT,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES (1, 'A', 'X1', 'Art', NULL, 'B');

Expected output

code  title  teacher
----  -----  -------
X1    Art    NULL
(1 row)

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

Create `courses` with `code` as its primary key.

Hint 2

`INSERT INTO courses (code, title, teacher) SELECT DISTINCT course, course_title, teacher FROM enroll_info;` copies each course once.

Solution

Show one correct solution (try it yourself first)

CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  teacher TEXT
);

INSERT INTO courses (code, title, teacher)
SELECT DISTINCT course, course_title, teacher FROM enroll_info;

SELECT * FROM courses ORDER BY code;

Lesson: Normalization →