SQL

Many-to-many relationship

Convert to two one-to-many relationships adding a junction table:

STUDENT           ENROLLMENT        COURSE
student_id  ―――→  student_id        title
name              course_id  ←――――  course_id
                  grade

Implementation:

CREATE TABLE student (
    student_id INTEGER PRIMARY KEY NOT NULL,
	name TEXT NOT NULL
);

CREATE TABLE course (
    course_id INTEGER PRIMARY KEY NOT NULL,
	title TEXT NOT NULL
);

CREATE TABLE enrollment (
    student_id INTEGER,
	course_id INTEGER,
	FOREIGN KEY (student_id) REFERENCES student(student_id),
	FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Insert

First insert a row in the student and a course tables, then insert a row in the enrollment table:

INSERT INTO student(name) VALUES ('John');
INSERT INTO course(title) VALUES ('Biology');
INSERT INTO enrollment(student_id, course_id) VALUES (
    (SELECT student_id FROM student WHERE name='John'),
    (SELECT course_id FROM course WHERE title='Biology')
);

Obtain all enrolled courses and the students:

SELECT title, name FROM student JOIN enrollment USING (student_id)
    JOIN course USING (course_id);

Update

Select a single row:

UPDATE student SET name='Joseph' WHERE name='John';

Select multiple rows:

UPDATE student SET name=NULL WHERE name='';