数据库：实验二

羊老开

已于 2024-04-25 17:57:16 修改

阅读量571

点赞数 19

文章标签： oracle 数据库

于 2024-04-25 17:57:05 首次发布

本文链接：https://blog.csdn.net/Y798711330/article/details/138195908

版权

一、实验2.1 数据查询

1、要求

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

表STUDENT(sid, sname, email, grade);
表TEACHERS(tid, tname, email, salary);
表COURSES(cid, cname, hour);
表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);