SQL实验与练习题.doc

数据库原理与应用（SQL Server）练习题 - 16 - 实验1 数据库操作1创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。 CREATE DATABASE testON PRIMARY(NAME=test_data,FILENAME=E:test_data.mdf,SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)LOG ON(NAME=test_log,FILENAME=E:test_log.ldf,SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=10%)2查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性 exec sp_heipdb test3删除数据库：操作1.3：使用T-SQL语句删除数据库test drop database test实验2 表操作1创建表：操作2.1：创建学生表：USE TESTGOCREATE TABLE student(st_id nVarchar(9) NOT NULL,st_nm nVarchar(8) NOT NULL,st_sex nVarchar(2) NULL,st_birth nVarchar NULL,st_score int NULL,st_date datetime NULL,st_from nChar(20) null,st_dpid nVarchar(2) NULL,stmnt tinyint NULL)表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9Not NullPK学生学号st_nmnVarChar8Not Null学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fromnChar20Null学生来源st_dpidnVarChar2Null所在系编号st_mnttinyintNull学生职务操作2.2：创建课程信息表：表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnVarChar4Not NullPK课程编号cs_nmnVarChar20Not Null课程名称cs_tmintNull课程学时cs_scintNull课程学分操作2.3：创建选课表：FOREIGN KEY表名：slt_couse说明：选课表属性列数据类型长度空值列约束说明cs_idnVarChar4Not NullFK课程编号st_idnVarChar9Not NullFK学生编号scoreintNull课程成绩sltdatedatetimeNull选课日期操作2.4：创建院系信息表：表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChar2Not Null系编号dp_nmnVarChar20Not Null院系名称dp_drtnVarChar8Null院系主任dt_telnVarChar12Null联系电话2修改表结构：（1）向表中添加列：ALTER TABLE deptADD dp_count nvarchar(3) NULL操作2.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）（2）修改列数据类型：ALTER TABLE deptALTER COLUMN dp_count int操作2.6：修改“dept”表的“dp_count”列数据类型为int（3）删除表中指定列： DROP TABLE deptDROP COLUMN dp_count操作2.7：删除“dept”表的“dp_count”列3删除表操作2.8：删除“dept”表4向表中输入数据记录操作2.9：分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录实验3 数据完整性1空值约束( NULL )操作3.1：将student表中的st_sex列属性更改为NOT NULL2默认值约束( DEFAULT )操作3.2：将student表中的st_from列默认值设置为“陕西省”3默认值对象操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。4检查约束( CHECK )操作3.4：将slt_couse表中的score列的检查约束设置为=0且=2008-1-1操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩select * from student where left(st_id,6)=0808082使用逻辑表达式表示查询条件 操作6.5：查询student表中非11系的学生信息select * from student where st_dpid=11操作6.6：查询选修了1002号课程且成绩在60以下的学生学号select st_id from slt_couse where cs_id=1002 and score75操作8.11：查询选修了2门以上课程的学生学号select st_id,count(cs_id) from slt_cousegroup by st_idhaving count(cs_id)2操作8.12：明细汇总年龄20的学生，并汇总学生数量、平均年龄select count(st_id),avg(year(getdate()-year(st_birth) from student where year(getdate()-year(st_birth)20操作8.13：按班级明细汇总成绩85实验9 数据查询（5）连接查询操作9.1：用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号select a.st_id,st_nm,st_sex,cs_id from student a,slt_couse b where a.st_id=b.st_id操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号select a.st_id,st_nm,st_sex,cs_id from student a join slt_couse b on a.st_id=b.st_id操作9.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩select a.st_id,st_nm,cs_nm,st_score from student a,couse b,slt_couse c where a.st_id=c.st_id and c.cs_id=b.cs_id操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩select a.st_id,st_nm,cs_nm,st_score from student a join slt_couse c on a.st_id=c.st_id,slt_couse join couse b on slt_couse.cs_id=b.cs_id（有问题）select a.st_id,st_nm,cs_nm,st_score from slt_couse c join student a on a.st_id=c.st_id join couse b on c.cs_id=b.cs_id(对的)操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩select st_id,st_nm,st_score from student a where a.st_id in (select b.st_id from slt_couse b where cs_id=1001 or cs_id=1002)操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩select st_id,st_nm,st_score from student a where a.st_id in (select b.st_id from slt_couse b where b.cs_id in (select c.cs_id from couse c where cs_nm=数据结构）操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名select student.st_id,st_nm from student left outer join slt_couse on student.st_id=slt_couse.st_id where slt_couse.score=null（）（）（）操作9.8：用右外连接查询选修各个课程的学生学号select student.st_id from student right outer join slt_couse on student.st_id=slt_couse.st_id order by student.st_id（）（）（）实验10 数据查询（6）子查询操作10.1：用子查询对各班人数进行查询（新增列）select distinct left(a.st_id,6) as 班级,人数=(select count(st_id) from student b where left(a.st_id,6)=left(b.st_id,6) from student a操作10.2：用子查询对各课程的选课人数进行查询（新增列）select distinct a.cs_id as 课程,人数=(select count(st_id) from slt_couse b where a.cs_id=b.cs_id) from slt_couse a操作10.3：查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序select a.st_id,st_nm from student a where (select score from slt_couse b where cs_id=1002 and a.st_id=b.st_id)any (select score from slt_couse b where left(b.st_id,6)=070511 and cs_id=1002)操作10.6：查询其它班比070511班任一学生的1002号课程成绩高的学生信息（ANY/ALL）操作10.7：查询大于等于60分且且比1003课程平均成绩低的学生课程信息（BetweenAnd）select st_id, cs_id,score from slt_couse a where a.score between 60 and (select avg(score) from slt_couse b where b.cs_id=1003)select * from student a where a.st_score between 60 and (select avg(score) from slt_couse b where b.cs_id=1003)操作10.8：查询系主任为“赵虎”的系的所有学生信息select * from student a where a.st_dpid in (select dp_id from dept b where dp_drt=赵大虎)通过子查询实现：IN运算符通过子查询实现：=运算符实验11 数据查询（7）数据更新与子查询操作11.1：将070511班所有学生信息插入到表student01(st_id,st_nm,st_sex)create table student01(st_id nVarchar(9),st_nm nVarchar(8), st_sex nVarchar(2)insert into student01select st_id,st_nm,st_sex from student where left(st_id,6)=070511操作11.2：生成1002号课程的成绩单student02(st_id,st_nm, score)create table student002(st_id nVarchar(9),st_nm nVarchar(8),st_score int)insert into student002select st_id,st_nm,st_score from student a where a.st_id in (select st_id from slt_couse b where cs_id=1002)操作11.3：将有不及格成绩的学生的st_mnt值更改为3update couse a set cs_sc=3where a.cs_id in (select b.st_id from slt_couse b操作11.4：将没有被选修的课程的学分更改为0操作11.5：删除5系学生的选课信息操作11.6：删除学分为0的选课信息实验12 事务和锁实验13 索引例6.1 在student表创建非聚集索引CREATE INDEX idx_std ON student( st_nm )例6.2 在student表创建复合索引CREATE INDEX idx_stdON student( st_id , st_nm )WITH DROP_EXISTING例6.3 在student表创建唯一非聚集索引CREATE UNIQUE NONCLUSTERED INDEX idx_stdON student( st_id )WITH DROP_EXISTING例6.4 使用填充因子创建唯一非聚集索引CREATE UNIQUE NONCLUSTERED INDEX idx_stdON student( st_id )WITH PAD_INDEX , FILLFACTOR = 50 , DROP_EXISTING例6.5查看student表的索引Exec sp_helpindex student例6.6 更改student表的索引名Exec sp_rename student.idx_stut, idx_st00, index例6.7 删除student表的索引DROP INDEX student.idx_stid实验14 视图例6.8 创建学生情况视图CREATE VIEW view_studentAS SELECT st_id, st_nm, st_sex, st_dpid FROM student例6.9 创建院系情况视图CREATE VIEW view_deptAS SELECT dp_id, dp_nm FROM dept例6.10 创建课程情况视图CREATE VIEW view_couseAS SELECT cs_id, cs_nm FROM couse WHERE a.st_id = c.st_id AND b.cs_id = c.cs_id例6.11 创建学生选课情况视图CREATE VIEW std_sltASSELECT a.st_id, a.st_nm, a.st_sex, b.cs_id, b.scoreFROM student a, slt_couse bWHERE a.st_id = b.st_id例6.12 由view_student视图创建01系学生情况视图CREATE VIEW view_student01AS SELECT st_id, st_nm, st_sex, st_dpid WHERE st_dpid = 01 FROM view_student在创建视图时，使用SELECT *子句，则当基表中的列发生变化时，此变化不会显示在视图中例6.13 创建视图时使用SELECT *子句CREATE VIEW vw_student ASSELECT * FROM studentGOSELECT * FROM vw_studentGOALTER TABLE student ADD st_tel varChar(12) NULLGOSELECT * FROM vw_studentGO例6.14修改std_slt视图CREATE VIEW std_slt AS SELECT a.st_id, a.st_nm, a.st_sex, b.cs_id, b.cs_nm, c.score FROM student a, couse b, slt_couse c WHERE a.st_id = c.st_id AND b.cs_id = c.cs_idGO例6.15把视图std_slt重命名为view_stdsp_rename std_slt, view_std例6.16删除std_slt视图DROP VIEW std_slt例6.17查询view_dept视图中所有院系的编号及其名称SELECT * FROM view_dept例6.18查询view_student视图中所有01系的学生SELECT * FROM view_studentWHERE st_dpid = 01例6.19查询std_slt视图中所有选修1001号课程的学生SELECT * FROM view_studentWHERE cs_id = 1001例6.20向view_student视图中插入学生记录INSERT INTO view_studentVALUES ( 070511121, 李丽, 女, 01)例6.21向view_student01视图中插入其他系所有女生记录INSERT INTO view_student01( SELECT * FROM view_student WHERE st_sex = 女 AND st_id 01 )例6.22将view_dept视图中01系的系名更改为信息管理系UPDATE view_dept SET dp_nm =信息管理系 WHERE dp_id = 01例6.23将std_slt视图中学生张三的操作系统课程成绩更改为75UPDATE std_slt SET score = 75WHERE st_nm = 张三 AND cs_nm = 操作系统例6.24删除view_student视图中学生李丽的信息DELETE FROM view_student WHERE st_nm = 李丽例6.25删除std_slt视图中学生张三对操作系统课程的选课信息DELETE FROM std_sltWHERE st_nm = 张三 AND cs_nm = 操作系统实验15 游标统计“多媒体技术”考试成绩的各分数段的分布情况。DECLARE course_cursor CURSOR FOR -声明游标SELECT score FROM slt_couseWHERE cs_id = ( SELECT cs_id FROM couse WHERE cs_nm = 多媒体技术 )DECLARE p_100 SMALLINT, p_90 SMALLINT, p_80 SMALLINTDECLARE p_70 SMALLINT, p_60 SMALLINT, p_others SMALLINTDECLARE p_grade SMALLINTSET p_100 = 0SET p_90 = 0SET p_80 = 0SET p_70 = 0SET p_60 = 0SET p_others = 0SET p_grade = 0OPEN course_cursor - 打开游标LOOP :FETCH NEXT FROM course_cursor INTO p_grade -使用游标提取数据IF ( p_grade = 100 ) SET p_100 = p_100+1ELSE IF ( p_grade = 90 ) SET p_90 = p_90+1ELSE IF ( p_grade = 80 ) SET p_80 = p_80+1ELSE IF ( p_grade = 70 ) SET p_70 = p_70+1ELSE IF ( p_grade = 60 ) SET p_60 = p_60+1ELSE SET p_others = p_others+1IF ( FETCH_STATUS = 0 ) GOTO LOOPPRINT STR( p_100 ) + , + STR( p_90 ) + , + STR( p_80 ) + ,PRINT STR( p_70 ) + , + STR( p_60 ) + , + STR( p_others )CLOSE course_cursor - 关闭游标DEALLOCATE course_cursor - 释放游标实验16 存储过程【示例7.1】针对学生表student，创建一个名称为student_proc的存储过程，该存储过程的功能是从数据表student中查询所有男学生的信息。USE testGOCREATE PROCEDURE proc_studentASSELECT * FROM student WHERE st_sex = 男GO【示例7.2】创建一个带参数的存储过程USE testGOCREATE PROCEDURE proc_stu_classclassnm nvarchar(6) = 070511ASSELECT * FROM student WHERE left(st_id,6) = classnmGO【示例7.3】创建一个带输入输出参数的存储过程USE testGOCREATE PROCEDURE proc_stuinforstuid nvarchar(9) ,stunm nvarchar(8) OUTPUT ,stuscore int OUTPUTASSELECT stunm=st_nm, stuscore=st_score FROM studentWHERE st_id = stuidGO【示例7.4】存储过程参数中使用通配符USE testGOCREATE PROCEDURE proc_stuwangstunm nvarchar(8) = 王%ASSELECT * FROM student WHERE st_nm LIKE stunmGO【示例7.5】执行不带参数的存储过程EXEC proc_student【示例7.6】执行带一个参数的存储过程EXEC proc_stu_class 070512【示例7.7】执行一个带输入输出参数的存储过程DECLARE stu_name NVARCHAR(8) , stu_score INTEXEC proc_stuinfor 070511001 , stu_name OUTPUT , stu_score OUTPUTSELECT stu_name , stu_score【示例7.8】执行参数中使用通配符的存储过程EXEC proc_stuwang 张%【示例7.9】查看存储过程proc_student的详细信息EXEC sp_helptext proc_student实验17 自定义函数【示例7.10】自定义标量函数，实现对学生成绩的评价CREATE FUNCTION fc_grade( score INT )RETURNS nVarChar(3)BEGINDECLARE grade nVarChar(3)IF score = 90SET grade = 优秀ELSE IF score = 80SET grade = 良好ELSE IF score = 70SET grade = 中等ELSE IF score = 60SET grade = 及格ELSESET grade = 不及格RETURN gradeEND函数的使用：SELECT a.st_id , a.st_nm , b.cs_id , b.cs_nm , c.score , dbo.fc_grade( c.score )FROM student a , couse b , slt_couse cWHERE a.st_id = c.st_id AND b.cs_id = c.cs_id【示例7.11】自定义内嵌表值函数，根据指定的班级查询此班所有的学生信息CREATE FUNCTION fc_class( class nVarChar(6) )RETURNS tableASRETURN( SELECT * FROM student WHERE LEFT( st_id,6 ) = class )函数的使用：SELECT * FROM dbo.fc_class( 070512 )【示例7.12】自定义多语句表值函数，根据输入的课程名称返回选修该课程的学生姓名和成绩CREATE FUNCTION fc_chengji( stuid nVarChar(9) )RETURNS chji TABLE(st_id nVarChar(9),st_nm nVarChar(8),cs_nm nVarChar(20),score INT)ASBEGININSERT chjiSELECT a.st_id , a.st_nm , b.cs_nm , c.scoreFROM student a INNER JOIN slt_couse c ON a.st_id = c.st_idINNER JOIN couse b ON b.cs_