SQL

はじめに

大学の時間割を例にSQLの勉強をする。

SQLの練習

CREATE TABLE students(
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    faculty_id INT
);
CREATE TABLE faculties(
    faculty_id INT PRIMARY KEY,
    faculty_name VARCHAR(50)
);
CREATE TABLE enrollments(
    student_id INT,
    subject_id INT,
    PRIMARY KEY (student_id, subject_id)
);
CREATE TABLE subjects(
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(50)
);

サンプルデータ

サンプルデータはChatGPTに作ってもらいます。

-- faculties（学部）
INSERT INTO faculties (faculty_id, faculty_name) VALUES
(1, 'Engineering'),
(2, 'Science'),
(3, 'Arts'),
(4, 'Business'),
(5, 'Law');

-- students（学生）
INSERT INTO students (student_id, name, faculty_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 3),
(4, 'Diana', 4),
(5, 'Ethan', 5);

-- subjects（科目）
INSERT INTO subjects (subject_id, subject_name) VALUES
(1, 'Mathematics'),
(2, 'Physics'),
(3, 'Literature'),
(4, 'Economics'),
(5, 'Constitutional Law');

-- enrollments（履修状況）
INSERT INTO enrollments (student_id, subject_id) VALUES
(1, 1), -- Alice → Mathematics
(1, 2), -- Alice → Physics
(2, 1), -- Bob → Mathematics
(2, 4), -- Bob → Economics
(3, 3), -- Charlie → Literature
(4, 4), -- Diana → Economics
(4, 5), -- Diana → Constitutional Law
(5, 5); -- Ethan → Constitutional Law

クエリを書いてみる

学生と所属学部一覧を表示

SELECT students.student_id, students.name, faculties.faculty_name
    FROM students
    JOIN faculties ON students.faculty_id = faculties.faculty_id;

まとめ

SQLでテーブルを作って、実際にデータを入れて、クエリを実行することができた