SQL SERVER 实验习题课.docx
- 文档编号:10584320
- 上传时间:2023-02-21
- 格式:DOCX
- 页数:14
- 大小:16.99KB
SQL SERVER 实验习题课.docx
《SQL SERVER 实验习题课.docx》由会员分享,可在线阅读,更多相关《SQL SERVER 实验习题课.docx(14页珍藏版)》请在冰豆网上搜索。
SQLSERVER实验习题课
p77.10
createtablestudent
(snochar(7)notnullunique,
snamevarchar(20)notnull,
ssexchar
(2)notnull,
sagesmallint,
clnochar(5)notnull);
createtablecourse
(cnochar
(1)notnullunique,
cnamevarchar(20)notnull,
creditsmallint);
createtableclass
(clnochar(5)notnullunique,
specialityvarchar(20)notnull,
inyearchar(4)notnull,
numberinteger,
monitorchar(7));
createtablegrade
(snochar(7)notnull,
cnochar
(1)notnull,
gmarknumeric(4,1));
11.
1altertablestudent
addnationvarchar(20);
2altertablestudent
dropcolumnnation;
3insertintograde
values(‘2001110’,’3’,80);
4updategrade
setgmark=70
wheresno=’2001110’;
5deletefromgrade
wheresno=’2001110’;
6createindexix_class
onstudent(clno);
7dropindexstudent.ix_class;
12.
1selectdistinctcno
fromgrade;
2select*fromstudent
whereclno=’01311’andssex=’女’;
3selectsname,ssex,year(getdate())–sage
fromstudent
whereclnoin(‘01311’,’01312’);
或:
selectsname,ssex,inyear-sagebirthyear
fromstudent,class
wherestudent.clno=class.clnoandclnoin(‘01311’,’01312’);
4select*fromstudent
wheresnamelike‘李%’;
5<一>
selectcount(sno)fromstudent
whereclno=
(selectclnofromstudent
wheresname=’李勇’);
<二>
selectcount(second.sno)
fromstudentfirst,studentsecond
wherefirst.clno=second.clnoandfirst.sname=’李勇’;
或:
<一>
selectnumber
fromstudent,class
wheresname=’李勇’andstudent.clno=class.clno;
<二>
selectnumberfromclass
whereclno=(selectclnofromstudent
wheresname=’李勇’);
6selectavg(gmark),max(gmark),min(gmark)
fromcourse,grade
whereo=oandcname=’操作系统’;
⑦selectcount(distinctsno)fromgrade;
⑧selectcount(sno)fromgrade,course
whereo=oandcname=’操作系统’;
⑨<一>
selectsnamefromstudent,grade,class
wherestudent.sno=grade.snoandstudent.clno=class.clno
andgmarkisnullandspeciality=’计算机软件’
andinyear=’2000’;
<二>
selectsnamefromstudent
whereclnoin(selectclnofromclass
wherespeciality='计算机软件'andinyear='2000')
andsnoin(selectsnofromgradewheregmarkisnull);
13.
①select*fromstudent
whereclnoin
(selectclnofromstudent
wheresname='李勇')
andsname<>'李勇';
②select*fromstudent
wheresnoin
(selectsnofromgrade课程号在李勇所选课程范围内的学号
wherecnoin
(selectcnofromgrade李勇的课程号
wheresnoin
(selectsnofromstudent李勇的学号
wheresname='李勇')))
andsname<>'李勇';
③select*fromstudent
wheresagebetween
(selectsagefromstudent
wheresname='李勇')and25;
④selectsno,snamefromstudent
wheresnoin
(selectsnofromgrade
wherecnoin
(selectcnofromcourse
wherecname=’操作系统’));
⑤selectsnamefromstudent
wheresnonotin
(selectsnofromgrade
wherecno=’1’);
或:
selectsnamefromstudent
wherenotexists子查询查不到则返回.t.
(select*fromgrade
wheresno=student.snoandcno=’1’);
或:
selectsnamefromstudent
wheresno<>all
(selectsnofromgrade
wherecno=’1’);
6selectsnamefromstudent
wherenotexists
(select*fromcourse
wherenotexists
(select*fromgrade
wheresno=student.sno
andcno=o));
或:
selectsname
fromstudent
wheresnoin
(selectsno
fromgrade
groupbysno
havingcount(cno)=
(selectcount(cno)
fromcourse));
14.
1selectsno,gmark
fromgrade
wherecno=’3’
orderbygmarkdesc;
2select*
fromstudent
orderbyclno,sagedesc;
3selectcno,count(sno)
fromgrade
groupbycno;
或:
selectcno,count(cno)
fromgrade
groupbycno;
或:
selectcno,count(*)
fromgrade
groupbycno;
4selectsno
fromgrade
groupbysno
havingcount(*)>=3;
15
①updategrade
setgmark=0
wheresnoin
(selectsnofromstudent
whereclno=’01311’);
updategrade
setgmark=0
wheregrade.sno=student.sno?
?
?
andstudent.clno=’01311’;
②deletefromgrade
wheresnoin
(selectsnofromstudent
whereclnoin
(selectclnofromclass
wherespeciality=’计算机软件’
andinyear=’2001’));
或:
deletefromgrade
wheresnoin
(selectsnofromstudent,class
wherestudent.clno=class.clno
andspeciality=’计算机软件’
andinyear=’2001’);
3deletefromgrade
wheresnoin
(selectsnofromstudent
wheresname=’李勇’);
updateclass
setnumber=number–1
whereclno=李勇所在班人数减1
(selectclnofromstudent
wheresname=’李勇’);
updateclass
setmonitor=null
wheremonitor=李勇是班长的班,班长置为空
(selectsnofromstudent
wheresname=’李勇’);
deletefromstudent
wheresname=’李勇’;
④<方法一>
createtableclassage
(clnochar(5),
avgagesmallint);
insertintoclassage
selectclno,avg(sage)
fromstudent
groupbyclno;
/*因student表中的sage为smallint类型,
其聚合函数avg(sage)返回值仍为smallint
类型,若年龄为20、21,得到的平均年龄
为20(小数位无条件截断),若要精确的
平均值,则将sage定义为带小数位的类型,
如numeric(3,1)*/
<方法二>
altertableclass
addavgageint;
go
updateclass
setavgage=
(selectavg(sage)fromstudent
whereclass.clno=student.clno
groupbyclno);
<方法三>
createviewv_classage
as
selectclno,avg(sage)avgage
fromstudent
groupbyclno
(注意sqlserver中
创建视图命令不能加’;’)
<方法四>
selectclno,avg(sage)avgage
intoclass_age创建一新表class_age
fromstudent
groupbyclno;
16.
①
createviewstu_01312_1
as
selectstudent.*,grade.gmark此处加一gmark便于下一题使用
fromstudent,grade
wherestudent.sno=grade.sno
andclno='01312'andcno='1'
②
createviewstu_01312_2
as
select*
fromstu_01312_1
wheregmark<60
③
createviewstu_year
as
selectsno,sname,2012-sagebirthyear
fromstudent
④
selectsname
fromstu_year
wherebirthyear>=1990;
⑤
selectsno,sname,birthyear
fromstu_year
wheresnoin
(selectsno
fromstu_01312_2);
或用连接写:
selectstu_year.*
fromstu_year,stu_01312_2
wherestu_year.sno=stu_01312_2.sno;
.p98:
10
一.student表
先不建外部码clno,因clno参照class表,而class表又反过来参照student表。
可最后添加外部码clno。
createtablestudent
(snochar(7)primarykey,
snamevarchar(20)notnull,
ssexchar
(2)notnullcheck(ssexin('男','女'))default'男',
sagesmallintcheck(sage>14andsage<65),
clnochar(5)notnull);
二.course表
可象上表那样创建时加上完整性约束,也可在创建完的表上后加完整性约束:
altertablecourseaddprimarykey(cno);[或:
altertablecourseaddconstraintpk_courseprimarykey(cno);]
altertablecourseaddconstraintchk_credcheck(creditin(1,2,3,4,5,6,));
三.class表
altertableclassaddprimarykey(clno);
altertableclassaddconstraintck_numbercheck(number>1andnumber<100);
altertableclassaddforeignkey(monitor)referencesstudent(sno);
建好class表之后,再添加student表的外部码clno:
altertablestudentaddforeignkey(clno)referencesclass(clno);
四.grade表
altertablegradeaddprimarykey(sno,cno);
altertablegradeaddforeignkey(sno)referencesstudent(sno);
altertablegradeaddforeignkey(cno)referencescourse(cno);
altertablegradeaddconstraintck_gmarkcheck(gmark>=0andgmark<=100);
或创建表时加约束:
createtablegrade
(snochar(7)notnull,
cnochar
(1)notnull,
gmarkdecimal(4,1)check(gmark>0andgmark<=100),
primarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),
foreignkey(cno)referencescourse(cno));
或
createtablegrade
(snochar(7)notnullreferencesstudent(sno),
cnochar
(1)notnullreferencescourse(cno),
gmarkdecimal(4,1)check(gmark>0andgmark<=100),
primarykey(sno,cno));
课堂演示
createtablestudent
(snochar(7)notnullunique,
snamevarchar(20)notnull,
ssexchar
(2)notnull,
sagesmallint,
clnochar(5)notnull);
insertintostudent
values('2000101','李勇','男',20,'00311');
insertintostudent
values('2000102','刘诗晨','女',22,'00311');
insertintostudent
values('2000103','王一鸣','男',20,'00312');
insertintostudent
values('2000104','张婷婷','女',22,'00312');
createtablegrade
(snochar(7)notnull,
cnochar
(1)notnull,
gmarkdecimal(4,1));
insertintograde
values('2000101','1',92);
insertintograde
values('2000101','3',88);
insertintograde
values('2000102','1',78);
insertintograde
values('2000102','2',100);
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL SERVER 实验习题课 实验 习题