SQLServerSQL实验与练习题参考答案

1、数据库原理与应用(SQL Server)练习题1 -实验1数据库操作1. 创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data,物理文件名test_data.mdf,初始人小 10MB,最大尺寸为无限大，增长速度1MB：数据库口志文件逻辑名称为test Jog,物理文件名为testjog.ldf, 初始大小为1ME,最大尺寸为5ME,增长速度为10%。参考答案：create database TestON primary(name = test_data,filename = 1d:testmdf1,size = 5MB,maxsize = unlimitedf

2、filegrowth = 1MB)LOG ON(name = test_log,filename = 1d:testtest_1ogldf1,size = lMBfmaxsize = 5MB,filegrowth = 10%)GO2. 查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性参考答案：EXEC sp_helpdb t est3. 删除数据库：操作1.3：使用T-SQL语句删除数据库test参考答案：drop database Test实验2表操作创建表：数据库原理与应用(SQL Server)练习题# -数据库原理与应用(SQL Server)练习题# -操作2.1

3、：创建学生表:表名：student说明：学生基本信息表数据库原理与应用(SQL Server)练习题3 -属性列数据类型长度空值列约束说明st_idnVarChai9Not NullPK学生学号st_nmnVarChai8Not Null学生姓名st_sexnVarChai2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fiomnChar20Null学生来源st_dpidnVarChai2Null所在系编号st_mnttinvint/Null学生职务参考答案:USE testGOCREA

4、TE TABLE studentstidnVarChar(9)primary key NOT NULL ,stnmnVarChar(8)NOT NULL ,stsexnVarChar(2)NULL zstbirthdatetimeNULL zstscoreintNULL zstdatedatetimeNULL zstfromnVarChar(20)NULL zstdpidnVarChar(2)NULL zstmnttinyintNULL)GO操作2.2：创建课程信息表:表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnVarChai4Not NullPK课程编号数据库

5、原理与应用(SQL Server)练习题11 -cs_nmnVarChai20Not Null课程名称cs_tmintNull课程学时cs_scintNull课程学分参考答案：USE testGOCREATE TABLE couseCS_idnVarChar(4)primary key NOT NULL .CSnmnVarChar(20)NOT NULL ,CStmintNULL zCSscintNULL)GO操作2.3：创建选课表：表名：slt_couse说明：选课表属性列娠类型长度空值列约束说明cs_idnVarChar4Not NullFK课程编号st_idnVarChar9Not Nu

6、llFK学生编号scoreintNull课磁绩sitdatedatetimeNull选课日期参考答案:USE testGOCREATE TABLE cousecs idnVarChar(4)NOT NULL ,st idnVarChar(9)NOT NULL ,scoreintNULL zsitdatedatetimeNULL)GO表名:dept操作24创建院系信息表:说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChai2Not Null系编号dp_nmnVarChai20Not Null院系名称dp_drtnVarChai8Null院系主任dt_telnVarChai1

7、2Null联系电话参考答案：USE testGONOT NULL ,NOT NULL ,NULL zNULLCREATE TABLE deptdp_id nVarChar(2) dp_nm nVarChar(20) dp_drt nVarChar(8) dp_tel nVarChar(12)GO1. 修改表结构：(1) 向表中添加列：操作2.5：为“dept"表添加“dp_count”列(数据类型为nvaichai-,长度为3,允许为空) 参考答案：ALTER TABLE dept ADD dp_count nvarchar(3) NULL(2) 修改列数据类型：操作2.6：修改“d

8、ept"表的"dp_count”列数据类型为int参考答案：ALTER TABLE dept ALTER COLUMN dp_count int NULL(3) 删除表中指定列：操作2.7：删除“dept”表的“dp_count”列参考答案：ALTER TABLE dept DROP COLUMN dp_count3删除表操作2.8：删除“dept”表 参考答案：DROP TABLE student4. 向表中输入数据记录操作29 分别向"student”表、couse”表、lt_couse"表、"dept”表中输入数据记录实验3数据完整性1空

9、值约束(NULL )操作3.1 ：将student表中的st_sex列属性更改为NOT NULL参考答案：ALTER TABLE student ALTER COLUME st_nm nVarChar(8) NOT NULL2. 默认值约束(DEFAULT )操作3.2：将student表中的st.fiom列默认值设置为“陕西省”参考答案：ALTER TABLE student ADD DEFAULT '陕西省，FOR st_from3. 默认值对象操作3.3：创建默认值对象dOoday为当前口期，并将其绑定到slt.couse表中的sitdate列，然后取消 绑定，最后删除默认值对彖

10、dfltodayo参考答案：CREATE DEFAULT df_today AS Getdate()GOEXEC sp_bindefault df_todayz 1slt_couse sitdate1GOEXEC sp_unbindefault 1slt_couse sitdate 1GODROP DEFAULT df_todayGO4. 检查约束(CHECK )操作3.4：将slt_couse表中的score列的检查约束设置为>=0且<=100参考答案：ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100)5

11、规则约束对象操作3.5：创建规则约束对彖H_sex,用于检查性别的取值仅限于“男”和“女”，并将其绑定到student 表中的st_sex列，然后取消绑定，最后删除规则约束对彖rl_sexo参考答案：CREATE RULE rl sex AS Qchksex '男'OR chksex='女'或CREATE RULE rl_sex AS Gchksex IN ('男'女')GOEXEC sp_bindrule rl_sexz 1 student st_sex1GOEXEC sp_unbindrule 1 student st_sex1GOD

12、ROP RULE rl_sexGO6. 主键操作3.6：将dept表中的dp_id列设置为主键参考答案：ALTER TABLE dept ADD PRIMARY KEY (dp_id)7. 唯一性约束(UNIQUE )操作3.7：将dept表中的dp_mn列设置为唯一性约束 参考答案：ALTER TABLE dept ADD UNIQUE (dp_nm)&标识列操作3.8：向slt.couse表中添加标识列id,第1行默认值为1,相邻两个标识列间的增量为1 参考答案：ALTER TABLE slt_couse ADD id INT IDENTITY 1,1) NOT NULL9.外键(

13、FOREIGN KEY )操作3.9：被参照表为dept,参照表为student参考答案：ALTER TABLE studentADD FOREIGN KEY (st_dpid) REFERENCES dept(dp_id)实验4数据更新1. 表中插入数据操作4.1：向dept表插入一条记录，系号11,系名自动控制系，系主任为李其余，电话81234567 INSERT INTO dept VALUES * 11 ，自动控制系'，李其余'，,81234567,)操作4.2：向student表插入一条记录，学号070201001,姓名为王小五，性别为男，出生口期为1990 年9月9

14、 口，系号为11,其余字段为NULL或默认值INSERT INTO student(st_id/ st_nm, st_sexz st_birthz st_dpid)VALUES (,070201001f,，王小五 J，男 J 7990.9.9J)操作4.3：向couse表插入一条记录，课程号1234,课程名为操作系统，其余字段为NULL或默认值 INSERT INTO couse (cs_idz cs_nm) VALUES (,1234 噪作系统冷操作4.4：向slt_couse表插入一条记录，课程号1234,学名070201001,其余字段为NULL或默认值INSERT INTO slt_c

15、ouse(cs_idz st_id) VALUES (112341, ,070201001,)2. 修改表中数据操作4.5：修改student表记录，将王小五的入学成绩改为88UPDATE student SET st_score=88 WHERE st_nm='王小五'操作4.6：修改couse表记录，将康有记录的学分改为4,牵时改为64UPDATE couse SET cs_tm=64z cs_sc=4操作4.7：修改slt_couse表记录，将课程号为1234,学名为070201001的记录的成绩改为77UPDATE sit couse SET score=77 WHER

16、E cs id=f12341 AND st id=107020100113. 删除表中数据操作4.8：删除slt_couse表记录，将课程号为1234,学名为070201001的记录删除DELETE FROM slt_couse WHERE cs_id=*1234' AND st_id=* 0702010011 操作4.9：删除couse表记录，将课程号为1234的记录删除DELETE FROM couse WHERE cs_id=*12341实验5数据查询（1） 简单查询（1）查询表中所有的列 操作5.1：查询所有系的信息SELECT * FROM dept（2）查询表中指定列的信息

17、操作5.2：查询所有的课程号与课程名称SELECT cs_id, cs_nm FROM couse（3）在查询列表中使用列表达式操作5.3：在查询student表时使用列表达式：入学成绩+400SELECT st_id, st_nm, st_score, st_score+400 AS new_scoreFROM student（4）重新命名查询结果操作5.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话SELECT dp_id AS 系号，dp_nm AS 系名，dp_drt AS 系主任，dp_tel AS 联系电话 FROM dept操作5.5：使用“=”号为c

18、ouse表中属性指定列名：课程号、课程名、学时（=cs_sc*16）.学分 SELECT 课程?'=cs_id,课程名=cs_nm,学*=cs_sc,学时=cs_sc*16FROM couse（5）增加说明列操作5.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任SELECT 1 系号st_id,，系名：，st_nm,，系主任st_drtFROM dept（6）查询列表中使用系统函数操作5.7：显示所有学生的学号、姓名、性别和入学年份SELECT st_id, st_nm, st_sex, DATE PART （yy, st_birth） AS 入学年

19、份FROM student操作5.&显示所有学生学号、姓名、性别和班级（学号前6位）SELECT st_id, st_nm, st_sex, LEFT （st_id, 6） AS 班级FROM student（7）消除查询结果中的重复项 操作5.9：显示所有学生班级SELECT DISTINCT LEFT （st_id, 6） AS 班级 FROM student（8）取得查询结果的部分行集 一 操作5.10：显示前5条学生记录信息SELECT TOP 5 * FROM student操作5.11：显示前25%条学生记录信息SELECT TOP 25 PERCENT * FROM st

20、udent操作5.12：显示前11条学生记录信息，n为局部变量DECLARE n INTSET n = 4SELECT TOP n * FROM student实验6数据查询(2)条件查询1.使用关系表达式表示査询条件操作6.1：查询dept表中系号为11的院系信息SELECT * FROM dept WHERE dp_id = 操作6.2：查询student表中11系的学生学可、姓名、性别和所在系编号SELECT st_id, st_nm, st_sexz st_dpid FROM studentWHERE st_dpid = 1llf操作6.3：查询student表中2008年及以后入学的

21、学生信息SELECT * FROM studentWHERE DATEPART( yy, st_date )>= 2008操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩SELECT st_id, st_nm, st_sex, st_score FROM studentWHERE Left(st_idz 6) = f080808 12-使用逻辑表达式表示查询条件操作6.5：查询student表中非11系的学生信息SELECT * FROM student WHERE NOT (st_dpid =操作6.6：查询选修了 1002号课程且成绩在60以下的学生学

22、号SELECT st_id FROM slt_couseWHERE (cs_id=f10021) AND (score<60)操作6.7：查询2007年入学的11系所有男生信息SELECT * FROM studentWHERE DATEPART(yyz st_date)=2007 AND st_dpid=lllI AND st_sex=f 1 操作6.8：查询11系和12系的事生信息一一SELECT * FROM studentWHERE st_dpid=,llf OR st_dpid=f121操作6.9：查询11系和12系所有200?年入学的学生信息SELECT * FROM stu

23、dentWHERE (st_dpid=f111 OR st_dpid=f12f) AND DATEPART(yy,st_date)=20073使用LIKE关键字进行模糊查询操作6.10：查询所有“计算机”开头的课程信息SELECT * FROM couse WHERE cs_nm LIKE '计算机字 操作6.11：查询所有由三个字组成的“王”姓辜生信息SELECT * FROM student WHERE st_nm LIKE '王_1操作6.12：查询所有课程名中包含“信息”的课無信息SELECT * FROM couse WHERE cs_nm LIKE 1操作6.13：

24、查询学生姓名介于王姓到张姓的石息SELECT * FROM studentWHERE st_nm LIKE 7王-张字4.使用Between.And关键字进行査询操作6.14：查询在1989.7.1到1990.6.30之间出生的学生信息SELECT st_id, st_nm, st_sex, st_birth FROM studentWHERE st_birth BETWEEN 1198171' AND 119996.30操作6.15：查询拡修了 1001号课程且成绩在60到80之间的学生选课信息SELECT * FROM slt_couseWHERE cs_id=,1001f AND

25、 (score BETWEEN 60 AND 80)5使用IN关键字进行査询操作6.16：查询11系、12系、13系的学生信息SELECT * FROM student WHERE st_dpid IN ('ll1,12','13')操作6.17：查询所有张，王，李，赵姓的学生&J学号、姓名、性别SELECT st_id, st_nm, st_sex FROM studentWHERE Left (st_nm, 1) IN ('张'J 王'J 李'J 赵')6.使用NOT NULL关键字进行査询操作6.18：查询所

26、有生源为非空的学生信息SELECT * FROM student WHERE st_from IS NOT NULL 操作6.19：查询选修了 1001号课程且成绩为空皿学生选课信息SELECT * FROM slt_couseWHERE cs_id=,1001f AND score IS NULL实验7数据查询(3) 询排序与查询结果存储操作7.1：查询课程信息，按课程名称降序排序SELECT * FROM couse ORDER BY cs_nm DESC操作7.2：查询选修了 1001号课程成绩非空的学生学号和成绩，并按成绩降序排序SELECT st_id, score FROM slt

27、_corseWHERE cs_id=,1001f AND score IS NOT NULLORDER BY score DESC操作7.3：查询11系学生学号、姓名和年龄，按年龄升序排序SELECT st_idz st_nm,DATEPART(yyf GETDATE( )-DATEPART(yyz st_birth) AS age FROM studentORDER BY age ASC操作7.4：查询学生信息，按姓名升序排序，再按系号降序排序SELECT * FROM student ORDER BY st_nm, st_dpid DESC 操作7.5：创建学生表副本studentOb仅保

28、留学生牵号、姓矗和性别SELECT st_id, st_nm, st_sex INTO studentOl FROM student操作7.6：查询陕西籍学生，将结果保存在新表st_shanxiSELECT * INTO st_shanxiFROM studentWHERE st from=陕西省'数据库原理与应用(SQL Server)练习题15 -操作7.7：查询选修了 1001号课程学生的选课信息，按学号升序排序，将结果保存在新表sltlOOl SELECT * INTO sltlOOl FROM slt_corseWHERE cs_id=f1001f ORDER BY st_i

29、d操作7.8：用局部变量©stage保存学生张三的年龄DECLARE stage intSELECT Qstage = DATEPART(yy,GETDATE()-DATEPART(yyz st_birth)FROM studentWHERE张三.操作7.9：用局駆变量©name和stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩 DECLARE name nVarChar(8) z Qstscore intSELECT name = st_nm, Qstscore = st_scoreFROM studentWHERE LEFT(st_idz 6)

30、= ,070101lORDER BY st_id实验8数据查询(4) 查询统计与汇总操作8.1：查询课程总数SELECT COUNT ( * ) FROM couse操作8.2：查询选修1001号课程的学生人数SELECT COUNT(st_id)FROM slt_couseWhere cs_id = 110011操作8.3：查询赢修课程的数量SELECT COUNT( DISTINCT cs_id ) FROM slt_couse操作8.4：查询选修070101班学生的平均入学成绩SELECT AVG(st_score)FROM studentWHERE LEFT(st_idz 6) = ,

31、070101l操作8.5：查询070101001号学生选修课程的数量、总分以及平均分SELECT COUNT (cs_id) AS 课程数量，SUM (score) AS 总分，AVG (score) AS 平均分 FROM slt_couseWHERE st_id= 10701010011操作8.6：查询1001号课程的学生人数、最高分、最低分和平均分SELECT COUNT (*) AS 学生人数' MAX (score) AS 最高分，MIN (score) AS 最彳氐分， AVG (score) AS 平均分FROM slt_couseWHERE cs_id=,1001,操作

32、8.7：求各石枭程号和相应的选课人数SELECT cs_idz COUNT(st_id)FROM slt_couse GROUP BY cs_id操作8.8：统计各班人数SELECT LEFT(st_idz 6) AS 班级，COUNT (st_id) AS 人数FROM studentGROUP BY LEFT(st id, 6)操作8.9：依次按班级、系号对学生进行分类统计人数、入学平均分SELECT st_dpid AS 系号/ LEFT (st_idz 6) AS 班级，COUNT (st_nm) AS 人数，AVG (st_score) AS 均分FROM studentGROUP

33、BY LEFT(st_idz 6) z st_dpid操作8.10：查询选修了均分在75以上的课程号及均分SELECT cs_id AS 课程编号，AVG (score) AS 均分FROM slt_couseGROUP BY cs_id HAVING AVG(score)>75操作8.11：查询选修了 2门以上课程的学生学号SELECT st_id FROM slt_couseGROUP BY st_id HAVING COUNT(*)>2操作8.12：明细汇总年龄<20的学生，并汇总学生数量、平均年龄SELECT st_nm, DATEPART (yy, GETDATE

34、( ) ) -DATEPART (yyz st_birth) AS 年龄 FROM studentWHERE DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)<20COMPUTE COUNT(st_nm)zAVG(DATEPART(yy,GETDATE()-DATEPART(yy,st_birth) 操作8.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分SELECT st_nm, LEFT (st_id, 6) AS 班级/ st_scoreFROM studentWHERE st_score<85ORDER BY 班级COMP

35、UTE COUNT (st_nm) , AVG (st_score) BY 班级实验9数据查询(5) 连接查询操作9.1：用SQLServer形式连接查询学生学号、姓名、性别及其所选课程编号SELECT a.st_id, st_nm, st_sexz cs_idFROM student az slt_couse bWHERE A.st_id = b.st_idORDER BY A st_id操作9.2：用ANSI形式忘接查询学生学号、姓名、性别及其所选课程编号SELECT a.st_id, st_nm, st_sexz cs_idFROM student a INNER JOIN slt_co

36、use bON a.st_id = bst_idORDER BY a.st_id操作9.3：用SQLServer形式连接查询学生学号、姓名及其所选课程名称及成绩SELECT a.st_id, st_nm, cs_nm, scoreFROM student az slt_couse bz couse cWHERE a.st_id = b.st_id AND bcs_id = c.cs_idORDER BY a.st_id操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩SELECT a.st_id, st_nm, cs_nm, scoreFROM sit couse a IN

37、NER JOIN student b ON a.st id = b.st idINNER JOIN couse c ON a.cs_id = c.cs_idORDER BY b st_id操作9.5：查询选修了 1002课程的学生学号、姓名及1001课程成绩SELECT a.st_id, st_nm, scoreFROM student a, slt_couse bWHERE A.st_id = b.st_id AND bcs_id = 11002 1ORDER BY b.st_id操作9.6：查询选修了 '破据结构”课程的学生学号、姓名及课程成绩SELECT a.st_id, st_

38、nm, scoreFROM student az slt_couse bz couse cWHERE a. st_id=b st_id AND b cs_id=c cs_id AND c. cs_nm='数据结构' ORDER BY A st_id操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名SELECT a.st_id, st_nm, scoreFROM student a LEFT OUTER JOIN slt_couse b ON Ast_id = bst_idWHERE b cs_id IS NULLORDER BY b.st_id操作9.8：用右外连接查询

39、选修各个课程的学生学号SELECT bcs_id, a.st_idFROM slt_couse a Right OUTER JOIN couse b ON a.cs_id = bcs_id ORDER BY b cs_id实验10数据查询(6)子查询操作10.1：用子查询对各班人数进行查询(新增列)SELECT DISTINCT LEFT (a St_id,6) AS 班级，人数 =( SELECT COUNT(st_id) FROM student bWHERE LEFT(a.st_idz 6) = LEFT(b.st_id,6)FROM student a ORDER BY LEFT(aS

40、t_id,6) ASC 操作10.2：用子查询对各课程的选课人数进行查询：新增列)SELECT DISTINCT a.cs_idz 人数=( SELECT COUNT(st_id) FROM slt_couse bWHERE Acs_id = bcs_id)FROM slt_couse a ORDER BY a.cs_id ASC操作10.3：查询选修了 1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序通过子查询实现：一一使用IN关键字SELECT st_id, st_nm, st_sex FROM studentWHERE st_id IN( SELECT st_id FRO

41、M slt_couseWHERE cs_id=l1002, AND score < 60)ORDER BY st_nm通过子查询实现：一一使用比较运算符SELECT st id, st nm, st sexFROM student aWHERE( SELECT score FROM slt_couse bWHERE A.st_id = bst_id AND cs_id = 110021 ) < 60 ORDER BY st_nm操作10.4：查询“东另红”同学所在班的学生信息，并按姓名降序排序通过子查询实现：一一IN运算符SELECT st_id, st_nm, st_sex FR

42、OM studentWHERE LEFT(st_dpid,6) IN( SELECT Left (st_dpid, 6) FROM student WHERE st_nm='东方红')ORDER BY st_nm DESC通过自连接查询实现：一JOINSELECT sl.st_idz slst_nm, si.st_sexFROM student sl JOIN student s2ON LEFT(sl.st_dpid,6) = LEFT(s2 St_dpid，6)WHERE s2 st_nm= 1 东方红 1ORDER BY s2st_nm操作10.5：查询其它班比070511班某一学生的1002号课程成绩高的学生信息(ANY/ALL)SELECT * FROM slt_couseWHERE score > ANY( SELECT score FROM slt_couseWHERE cs_id = ,1002, AND LEFT(st_idz 6) = 10705111)AND LEFT(st_idz 6) <> ' 070511' AND cs_id = 110021操作10.6：查询其它班比070511班任一学生的1002号课程成绩高的学生信息(ANY/A