SQl 实验.docx
《SQl 实验.docx》由会员分享，可在线阅读，更多相关《SQl 实验.docx（16页珍藏版）》请在冰豆网上搜索。
SQl实验
第七章:
1.查询成绩在80.90之间的记录.
selectstudent.sno,sn,cn,score,deptfromsc,course,student
wherestudent.sno=sc.snoando=o
andscorebetween80and90
2.查询至少有4个同学选修的课程名.
selectcnfromsc,course
whereo=o
groupbyo,cnhavingCOUNT（sno）>=4
3.查询其他系中比信息系所有学生年龄都大的学生名单及年龄,并按年龄降序输出.
方法1:
selectsn,agefromstudent
whereage>（selectMAX（age）fromstudentwheredept='信息'）
orderbyagedesc
方法2:
selectsn,agefromstudent
whereage>all（selectagefromstudentwheredept='信息'）
anddept<>'信息'orderbyagedesc
4.查询与学生张建国同岁的所有学生的学号,姓名和系别.
selectsno,sn,deptfromstudent
whereage=（selectagefromstudent
wheresn='张建国'）andsn<>'张建国'
orderbyagedesc
5.查询选修了两门以上课程的学生名单.
方法1:
selectsnfromstudent,scwherestudent.sno=sc.sno
groupbysc.sno,snhavingCOUNT（cno）>2
方法2:
selectsnfromstudentwheresno=（selectsnofromscgroupbysnohavingcount（*）>2）
6.查询至少有一门课程与张建国选课相同的学生的姓名，课程和系别.
方法1：
selectsn,cn,deptfromstudent,sc,coursewherestudent.sno=sc.snoando=oandoin（selectcnofromscwheresno=（selectsnofromstudentwheresn='张建国'））andsn<>'张建国'
方法2：
selectsn,cn,deptfromstudent,sc,coursewherestudent.sno=sc.snoando=oandoin（selectcnofromstudent,scwherestudent.sno=sc.snoandsn='张建国'）andsn<>'张建国'
7.查询成绩比该课程平均成绩高的学生的成绩表
select*fromscsc1wherescore>（selectavg（score）fromscsc2whereo=o）
8.查询选修了课号为01001的课程且成绩高于课程号为01002的课程的学生的姓名，此两门课程的课程名和成绩。
selectsn,cn,scorefromstudent,course,sc
wheresc.sno=student.snoando=o
andsc.snoin（selectsc1.snofromscsc1,scsc2wheresc1.sno=sc2.sno
ando='01001'ando='01002'andsc1.score>sc2.score）
9.查询所有未修01001号课程的学生名单。
方法1：
selectsnfromstudent,scwherestudent.sno=sc.sno
andsc.snonotin（selectsnofromscwherecno='01001'）
方法2：
selectsnfromstudentwherestudent.snoin
（selectsc1.snofromscsc1wheresnonotin
（selectsc2.snofromscsc2wherecno='01001'））
10.查询每个学生各门课程的平均成绩和最高成绩，按降序排列输出姓名，平均成绩和最高成绩。
selectsn,AVG（score）,MAX（score）fromstudent,sc
wherestudent.sno=sc.snogroupbysc.sno,sn
orderbysndesc,AVG（score）desc,MAX（score）desc
11.查询所有学生都选修了的课程的课程号和课程名。
方法1：
selectcno,COUNT（*）fromscgroupbycno
havingCOUNT（*）=（selectCOUNT（*）fromstudent）
方法2：
selectcno,cnfromcoursewherenotexists（
select*fromstudentwherenotexists（select*fromscwherestudent.sno=sc.snoando=o））
12．查询选修了991102号学生选修了的课程的学生的学号和姓名。
selectsno,snfromstudent
wheresnoin（selectdistinctsnofromscsc1wherenotexists（
select*fromscsc2wheresno='991101'and
notexists（select*fromscsc3whereo=o
andsc1.sno=sc3.sno）））
作业题：
选择不选01002课程学生的学号：
selectsnofromscwheresnonotin（selectsnofromscwherecno='01002'）
查询选了01001，没有选01002的学生的学号
selectsnofromscwherecno='01001'andsnonotin（selectsnofromscwherecno='01002'）
等同于：
selectsnofromscwherecno='01001'andsnonotin（selectsc1.snofromscsc1wherecno='01002'）
查询选了01001，没有选01002的学生的姓名，学号和专业
selectsn,sno,deptfromstudentwheresnoin（
selectsnofromscwherecno='01001'andsnonotin（selectsnofromscwherecno='01002'））
查询选了01001又选了01002的学生的学号：
方法1：
selectsnofromscwherecno='01001'andsnoin（selectsnofromscwherecno='01002'）
方法2：
selectsc1.snofromscsc1,scsc2wheresc1.sno=sc2.snoando='01001'ando='01002'
第八章：
习题
1．利用学生姓名查询该生选修的课程名，成绩，以及任课老师姓名。
CREATEPROCEDUREpro_sn@snchar（10）
asselectcn,score,tnfromstudent,sc,course,tc,teacher
wherestudent.sno=sc.snoando=o
andteacher.tno=tc.tnoo=o
ando=oandsn=@sn
declare@snchar（10）
select@sn='王蕾'
execpro_sn@sn
2.查询某系的学生的最大年龄和最小年龄。
createprocedurepro_age@deptchar（10）
asselectmax（age）,min（age）fromstudent
wheredept=@dept
declare@deptchar（10）
select@dept='计算机'
execpro_age@dept
3.利用学生的姓名和课程名检索该生该课程的成绩。
createprocedurepro_score@snchar（10）,@cnchar（10）
asselectscorefromstudent,sc,course
wherestudent.sno=sc.snoando=oand
sn=@snandcn=@cn
declare@snchar（10）,@cnchar（10）
select@sn='张彬'
select@cn='计算机基础'
execpro_score@sn,@cn
4.根据职称查询人数，并给出副教授的人数
createprocedurepro_count@profchar（10）
asselectprof职称,COUNT（prof）人数fromteacher
whereprof=@profgroupbyprof
declare@profchar（10）
select@prof='教授'
execpro_count@prof
5.统计某系某职称的人数，平均年龄，平均工资，最高工资。
加专业和职称时dept专业,prof职称，需用groupby语句
createprocedurepro_n@deptchar（10）,@profchar（10）
asselect
count（tno）人数,avg（age）平均年龄,
avg（sal）平均工资,max（sal）最高工资
fromteacher
wheredept=@deptandprof=@prof
declare@deptchar（10）,@profchar（10）
select@dept='计算机'
select@prof='讲师'
execpro_n@dept,@prof
6.查询某系的教师人数,平均年龄和学生人数
createprocedurepro_nage@deptchar（10）
asselectdept,COUNT（teacher.dept）教师人数,
avg（age）教师平均年龄
into#afromteacherwhereteacher.dept=@deptgroupbydept
selectdept,COUNT（student.dept）学生人数
into#bfromstudentwherestudent.dept=@deptgroupbydept
select#b.dept系别,教师人数,教师平均年龄,学生人数
from#a,#b
where#a.dept=#b.dept
declare@deptchar（10）
select@dept='计算机'
execpro_nage@dept
7.利用课程名查询选修该课程的学生姓名，系别，成绩，并给出‘程序设计’课程该查询信息。
createprocedurepro_inf@cnchar（10）
asselectsn,dept,scorefromstudent,sc,course
wherestudent.sno=sc.snoando=o
andcn=@cn
declare@cnchar（10）
select@cn='程序设计'
execpro_inf@cn
8.利用教师姓名和课程名检索该教师该任课的课程名，课时数，选课人数，平均成绩，最高成绩，并查询教师‘张雪’的‘微机原理’课程的情况记录。
createprocedurepro_information@tnchar（10）,@cnchar（10）
asselectcn课程名,ct课时数,COUNT（sno）选课人数,
AVG（score）平均成绩,MAX（score）最高成绩
fromteacher,sc,course,tc
whereteacher.tno=tc.tnoando=o
ando=oando=oandtn=@tn
andcn=@cngroupbytn,cn,ct
declare@tnchar（10）,@cnchar（10）
select@tn='李英'
select@cn='计算机基础'
execpro_information@tn,@cn
9.删除实验1创建的存储过程
dropprocedurepro_sn
10.删除实验3和4创建的存储过程
第九章：
练习
（1）修改学生表里的学号，选课表里的学号也自动修改。
ifobject_id（'trigger_s','tr'）isnotnull
droptriggertrigger_s
CREATETRIGGERtrigger_s
ONstudentforupdate
AS
BEGIN
declare@sno1char（10）,@sno2char（10）
select@sno1=snofromdeleted
select@sno2=snofrominserted
updatescsetsno=@sno2wheresno=@sno1
END
GO
updatestudentsetsno='007'wheresn='张彬'
（2）修改选课表里的学号，则要检查学生表中是否存在与该学号相同的记录，若存在则不许修改，若不存在则可修改。
usejiaoxuedb
ifOBJECT_ID（'tri_sc','tr'）isnotnull
droptriggertri_s
CREATETRIGGERtri_sc
ONscforupdate
AS
BEGIN
declare@nint,@snochar（10）
select@sno=snofromdeleted
select@n=count（*）fromstudentwheresno=@sno
if@n<>0
rollbacktransaction
END
GO
（3）当插入一个记录或修改成绩时，确保此记录的成绩在0到100之间。
（书上）
usejiaoxuedb
ifobject_id（'tri_sc2','tr'）isnotnull
droptriggertri_sc2
CREATETRIGGERtri_sc2
ONscforinsert,update
AS
declare@scoreint
select@score=scorefrominserted
if@score>=0and@score<=100
beginprint'输入成功'
return
end
print'成绩不合法'
rollbacktransaction
GO
方法二：
CREATETRIGGERtri_sc2
ONscforinsert,update
AS
declare@scoreint
select@score=scorefrominserted
if@score<0or@score>100
begin
print'成绩不合法'
rollbacktransaction
end
GO
updatescsetscore=19wheresno='001'andcno='01002L'
insertintoscvalues（'s9','01001','190'）
insertintoscvalues（'s9','01001','90'）
（4）当从课程表里删除某课程的信息时，同时删除教师选课表和学生选课表里与此课程相关的信息。
usejiaoxuedb
ifOBJECT_ID（'tri_c','tr'）isnotnull
droptriggertri_c
CREATETRIGGERtri_c
ONcoursefordelete
AS
BEGIN
declare@cnochar（10）
select@cno=cnofromdeleted
deletefromscwherecno=@cno
deletefromtcwherecno=@cno
END
GO
deletefromcoursewherecno='01001'
习题：
（2）在教师表里，男职工年龄不能超过60周岁，女职工职称是教授的年龄不能超过60周岁，其它女职工不能超过55周岁。
ifOBJECT_ID（'tri_t','tr'）isnotnull
droptriggertri_t
createtriggertri_t
onteacherforinsert,update
asdeclare@sexchar（10）,@ageint,@profchar（10）
select@sex=sexfrominserted
select@age=agefrominserted
select@prof=proffrominserted
if（@sex='男'and@age<=60）or（@sex='女'and@prof='教授'and@age<=60）
or（@sex='女'and@prof!
='教授'and@age<=55）
begin
print'输入数据合法'
return
end
print'输入数据不合法'
rollbacktransaction
方法二：
createtriggertri_t
onteacherforinsert,update
asdeclare@sexchar（10）,@ageint,@profchar（10）
select@sex=sexfrominserted
select@age=agefrominserted
select@prof=proffrominserted
if（@sex='男'and@age>60）or（@sex='女'and@prof='教授'and@age>60）
or（@sex='女'and@prof!
='教授'and@age>55）
begin
print'输入数据不合法'
rollbacktransaction
end
go
（3）为课程表，教师选课表和学生选课表创建参照完整性：
级联删除和级联修改触发器。
ifOBJECT_ID（'tri_3','tr'）isnotnull
droptriggertri_3
createtriggertri_3
oncoursefordelete,update
asbegin
declare@cno1char（10）,@cno2char（10）
select@cno1=cnofromdeleted
select@cno2=cnofrominserted
if@cno2isnotnull
begin
updatescsetcno=@cno2wherecno=@cno1
updatetcsetcno=@cno2wherecno=@cno1
return
end
deletefromscwherecno=@cno1
deletefromtcwherecno=@cno1
end
go
updatecoursesetcno='008'wherecn='程序设计'
（4）为数据库表创建一个触发器，当职称从讲师晋升为副教授时，岗位津贴自动增加500元，从副教授晋升为教授时，岗位津贴自动增加900元
ifOBJECT_ID（'tri_prof','tr'）isnotnull
droptriggertri_prof
createtriggertri_profonteacherforupdate
as
begin
declare@prof1char（10）,@prof2char（10）,@tnochar（10）
select@prof1=proffromdeleted
select@prof2=proffrominserted
select@tno=tnofrominserted
if@prof1='讲师'and@prof2='副教授'
updateteachersetcomm=comm+500wheretno=@tno
if@prof1='副教授'and@prof2='教授'
updateteachersetcomm=comm+900wheretno=@tno
end
updateteachersetprof='教授'wheretno=000001
第十一章：
习题
（3）创建一个函数，要求：
根据学生姓名和课程名查询该生该课程的成绩。
（标量值函数）
usejiaoxuedb
ifOBJECT_ID（'fun_t','fn'）isnotnull
dropfunctionfun_t
go
CREATEFUNCTIONfun_t
（@snchar（10）,@cnchar（10））
RETURNSint
AS
BEGIN
DECLARE@scoreint
SELECT@scor
升级会员 