西电_2022春_数据库原理_实验二

最新推荐文章于 2024-04-18 14:28:29 发布

周木_1

最新推荐文章于 2024-04-18 14:28:29 发布

阅读量1.9k

点赞数 1

分类专栏：实验报告文章标签： sql

本文链接：https://blog.csdn.net/apple_52841031/article/details/124177131

版权

实验报告专栏收录该内容

4 篇文章 1 订阅

订阅专栏

实验内容及要求

【使用实验一创建的六个表，即学院表College，学生表Student，教师表Teacher，课程表Course，学习表Study，开课表Class 】

求选修了Wang老师所授课程Database的学生，且grade在90分以上的学生的姓名student_name、课程名course_name和分数grade。
求选修了Zhang老师所授全部课程的学生的姓名student_name和学院名college_name。
求至少选修了两门课程的学生的学号student_id和姓名student_name。
查询Bert同学没有选修的课程号course_id和课程名course_name。
查询没有被任何学生选修的课程的课程号course_id,课程名course_name和授课教师的教师工号teacher_id。
求选修了全部课程的学生姓名student_name和其所有课程平均成绩avg_grade。
查询各学院Database课程的平均分avg_grade，并按照成绩降序的方式排列。
查询选修Database课程的学生姓名student_name和所在院系college_name，结果按各院系排列，同时成绩按降序的方式排列。
检索选修Database课程的最高分学生的学生姓名student_name和分数grade。
检索选修Database课程的第二高分学生的学生姓名student_name和分数grade。
查询选课人数超过30人的课程的课程号course_id和课程名course_name。
查询选修Database课程的学生姓名student_name和分数grade,按照分数降序显示，同时额外增加一列显示其排名（相同分数排名应该相同，在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字，比如第一名有两个都是100分，99分则为第二名）。
检索所有课程的课程号course_id，课程名course_name以及每个课程前三高学生的学生姓名student_name和分数grade。
自行提出一个复杂查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。

实验步骤

运行环境：macOS Monterey 12.3.1
MySQL版本：8.0.28
终端：item2

数据准备

由于下列查询中需要一些特定的条件，所以我们优先创建可以供其查询的数据，然后再编写查询语句。

INSERT INTO Course VALUES ('6', 'Database', '4', '5');
INSERT INTO Student VALUES ('20009200286', 'Zhou', 'M', '1', '2002-06-01');

/*关于Study，在实验一中只有student_id是主键，所以该表中一个学生只能选取一门课程在此处显然是不合理的，故重新创建表Study_2代替Study，将student_id和course_id都设置成主键*/

CREATE TABLE Study_2
(student_id CHAR(20),
course_id CHAR(20),
grade INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

INSERT INTO Study_2 VALUES ('20009200286', '6', '95');
INSERT INTO Study_2 VALUES ('20009200286', '4', '45');
INSERT INTO Study_2 VALUES ('20009200286', '2', '75');
INSERT INTO Study_2 VALUES ('20009200286', '3', '15');
INSERT INTO Study_2 VALUES ('20009200281', '3', '95');
INSERT INTO Study_2 VALUES ('20009200281', '1', '85');
INSERT INTO Study_2 VALUES ('20009200282', '2', '90');
INSERT INTO Study_2 VALUES ('20009200283', '3', '83');
INSERT INTO Study_2 VALUES ('20009200284', '4', '90');
INSERT INTO Study_2 VALUES ('20009200285', '5', '100');
INSERT INTO Study_2 VALUES ('20009200281', '2', '100');
INSERT INTO Study_2 VALUES ('20009200281', '5', '100');
INSERT INTO Class VALUES ('1', '6');
INSERT INTO Teacher VALUES('6', 'Zhang', '1', 'F');
INSERT INTO Class VALUES('6', '2');
INSERT INTO Class VALUES('6', '5');
INSERT INTO Student VALUES('20009200287', 'Bert', 'M', '3', '2002-06-01');
INSERT INTO Study_2 VALUES ('20009200287', '4', '90');
INSERT INTO Course VALUES ('7', 'C++', '0', '5');
INSERT INTO Class VALUES('4', '7');
INSERT INTO Class VALUES('1', '7');
INSERT INTO Study_2 VALUES ('20009200281', '4', '55');
INSERT INTO Study_2 VALUES ('20009200281', '6', '75');
INSERT INTO Study_2 VALUES ('20009200281', '7', '65');
/*7*/
INSERT INTO Study_2 VALUES ('20009200284', '6', '79');
INSERT INTO Study_2 VALUES ('20009200285', '6', '75');

查询步骤

1、求选修了Wang老师所授课程Database的学生，且grade在90分以上的学生的姓名student_name、课程名course_name和分数grade。

SELECT student_name, course_name, grade
FROM Student, Course, Study_2
WHERE Study_2.course_id = Course.course_id AND Student.student_id = Study_2.student_id AND Student.student_id IN
(SELECT student_id
FROM Study
WHERE grade > 90 AND course_id IN
(SELECT course_id
FROM Course 
WHERE course_name = 'Database' AND course_id IN 
(SELECT course_id
FROM Class
WHERE teacher_id IN
(SELECT teacher_id 
FROM Teacher
WHERE teacher_name = 'Wang'))));

请添加图片描述
2、求选修了Zhang老师所授全部课程的学生的姓名student_name和学院名college_name。

SELECT student_id, college_name
FROM Student, College
WHERE Student.college_id = College.college_id AND Student.student_id IN
(SELECT student_id 
FROM Study_2
WHERE course_id IN
(SELECT course_id 
FROM Class
WHERE teacher_id IN
(SELECT teacher_id 
FROM TEACHER
WHERE teacher_name = 'Zhang')));

请添加图片描述
3、求至少选修了两门课程的学生的学号student_id和姓名student_name。

SELECT student_id, student_name
FROM Student
WHERE student_id IN
(SELECT student_id 
FROM Study_2
GROUP BY student_id
HAVING COUNT(*) >= 2);

请添加图片描述
4. 查询Bert同学没有选修的课程号course_id和课程名course_name。

SELECT course_id, course_name
FROM Course
WHERE course_id NOT IN
(SELECT course_id
FROM Study_2
WHERE student_id IN
(SELECT student_id
FROM Student
WHERE student_name = 'Bert'));

请添加图片描述
5、查询没有被任何学生选修的课程的课程号course_id,课程名course_name和授课教师的教师工号teacher_id。

SELECT course_name, teacher_id
FROM Course, Class
WHERE Class.course_id = Course.course_id AND Class.course_id NOT IN
(SELECT course_id
FROM Study_2);

请添加图片描述
6、求选修了全部课程的学生姓名student_name和其所有课程平均成绩avg_grade。

SELECT student_name, AVG(grade)
FROM Study_2, Student
WHERE Study_2.student_id = Student.student_id AND Student.student_id IN
(SELECT student_id
FROM Study_2
GROUP BY student_id
HAVING COUNT(*) = 
(SELECT COUNT(*)
FROM Course))
GROUP BY student_name;

请添加图片描述
7、查询各学院Database课程的平均分avg_grade，并按照成绩降序的方式排列。

SELECT college_id, AVG(grade) avg_grade
FROM Student, Study_2
WHERE Student.student_id = Study_2.student_id AND Study_2.course_id IN
(SELECT course_id 
FROM Course 
WHERE course_name = 'Database')
GROUP BY college_id
ORDER BY AVG(grade) DESC;

请添加图片描述
8、查询选修Database课程的学生姓名student_name和所在院系college_name，结果按各院系排列，同时成绩按降序的方式排列。

SELECT student_name, college_name
FROM Student, Study_2, College
WHERE Student.student_id = Study_2.student_id AND Student.college_id = College.college_id AND Study_2.course_id IN
(SELECT course_id
FROM Course
WHERE course_name = 'Database')
ORDER BY Student.College_id, Study_2.grade DESC;

请添加图片描述
9、检索选修Database课程的最高分学生的学生姓名student_name和分数grade。

SELECT student_name, grade
FROM 
(SELECT student_name, grade
FROM Student, Study_2
WHERE Student.student_id = Study_2.student_id AND Study_2.course_id =
(SELECT course_id
FROM Course
WHERE course_name = 'Database')) A
WHERE grade = 
(SELECT MAX(grade)
FROM Study_2
WHERE course_id = 
(SELECT course_id
FROM Course
WHERE course_name = 'Database'));

请添加图片描述
10、检索选修Database课程的第二高分学生的学生姓名student_name和分数grade。

SELECT student_id, grade
FROM Study_2, Course
WHERE Course.course_name = 'Database' AND Course.course_id = Study_2.course_id AND grade = 
(SELECT grade 
FROM Study_2, Course
WHERE Course.course_name = 'Database' AND Course.course_id = Study_2.course_id AND grade < 
(SELECT MAX(grade)
FROM Study_2, Course
WHERE Course.course_name = 'Database' AND Course.course_id = Study_2.course_id)
LIMIT 1);

请添加图片描述
11、查询选课人数超过2人的课程的课程号course_id和课程名course_name。

SELECT course_id, course_name
FROM Course
WHERE course_id IN
(SELECT course_id 
FROM Study_2
GROUP BY course_id
HAVING COUNT(*) > 2);

请添加图片描述
12、查询选修Database课程的学生姓名student_name和分数grade,按照分数降序显示，同时额外增加一列显示其排名（相同分数排名应该相同，在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字，比如第一名有两个都是100分，99分则为第二名）。

SET @rank = 0;
SELECT student_name, grade, @rank := @rank+1
FROM
(SELECT student_name, grade
FROM Study_2, Student, Course
WHERE Course.course_name = 'Database' AND Course.course_id = Study_2.course_id AND Student.student_id = Study_2.student_id) A
ORDER BY grade DESC;

请添加图片描述
13、检索所有课程的课程号course_id，课程名course_name以及每个课程前三高学生的学生姓名student_name和分数grade。

SELECT r.course_id,c.course_name,stu.student_name,r.grade
FROM(
SELECT *
FROM study_2 AS s
WHERE (SELECT COUNT(student_id) FROM study_2 WHERE s.course_id = course_id AND s.grade < grade) < 3
ORDER BY course_id,grade DESC)r LEFT JOIN course c
ON r.course_id=c.course_id 
LEFT JOIN student stu
ON stu.student_id=r.student_id
ORDER BY course_id DESC

周木_1

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
2
评论
西电_2022春_数据库原理_实验二

实验内容及要求【使用实验一创建的六个表，即学院表College，学生表Student，教师表Teacher，课程表Course，学习表Study，开课表Class 】求选修了Wang老师所授课程Database的学生，且grade在90分以上的学生的姓名student_name、课程名course_name和分数grade。求选修了Zhang老师所授全部课程的学生的姓名student_name和学院名college_name。求至少选修了两门课程的学生的学号student_id和姓名student
复制链接

扫一扫