Oracle数据库-6张表关联查询练习

-- 创建班级表，用于存储班级信息
CREATE TABLE Classes
(
    class_id   NUMBER PRIMARY KEY, -- 班级ID，表的主键
    class_name VARCHAR2(50)        -- 班级名称
);

-- 创建学生表，用于存储学生信息
CREATE TABLE Students
(
    student_id     NUMBER PRIMARY KEY, -- 学生ID，表的主键
    student_name   VARCHAR2(50),       -- 学生姓名
    student_age    NUMBER,             -- 学生年龄
    student_gender VARCHAR2(10),       -- 学生性别
    course_id      NUMBER,             -- 外键，关联课程表中的课程ID
    class_id       NUMBER,             -- 班级ID，外键，关联Classes表
    CONSTRAINT fk_course
        FOREIGN KEY (course_id)
            REFERENCES Courses (course_id),
    CONSTRAINT fk_class
        FOREIGN KEY (class_id)
            REFERENCES Classes (class_id)
);

-- 创建课程表，用于存储课程信息
CREATE TABLE Courses
(
    course_id          NUMBER PRIMARY KEY, -- 课程ID，表的主键
    course_name        VARCHAR2(50),       -- 课程名称
    course_description VARCHAR2(100),      -- 课程描述
    teacher_id         NUMBER,             -- 外键，关联教师表中的教师ID
    CONSTRAINT fk_teacher
        FOREIGN KEY (teacher_id)
            REFERENCES Teachers (teacher_id)
);

-- 创建教师表，用于存储教师信息
CREATE TABLE Teachers
(
    teacher_id        NUMBER PRIMARY KEY, -- 教师ID，表的主键
    teacher_name      VARCHAR2(50),       -- 教师姓名
    teacher_specialty VARCHAR2(50),       -- 教师专业领域
    class_id          NUMBER,             -- 外键，关联Classes表的班级ID
    CONSTRAINT fk_class_teachers
        FOREIGN KEY (class_id)
            REFERENCES Classes (class_id)
);
-- 创建考试表，用于存储考试信息
CREATE TABLE Exams
(
    exam_id   NUMBER PRIMARY KEY, -- 考试ID，表的主键
    exam_name VARCHAR2(50),       -- 考试名称
    exam_date DATE,               -- 考试日期
    course_id NUMBER,             -- 外键，关联课程表中的课程ID
    CONSTRAINT fk_course_exams
        FOREIGN KEY (course_id)
            REFERENCES Courses (course_id)
);
-- 创建考试成绩表，用于存储考试成绩信息
CREATE TABLE ExamScores
(
    score_id   NUMBER PRIMARY KEY, -- 成绩ID，表的主键
    student_id NUMBER,             -- 外键，关联学生表中的学生ID
    exam_id    NUMBER,             -- 外键，关联考试表中的考试ID
    score      NUMBER,             -- 考试成绩
    CONSTRAINT fk_student_scores
        FOREIGN KEY (student_id)
            REFERENCES Students (student_id),
    CONSTRAINT fk_exam_scores
        FOREIGN KEY (exam_id)
            REFERENCES Exams (exam_id)
);
-- 向Classes表中添加数据
INSERT INTO Classes (class_id, class_name)
VALUES (1, '一班');
INSERT INTO Classes (class_id, class_name)
VALUES (2, '二班');
INSERT INTO Classes (class_id, class_name)
VALUES (3, '三班');

-- 向Students表中添加数据
INSERT INTO Students (student_id, student_name, student_age, student_gender, course_id, class_id)
VALUES (1, '张三', 18, '男', 1, 1);
INSERT INTO Students (student_id, student_name, student_age, student_gender, course_id, class_id)
VALUES (2, '李四', 19, '女', 2, 2);
INSERT INTO Students (student_id, student_name, student_age, student_gender, course_id, class_id)
VALUES (3, '王五', 20, '男', 3, 3);
INSERT INTO Students (student_id, student_name, student_age, student_gender, course_id, class_id)
VALUES (4, '赵六', 21, '男', 4, 2);
INSERT INTO Students (student_id, student_name, student_age, student_gender, course_id, class_id)
VALUES (5, '钱七', 22, '女', 2, 3);
insert into students
values (6, '孙八', 23, '女', 3, 1);
-- 向Courses表中添加数据
INSERT INTO Courses (course_id, course_name, course_description, teacher_id)
VALUES (1, '语文', '学习语文', 1);
INSERT INTO Courses (course_id, course_name, course_description, teacher_id)
VALUES (2, '数学', '学习数学', 2);
INSERT INTO Courses (course_id, course_name, course_description, teacher_id)
VALUES (3, '英语', '学习英语', 3);
INSERT INTO Courses (course_id, course_name, course_description, teacher_id)
VALUES (4, '物理', '学习物理', 4);
-- 向Teachers表中添加数据
INSERT INTO Teachers (teacher_id, teacher_name, teacher_specialty, class_id)
VALUES (1, '张老师', '语文', 1);
INSERT INTO Teachers (teacher_id, teacher_name, teacher_specialty, class_id)
VALUES (2, '李老师', '数学', 2);
INSERT INTO Teachers (teacher_id, teacher_name, teacher_specialty, class_id)
VALUES (3, '王老师', '英语', 3);
INSERT INTO Teachers (teacher_id, teacher_name, teacher_specialty, class_id)
VALUES (4, '赵老师', '物理', 2);
-- 向Exams表中添加数据
INSERT INTO Exams (exam_id, exam_name, exam_date, course_id)
VALUES (1, '语文考试', TO_DATE('2023-02-01', 'YYYY-MM-DD'), 1);
INSERT INTO Exams (exam_id, exam_name, exam_date, course_id)
VALUES (2, '数学考试', TO_DATE('2023-02-02', 'YYYY-MM-DD'), 2);
INSERT INTO Exams (exam_id, exam_name, exam_date, course_id)
VALUES (3, '英语考试', TO_DATE('2023-02-03', 'YYYY-MM-DD'), 3);
INSERT INTO Exams (exam_id, exam_name, exam_date, course_id)
VALUES (4, '物理考试', TO_DATE('2023-02-04', 'YYYY-MM-DD'), 4);
-- 向ExamScores表中添加数据
INSERT INTO ExamScores (score_id, student_id, exam_id, score)
VALUES (1, 1, 1, 85);
INSERT INTO ExamScores (score_id, student_id, exam_id, score)
VALUES (2, 2, 2, 76);
INSERT INTO ExamScores (score_id, student_id, exam_id, score)
VALUES (3, 3, 3, 92);
INSERT INTO ExamScores (score_id, student_id, exam_id, score)
VALUES (4, 4, 4, 80);
INSERT INTO ExamScores (score_id, student_id, exam_id, score)
VALUES (5, 5, 2, 90);
INSERT INTO ExamScores (score_id, student_id, exam_id, score)
VALUES (6, 6, 3, 60);
-- 查询所有表的数据
SELECT *
FROM Classes;
SELECT *
FROM Students;
SELECT *
FROM Courses;
SELECT *
FROM Teachers;
SELECT *
FROM Exams;
SELECT *
FROM ExamScores;
-- 查询所有学生的姓名、年龄、性别、课程名称、班级名称、教师姓名、考试名称、考试日期、考试成绩
SELECT s.student_name   as 姓名,
       s.student_age    as 年龄,
       s.student_gender as 性别,
       c.course_name    as 课程名称,
       cl.class_name    as 班级名称,
       t.teacher_name   as 教师姓名,
       e.exam_name      as 考试名称,
       e.exam_date      as 考试日期,
       es.score         as 考试成绩
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN Teachers t ON c.teacher_id = t.teacher_id
         JOIN Classes cl ON t.class_id = cl.class_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id;
-- 查询2023年2月1日及以后所有考试信息，包括考试名称、考试日期、课程名称、教师姓名、班级名称
SELECT e.exam_name    as 考试名称,
       e.exam_date    as 考试日期,
       c.course_name  as 课程名称,
       t.teacher_name as 教师姓名,
       cl.class_name  as 班级名称
FROM Exams e
         JOIN Courses c ON e.course_id = c.course_id
         JOIN Teachers t ON c.teacher_id = t.teacher_id
         JOIN Classes cl ON t.class_id = cl.class_id
WHERE e.exam_date >= TO_DATE('2023-02-01', 'YYYY-MM-DD');
-- 查询数学考试成绩排名
SELECT s.student_name                                              as 姓名,
       s.student_age                                               as 年龄,
       s.student_gender                                            as 性别,
       c.course_name                                               as 课程名称,
       cl.class_name                                               as 班级名称,
       t.teacher_name                                              as 教师姓名,
       e.exam_name                                                 as 考试名称,
       e.exam_date                                                 as 考试日期,
       es.score                                                    as 考试成绩,
       RANK() OVER (PARTITION BY e.exam_id ORDER BY es.score DESC) as 排名
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN Teachers t ON c.teacher_id = t.teacher_id
         JOIN Classes cl ON t.class_id = cl.class_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE c.course_name = '数学';
-- 查询语文考试成绩排名
SELECT s.student_name                                              as 姓名,
       s.student_age                                               as 年龄,
       s.student_gender                                            as 性别,
       c.course_name                                               as 课程名称,
       cl.class_name                                               as 班级名称,
       t.teacher_name                                              as 教师姓名,
       e.exam_name                                                 as 考试名称,
       e.exam_date                                                 as 考试日期,
       es.score                                                    as 考试成绩,
       RANK() OVER (PARTITION BY e.exam_id ORDER BY es.score DESC) as 排名
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN Teachers t ON c.teacher_id = t.teacher_id
         JOIN Classes cl ON t.class_id = cl.class_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE c.course_name = '语文';
-- 查询赵老师的班级考试成绩排名
SELECT s.student_name                                              as 姓名,
       s.student_age                                               as 年龄,
       s.student_gender                                            as 性别,
       c.course_name                                               as 课程名称,
       cl.class_name                                               as 班级名称,
       t.teacher_name                                              as 教师姓名,
       e.exam_name                                                 as 考试名称,
       e.exam_date                                                 as 考试日期,
       es.score                                                    as 考试成绩,
       RANK() OVER (PARTITION BY e.exam_id ORDER BY es.score DESC) as 排名
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN Teachers t ON c.teacher_id = t.teacher_id
         JOIN Classes cl ON t.class_id = cl.class_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE t.teacher_name = '赵老师';
-- 查询二班的老师个人信息
SELECT t.teacher_name as 教师姓名, t.teacher_specialty as 特长, cl.class_name as 班级名称, c.course_name as 课程名称
FROM Teachers t
         JOIN Classes cl ON t.class_id = cl.class_id
         JOIN Courses c ON t.teacher_id = c.teacher_id
WHERE cl.class_name = '二班';
-- 查询语文成绩超过80分的学生的姓名和年龄
SELECT s.student_name as 姓名, s.student_age as 年龄
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE c.course_name = '语文'
  AND es.score > 80;
-- 查询英语考试的时间
SELECT e.exam_date as 考试日期
FROM Exams e
WHERE e.exam_name = '英语考试';
-- 查询参加英语考试的学生信息
SELECT s.student_name   as 姓名,
       s.student_age    as 年龄,
       s.student_gender as 性别,
       c.course_name    as 课程名称,
       cl.class_name    as 班级名称,
       t.teacher_name   as 教师姓名,
       e.exam_name      as 考试名称,
       e.exam_date      as 考试日期,
       es.score         as 考试成绩
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN Teachers t ON c.teacher_id = t.teacher_id
         JOIN Classes cl ON t.class_id = cl.class_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE c.course_name = '英语';
-- 查询参加英语考试的学生的姓名和年龄(按照年龄倒序)
SELECT s.student_name as 姓名, s.student_age as 年龄
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE c.course_name = '英语'
ORDER BY s.student_age DESC;
-- 查询参加英语考试的学生的姓名和年龄(按照年龄正序)
SELECT s.student_name as 姓名, s.student_age as 年龄
FROM Students s
         JOIN Courses c ON s.course_id = c.course_id
         JOIN ExamScores es ON s.student_id = es.student_id
         JOIN Exams e ON es.exam_id = e.exam_id
WHERE c.course_name = '英语'
ORDER BY s.student_age ASC;