SQL练习【基于PostgreSQL】

数据库初始化

文件: init_db.sql

-- 0. 创建数据库 init_db
CREATE DATABASE init_db;

-- 1. 创建 Students 表 (学生信息)
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age INT,
    gender VARCHAR(10), -- 'Male' 或 'Female'
    enrollment_date DATE
);

-- 2. 创建 Courses 表 (课程信息)
CREATE TABLE Courses (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credit_hours INT
);

-- 3. 创建 Grades 表 (成绩记录 - 关联学生和课程)
CREATE TABLE Grades (
    grade_id INT PRIMARY KEY,
    student_id INT,
    course_id VARCHAR(10),
    score DECIMAL(5, 2), -- 成绩，例如 95.50
    semester VARCHAR(50),
    -- 设置外键关联
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

-- 4. 插入 Students 数据
INSERT INTO Students (student_id, student_name, age, gender, enrollment_date) VALUES
(1001, '张三', 20, 'Male', '2023-09-01'),
(1002, '李四', 19, 'Female', '2023-09-01'),
(1003, '王五', 21, 'Male', '2022-09-01'),
(1004, '赵六', 20, 'Female', '2023-09-01'),
(1005, '孙七', 22, 'Male', '2022-09-01');

-- 5. 插入 Courses 数据
INSERT INTO Courses (course_id, course_name, credit_hours) VALUES
('CS101', '数据结构', 4),
('MA202', '高等数学', 3),
('EN301', '大学英语', 2),
('PH401', '物理学', 3);

-- 6. 插入 Grades 数据
INSERT INTO Grades (grade_id, student_id, course_id, score, semester) VALUES
(1, 1001, 'CS101', 90.00, '2023 Fall'),
(2, 1001, 'MA202', 85.50, '2023 Fall'),
(3, 1002, 'CS101', 78.50, '2023 Fall'),
(4, 1003, 'EN301', 92.00, '2022 Fall'),
(5, 1004, 'MA202', 65.00, '2023 Fall'),
(6, 1005, 'CS101', 88.00, '2022 Fall'),
(7, 1001, 'EN301', 80.00, '2024 Spring'),
(8, 1003, 'CS101', 75.00, '2023 Fall'),
(9, 1005, 'MA202', 72.50, '2023 Fall');

练习题

基础查询 (SELECT, WHERE, ORDER BY)

1. 查询所有课程: 查询 Courses 表中所有的课程名称 (course_name) 和学分 (credit_hours)。
2. 条件查询: 查询年龄在 20岁及以上 的所有学生的 姓名 (student_name) 和 年龄 (age)。
3. 排序查询: 查询所有学生的 姓名 (student_name) 和 入学日期 (enrollment_date)，结果按入学日期降序排列。
4. 模糊查询: 查询名字中包含 "三" 字的所有学生的详细信息。

II. 聚合函数和分组 (GROUP BY, HAVING)

5. 计算平均分: 计算所有课程的平均成绩 (score)。
6. 按课程分组: 统计每门课程的 选课人数。显示课程ID (course_id) 和人数。
7. 按性别分组: 统计 男生 和 女生 的人数。
8. 筛选分组: 找出平均成绩 低于 80 分 的课程ID (course_id)。

III. 关联查询 (JOIN)

9. 内连接 (INNER JOIN): 查询 张三 同学所有课程的 课程名称 和 成绩。
10. 左连接 (LEFT JOIN): 查询 所有学生 的 姓名，以及他们选修 '数据结构' 课程的 成绩。即使学生没有选修这门课，也要显示出来。
11. 多表联合查询: 查询 2023 Fall 学期，李四 同学的 高等数学 课程的 成绩。

IV. 子查询 (Subquery)

12. IN 子查询: 找出所有选修了 '数据结构' 课程的学生的 姓名。
13. 比较运算符子查询: 找出成绩高于所有学生的平均成绩的 学生姓名、课程名称 和 分数。
14. EXISTS 子查询: 查询所有 至少有一门课程 成绩 低于 70 分 的学生的 姓名。
15. SELECT 子查询 (标量子查询): 查询所有学生的 姓名 和他们的 成绩，同时在结果中添加一列，显示 所有课程的平均分（该平均分对每行都一样）。

V. 进阶操作 (INSERT, UPDATE, DELETE)

16. 插入数据 (INSERT): 插入一个新学生：student_id: 1006, student_name: 周杰伦, age: 25, gender: Male, enrollment_date: '2024-03-01'。
17. 更新数据 (UPDATE): 将 王五 的 大学英语 成绩修改为 95.00。
18. 删除数据 (DELETE): 删除 赵六 所有在 2023 Fall 学期的成绩记录。（注意：实际操作中删除数据需谨慎）

答案