数据库编程题（练习题）：学生课程数据库中有三个关系（附参考答案）

最新推荐文章于 2024-08-06 14:44:36 发布

impl柴猿滚滚

最新推荐文章于 2024-08-06 14:44:36 发布

阅读量2.9w

点赞数 13

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/weixin_44316527/article/details/108472071

版权

数据库专栏收录该内容

9 篇文章

订阅专栏

设学生课程数据库中有三个关系：
学生关系S（S#，SNAME，AGE，SEX）
学习关系SC（S#，C#，GRADE）
课程关系C（C#，CNAME）
其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分别表示学号、课程号、姓名、年龄、性别、成绩和课程名。
用SQL语句表达下列操作
（1）检索选修课程名称为“MATHS”的学生的学号与姓名
SELECT S.S#,SNAME FROM S,C,SC WHERE S.S#=SC.S# AND C.C#=SC.C# AND CNAME=‘MATHS’;

（2）检索至少学习了课程号为“C1”和“C2”的学生的学号
SELECT S# FROM SC WHERE C#=‘C1’ AND S# IN(SELECT S# FROM SC WHERE C#=‘C2’);

（3）检索年龄在18到20之间（含18和20）的女生的学号、姓名和年龄
SELECT S#,SNAME,AGE FROM S WHERE AGE BETWEEN 18 AND 20 AND SEX=‘女’;

（4）检索平均成绩超过80分的学生学号和平均成绩
SELECT S#,AVG(GRADE) FROM SC GROUP BY S# HAVING AVG(GRADE)>80;

（5）检索选修了全部课程的学生姓名
SELECT SNAME FROM S WHERE NOT EXISTS(SELECT * FROM C WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#));

（6）检索选修了三门课以上的学生的姓名
SELECT SNAME FROM S,SC WHERE S.S#=SC.S# GROUP BY S.S# HAVING COUNT(*)>3;

SQL测试

1.设计表（#为特殊符号，全部替换为NO进行测试）

学生关系

在这里插入图片描述
学习关系

在这里插入图片描述

课程关系

在这里插入图片描述
2.插入数据

INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('1', '张三', 15, '男');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('2', '李四', 16, '女');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('3', '王五', 17, '男');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('4', '赵六', 18, '女');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('5', '琪琪', 19, '男');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('6', '粑粑', 20, '女');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('7', '久久', 21, '男');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('8', '试试', 22, '女');
INSERT INTO `s`(`SNO`, `SNAME`, `AGE`, `SEX`) VALUES ('9', '十一', 23, '男');

INSERT INTO `c`(`CNO`, `CNAME`) VALUES ('C1', 'CHINESE');
INSERT INTO `c`(`CNO`, `CNAME`) VALUES ('C2', 'COMPUTER');
INSERT INTO `c`(`CNO`, `CNAME`) VALUES ('C3', 'MATHS');
INSERT INTO `c`(`CNO`, `CNAME`) VALUES ('C4', 'ENGLISH');
INSERT INTO `c`(`CNO`, `CNAME`) VALUES ('C5', 'BIOLOGY');

INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (1, '1', 'C1', '76');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (2, '1', 'C2', '84');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (3, '1', 'C3', '85');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (4, '1', 'C4', '91');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (5, '2', 'C1', '81');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (6, '2', 'C2', '72');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (7, '2', 'C3', '82');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (8, '3', 'C1', '91');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (9, '3', 'C2', '60');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (10, '3', 'C3', '82');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (11, '3', 'C4', '60');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (12, '4', 'C1', '52');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (13, '4', 'C2', '91');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (14, '5', 'C2', '82');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (15, '5', 'C3', '73');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (16, '6', 'C2', '72');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (17, '6', 'C4', '82');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (18, '7', 'C2', '92');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (19, '7', 'C3', '75');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (20, '7', 'C4', '88');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (21, '8', 'C1', '89');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (22, '8', 'C2', '84');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (23, '8', 'C3', '74');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (24, '8', 'C4', '76');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (25, '8', 'C5', '77');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (26, '9', 'C1', '88');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (27, '9', 'C2', '95');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (28, '9', 'C3', '75');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (29, '9', 'C4', '85');
INSERT INTO `sc`(`NO`, `SNO`, `CNO`, `GRADE`) VALUES (30, '9', 'C5', '78');

3.查询测试

SELECT S.SNO,SNAME FROM S,C,SC WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND CNAME='MATHS';
SELECT SNO FROM SC WHERE CNO='C1' AND SNO IN(SELECT SNO FROM SC WHERE CNO='C2');
SELECT SNO,SNAME,AGE FROM S WHERE AGE BETWEEN 18 AND 20 AND SEX='女';
SELECT SNO,AVG(GRADE) FROM SC GROUP BY SNO HAVING AVG(GRADE)>80;
SELECT SNAME FROM S WHERE NOT EXISTS(SELECT * FROM C WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.SNO=S.SNO AND SC.CNO=C.CNO));
SELECT SNAME FROM S,SC WHERE S.SNO=SC.SNO GROUP BY S.SNO HAVING COUNT(*)>3;

4.查看结果

在这里插入图片描述