综合练习二习题Word文件下载.docx
- 文档编号:20794801
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:11
- 大小:19.72KB
综合练习二习题Word文件下载.docx
《综合练习二习题Word文件下载.docx》由会员分享,可在线阅读,更多相关《综合练习二习题Word文件下载.docx(11页珍藏版)》请在冰豆网上搜索。
cno
课程号
cname
课程名
ccredit
学分
取值大于0
semster
学期
period
学时
SC表结构
sno
主键,引用Student的外键
主键,引用Course的外键
grade
成绩
取值0~100
答案:
1、Student表
CREATETABLEStudent(
snoVARCHAR2(7)PRIMARYKEY,
snameVARCHAR2(10)NOTNULL,
ssexVARCHAR2
(2)CHECK(ssex=‘男’ORssex=‘女’),
sageNUMBER
(2)CHECK(sage>
=15ANDsage<
=45),
sdeptVARCHAR2(20)DEFAULT‘计算机系’
)
2、Course表
CREATETABLECourse(
cnoVARCHAR2(10)NOTNULL,
cnameVARCHAR2(20)NOTNULL,
ccreditNUMBER
(2)CHECK(ccredit>
0),
semesterNUMBER
(2)CHECK(semester>
periodNUMBER(3)CHECK(period>
CONTRAINTcourse_cno_pkPRIMARYKEY(cno)
3、SC表
CREATETABLESC(
snochar(7)NOTNULL,
cnochar(10)NOTNULL,
gradetinyintCHECK(grade>
=0ANDgrade<
=100),
CONTRAINTsc_sno_cno_pkPRIMARYKEY(sno,cno),
CONTRAINTstudent_sno_fkFOREIGNKEY(sno)REFERENCEStudent(sno),
CONTRAINTcourse_cno_fkFOREIGNKEY(cno)REFERENCECourse(cno)
)
二、使用SQL语句分别向Student、Course、SC表中加入如下数据:
Student表数据
Sname
9512101
李勇
男
19
计算机系
9512102
刘晨
20
9512103
王敏
女
9512104
张立
22
信息系
9512105
钱小平
21
9512106
吴宾
数学系
9512107
王大力
Course表数据
semester
c01
计算机文化学
3
1
c02
Java
8
5
c03
计算机网络
4
c04
数据库基础
6
c05
高等数学
2
c06
数据结构
7
C07
VB
SC表数据
90
<
NULL>
78
66
82
75
三、完成如下查询
1、查询全体学生的学号和姓名
SELECTsno,snamefromStudent
2、查询全体学生的姓名、学号和所在系
SELECTsname,sno,sdeptfromStudent
3、查询全体学生的姓名及其出生年份
●分析:
由于Student表中只记录了学生的年龄,而没有记录学生的出生年份,所以需要经过计算得到学生的出生年份,即用当前年减去年龄,得到出生年份。
SELECTsname,2011–sageFROMStudent
―或―
SELECTsname,to_number(to_char(sysdate,’yyyy’))–sageFROMStudent
4、查询全体学生的姓名和出生年份,并在出生年份列前加入一个列,此列的每行数据均为“YearofBirth”常量值
SELECTsnamem,‘YearofBirth’,to_number(to_char(sysdate,’yyyy’))–sageFROMStudent
5、在选课表(SC)中查询有哪些学生选修了课程,并列出学生的学号
SELECTsnoFROMSC
在结果集中会有许多重复的行(实际上一个学生选修了多少门课程,其学号就会在结果集中重复出现多少次)。
使用DISTINCT关键字就可以去掉结果集中的重复行。
去掉结果集中的重复行
SELECTDISTINCTsnoFROMSC
6、查询计算机系全体学生的姓名
SELECTsnameFROMStudentWHEERsdept=‘计算机系’
7、查询所有年龄在20岁以下的学生的姓名及年龄
SELECTsname,sageFROMStudentWHEERsage<
8、查询考试成绩不及格的学生的学号
●当一个学生有多门不极格课程时,只列出一个学号。
SELECTDISTINCTsnoFROMSCWHEREgrade<
60andgradeisnotnull
9、查询年龄在20~23岁之间的学生的姓名、所在系和年龄
SELECTsname,sdept,sage
FROMStudent
WHEREsageBETWEEN20AND23
10、查询年龄不在20~23之间的学生的姓名、所在系和年龄
WHEREsageNOTBETWEEN20AND23
11、查询信息系、数学系、和计算机系学生的姓名和性别
SELECTsname,ssex,
FROMStudent
WHEREsdeptIN(‘信息系’,’数学系’,’计算机系’)
12、查询既不属于信息系、数学系、也不属于计算机系学生的姓名和性别
WHEREsdeptNOTIN(‘信息系’,’数学系’,’计算机系’)
13、查询姓“张”的学生的详细信息
SELECT*FROMStudentWHEREsnameLIKE‘张%’
14、查询学生表中姓“张”、姓“李”、姓“刘”的学生的情况
SELECT*FROMStudentWHEREsnameLIKE‘张%’ORsnameLIKE‘李%’ORsnameLIKE‘刘%’
15、查询名字中第2个字为“小”或“大”字的学生姓名和学号
SELECTsname,sno
WHEREsnameLIKE‘_小%’ORsnameLIKE‘_大%’
16、查询所有不姓“刘”的学生
WHEREsnameNOTLIKE‘刘%’
17、从学生表中查询学号的最后一位不是2、3、5的学生的情况
SELECT*
WHEREsnoNOTLIKE‘%2’ANDNOTLIKE‘%3’
ANDNOTLIKE‘%5’
18、查询无考试成绩的学生的学号和相应的课程号
SELECTsno,cno
FROMSC
WHEREgradeISNULL
19、查询所有有考试成绩的学生的学号和课程号
WHEREgradeISNOTNULL
20、查询计算机系年龄在20岁以下的学生的姓名
SELECTsname
WHEREsdept=‘计算机系’ANDsage<
20
21、查询选修了课程“c02”的学生的学号及其成绩,查询结果按成绩降序排列
SELECTsno,gradeFROMSCWHEREcno=‘c02’
ORDERBYgradeDESC
22、查询全体学生的信息,查询结果按所在系的系名升序排列,同一系的学生按年龄降序排列
SELECT*FROMStudent
ORDERBYsdept,sageDESC
23、统计学生总人数
SELECTCOUNT(sno)FROMStudent
24、统计选修了课程的学生的人数
●由于一个学生可以选多门课程,应使用DISTINCT关键字去掉重复值。
SELECTCOUNT(DISTINCTsno)FROMSC
25、计算学号为9512101的学生的考试总成绩之和
SELECTSUM(grade)FROMSCWHEREsno=‘9512101’
26、计算课程“c01”的学生的考试平均成绩
SELECTAVG(grade)FROMSCWHEREcno=‘c01’
27、查询选修了课程“c01”的学生的最高分和最低分
SELECTMAX(grade),MIN(grade)FROMSCWHEREcno=‘c01’
28、查询年龄最大的学生的姓名
SELECTsnameFROMStudent
WHEREsage=(SELECTMAX(sage)FROMStudent)
29、统计每门课程的选课人数,列出课程号和人数
该语句首先将查询结果按cno的值分组,所有cno值相同的记录归为一组,然后再用COUNT函数对每一组进行计算,求得每组的学生人数。
SELECTcnoas课程号,COUNT(sno)as选课人数
FROMSC
GROUPBYcno
30、查询每名学生的选课门数和平均值
SELECTsno学号,COUNT(cno)选课门数,AVG(grade)平均成绩
GROUPBYsno
31、查询选修了3门以上课程的学生的学号
先用GROUPBY按sno列进行分组,然后再用统计函数COUNT分别对每一组进行统计,最后挑选出统计结果大于3的组的sno
SELECTsnoFROMSC
HAVINGCOUNT(cno)>
32、查询选课门数等于或大于4门的学生的平均成绩和选课门数
SELECTsno,AVG(grade)平均成绩,COUNT(cno)修课门数
GROUPBYsno
=4
33、查询每个学生的情况及其选课的情况
●当不给表指定别名时,需要使用原表名
SELECTStudent.sno,sname,ssex,sge,sdept,cno,grade
FROMStudent,SC
WHEREStudent.sno=SC.sno
34、查询计算机系学生的选课情况,要求列出学生的名字、所修课的课程号和成绩。
SELECTsname,cno,grade
FROMStudents,SCa
WHEREs.sno=a.snoANDs.sdept=‘计算机系’
35、查询信息系选修Java课程的学生的成绩,要求列出学生姓名、课程名和成绩
●此查询涉及了三张表,每连接一张表,就需要加入一个连接条件用于去掉产生的笛卡尔积。
●在该查询中,虽然所要查询的列和记录的选择条件均与SC表无关,但还是用了三张表,原因是Student和Course表没有可以进行连接的列(语义相同的列),因此,这两张表的连接需要借助于第三张表——SC表。
SELECTsname,cname,grade
FROMStudents,SCa,Coursec
WHEREs.sno=a.snoANDo=oANDs.sdept=‘信息系’
ANDame=‘Java’
36、查询学生的选课情况,包括选修课程的学生和没有选修课程的学生
●如果Student表中有没有选课的学生时,也同样输出Student表中的数据,但是最后输出列的cno和grade的值均为NULL,因为在SC表中没有与之对应的记录,即对于不满足连接条件的结果在相应的列上放置NULL值。
SELECTs.sno,sname,cno,grade
FROMStudents,SCa
WHEREs.sno=a.sno(+)
37、查询与“刘晨”在同一个系的学生
实际的查询过程如下:
a.确定“刘晨”所在的系,即执行子查询:
SELECTsdeptFROMStudentWHEREsname=‘刘晨’
b.在子查询的结果中查找所有在此系学习的学生:
SELECTsno,sname,sdeptFROMStudentWHEREsdeptIN(‘计算机系’)
●通过结果可以看到,查询结果中也有“刘晨”。
如果不希望“刘晨”出现在查询结果中,如何实现?
SELECTsno,sname,sdeptFROMStudent
WHEREsdeptIN
(SELECTsdeptFROMStudentWHEREsname=‘刘晨’)
ANDsname!
=‘刘晨’
注意:
这里的外层sname!
=‘刘晨’不需要使用表名前缀,因为对于外层查询来说,其表名没有二义性。
38、查询成绩大于90分的学生的学号和姓名
SELECTsno,sname
WHEREsnoIN(SELECTsnoFROMSCWHEREgrade>
90)
⏹使用多表连接的方式实现:
WHEREs.sno=a.snoANDa.grade>
90
39、查询选修了“数据库基础”课程的学生的学号和姓名
WHEREsnoIN
(SELECTsnoFROMSC
WHEREcnoIN
(SELECTcno
FROMCourse
WHEREcname=‘数据库基础’))
⏹使用多表连接方式实现:
SELECTs.sno,sname
FROMStudents,SCa,Coursec
WHEREs.sno=a.snoANDo=oANDame=‘数据库基础’
40、查询选修了课程“c02”且成绩高于此课程的平均成绩的学生的学号和成绩
SELECTsno,grade
FROMSC
WHEREcno=’c02’ANDgrade>
(
SELECTAVG(grade)FROMSCWHEREcno=’c02’)
41、查询选修了课程“c01”的学生的姓名和所在系
SELECTsname,sdeptFROMStudent
WHEREsnoIN(SELECTsnoFROMSCWHEREcno=’c01’)
42、查询数学系成绩在80分以上的学生的学号、姓名
SELECTsno,snameFROMStudent
WHEREsnoIN(SELECTsnoFROMSCWHEREgrade>
80)
ANDsdept=’数学系’
43、查询计算机系考成绩最高的学生的姓名
SELECTsnameFROMStudentsJOINSCONs.sno=sc.sno
WHEREsdept=‘计算机’
ANDsc.grade=
(SELECTMAX(grade)FROMSCsc,Students
WHEREs.sno=sc.snoANDs.sdept=’计算机’)
44、删除选课成绩小于50分的学生的选课记录
DELETEFROMSCWHEREgrade<
50
45、将所有选修了课程“c01”的学生的成绩加10分
UPDATESCSETgrade=grade+10WHEREcno=’c01’
46、将计算机系所有选修了“计算机文化学”课程的学生的成绩加10分
UPDATESCSETgrade=grade+10
WHEREsnoIN
(SELECTsnoFROMStudentWHEREsdept=’计算机’)
ANDcno=(SELECTcnoFROMCourseWHEREcname=’计算机文化学’)
47、查询总绩超过200分的学生,要求列出学号、总成绩
SELECTsno,SUM(grade)
HAVINGSUM(grade)>
200
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 综合 练习 习题