SQL实训题解析

1、1.关系模式R1学生（学号，姓名，性别，专业号，年龄） 主键是（学号）候选关键字是（ 姓名）， 是否属于第一范式？（是）另有一表R2主键是（专业号），两表的公共建是（专业号）, 外键是（专业号 ）。R1:姓名性别专业号801张三女0119802李四男0120803王五男0120804赵六女0220805钱七男0219R2:专业号专业名01信息02数学03计算机2.下列关系模式属于第几范式。请试着分解成符合第三范式多个表。关系模式P3 （学号、姓名、性别、课程号、成绩、所在系、住处）属于第一范式第二范式S1 （学号，课程号，成绩）S2 （学号，姓名，所在系，住处）第三范式S1 （学号，姓名，性别

2、）S2 （学号，所在系）S3 （所在系，住处）1、【实验实例一】 定义2个字符变量al和a2,分别赋值为"I AM A STUDENT”，“我是一个好学生”，并进行输出。DECLARE a1 char , a2 charSET a1 ='I AM A STUDENT'SET a2 ='我是一个好学生select a1select a22、【实验实例二】返回自上次启动SQL SERVER以来，连接或试图连接的次数以及当前SQL SERVER服务器的版本。selectconnectionsselectversion(三)函数(下列各题均需使用一条语句完成)1、分别

3、用函数求出 2、 3.2 的绝对值， 36 的平方根及5 的立方。select abs (- 2), abs ( 3.2 ), sqrt ( 36 ), power ( 5, 3)2、返回字符串“ Abcd”第一个字符的 ASCH码值。select ascii ( 'Abcd' )3、使用RTRIMB LTRIM函数去掉字符串“ 机械工业 ”两边的空格，再与“中国”及“出版社”连接起来。select ' 中国 ' + rtrim ( ltrim ( ' 机械工业 ' )+ ' 出版社 '4、使用函数从字符串“中国机械工业出版社”中

4、返回字符串“机械”，并使用 REVERSE函数将字符串“机械”逆序返回。selectsubstring ( ' 中国机械工业出版社 ' , 3, 2)selectreverse ( ' 机械 ' )5、将字符串“abcdabcd ”中的所有字符“a ”换成字符“”。select replace ( 'abcdabcd' , 'a' , '%' )6、使用函数返回系统当前日期及当前日期的年分，月份及日期子部分。select year_now =datename ( year , getdate (), month_n

5、ow =datename ( month , getdate (),weekday_now =datename ( weekday , getdate (), date_now =getdate ()三实验作业1、用 WHILE 语句求 1 100之间的累加和declare sum int , count intselect sum =0, count =1label :select sum =sum+countselect count = count +1while count <= 100goto labelselect sum , count2、 使用 PRINT 语句在屏幕上输出

6、 2 的 10 次方的值，输出的形式为“ 2 的 10 次方为： ”print '2 的次方为 ' +rtrim ( power ( 2, 10)3、 用 IF 语句，求分段方程的值，方程如下：X2 (X<=0)Y= Y X+10(0<X<5)(X>5)declare x int , y intset x = 7if x<=0 set y = power ( x, 2)elseif x>5 set y =xelse set y =x+10select y , x4、设置在20秒钟以后进行一次返回当前系统日期时间的操作。beginwaitfor

7、 delay '00:00:20'select getdate ()end5、创建一个数据库 NEW,并在数据库中创建一个表student,该表中有四个字段，表如下:学号姓名性别年龄95001李萍女2295002黄宏男2095003刘玲女2195004士丽女21用case语句判断，如果表中有“黄宏”这个人，则显示“有这个人”，否则显示“查无此人！”use newselect *,'显示的信息=case 姓名when '黄宏'then '有这个人'else '查无此人' endfrom student三、实训作业：本次实验的

8、1, 2, 3, 4四个题。1、数据库的创建1)使用SSMS创建一个只含一个数据文件和一个事务日志文件的数据库，数据库名为new,主数据库文件逻辑名称为new_data,数据文件初始大小为5MB ,最大值为200MB ,数据文件大小以3MB的增量增加。日志逻辑文件名称new_log.ldf ,日志文件初始大小为2MB ,最大值100MB,日志文件以15%增量增加。create database new2onprimary (name =new2_date ,filename='C:Program FilesMicrosoft SQLServerMSSQL.3MSSQLDatanew2.

9、mdf13size =5MB,maxsizefilegrowth log on=200MB,=3MB)( name =new2_log , filename ='C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLDatanew2.ldf'size =2MB,maxsizefilegrowth=100MB,=15%)go2) 创建一个具有多个数据文件和日志文件的数据库。 该数据库名称为 old ， 有 3 个 5MB 的数据文件,名字自定，最大空间均为20MB ，且均按10的增量增长,其中第三个数据文件在 old_group 文件组里

10、,主文件是列表中的第一个文件；还有1 个 10MB 的事务日志文件，最大到 30MB, 按 1MB 增量增长，名字自定。 create database old on primary ( name =old1_date , filename ='C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLDataold1.mdf',size =5MB, maxsize =20MB, filegrowth = 10 %), ( name =old2_date , filename ='C:Program FilesMicrosoft SQ

11、L ServerMSSQL.3MSSQLDataold2 .mdf',size =5MB, maxsize =20MB, filegrowth = 10 %), filegroup old_group ( name =old3_date , filename ='C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLDataold3 .mdf',size =5MB, maxsize =20MB, filegrowth = 10 %) log on ( name =old4_log , filename ='C:Progra

12、m FilesMicrosoft SQL ServerMSSQL.3MSSQLDataold4.ldf',size =10MB, maxsize =30MB, filegrowth = 1MB)go2、查看并修改数据库的属性1)用 SQL 语句将 NEW 数据库中数据文件的最大值改为 180MB, 数据增长改为 15%,将 日志文件的初始大小改为3MB 。use new2 go alter database new2 modify file ( name = new2_date , maxsize =180MB , filegrowth = 15%) alter database ne

13、w2 modify file ( name=new2_log ,size =3MB) 2)执行系统存储过程sp_helpdb 查看 old 数据库的信息。exec sp_helpdb new2 3、数据库的缩小、更名及删除 drop database old 1)使用命令缩小数据库NEW 的大小，收缩成原来大小的 80。dbcc shrinkdatabase ( new2 , 80 ) 2)使用两种方法实验：将NEW 更名为 NEWDATA ，并删除该数据库。create database new gosp_renamedb new , newdata 1、数据库的创建 2、自定义数据类型的创

14、建使用对象资源管理器创建一个名为a0、数据长度为16,可变长字符，允许为空的自定义的数据类型 use SC go exec sp_addtype a0 , 'varchar(16)' , 'null'3、表的创建与管理1)用SQL语句在SC数据库中创建表 student,有五个属性，分别为(学号nchar(5) not null , 姓名 nvarchar(8) not null, 性别 bit, 年龄int )use sc go create table student ( 学号 nchar ( 5) not null, 姓名 nvarchar ( 8) no

15、t null, 性别 char ( 2), 年龄 int ) 2)使用 SQL 语句设置表student 中学号为主键use sc go alter table student5add constraintpk1_student_idprimary key clustered （ student_id ）3）使用SQL语句为表student设置检查约束，要求所输入的年龄必须在1535岁（含15 及 35）之间 use sc go alter table student2 add constraint check_student_ 年龄 check （ student_ 年龄 >= 15

16、and student_ 年龄 <= 35） 。4）使用SQL语句为student表再添加一个属性，名为“系部”，数据类型为a0,允许空 use sc go alter table student add student_xibu a0 not null5）使用SQL语句为student表添加记录（95001,李咏，false, 20,计算机） use sc go insert into student5 （ student_id , student_name , student_sex ） values（ '95001', ' 李咏 ' , '

17、男 ' ）6）使用SQL 语句将学号为 95001 这个学生的年龄改为 19 岁。use sc go update student2 set student_ 年龄 ='19'where student_id ='95001'第二大题：约束有哪6 种类型，各是什么含义？（ 1）非空约束：表中的某些列必须存在的有效值，不允许有空值出现。（ 2） 缺省约束： 当向数据库中的表插入数据时， 如果用户没有明确给出某列的值， SQL Sever 自动为该列输入指定值。（ 3）检查约束：限制插入列中的值的范围。（ 4）主键约束：要求主键的列上没有两行具有相同值，也没

18、有空值。（5） 唯一约束：要求表中所有行在指定的列上没有完全相同的列值。（6） 外键约束：要求正被插入或更新的列（外键）的新值，必须在被参照表（主表）的相应列（主键）中已经存在1、默认值的创建及管理使用SQL语句创建一个默认值abcl,该默认值为真值TRUE,将该默认值分别绑定到student 表的性别列上，然后解除该默认值的绑定，并删除该默认值。 use sc go create default abc1 AS 'TRUE' GO sp_bindfault abc1, 'student. 性别 'gosp_unbindfault 'student. 性

19、别drop default abc1go 2、规则的创建与管理使用SQL语句创建一个规则 abc2,该规则用于限定所取值在 8至55之间，将该规则绑 定到student表的年龄列上，以及 xskc表的成绩列上，然后解除 xskc表的成绩列上规则的绑定。use sc gocreate rule abc2gosp_bindruleabc2gosp_bindruleabc2gosp_unbindrule3、标识列的定义as abc2 >=8 and abc2 <= 55, 'student. 年龄 ', 'xskc. 成绩 ''xskc. 成绩 &

20、#39;int ，初始值为1 ，使用 SQL 语句为 xskc 表添加一个标识列，列名为“标识列”步长也为1，注意观察xskc 表的数据变化情况。use sc goalter table xskcadd 标识列 int identity （ 1 , 1 ）（ 1 ）查询全体学生的学号及姓名use scgoselect * from studentselect 学号 , 姓名 from student（ 2 ）查询全体学生的姓名及出生年月（经过计算所得到的列），并将计算所得列的列名定义为出生年月。use scgoselect * from studentselect 姓名 , 2011 - 年龄

21、 from studentselect ' 出生年月 ' = 2011 - 年龄 from student（ 3 ）查询一下都有哪些系。（ 4 ）查询所有20 岁以下学生的姓名和年龄。use scgoselect 姓名 from student where 年龄 <20（ 5 ）查询至少有一科的成绩在85 分以上的学生的学号。use sc goselect 学号 from xskc where 成绩 >85（ 6 ）查询年龄不在19 到 21 之间的学生的姓名和他们的年龄use scgoselect 姓名 , 年龄 from student where 年龄 not

22、 between 19 and 21（ 7 ）查询除了计算机系和机电系学生的学号和姓名use scgoselect 学号 , 姓名 from studentwhere 系部 not between ' 计算机 ' and ' 机电 '（ 8 ）查询所有不姓刘的学生姓名use scgoselect 姓名 from student where 姓名 not like ' 刘 %'（ 9 ）查询姓林的全名为两个字的学生的姓名及其所在系部use scgoselect * from studentwhere 姓名 like ' 林 _'（

23、10 ）查询前两个字是李红，第三个字符是“ ”的学生姓名及其所在系use scgoselect姓名, 系部 from studentwhere 姓名like' 李红 b%'escape 'b'（ 11）查询选修了课程但没有参加考试的学生学号。use scgoselect 学号 from xskcwhere 课程号 is not null and 成绩 is null（ 12 ）查询计算机系年龄小于21 岁的学生姓名use scgoselect 姓名 from studentwhere 年龄 <21 and 系部 =' 计算机 '（ 13 ）

24、查询全体学生信息，查询结果按所在系降序排序。use scgoselect * from studentorder by 系部 desc1）从 student 和 xskc 表中查询成绩在 80 分以上的学生的学号、姓名、课程号和成绩use scgoselect student . 学号 , student . 姓名 , xskc . 课程号 , xskc . 成绩from student , xskcwhere 成绩 >802）查询成绩在80 分以上的学生的学号，姓名，课程号，课程名，成绩。use scgoselect student . 学号 , student . 姓名 , xskc

25、 . 课程号 , xskc . 成绩 , course . 课程名 from student, xskc , coursewhere 成绩 >803）查询所有20 岁以下学生的学号use scgoselect student . 学号from student , xskc , coursewhere 年龄 <204）查询所有20 岁以下学生选修课程的课程号use scgoselect 课程号 from xskcwhere 学号 in（ select学号 from studentwhere 年龄 <20 ）group by 课程号5）查询所有20 岁以下学生选修课程的课程名及学

26、分use scgoselect课程名, 学分 from coursewhere 课程号 in（select课程号from xskcwhere 学号 in（ select学号 from studentwhere 年龄 <20 ）6）查询所有选过课学生的学号；use scgoselect student . 学号from student , coursewhere 课程号 is not null7）查询所有选过课学生的姓名及所在系；use scgoselect student . 学号 , student . 姓名 , student . 系部from student , coursewhe

27、re 课程号 is not null8）查询被选修课程中每门课程的最高分；use scgoselect xskc . 学号 , max（ 成绩 ） from xskc group by 学号 9）查询每个系有多少个学生； USE SC GO SELECT 系部 , count （ 姓名 ） from studentGROUP BY 系部10）用连接查询的方法查询所有20 岁以下学生选修课程的课程号。use sc go select distinct course . 课程号 from student , xskc , course where 年龄 <2011）创建一个新的永久表 com

28、puter,用来存放student表中所有计算机系学生有关信息。 use scgoselect * into computer1 from student where 系部 =' 计算机 '（一）创建视图1、用两种方法创建view1 视图，显示所有女同学的有关资料use scgo create view view2asselect * from student where 性别 ='false' 2、用T-SQL 语句在 SC 数据库中创建一个名为 view2 ，能输出成绩大于80 分的学生选修成绩资料的加密 的视图。 use scgo create view

29、view2 with encryptionas select * from xskc where 成绩 >803、用T-SQL 语句创建 view3 视图，显示选修2 学分这样的课程的学生的姓名，课程名，成绩use sc gocreate view view3 with encryption asselect 姓名 , 成绩 , 课程名from xskc , student , course where 学分 ='2' and student . 学号 =xskc . 学号 and xskc . 课程号 =course . 课程号4、用T-SQL 语句创建 view4 视

30、图，显示选修了 C 语言的学生的学号，姓名及成绩。use sc go create view view4 with encryption as select 姓名 , 成绩 , student . 学号 from xskc , student , course where 课程名 ='C 语言 ' and student . 学号 =xskc . 学号 and xskc . 课程号 =course . 课程号（二）查看视图的创建信息5、查看一下view3 视图的定义脚本sp_HELPTEXT VIEW3_1思考：能否使用该方法能否查看到 view2 的脚本不能，因为已加密。（三

31、）修改视图的定义6、用T-SQL 语句将 view3 视图改为显示选修 4 学分课程的学生的姓名，课程名， 成绩。use sc go alterview view3_1as select 姓名, 成绩 , 课程名from xskc , student , course where 学分 ='4' and student . 学号 =xskc . 学号 and xskc . 课程号 =course . 课程号7、查询view1 中年龄小于 20 岁的学生的有关信息use sc go select * from view1_2 where 年龄 <208、查询view3 中成

32、绩 >75 分的学生的姓名use scgoselect 姓名 from view3_1 where 成绩 >759、向 view1 中插入一个人，其信息为（ 95009，赵春梅，女， 21，计算机） use scgoinsert into view1（ 学号 , 姓名 , 性别 , 年龄 , 系部 ）values （ '95009', ' 赵春梅 ' , 'false' , '21' , ' 计算机 ' ）10、利用view1 将赵春梅的系部改为外语。use scgoupdate view1set 系部

33、 =' 外语 'where 姓名 =' 赵春梅 '11、利用view1 删除赵春梅这个人的信息。use scgodelete from view1where 姓名 =' 赵春梅 '（一）创建索引在做下题之前请先想办法删除course表中已经存在的聚簇索引。1、用T-SQL为course表创建一个惟一的聚簇索引aa,索引列为课程名，降序，填充因子为 60。 use sccreate unique clustered index aaon course （ 课程名 desc ）withfillfactor =602、用T-SQL为student表创建

34、一个唯一的非聚簇索引bb,索引列为姓名，升序，填充因子为 40USE SCCREATE NONCLUSTERED INDEX bbON student（姓名 ASC）WITHFILLFACTOR = 403、 用 T-SQL 为 xskc 表创建一个非唯一的非聚簇索引 cc， 索引列为课程号， 学号， 降序， 填充因子为50use sccreate unique clustered index cc on xskc （ 课程号 desc , 学号 desc ） withfillfactor =50（二）重建索引4、用T-SQL语句重建bb,填充因子改为60%。 use sccreate unique clustered index bbon student（ 姓名 asc ）withfillfactor=50（三）重命名索引5、用T-SQL 语句将索引 aa 的名字改为aaa。use scgosp_rename 'co