-- create a table
CREATE TABLE students (
  s_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  birthday DATE
);

CREATE TABLE enrolments (
    s_id INTEGER,
    course_code TEXT NOT NULL,
    year YEAR,
    FOREIGN KEY (s_id)
    REFERENCES students(s_id)
);

-- insert some values
INSERT INTO students VALUES (1, 'Bryan', 'Davies', '2002-01-23');
INSERT INTO students VALUES (2, 'Kate', 'Anthony', '2002-04-17');
INSERT INTO students VALUES (3, 'Leah', 'Taylor', '2003-02-19');
INSERT INTO students VALUES (4, 'Charles', 'Brown', '2001-11-14');
INSERT INTO students VALUES (5, 'Victoria', 'Jones', '2001-12-18');

INSERT INTO enrolments VALUES (1, 'COMP201', '2021');
INSERT INTO enrolments VALUES (1, 'COMP258', '2021');
INSERT INTO enrolments VALUES (1, 'COMP207', '2021');
INSERT INTO enrolments VALUES (2, 'COMP201', '2021');
INSERT INTO enrolments VALUES (2, 'COMP207', '2021');
INSERT INTO enrolments VALUES (3, 'COMP299', '2021');
INSERT INTO enrolments VALUES (4, 'COMP201', '2021');
INSERT INTO enrolments VALUES (4, 'COMP207', '2021');
INSERT INTO enrolments VALUES (5, 'COMP207', '2021');
-- fetch some values
SELECT * FROM students WHERE s_id = '1';