SQL+SERVER++数据库实验.docx
《SQL+SERVER++数据库实验.docx》由会员分享,可在线阅读,更多相关《SQL+SERVER++数据库实验.docx(13页珍藏版)》请在冰豆网上搜索。
SQL+SERVER++数据库实验
SQLSERVER2000数据库实验
1. 在企业管理器就可以查看刚才建立的数据库“MS”,在查询窗口中选择新建的数据库“MS”,并在MS中新建student,course,SC表。
如下图
1.
1.查询所有学生的全部信息
select*fromstudent
查询所有女生的信息:
查询所有不是计科系的学生:
selectSnamefromstudentwhereSdept!
='CS'
查询选课成绩在80分和90分之间的选课记录
select*fromSCwhereGradebetween80and90;
查询课程名以“数据”开头的课程信息
select*fromCoursewhereCnamelike'数据%';
查询所有具备先行课的课程信息
select*fromCoursewhereCPnoisnotnull
求学生“李勇”的各科平均成绩
一、创建存储过程统计学生总数
createprocCountstudents
as
selectcount(*)
fromstudent
execCountstudents
二、创建存储过程统计各个系的学生总数
createprocCountdeptstudent
as
selectSdept,count(Sno)fromStudent
groupbySdept
execCountdeptstudent
三、创建存储过程实现功能:
输入系名,可查询到该系的学生总数
createprocdeptstudentnumble@namevarchar(10)
as
selectcount(*)
fromStudent
whereSdept=@name
四、创建存储过程实现功能:
输入学生姓名,可查询到该学生的学号、姓名、选课名称、成绩信息。
createprocstudentInformation@nameSnovarchar(10)
as
selectStudent.Sno,Sname,Cname,Grade
fromStudent,SC,Course
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cnoand
Student.Sno=@nameSno
execstudentInformation200215123
<1>建立一个包含学号、姓名、年龄、系别、课程名称、选课成绩属性的视图,取名studentinfo(此视图是建立在student、course、sc基本表基础之上的)
createviewmystudentinfo
as
selectstudent.Sno,Sname,Sage,Sdept,SC.Cno,Grade
fromstudent,Course,SC
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cno
<2>查看视图studentinfo中所有数据
select*frommystudentinfo
<3>在course中新增一个属性(列名)state,类型为varchar,长度为4。
该列的值全为“选修”
altertableCourseADDstatevarchar
updateCourse
setstate='选修'
whereCnoisnotnull
<4>修改course中“数据库”和“信息处理”课程记录的state属性值为“必修”updateCourse
setstate='必修'
whereCname='数据库'orCname='信息系统';
<5>找出所有没有选课记录的学生,将这些学生的所有必修课成绩置为“0”
selectdistinctSno,Sname
from student
wherenotexists
(select*
fromSC
whereSC.Sno=student.Sno)
insertintoSC(Sno,Cno,Grade)values('20021523',1,0);
insertintoSC(Sno,Cno,Grade)values('20021523',3,0);
insertintoSC(Sno,Cno,Grade)values('20021525',1,0);
insertintoSC(Sno,Cno,Grade)values('20021525',3,0);
<6>新建表excellantstudent,属性包括:
学号、姓名、系别、平均成绩
createtableexcellantstudent
(Snochar(9)primarykey,
Snamechar(20)unique,
Sdeptchar(20),
AvgGradesmallint);
<7>将SC中所有平均成绩在80分以上的学生及其相关数据存储到excellantstudent
insertintoexcellantstudent(Sno,Sname,Sdept,AvgGrade)
selectstudent.Sno,Sname,Sdept,Avg(Grade)
fromstudent,SC
wherestudent.Sno=SC.Sno
groupbystudent.Sno,Sname,Sdept
havingAvg(Grade)>80
<8>删除所有成绩为0分的选课记录
deletefromSCwhereGrade=0;
<9>将新开的课程信息存储到数据库中:
数据库应用系统设计、先行课是数据库、学分是2分
insert
intoCourse(Cno,Cname,Cpno,Ccredit,State)
values(8,'数据库应用系统设计',1,2,'必修');
一、用户需求:
课程“数据处理”从现在起废止。
创建存储过程,名为“StorePro2”,完成如下业务逻辑需求(1、2)
1、将所有以“数据处理”为先行课的课程“先行课”属性置为null
2、删除“数据处理”课程信息
CREATEPROCStorePro2
AS
BEGINTRANMyTran
updateCourse
CREATEPROCEDUREStorePro2
AS
BEGINTRANMyTran
update
Course
setCpno=null
whereCpno=(selectCno
fromCourse
whereCname='数据处理')
IF@@ERROR<>0
BEGIN
PRINT'AnErroroccurDuringupdateCoursethetable[Course]'
ROLLBACKTRAN
RETURN
END
delete
from
Course
whereCname='数据处理'
IF@@ERROR<>0/*检测是否成功更新,@@ERROR返回上一个SQL语句状态*/
BEGIN
PRINT'AnErroroccurDuringdeletethetable[Course]'
ROLLBACKTRAN/*回滚*/
RETURN /*退出本过程*/
END
COMMITTRANMyTran/*提交事务*/
GO
execStorePro2
二、创建存储过程完成用户需求:
学号为“200215122”的学生退学了!
(思考业务逻辑需求)
CREATEPROCEDUREleaveSchool
AS
BEGINTRANMyTran
update
Student
setSdept='退学'
whereSno=200215122
IF@@ERROR<>0
BEGIN
PRINT'AnErroroccurDuringupdatethetable[student]'
ROLLBACKTRAN
RETURN
END
deletefrom
SC
whereSno=200215122
IF@@ERROR<>0/*检测是否成功更新,@@ERROR返回上一个SQL语句状态*/
BEGIN
PRINT'AnErroroccurDuringdeletethetable[CourseandSC]'
ROLLBACKTRAN/*回滚*/
RETURN /*退出本过程*/
END
COMMITTRANMyTran/*提交事务*/
GO
execleaveSchool
三、创建存储过程完成用户需求:
输入任一学生学号,完成该学生的退学操作。
/*CREATEPROCEDURECtrl_leave_School
AS*/
createprocstudentInformation@nameSnovarchar(10)
as
BEGINTRANMyTran
selectStudent.Sno,Sname,Cname,Grade
fromStudent,SC,Course
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cnoand
Student.Sno=@nameSno
update
Student
setSdept='退学'
whereSno=@nameSno
IF@@ERROR<>0
BEGIN
PRINT'AnErroroccurDuringupdatethetable[student]'
ROLLBACKTRAN
RETURN
END
deletefrom
SC
whereSno=@nameSno
IF@@ERROR<>0/*检测是否成功更新,@@ERROR返回上一个SQL语句状态*/
BEGIN
PRINT'AnErroroccurDuringdeletethetable[CourseandSC]'
ROLLBACKTRAN/*回滚*/
RETURN /*退出本过程*/
END
COMMITTRANMyTran/*提交事务*/
GO
execstudentInformation200215121
所做的作业
创建yangyongguang200722328数据库
创建STUDENT表
创建COURSE表
创建SC表
在STUDENT表中填写数据
查询所有学生
查询全体学生的姓名,出生年月份和所在的院系,用小写字母表示所有系名。
查询选课成绩在80分和90分之间的选课记录
查询所有具备先行课的课程信息
建立视图V-200722328