实验二数据的查询更新Word下载.docx
- 文档编号:18765980
- 上传时间:2023-01-01
- 格式:DOCX
- 页数:33
- 大小:548.59KB
实验二数据的查询更新Word下载.docx
《实验二数据的查询更新Word下载.docx》由会员分享,可在线阅读,更多相关《实验二数据的查询更新Word下载.docx(33页珍藏版)》请在冰豆网上搜索。
20
CS
95002
刘晨
女
19
IS
95003
王敏
18
MA
95004
张立
createtablestudent(snointprimarykey,
snamechar(10),
ssexchar
(2)constraintssex_chcheck(ssexin('
男'
'
女'
)),
sageintconstraintsage_chcheck(sage>
=15andsage<
=30),
sdeptchar(10)
)
insertintostudent
values(95001,'
李敏勇'
20,'
CS'
)
values(95002,'
刘晨'
19,'
IS'
values(95003,'
王敏'
18,'
MA'
values(95004,'
张立'
课程表:
Course(Cno,Cname,Cpno,Credeit,remarks)其中Cno为主键、Teacher的类型为char20即为用户定义的数据类型;
Cno
Cname
Cpno
Credit
Teacher
1
数据库
5
4
王芳
2
数学
NULL
刘新
3
信息系统
操作系统
6
高升
数据结构
7
宋明
数据处理
张彬
Pascal语言
李磊
createtablecourse(cnointprimarykey,
cnamechar(15),
cpnointnull,
creditint,
teacherchar20
insertintocourse
values(1,'
数据库'
5,4,'
王芳'
values(2,'
数学'
NULL,2,'
刘新'
values(3,'
信息系统'
1,4,'
values(4,'
操作系统'
6,3,'
高升'
values(5,'
数据结构'
7,4,'
宋明'
values(6,'
数据处理'
张彬'
values(7,'
PASCAL语言'
6,4,'
李磊'
学生选修表:
SC(Sno,Cno,Grade)其中Sno,Cno为主键同时又为外键、Grade值在0到100;
Grade
92
85
88
90
55
70
createtablesc(snoint,
cnoint,
gradeintconstraintgrade_chcheck(gradebetween0and100),
primarykey(sno,cno),
constraintfk_snoforeignkey(sno)referencesstudent(sno),
constraintfk_cnoforeignkey(cno)referencescourse(cno),
insertintosc
values(95001,1,92)
values(95001,2,85)
values(95001,3,88)
values(95002,2,90)
values(95003,2,55)
values(95004,2,70)
3、用T-SQL语句,修改上面所建学生课程数据库中数据:
1)向学生表:
Student中加入一条记录:
(95030,谢非,男,22,CS)并保存
values(95030,'
谢非'
22,'
2)将李敏勇的数据库的成绩改为98分
updatesc
setgrade=98
whereo=(selecto
fromcourse
whereame='
andsno=(selectsno
fromstudent
wheresname='
)
3)删除学生表Student中谢非的记录并保存
delete
fromstudent
wheresname='
4)能不能从Student表中删除李敏勇学生的记录,为什么?
能不能删除王敏,张立两个学生的记录?
不能,sc表中列sno是外码,参照student表的sno列。
4、用T-SQL语句,完成下面简单的查询
1)查询全体学生的学号、姓名及年龄.
use学生课程数据库
select*
2)查询全体学生的姓名,年龄及所在系(要用小写字母表示系名,并用“系名”来表示
列名)。
selectsname,sage,lower(sdept)'
系名'
3)查询选修了课程的学生学号
selectdistinctsno
fromsc
4)查询信息系全体学生的姓名
selectsname
wheresdept='
5)查询所有年龄在20岁以下的学生姓名及其年龄
selectsname,sage
wheresage<
6)查询年龄在20到18间的学生的姓名,系别及年龄
selectsname,sdept,sage
wheresagebetween18and20
7)查询年龄不在23到19间的学生的姓名,系别及年龄
wheresagenotbetween19and23
8)查询不是信息系(IS)和计算机系(CS)学生的姓名和性别
selectsname,ssex
wheresdeptnotin('
9)查询所有姓刘的学生的姓名,学号和性别
selectsname,sno,ssex
wheresnamelike'
张%'
10)查询姓“张”且名为一个汉字的学生的姓名
张_'
11)查询名字中第2个字为”敏”字的学生姓名和学号
selectsname,sno
_敏%'
12)查询所有不姓刘的学生姓名
wheresnamenotlike'
刘%'
13)查询全体学生情况,结果按所在系升序排列,同一系中的学生按年龄降序
orderbysdeptasc,sagedesc
14)查询学生表中所有学生信息,要求只显示前10%行数据
selecttop10percent*
15)按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号,要求只显示前2
行数据,若第3行后的数据也等于70分也要显示。
selecttop2withtiessno
wheregrade>
=70
orderbygradedesc
16)查询每个学生的学号、课程号及分数,同时统计每个学生的总分
selectsno,cno,grade
orderbysno
computesum(grade)bysno
17)查询每个学生的各科分数、最高分、最低分、总分、平均分
computemax(grade),min(grade),sum(grade),avg(grade)bysno
5、用T-SQL语句完成下面的查询
1)查询学生的总人数
selectCOUNT(sno)'
人数'
2)查询选修了课程的学生人数
selectCOUNT(distinctsno)'
选课人数'
3)计算选2号课程的学生平均成绩
selectAVG(grade)'
平均分'
wherecno=2
4)查询选修2号课程的学生最高分数
selectMAX(grade)'
最高分'
5)求各个课程号及相应的选课人数
selectcno,COUNT(sno)'
groupbycno
6)查询选修了2门以上的课程的学生学号
selectsno
groupbysnohaving(COUNT(cno)>
2)
7)查询每个学生及其选修课程的情况
selectstudent.sno,o,cname
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
8)查询每一门课的间接先修课(即先修课的先修课)
selecto,c2.cpno
fromcoursec1,coursec2
whereo=o
9)查询选修2号课程且成绩在90分以上(包括90分)的所有学生。
selectstudent.sno,sname
wherestudent.snoin(selectsc.sno
fromsc
wherecno=2andgrade>
=90)
6.用T-SQL语句完成下面的查询
1)查询与“刘晨”在同一个系学习的学生
selectsno,sname
wheresname<
>
'
andsdept=(selectsdept
fromstudent
wheresname='
2)查询选修了课程名为“数学”的学生学号和姓名
wheresnoin(selectsno
fromsc
wherecno=(selectcno
wherecname='
))
3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄
wheresdept<
andsage<
any(selectsage
fromstudent
wheresdept='
)
4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄
all(selectsage
5)查询所有选修了2号课程的学生姓名
fromstudent,sc
wherestudent.sno=sc.snoandcno=2
6)查询没有选修3号课程的学生姓名
selectdistinctsname
wherestudent.sno=sc.snoandcno!
=3
7、用T-SQL语句完成下面的复杂查询
1)至少选修刘老师所授课程中一门课程的女学生姓名
whereexists(selectsno
fromsc
whereexists(selectcno
fromcourse
whereteacherlike'
andssex='
))
2)检索王同学不学的课程的课程号
selectcno
fromcourse
wherecnonotin(selecto
fromsc,student
wheresnamelike'
王%'
andstudent.sno=sc.sno
ando=o
)
3)检索全部学生都选修的课程的课程号与课程名。
selectcno,cname
wherenotexists(select*
fromstudent
wherenotexists(select*
wherestudent.sno=sc.sno
ando=o))
4)检索选修课程包含刘老师所授课的学生学号。
selectdistinctsno
whereexists(select*
fromcourse
whereo=o
andteacherlike'
5)求选修课程号为2的学生的平均年龄。
selectAVG(sage)'
平均年龄'
wherestudent.sno=sc.snoando=2
6)求刘老师所授课程的每门课程的学生平均成绩。
selectteacher,cname,AVG(grade)'
fromsc,course,student
whereo=o
andstudent.sno=sc.sno
andcourse.teacherlike'
groupbyteacher,o,cname
7)检索学号比刘同学大,而年龄比他小的学生姓名。
wheresno>
(selectsno
wheresnamelike'
andsage<
(selectsage
8)求年龄大于女同学平均年龄的男同学姓名和年龄。
wheresage>
(selectavg(sage)
fromstudent
wheressex='
andssex='
9)求年龄大于所有女同学年龄的男学生姓名和年龄。
wheressex='
andsage>
all(selectsage
wheressex='
10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别,并把检索到的值送往另一个已存在的基本表S(SNO,SNAME,SEX)。
selectsno,sname,ssex
intos
wheregrade>
=80)
11)把选课数学课不及格的成绩全改为空值。
updatesc
setgrade='
wheregrade<
60)
andcno=(selectcno
12)把王同学的选课信息全部删去。
wheresno=(selectsno
13)把低于总平均成绩的男同学成绩提高5%。
setgrade=grade*1.05
wheregrade<
(selectAVG(grade)
fromsc)
andcnoin(selecto
fromstudent,sc
wheressex='
andstudent.sno=sc.sno)
14)检索没有选修‘1’课程的学生学号和姓名
wheresnonotin(selectsc.sno
wherecno=1andstudent.sno=sc.sno)
15)检索至少有一门课程超过学生‘95001’一门成绩的学生学号
selectdistinctsc.sno
fromsc,student
wheresc.sno<
andsc.sno=student.sno
andgrade>
any(selectgrade
wheresno=95001)
16)向学生选修课程表中插入元组“学生95003选修课程1”。
insertintosc(sno,cno)
values(95003,1)
17)求出女同学的每一年龄组(超过10人)有多少人?
要求查询结果按人数升序排列,人数相同的按年龄降序排列。
selectsage,COUNT(sno)'
groupbysagehavingcount(*)>
10
orderbycount(sno)asc,sagedesc
18)定义视图S_AVG,其中包括学生学号以及每个学生选修课程的门数(要求成绩非空)和平均成绩。
createviews_avg(学号,选修门数,平均成绩)
as
selectsno,COUNT(cno),AVG(grade)
wheregradeisnotnull
groupbysno
四、思考题
1、在创建基本表时是否可以缺省主码?
可以
2、简述GROUPBY、COMPUTE、COMPUTEBY的区别?
GROUP
BY
生成单个结果集。
每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。
选择列表只能包含分组依据列和聚合函数。
COMPUTE
生成多个结果集。
一类结果集包含每个组的明细行,其中包含选择列表中的表达式。
另一类结果集包含组的子聚合,或
SELECT
语句的总聚合。
选择列表可包含除分组依据列或聚合函数之外的其它表达式。
聚合函数在
子句中指定,而不是在选择列表中。
BY子句返回的结果集中只有合计数据,而没有原始的详细记录。
COMPTE生成合计作为附加的汇总列出现在结果集的最后。
当与BY一起使用时,COMPUTE
子句在结果集内生成控制中断和分类汇总。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 数据 查询 更新