database_lecture.sql

DROP TABLE IF EXISTS result;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS subject;

CREATE TABLE student(
    student_id INT NOT NULL,
    name       VARCHAR(256) NOT NULL,
    prefecture VARCHAR(256) NOT NULL,
    age        INT NOT NULL,
    PRIMARY KEY(student_id)
);

CREATE TABLE subject(
    subject_id INT NOT NULL,
    name       VARCHAR(256) NOT NULL,
    teacher    VARCHAR(256) NOT NULL,
    utils      INT NOT NULL,
    PRIMARY KEY(subject_id)
);

CREATE TABLE result(
    student_id   INT NOT NULL,
    subject_id   INT NOT NULL,
    point        INT NOT NULL,
    PRIMARY KEY(student_id, subject_id),
    FOREIGN KEY(student_id) REFERENCES student(student_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION,
    FOREIGN KEY(subject_id) REFERENCES subject(subject_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

INSERT INTO student VALUES 
    (1, 'yamada', 'kyoto', 19),
    (2, 'suzuki', 'osaka', 20),
    (3, 'kozima', 'nara', 22),
    (4, 'takeda', 'kyoto', 18),
    (5, 'takagi', 'kobe', 19);

INSERT INTO subject VALUES 
    (1, 'database', 'tanaka', 4),
    (2, 'logic', 'sato', 2),
    (3, 'hardware', 'kobayasi', 6),
    (4, 'database', 'ono', 4),
    (5, 'os', 'saito', 5),
    (6, 'ai', 'tanaka', 3);

INSERT INTO result VALUES 
    (1, 1, 75),
    (1, 2, 60),
    (2, 2, 50),
    (3, 3, 90),
    (3, 4, 70),
    (3, 6, 65),
    (4, 1, 50),
    (4, 2, 80),
    (4, 4, 55),
    (4, 5, 75),
    (4, 6, 80);

Usage

sqlite3 <db_name> < database_lecture.sql

or

cat database_lecture.sql | sqlite3 <db_name>