《SQL_Server2000_实验指导》课后作业

《SQL Server2000 实验指导》课程作业 课程：《SQL Server 2000实验》 班级：信息管理与信息系统06-2班 姓名：陈龙 学号：2006133305 目录 - 1 -创建数据库jxsk： - 1 -创建jxsk中的 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ： - 1 -插入jxsk库中各表值： - 3 -创建数据库jiaoxuedb： - 3 -创建jiaoxuedb中的表： - 3 -插入jiaoxuedb库中各表值： - 5 -实验4：习题 - 7 -实验5：习题 - 7 -实验6：习题 - 8 -实验7：习题 - 12 -实验8：习题 - 17 -实验9：习题 - 20 -实验10：习题 - 23 -实验11：习题 - 26 -实验12：习题 - 38 -实验13：习题 创建数据库jxsk： CREATE DATABASE jxsk ON (NAME=jxsk_dat,FILENAME='d:\jxsk_dat.mdf') LOG ON (NAME=jxsk_log,FILENAME='d:\jxsk_log.ldf') GO 创建jxsk中的表： USE jxsk CREATE TABLE S( SNO CHAR(2) NOT NULL, SN CHAR(8) NOT NULL, SEX CHAR(2) NULL, AGE TINYINT NULL, DEPT CHAR(10) NULL) CREATE TABLE T( TNO CHAR(2) NOT NULL, TN CHAR(8) NOT NULL, SEX CHAR(2) NULL, AGE TINYINT NULL, PROF CHAR(10) NULL, SAL SMALLINT NULL, COMM SMALLINT NULL, DEPT CHAR(10) NULL) CREATE TABLE C( CNO CHAR(2) NOT NULL, CN CHAR(10) NOT NULL, CT TINYINT NULL, ID_TC TINYINT NULL) CREATE TABLE SC( SNO CHAR(2) NOT NULL, CNO CHAR(2) NOT NULL, SCORE TINYINT NULL) CREATE TABLE TC( TNO CHAR(2) NOT NULL, CNO CHAR(2) NOT NULL) 插入jxsk库中各表值： USE jxsk INSERT INTO S VALUES('S2','钱尔','男',18,'信息') INSERT INTO S VALUES('S3','孙姗','女',20,'信息') INSERT INTO S VALUES('S4','李思','男',21,'自动化') INSERT INTO S VALUES('S5','周武','男',19,'计算机') INSERT INTO S VALUES('S6','吴丽','女',20,'自动化') INSERT INTO S VALUES('S7','李奇','男',18,'外语') INSERT INTO S VALUES('S8','古明','男',NULL,'计算机') INSERT INTO S VALUES('S9','赵亦','女',17,'计算机') INSERT INTO T VALUES('T5','张兰','女',39,'副教授',1300,2000,'信息') INSERT INTO T VALUES('T4','张雪','女',51,'教授',1600,3000,'自动化') INSERT INTO T VALUES('T3','刘伟','男',30,'讲师',900,1200,'计算机') INSERT INTO T VALUES('T2','王平','女',28,'讲师',800,1200,'信息') INSERT INTO T VALUES('T1','李力','男',47,'教授',1500,3000,'计算机') INSERT INTO C VALUES('C1','程序 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 ',60,1) INSERT INTO C VALUES('C2','微机原理',80,2) INSERT INTO C VALUES('C3','数字逻辑',60,3) INSERT INTO C VALUES('C4','数据结构',80,4) INSERT INTO C VALUES('C5','数据库',60,5) INSERT INTO C VALUES('C6','编译原理',60,6) INSERT INTO C VALUES('C7','操作系统',60,7) INSERT INTO SC VALUES('S2','C5',57) INSERT INTO SC VALUES('S2','C6',80) INSERT INTO SC VALUES('S2','C7',NULL) INSERT INTO SC VALUES('S3','C1',75) INSERT INTO SC VALUES('S3','C2',70) INSERT INTO SC VALUES('S3','C4',85) INSERT INTO SC VALUES('S4','C1',93) INSERT INTO SC VALUES('S4','C2',85) INSERT INTO SC VALUES('S4','C3',83) INSERT INTO SC VALUES('S5','C2',89) INSERT INTO SC VALUES('S9','C1',90) INSERT INTO SC VALUES('S9','C2',85) INSERT INTO TC VALUES('T2','C5') INSERT INTO TC VALUES('T3','C1') INSERT INTO TC VALUES('T3','C5') INSERT INTO TC VALUES('T4','C2') INSERT INTO TC VALUES('T4','C3') INSERT INTO TC VALUES('T5','C5') INSERT INTO TC VALUES('T5','C7') INSERT INTO TC VALUES('T1','C1') INSERT INTO TC VALUES('T1','C4') 创建数据库jiaoxuedb： CREATE DATABASE jiaoxuedb ON (NAME=jiaoxuedb_dat,FILENAME='d:\jiaoxuedb_dat.mdf') LOG ON (NAME=jiaoxuedb_log,FILENAME='d:\jiaoxuedb_log.ldf') GO 创建jiaoxuedb中的表： USE jiaoxuedb CREATE TABLE Student( Sno CHAR(10) NOT NULL, Sname CHAR(10) NOT NULL, Sex CHAR(2) NULL, Age TINYINT NULL, Dept CHAR(10) NULL) CREATE TABLE Teacher( Tno CHAR(10) NOT NULL, Tname CHAR(10) NOT NULL, Sex CHAR(2) NULL, Age TINYINT NULL, Prof CHAR(10) NULL, Sal SMALLINT NULL, Comm SMALLINT NULL, Dept CHAR(10) NULL) CREATE TABLE Course( Cno CHAR(10) NOT NULL, Cname CHAR(10) NOT NULL, Chour TINYINT NULL, Ccredit TINYINT NULL) CREATE TABLE SC( Sno CHAR(10) NOT NULL, Cno CHAR(10) NOT NULL, SCORE TINYINT NULL) CREATE TABLE TC( Tno CHAR(10) NOT NULL, Cno CHAR(10) NOT NULL) 插入jiaoxuedb库中各表值： USE jiaoxuedb /*Insert values to Student Table*/ INSERT INTO Student VALUES('001101','宋大方','男',19,'计算机') INSERT INTO Student VALUES('002102','李王','男',20,'信息') INSERT INTO Student VALUES('991101','张彬','男',18,'计算机') INSERT INTO Student VALUES('991102','王蕾','女',19,'计算机') INSERT INTO Student VALUES('991103','张建国','男',18,'计算机') INSERT INTO Student VALUES('991104','李平方','男',18,'计算机') INSERT INTO Student VALUES('991201','陈东辉','男',19,'计算机') INSERT INTO Student VALUES('991202','葛鹏','男',21,'计算机') INSERT INTO Student VALUES('991203','潘桃芝','女',19,'计算机') INSERT INTO Student VALUES('991204','姚一峰','男',18,'计算机') INSERT INTO Student VALUES('001102','许辉','女',22,'计算机') INSERT INTO Student VALUES('001201','王一山','男',20,'计算机') INSERT INTO Student VALUES('001202','牛莉','女',19,'计算机') INSERT INTO Student VALUES('002101','李丽丽','女',19,'信息') /*Insert values to Teacher Table*/ INSERT INTO Teacher VALUES('000006','许红霞','女',39,'讲师',1100,1200,'计算机') INSERT INTO Teacher VALUES('000007','许永军','男',57,'教授',2000,3000,'计算机') INSERT INTO Teacher VALUES('000008','李贵青','女',65,'教授',2000,3000,'计算机') INSERT INTO Teacher VALUES('000009','王一凡','女',43,'讲师',1200,1200,'计算机') INSERT INTO Teacher VALUES('000010','田峰','男',33,'助教',500,800,'信息') INSERT INTO Teacher VALUES('000001','李英','女',39,'副教授',1500,2000,'信息') INSERT INTO Teacher VALUES('000002','张雪','女',51,'教授',1900,3000,'信息') INSERT INTO Teacher VALUES('000003','张朋','男',30,'讲师',1000,1200,'计算机') INSERT INTO Teacher VALUES('000004','王平','女',28,'讲师',850,1200,'信息') INSERT INTO Teacher VALUES('000005','李力','男',47,'教授',1800,3000,'计算机') /*Insert values to Course Table*/ INSERT INTO Course VALUEs('01001','计算机基础',60,3) INSERT INTO Course VALUEs('01002','程序设计',80,5) INSERT INTO Course VALUEs('02003','数据结构',60,6) INSERT INTO Course VALUEs('02001','数据库',80,6) INSERT INTO Course VALUEs('02002','计算机网络',60,6) INSERT INTO Course VALUEs('01003','微机原理',60,8) INSERT INTO Course VALUEs('02004','操作系统',60,6) INSERT INTO Course VALUEs('03001','软件 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 ',60,3) INSERT INTO Course VALUEs('03002','大型数据库',48,2) INSERT INTO Course VALUEs('03003','图像处理',48,2) /*Insert values to SC Table*/ INSERT INTO SC VALUES('991101','01001',88) INSERT INTO SC VALUES('991102','01001',93) INSERT INTO SC VALUES('991103','01001',90) INSERT INTO SC VALUES('991101','01002',90) INSERT INTO SC VALUES('991102','01002',98) INSERT INTO SC VALUES('991103','01002',74) INSERT INTO SC VALUES('991104','01002',85) INSERT INTO SC VALUES('001201','01002',64) INSERT INTO SC VALUES('991104','02001',33) INSERT INTO SC VALUES('991104','01001',35) INSERT INTO SC VALUES('991201','01001',76) /*Insert values to TC Table*/ INSERT INTO TC VALUES('000001','02001') INSERT INTO TC VALUES('000008','02002') INSERT INTO TC VALUES('000003','02001') INSERT INTO TC VALUES('000011','02003') INSERT INTO TC VALUES('000001','01001') INSERT INTO TC VALUES('000002','01002') INSERT INTO TC VALUES('000002','01003') INSERT INTO TC VALUES('000004','02002') INSERT INTO TC VALUES('000005','01001') INSERT INTO TC VALUES('000006','01002') INSERT INTO TC VALUES('000003','01003') 实验4：习题 基于jiaoxuedb实验 1、 对个表创建实体完整性； USE jiaoxuedb ALTER TABLE Student ADD CONSTRAINT PK_SNO PRIMARY KEY(Sno) ALTER TABLE Teacher ADD CONSTRAINT PK_TNO PRIMARY KEY(Tno) ALTER TABLE Course ADD CONSTRAINT PK_CNO PRIMARY KEY(Cno) ALTER TABLE Student ADD CONSTRAINT UNIQUE_S UNIQUE(Sno) ALTER TABLE Teacher ADD CONSTRAINT UNIQUE_T UNIQUE(Tno) ALTER TABLE Course ADD CONSTRAINT UNIQUE_C UNIQUE(Cno) 2、 创建表之间的参照完整性。 USE jiaoxuedb ALTER TABLE SC WITH NOCHECK ADD CONSTRAINT FK_S_SC FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADE ALTER TABLE SC WITH NOCHECK ADD CONSTRAINT FK_C_SC FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADE ALTER TABLE TC WITH NOCHECK ADD CONSTRAINT FK_T_TC FOREIGN KEY(Tno) REFERENCES Teacher(Tno) ON DELETE CASCADE ALTER TABLE TC WITH NOCHECK ADD CONSTRAINT FK_C_TC FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADE 3、 用不同的方法创建下面约束。 （1） “姓名”字段取唯一值： USE jiaoxuedb ALTER TABLE Student ADD CONSTRAINT UNIQUE_SNA UNIQUE(Sname) ALTER TABLE Teacher ADD CONSTRAINT UNIQUE_TNA UNIQUE(Tname) (2) “性别”字段取值为：‘男’或‘女’；默认值：‘男’； USE jiaoxuedb ALTER TABLE Teacher WITH CHECK ADD CONSTRAINT CHECK_T_SEX CHECK(SEX='男' OR SEX='女') ALTER TABLE Teacher ADD CONSTRAINT DF_T_SEX DEFAULT '男' FOR Sex ALTER TABLE Student WITH CHECK ADD CONSTRAINT CHECK_S_SEX CHECK(SEX='男' OR SEX='女') ALTER TABLE Student ADD CONSTRAINT DF_S_SEX DEFAULT '男' FOR Sex （3）“年龄”和“分数”字段取值限定为：0~100之间。 USE jiaoxuedb ALTER TABLE Student WITH CHECK ADD CONSTRAINT CHECK_S_AGE CHECK(AGE>0 AND AGE<100) ALTER TABLE Teacher WITH CHECK ADD CONSTRAINT CHECK_T_AGE CHECK(AGE>0 AND AGE<100) 实验5：习题 1、 对教师表Teacher中的教师号Tno创建聚族索引，并按降序排列。 USE jiaoxuedb CREATE CLUSTERED INDEX IND_TNO ON Teacher(Tno DESC) 2、 对学生选课表SC，先按课号Cno升序排列，再按成绩Score降序排列。 Cno升序： USE jiaoxuedb CREATE CLUSTERED INDEX IND_SC_CNO ON SC(Cno ASC) Score降序： USE jiaoxuedb CREATE CLUSTERED INDEX IND_SC_SCORE ON SC(Score DESC) 3、 对表Student中的学号Sno创建唯一索引，并按升序排列。 USE jiaoxuedb CREATE UNIQUE INDEX IND_S_SNO ON Student(Sno ASC) 实验6：习题 基于jiaoxuedb实验 1、创建成绩视图SCORE_VIEW,包含学号SNO,姓名SN，课程名CN，成绩SCORE。 USE jiaoxuedb GO CREATE VIEW SCORE_VIEW AS SELECT Student.Sno,Sname,Cname,Score FROM Student,Course,SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno GO 2、创建一个计算机系学生名单视图S_VIEW，包含学号SNO，姓名SN，性别SEX。 USE jiaoxuedb GO CREATE VIEW S_VIEW AS SELECT Sno,Sname,Sex FROM Student WHERE Dept='计算机' GO 4、 通过上面的视图，修改学号为991102，课号为01001的成绩记录。 USE jiaoxuedb UPDATE SCORE_VIEW SET Score=100 WHERE Sno='991102' AND Cname= (SELECT Cname FROM Course WHERE Cno='01001') GO 5、创建一个视图，计算机系学生的成绩单SCORE_VIEW_CDEPT,版含学号SNO，姓名SN，课程名CN，成绩SCORE。 USE jiaoxuedb GO CREATE VIEW SCORE_VIEW_CDEPT AS SELECT Student.Sno,Sname,Cname,Score FROM Student,Course,SC WHERE Student.Dept='计算机' AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno GO 6、给视图S_VIEW增加一个年龄AGE字段。 USE jiaoxuedb GO ALTER VIEW S_VIEW AS SELECT Sno,Sname,Sex,Age FROM Student WHERE Dept='计算机' GO 7、创建一个教师工资表视图SAL_VIEW，版含字段教师名Tname，性别Sex，职称Prof，工资总和SALSUM，系别Dept。 USE jiaoxuedb GO CREATE VIEW SAL_VIEW AS SELECT Tname,Sex,Prof,Sal+Comm AS SALSUM FROM Teacher GO 8、通过视图查询教师“张明”的工资收入。 USE jiaoxuedb SELECT Tname,SALSUM FROM SAL_VIEW WHERE Tname='张朋' GO 实验7：习题 基于jiaoxuedb实验 1、 查询成绩在80~90之间的记录。 SELECT Student.Sno,Sname,Dept,Course.Cno,Score FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Score BETWEEN 80 AND 90 AND SC.Cno=Course.Cno; 2、 查询至少有4个同学选修的课程名。 USE jiaoxuedb SELECT Cno AS 课程号,Cname AS 课程名 FROM Course WHERE Cno=ANY (SELECT Cno AS 课程号 FROM SC GROUP BY Cno HAVING COUNT(*)>=4) GO 3、 查询其他系中比“信息系”所有学生年龄都打的学生名单及年龄，并按年龄降序输出。 USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Age>ALL (SELECT Age FROM Student WHERE Dept='信息') AND Dept <> '信息' ORDER BY Age DESC GO 4、 查询与学生张建国同岁的所有学生的学号、姓名和系别。 /*方法一*/ USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Age=ANY (SELECT Age FROM Student WHERE Sname='张建国') AND Sname <> '张建国' ORDER BY Age DESC GO /*方法二*/ USE jiaoxuedb SELECT s2.Sno,s2.Sname,s2.Dept FROM Student s1, Student s2 WHERE s1.age=s2.age AND s1.Sname='张建国' AND s2.Sname <> '张建国' 5、 查询选修了2门以上课程的学生名单。 USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Sno=ANY (SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >=2) GO 6、 查询至少有一门与“张建国”选课相同的学生的姓名、课程名和系别。 USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Sno=ANY (SELECT Sno FROM SC WHERE Cno=ANY (SELECT Cno FROM SC WHERE Sno=’991103’)) AND Sname <> '张建国' GO 7、 查询成绩比该课程平均成绩高的学生的成绩表。 /*方法一*/ USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Sno=ANY (SELECT Sno FROM SC x WHERE Score >= (SELECT AVG(Score) FROM SC y WHERE y.Sno=x.Sno)); GO /*方法二*/ SELECT * FROM SC sc1 WHERE Score > (SELECT AVG(Score) FROM SC sc2) AND sc1.Cno=sc2.Cno; GO 8、 查询选修课号为01001课程且成绩高于课程01002学生的姓名、此两门课程的k而成名和成绩。 /*方法一*/ USE jiaoxuedb SELECT Sname,Cname,Score FROM Student ,SC,Course WHERE Student.Sno= (SELECT s1.Sno FROM SC s1,SC s2 WHERE s1.Cno='01001' AND s2.Cno='01002' AND s1.Score>s2.Score AND s1.Sno=s2.Sno) AND SC.Sno=Student.Sno AND Course.Cno=SC.Cno GO /*方法二*/ USE jiaoxuedb SELECT Sname,Cname,Score FROM Student ,SC,Course WHERE Student.Sno= (SELECT Sno FROM SC x WHERE Score >= (SELECT AVG(Score) FROM SC y WHERE y.Sno=x.Sno AND x.Cno='01001' AND y.Cno='01002') AND SC.Sno=Student.Sno AND Course.Cno=SC.Cno) GO 9、查询所有未修01001号课程的学生名单。 USE jiaoxuedb SELECT Student.Sno,Sname FROM Student WHERE Student.Sno IN (SELECT s1.Sno FROM SC s1 WHERE Sno NOT IN (SELECT s2.Sno FROM SC s2 WHERE Cno='01001')) GO 10、查询每个同学各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩、最高成绩。 USE jiaoxuedb SELECT Sname AS 姓名,AVG(Score) AS 平均,MAX(Score) AS 最高 FROM Student,SC WHERE Student.Sno=SC.Sno GROUP BY Sname ORDER BY Sname DESC GO 11、查询所有学生都选修了的课程号和课程名。 USE jiaoxuedb SELECT Cname AS 课程号,Cno AS 课程名 FROM Course WHERE NOT EXISTS (SELECT * FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno)); GO 12、查询选修了991102号学生选修了的课程的学生学号和姓名。 use jiaoxuedb select distinct sno from sc s1 where not exists(select * from sc s2 where sno='991101' and not exists(select * from sc s3 where s1.sno=s3.sno and s2.cno=s3.cno)) GO 实验8：习题 基于jiaoxuedb实验： 1、利用学生名查询该生选修的课程名、成绩、任课教师名。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Sn' AND TYPE='p') DROP PROCEDURE Pro_Sn GO CREATE PROCEDURE Pro_Sn @sname_in CHAR(10) AS SELECT Student.Sname 学生,Cname 选修课程,Score 分数,Tname 教师 FROM Student,SC,Teacher,Course,TC WHERE Student.Sname=@sname_in AND SC.Sno=Student.Sno AND SC.Cno=Course.Cno AND SC.Cno=TC.Cno AND TC.Cno=Course.Cno AND TC.Tno=Teacher.Tno GO --执行存储过程 USE jiaoxuedb DECLARE @sname_in CHAR(10) SELECT @sname_in='王蕾' EXEC Pro_Sn @sname_in 2、查询某系的学生的最大年龄和最小年龄。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Age_Max_Min' AND TYPE='p') drop procedure Pro_Age_Max_Min GO CREATE PROCEDURE Pro_Age_Max_Min @sdept_in CHAR(10) AS SELECT Dept 系别,MAX(Age) 最大年龄,MIN(Age) 最小年龄 FROM Student WHERE Dept=@sdept_in GROUP BY Dept GO --执行存储过程 USE jiaoxuedb DECLARE @sdept_in CHAR(10) SELECT @sdept_in='计算机' EXEC Pro_Age_Max_Min @sdept_in 3、利用学生姓名和课程名检索该生课程的成绩。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Qscore' AND TYPE='P') DROP PROCEDURE Pro_Qscore GO CREATE PROCEDURE Pro_Qscore @sname_in CHAR(10), @scname_in CHAR(10) AS SELECT Sname 学生姓名,Cname 课程名,Score 分数 FROM Student,SC,Course WHERE Student.sname=@sname_in AND Course.Cname=@scname_in AND SC.Sno=Student.Sno AND SC.Cno=Course.Cno GO --执行存储过程 USE jiaoxuedb DECLARE @sname_in CHAR(10), @scname_in CHAR(10) SELECT @sname_in='王蕾' SELECT @scname_in='程序设计' EXEC Pro_Qscore @sname_in,@scname_in 4、根据职称查询人数，并给出“副教授”的人数。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Prof_Count' AND TYPE='p') DROP PROCEDURE Pro_Prof_Count GO CREATE PROCEDURE Pro_Prof_Count @prof_in CHAR(10) AS SELECT Prof 职称,COUNT(Prof) 人数 FROM Teacher WHERE Prof=@prof_in GROUP BY Prof GO --执行存储过程 USE jiaoxuedb DECLARE @prof_in CHAR(10) SELECT @prof_in='副教授' EXEC Pro_Prof_Count @prof_in 副教授 1 5、统计某系某职称的人数平均年龄、平均工资最高工资。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Dept_Prof' AND TYPE='p') DROP PROCEDURE Pro_Dept_Prof GO CREATE PROCEDURE Pro_Dept_Prof @dept_in CHAR(10), @prof_in CHAR(10) AS SELECT Dept 系别,Prof 职称, COUNT(*) 人数,AVG(AGE) 平均年龄, AVG(SAL) 平均工资,MAX(SAL) 最高工资 FROM Teacher WHERE Dept=@dept_in AND Prof=@prof_in GROUP BY Dept,Prof GO --执行存储过程 USE jiaoxuedb DECLARE @dept_in CHAR(10), @prof_in CHAR(10) SELECT @dept_in='计算机' SELECT @prof_in='教授' EXEC Pro_Dept_Prof @dept_in,@prof_in 6、查询某系的教师人数、平均年龄和学生人数。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Dept_Count' AND TYPE='p') DROP PROCEDURE Pro_Dept_Count GO CREATE PROCEDURE Pro_Dept_Count @dept_in CHAR(10) AS SELECT Dept,COUNT(Teacher.Dept) 教师人数,AVG(Age) 教师平均年龄 INTO #a FROM Teacher WHERE Teacher.Dept=@dept_in GROUP BY Dept SELECT Dept,COUNT(Student.Dept) 学生人数 INTO #b FROM Student WHERE Dept=@dept_in GROUP BY Dept SELECT #a.Dept 系别,教师人数,教师平均年龄,学生人数 FROM #a,#b WHERE #a.Dept=#b.Dept GO --执行存储过程 USE jiaoxuedb DECLARE @dept_in CHAR(10) SELECT @dept_in='信息' EXEC Pro_Dept_Count @dept_in 7、利用课程名查询选修该课程的学生姓名、系别、成绩，并给出“程序设计”课程的该查询信息。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Cname' AND TYPE='p') DROP PROCEDURE Pro_Cname GO CREATE PROCEDURE Pro_Cname @cname CHAR(10) AS SELECT Sname,Sex,Dept,Score FROM Student,SC,Course WHERE Cname=@cname AND SC.Cno=Course.Cno AND SC.Sno=Student.Sno GO --执行存储过程 USE jiaoxuedb DECLARE @cname CHAR(10) SELECT @cname='程序设计' EXEC Pro_Cname @cname 张彬 男 计算机 90 王蕾 女 计算机 98 张建国 男 计算机 74 李平方 男 计算机 85 王一山 男 计算机 64 8、利用教师姓名和课程名检索该教师该任课的课程名、课时数、选课人数、平均成绩、最高成绩，并查询教师“张雪”的“微机原理”课程的情况记录。 --创建存储过程 IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Pro_Tname_Cname' AND TYPE='p') DROP PROCEDURE Pro_Tname_Cname GO CREATE PROCEDURE Pro_Tname_Cname @tname CHAR(10), @cname CHAR(10) AS SELECT Tname,Cname,SUM(Chour) 课时数,COUNT(Sno) 选课人数,AVG(Score) 平均成绩 FROM Teacher,Course,SC,TC WHERE Tname=@tname AND Cname=@cname AND TC.Tno=Teacher.Tno AND TC.Cno=Course.Cno AND SC.Cno=Course.Cno GROUP BY Tname,Cname GO --执行存储过程 USE jiaoxuedb DECLARE @tname CHAR(10), @cname CHAR(10) SELECT @tname='张雪' SELECT @cname='微机原理' EXEC Pro_Tname_Cname @tname,@cname 没有学生选“微机原理”这门课程，故课时数，选课人数，平均成绩，统统为零。 9、删除上面第1题创建的存储过程。 USE jiaoxuedb DROP PROCEDURE Pro_Sn 10、删除上面第3、4题创建的存储过程。 USE jiaoxuedb DROP PROCEDURE Pro_Qscore DROP PROCEDURE Pro_Prof_Count 实验9：习题 基于jxsk实验： 1、为表SC创建一触发器：当插入或修改一个记录时，确保此记录的成绩在0~100之间。 USE jxsk GO IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Score_sc_tri' AND TYPE='tr') DROP TRIGGER Score_sc_tri GO CREATE TRIGGER Score_sc_tri ON SC FOR INSERT,UPDATE AS DECLARE @score_read TINYINT SELECT @score_read=SCORE FROM INSERTED IF @score_read < 0 OR @score_read > 100 BEGIN PRINT '成绩超出0~100！请重新输入。' Rollback Transaction END GO 2、为教师表T创建一触发器：男职工年龄不能超过60周岁，女职工职称是“教授”的年龄不能超过60岁，其他女职工年龄不能超过55岁。 USE jxsk GO IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='Age_T_Tri' AND TYPE='tr') DROP TRIGGER Age_T_Tri GO CREATE TRIGGER Age_T_Tri ON T FOR INSERT,UPDATE AS DECLARE @sex_read CHAR(2) DECLARE @age_read TINYINT DECLARE @prof_read CHAR(10) SELECT @sex_read=SEX FROM INSERTED SELECT @age_read=AGE FROM INSERTED SELECT @prof_read=PROF FROM INSERTED IF ( @sex_read = '男' AND @age_read > 60 ) OR (@sex_read = '女' AND @prof_read = '教授' AND @age_read > 60) OR (@sex_read = '女' AND @prof_read <> '教授' AND @age_read > 60) BEGIN PRINT '数据不合法。' Rollback Transaction END GO 3、为表C、表TC和表SC创建参照完整性：级联删除和级联修改触发器。 --级联删除 USE jxsk GO IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='DC_SC_TC' AND TYPE='tr') DROP TRIGGER DC_SC_TC GO CREATE TRIGGER DC_SC_TC ON C FOR DELETE AS DECLARE @cno_del CHAR(2) SELECT @cno_del=CNO FROM DELETED DELETE FROM SC WHERE CNO=@cno_del DELETE FROM TC WHERE CNO=@cno_del GO --级联更新 USE jxsk GO IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='UC_SC_TC' AND TYPE='tr') DROP TRIGGER UC_SC_TC GO CREATE TRIGGER UC_SC_TC ON C FOR UPDATE AS DECLARE @cno_old CHAR(2) DECLARE @cno_new CHAR(2) SELECT @cno_old=CNO FROM DELETED SELECT @cno_new=CNO FROM INSERTED UPDATE SC SET CNO=@cno_new WHERE CNO=@cno_old UPDATE TC SET CNO=@cno_new WHERE CNO=@cno_old GO --验证级联删除 USE jxsk DELETE FROM C WHERE CNO='C2' GO --验证级联更新 USE jxsk UPDATE C SET CNO='C0' WHERE CNO='C1' GO 4、为表T创建一个触发器：当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元；从“副教授”晋升为“教授”时，岗位津贴自动增加900元。 USE jxsk GO IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='UT' AND TYPE='tr') DROP TRIGGER UT GO CREATE