数据库：实验二

devtools/2024/10/18 18:21:19/

一、实验2.1 数据查询

1、要求

        以School数据库为例，在该数据库中存在四张表格，分别为：

  1. 表STUDENT(sid, sname, email, grade);
  2. 表TEACHERS(tid, tname, email, salary);
  3. 表COURSES(cid, cname, hour);
  4. 表CHOICES(no, sid, tid, cid, score)

        在数据库中，存在这样的关系：学生可以选择课程，一个课程对应一个教师。在表CHOICES中保存学生的选课记录。

2、按以下要求对数据库进行查询操作

（1）查询年级为2001的所有学生的名称并按编号升序排列。

SELECT sname FROM STUDENTS WHERE grade = '2001' ORDER BY sid;

（2）查询学生的选课成绩合格的课程成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。

SELECT sid, cid, (1 + (score - 60) * 0.1) AS points FROM CHOICES WHERE score >= 60;

 （3）查询课时是48或64的课程的名称。

SELECT cname FROM COURSES WHERE hour IN (48, 64);

 （4）查询所有课程名称中含有data的课程编号。

SELECT cid FROM COURSES WHERE cname LIKE '%data%';

（5）查询所有选课记录的课程号（不重复显示）。

SELECT DISTINCT cid FROM CHOICES;

（6）统计所有教师的平均工资。

SELECT AVG(salary) FROM TEACHERS;

（7）查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。

SELECT tid, AVG(score) AS avg_score FROM CHOICES GROUP BY tid ORDER BY avg_score DESC;

（8）统计各个课程的选课人数和平均成绩。

SELECT cid, COUNT(sid) AS num_students, AVG(score) AS avg_score FROM CHOICES GROUP BY cid;

（9）查询至少选修了三门课程的学生编号。

SELECT sid FROM CHOICES GROUP BY sid HAVING COUNT(cid) >= 3;

（10）查询编号800009026的学生所选的全部课程的课程名和成绩。

SELECT COURSES.cname, CHOICES.score FROM CHOICES JOIN COURSES ON CHOICES.cid = COURSES.cid WHERE sid = '800009026';

 （11）查询所有选修了database的学生的编号。

SELECT CHOICES.sid FROM CHOICES JOIN COURSES ON CHOICES.cid = COURSES.cid WHERE COURSES.cname = 'database';

（12）求出选择了同一个课程的学生数。

SELECT cid, COUNT(sid) AS student_count FROM CHOICES GROUP BY cid;

（13）求出至少被两名学生选修的课程编号。

SELECT cid FROM CHOICES GROUP BY cid HAVING COUNT(sid) >= 2;

（14）查询选修了编号800009026的学生所选的某个课程的学生编号。

SELECT sid FROM CHOICES WHERE cid IN (SELECT cid FROM CHOICES WHERE sid = '800009026');

（15）查询学生的基本信息及选修课程编号和成绩。

SELECT STUDENTS.*, CHOICES.cid, CHOICES.score FROM STUDENTS JOIN CHOICES ON STUDENTS.sid = CHOICES.sid;

（16）查询学号850955252的学生的姓名和选修的课程名及成绩。

SELECT STUDENTS.sname, COURSES.cname, CHOICES.score FROM STUDENTS JOIN CHOICES ON STUDENTS.sid = CHOICES.sid JOIN COURSES ON CHOICES.cid = COURSES.cid WHERE STUDENTS.sid = '850955252';

850955252同学的信息没找到，用800009026同学验证 

（17）查询与学号850955252的学生同年级的所有学生资料。

SELECT * FROM STUDENTS WHERE grade IN (SELECT grade FROM STUDENTS WHERE sid = '850955252');

（18）查询所有有选课的学生的详细信息。

SELECT DISTINCT STUDENTS.* FROM STUDENTS JOIN CHOICES ON STUDENTS.sid = CHOICES.sid;

（19）查询没有学生选的课程的编号。

SELECT cid FROM COURSES WHERE cid NOT IN (SELECT cid FROM CHOICES);

（20）查询与课程名为c++的课时一样的课程名称。

SELECT cname FROM COURSES WHERE hour = (SELECT hour FROM COURSES WHERE cname = 'c++');

（21）找出选修课程成绩最好的选课记录。

SELECT * FROM CHOICES WHERE score = (SELECT MAX(score) FROM CHOICES);

（22）找出和课程c或课程c++的课时一样课程名称。

SELECT cname FROM COURSES WHERE hour IN (SELECT hour FROM COURSES WHERE cname = 'c' OR cname = 'c++');

（23）查询所有选修编号10001的课程的学生的姓名。

SELECT sname FROM STUDENTS JOIN CHOICES ON STUDENTS.sid = CHOICES.sid WHERE cid = '10001';

（24）查询选修了所有课程的学生姓名。

SELECT sname FROM STUDENTS WHERE sid NOT IN (SELECT sid FROM STUDENTS EXCEPT SELECT sid FROM CHOICES);

（25）利用集合运算，查询选修课程c++或选修课程c的学生的编号。

SELECT sid FROM CHOICES WHERE cid IN (SELECT cid FROM COURSES WHERE cname = 'c++' UNION SELECT cid FROM COURSES WHERE cname = 'c');

（26）实现集合交运算，查询既选修课程c++又选修课程c的学生的编号。

SELECT sid FROM CHOICES WHERE cid IN (SELECT cid FROM COURSES WHERE cname = 'c++') INTERSECT SELECT sid FROM CHOICES WHERE cid IN (SELECT cid FROM COURSES WHERE cname = 'c');

（27）实现集合减运算，查询选修课程c++而没有选修课程c的学生的编号。

SELECT sid FROM CHOICES WHERE cid IN (SELECT cid FROM COURSES WHERE cname = 'c++') EXCEPT  SELECT sid FROM CHOICES WHERE cid IN (SELECT cid FROM COURSES WHERE cname = 'c');

二、实验2.2 空值和空集的处理

1、按以下要求对数据库进行查询操作

（1）查询所有选课记录的成绩并将它换算为五分制（满分5分，合格3分），注意SCORE取NULL值的情况。

SELECT     no, sid, tid, cid, score*0.05

FROM         CHOICES

where      score is not null

（2）通过查询选修编号10028的课程的学生的人数，其中成绩合格的学生人数，不合格的学生人数，讨论NULL值的特殊含义。

        count 对null记录取为0,score<60比较中，如果是null，结果为unknown，而只有当结果是true时才被选入结果

SELECT     COUNT(sid) AS Expr1

FROM         CHOICES

WHERE     (cid = '10028')

SELECT     COUNT(sid) AS Expr2

FROM         CHOICES

WHERE     (cid = '10028' and score>=60)

SELECT     COUNT(sid) AS Expr3

FROM         CHOICES

WHERE     (cid = '10028' and score<60)

（3）通过实验检验在使用ORDER BY进行排序时，取NULL的项是否出现在结果中？如果有，在什么位置？

有出现，被认为最小

SELECT     CHOICES.*

FROM         CHOICES

WHERE     (cid = '10028')

ORDER BY score desc

（4）在上面的查询过程中如果加上保留字DISTINCT会有什么效果？

SELECT     distinct   score, cid  

FROM         CHOICES

WHERE     (cid = '10028')

ORDER BY score desc

删去DISTINCT后会出现重复的结果  

（5）通过实验说明使用分组GROUP BY对取值为NULL的项的处理。

SELECT     COUNT(sid) AS Expr1, score

FROM         CHOICES

GROUP BY score

SELECT     COUNT(sid) AS Expr2

FROM         CHOICES

where    score is null

NULL会被单独分为一类

（6）结合分组，使用集合函数求每个同学的平均分、总的选课记录、最高成绩、最低成绩和总成绩。

SELECT     sid ,COUNT(cid) AS 总选课数, AVG(score) AS 平均分, MAX(score) AS 最高成绩, MIN(score) AS 最低成绩

FROM         CHOICES

GROUP BY sid

（7）查询成绩小于60的选课记录，统计总数、平均分、最大值和最小值。

SELECT     sid, score

FROM         CHOICES

WHERE     (score < 60)

SELECT     COUNT('CHOICES.*') AS 总记录, AVG(score) AS 平均分, MAX(score) AS 最高分, MIN(score) AS 最低分

FROM         CHOICES

WHERE     (score < 60)

（8）采用嵌套查询的方式，利用比较运算符和谓词ALL的结合来查询表COURSES中最少的课时。假设数据库中只有一个记录的时候，使用前面的方法会得到什么结果，为什么？

SELECT * from COURSES

WHERE hour<=all(select hour from COURSES)

SELECT MIN(hour) FROM COURSES

CREATE TABLE oneCOURSES(

cid CHAR(18) NOT NULL,

cname CHAR(20),

hour int

);

INSERT INTO oneCOURSES VALUES(

'12138',

'cs',

48

);

select * from oneCOURSES

WHERE hour<=all(select hour from oneCOURSES)

SELECT MIN(hour) FROM oneCOURSES

当只有一条记录时，这条记录的课时会是最少的，因为它同时是最大和最小的课时。

（9）创建一个学生表S（NO， SID， SNAME），教师表T（NO， TID， TNAME）作为实验用的表。其中NO分别是这两个表的主键，其他键允许为空。

create table S

(NO CHAR(9) PRIMARY KEY,

 SID CHAR(20),

 SNAME CHAR(20)

);

create table T

(NO CHAR(9) PRIMARY KEY,

 TID CHAR(20),

 TNAME CHAR(20)

);

（10）向S插入元组（1， 0129871001， 王小明）、（2， 0129871002， 李兰）、（3， 0129871005， NULL）、（4， 0129871004， 关红）；

insert into S(NO,SID,SNAME) values('1','0129871001','王小明');

insert into S values('2','0129871002','李兰');

insert into S(NO,SID,SNAME) values('3','0129871005',null);

insert into S(NO,SID,SNAME) values('4','0129871004','关红');

（11）向T插入元组1， 100189， 王小明）、（2， 100180， 李小）、（3， 100121， NULL）、（4， 100128， NULL）。

insert into T values('1','100189','王小明');

insert into T values('2','100180','李小');

insert into T values('3','100121',null);

insert into T values('4','100128',null);

（12）对这两个表作对姓名的等值连接运算，找出既是老师又是学生的人员的学生编号和老师编号。

select S.NO,T.NO from S JOIN T ON S.SNAME=T.TNAME;

NULL值在比较中不会判等，所以不会出现（3，3）（3，4）这样的记录 


http://www.ppmy.cn/devtools/22685.html

相关文章

数据库的嵌套查询（子查询），分组查询和统计查询—学生选课

数据库的嵌套查询（子查询），分组查询和统计查询—学生选课

学生选课数据库中的表的结构参考学生选课数据库的定义和维护 1.嵌套查询 1.1查询选修了数据库原理与应用课程的学生学号和姓名 SELECT sno 学号, sname 姓名 FROM student WHERE sno IN(SELECT snoFROM SCINNER JOIN Course c ON c.cno SC.cno AND c.cname 数据库原理与应…
阅读更多...
Debezium分享系列之：Debezium2.6稳定版本SQLSerer数据库Debezium connector核心知识点

Debezium分享系列之：Debezium2.6稳定版本SQLSerer数据库Debezium connector核心知识点

Debezium分享系列之：Debezium2.6稳定版本SQLSerer数据库Debezium connector核心知识点 一、综述二、snapshot.mode参数三、临时快照四、增量快照五、触发增量快照六、使用Kafka信令通道触发增量快照七、停止增量快照八、使用Kafka信令通道停止增量快照九、阻止快照十、读取变更…
阅读更多...
38-1 防火墙了解

38-1 防火墙了解

一、防火墙的概念： 防火墙（Firewall)，也称防护墙，是由Check Point创立者Gil Shwed于1993年发明并引入国际互联网（US5606668 [A]1993-12-15)。它是一种位于内部网络与外部网络之间的网络安全系统，是一项信息安全的防护系统，依照特定的规则，允许或是限制传输的数据通过。…
阅读更多...
数据处理——avi视频数据转png图片格式代码，mat转png图片格式代码，图片裁剪成固定大小代码。

数据处理——avi视频数据转png图片格式代码，mat转png图片格式代码，图片裁剪成固定大小代码。

avi2png 处理HMC-QU数据集记录&#xff0c;每一个avi视频&#xff0c;通过xlsx中记录的初始帧和结束帧进行帧转png&#xff0c;保存在以avi视频名为名的文件夹下。 import os import cv2 import pandas as pd# 输入文件夹路径 folder_path avidata# 输出文件夹路径 output_f…
阅读更多...
TCP四次挥手

TCP四次挥手

1. 四次挥手过程 一开始客户端和服务端都是处于“ESTABLISHED”状态 第一次挥手&#xff1a;客户端主动向服务端发送连接释放请求&#xff0c;FIN标志位设置为1&#xff0c;客户端进入”FIN_WAIT1“状态。 第二次挥手&#xff1a;服务端向客户端发送确认请求&#xff0c;ACK标…
阅读更多...
Java技术学习|消息队列|初级RabbitMQ

Java技术学习|消息队列|初级RabbitMQ

学习材料声明 黑马RabbitMQ快速入门教程&#xff0c;快速掌握rabbitmq、springAMQP消息中间件 是非常初级的学习&#xff0c;听说后续的高级课程会涉及到微服务之类的&#xff0c;所以等学完微服务再回来学。还有redis的高级部分也涉及了微服务&#xff0c;所以也都暂时停止学…
阅读更多...
美国站群服务器的配置选择要点?

美国站群服务器的配置选择要点?

美国站群服务器的配置选择要点? 随着互联网的快速发展&#xff0c;站群已经成为许多网站主和企业选择的一种有效的网络推广方式。而在搭建站群时&#xff0c;选择适合的服务器配置是至关重要的。美国作为全球互联网技术的先锋之一&#xff0c;拥有先进的服务器设施和强大的网…
阅读更多...
Doxygen

Doxygen

Doxygen是一种开源跨平台的文档系统&#xff0c;它使用类似JavaDoc风格的注释来描述代码&#xff0c;并可以从源代码中生成各种格式的文档。Doxygen完全支持C、C、Java、Objective-C和IDL语言&#xff0c;部分支持PHP、C#&#xff0c;其注释语法与Qt-Doc、KDoc和JavaDoc兼容。 …
阅读更多...
最新文章

Copyright @ 2022~2023