club-example.sql

DROP TABLE IF EXISTS club_roster;

DROP TABLE IF EXISTS students;

DROP TABLE IF EXISTS clubs;

CREATE TABLE clubs (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE students (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE club_roster (
    id serial PRIMARY KEY,
    club_id integer REFERENCES clubs (id),
    student_id integer REFERENCES students (id)
);

INSERT INTO clubs (name)
    VALUES ('soccer'), ('baseball'), ('tennis');

INSERT INTO students (name)
    VALUES ('Gerda'), ('Lorine'), ('Karianne'), ('Destin'), ('Emilie'), ('Jerod'), ('Kristy'), ('Rodrick');

INSERT INTO club_roster (club_id, student_id)
    VALUES (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (3, 7), (3, 1);

-- inner join example
SELECT
    students.id AS student_id,
    students.name AS student_name
FROM
    club_roster
    INNER JOIN students ON club_roster.student_id = students.id
ORDER BY
    student_id;

-- outer join example
SELECT
    students.id AS student_id,
    students.name AS student_name,
    clubs.name AS club_name
FROM
    students
    LEFT OUTER JOIN club_roster ON students.id = club_roster.student_id
    LEFT OUTER JOIN clubs ON club_roster.club_id = clubs.id
ORDER BY
    student_id;