Creating the students table and linking it to the courses table

Let’s take a minute and walk through what we’ve already learned. We’ll go step by step through everything that’s happening. There’s a lot coming up that’s built on this stuff, so it’s a good idea to really get what’s going on with every command you run.

Let’s go over our students and courses example one more time. From the very beginning. From creating the tables to breaking down what’s happening at each stage. Ready?

Now we’re going to create a students table to store info about students, and link it to the courses table using a foreign key. Along the way, you’ll see how foreign keys help us model relationships between entities.

Creating the students table

The students table will store info about students: their unique ID, name, and birth date. We’ll create it using the CREATE TABLE command.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Unique key for each record
    name TEXT NOT NULL,            -- Student name (required field)
    birth_date DATE                -- Student's birth date
);

What’s going on here?

1. student_id SERIAL PRIMARY KEY: This is a unique ID for each student. The SERIAL type creates an auto-incrementing field, and PRIMARY KEY makes sure the ID is unique for every row.
2. name TEXT NOT NULL: This is the student’s name. We put NOT NULL so you can’t add a record without a name.
3. birth_date DATE: This field stores the birth date. The DATE type helps us work with dates.

If the table was a person, student_id would be its passport, name is the obvious name, and birth_date is that thing we sometimes like to hide, but for the database, it’s important.

Creating the courses table

Now let’s make a table to store info about courses. It’ll have a course ID, a title, and a description.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Unique course ID
    title TEXT NOT NULL,           -- Course title (required field)
    description TEXT               -- Course description
);

What’s going on here?

1. course_id SERIAL PRIMARY KEY: Same idea, this is an auto-incrementing field that gives each course a unique ID.
2. title TEXT NOT NULL: This is the course title. We put NOT NULL because, let’s be real, every course needs a name.
3. description TEXT: This is a short description of the course. It’s optional (no NOT NULL restriction).

A course without a title is like a book with no cover. But the database won’t let that happen!

Linking the students table to the courses table

Now let’s say each student can only be enrolled in one course. To do this, we’ll add a foreign key to the courses table that references the student ID from the students table.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Unique course ID
    title TEXT NOT NULL,           -- Course title (required field)
    description TEXT,              -- Course description
    student_id INT REFERENCES students(student_id) -- Foreign key linking to students table
);

What does student_id INT REFERENCES students(student_id) do?

1. We’re creating a student_id field that will point to the unique student ID (student_id) in the students table.
2. The link between tables is set up with the REFERENCES keyword.
3. This means that every time you insert data into courses, the student_id value has to exist in the students table. If you try to enroll a student with a non-existent student_id, you’ll get an error.

Inserting data into the tables

Once the tables are created, let’s add some students and courses. Because, hey, it’s boring to sit in an empty classroom, right?

Adding students

INSERT INTO students (name, birth_date) VALUES
('Alex Lin', '2000-05-10'),
('Maria Chi', '1998-02-15'),
('Otto Song', '2001-09-25');

Adding courses

INSERT INTO courses (title, description, student_id) VALUES
('SQL Basics', 'Learning basic SQL syntax', 1),
('Relational Databases', 'Understanding relational models', 2),
('PostgreSQL for Beginners', 'Installing and setting up PostgreSQL', 3);

We specified the student_id for each course, linking it to the student’s ID in the students table.

Checking the link between tables

Now let’s make sure our tables are actually connected. We’ll write a query that shows info about courses along with student names.

SELECT
    courses.title AS course_title,
    courses.description AS course_description,
    students.name AS student_name
FROM 
    courses
JOIN 
    students ON courses.student_id = students.student_id;

Sample result: Query result:

We linked courses to students using a foreign key — and now we can get related data in a single query. That’s how the relational model works!

Summary

I hope everything we just went through made sense and was clear for you. Because soon we’ll move on, and I want you to feel confident in your knowledge.

2

Task

SQL SELF, level 20, lesson 0

Locked

Creating the `students` table

Creating the `students` table

Open

2

Task

SQL SELF, level 20, lesson 0

Locked

Creating the `courses` table and a foreign key

Creating the `courses` table and a foreign key

Open