SQL实训题解析.docx
- 文档编号:6186963
- 上传时间:2023-01-04
- 格式:DOCX
- 页数:20
- 大小:41.38KB
SQL实训题解析.docx
《SQL实训题解析.docx》由会员分享,可在线阅读,更多相关《SQL实训题解析.docx(20页珍藏版)》请在冰豆网上搜索。
SQL实训题解析
1.关系模式R1
学生(学号,姓名,性别,专业号,年龄)主键是(学号)候选关键字是(姓名),是否属于第一范式?
(是)另有一表R2主键是(专业号),两表的公共建是(专业号),外键是(专业号)。
R1:
R2:
2.下列关系模式属于第几范式。
请试着分解成符合第三范式多个表。
关系模式P3(学号、姓名、性别、课程号、成绩、所在系、住处)
属于第一范式
第二范式
S1(学号,课程号,成绩)
S2(学号,姓名,所在系,住处)
第三范式
S1(学号,姓名,性别)
S2(学号,所在系)
S3(所在系,住处)
1、【实验实例一】定义2个字符变量a1和a2,分别赋值为“IAMASTUDENT”,“我是一个好学生”,并进行输出。
DECLARE@a1char,@a2char
SET@a1='IAMASTUDENT'
SET@a2='我是一个好学生'
select@a1
select@a2
2、【实验实例二】返回自上次启动SQLSERVER以来,连接或试图连接的次数以及当前SQLSERVER服务器的版本。
select@@connections
select@@version
(三)函数(下列各题均需使用一条语句完成)
1、分别用函数求出-2、3.2的绝对值,36的平方根及5的立方。
selectabs(-2),abs(3.2),sqrt(36),power(5,3)
2、返回字符串“Abcd”第一个字符的ASCⅡ码值。
selectascii('Abcd')
3、使用RTRIM和LTRIM函数去掉字符串“机械工业”两边的空格,再与“中国”及“出版社”连接起来。
select'中国'+rtrim(ltrim('机械工业'))+'出版社'
4、使用函数从字符串“中国机械工业出版社”中返回字符串“机械”,并使用REVERSE函数将字符串“机械”逆序返回。
selectsubstring('中国机械工业出版社',3,2)
selectreverse('机械')
5、将字符串“abcdabcd”中的所有字符“a”换成字符“%”。
selectreplace('abcdabcd','a','%')
6、使用函数返回系统当前日期及当前日期的年分,月份及日期子部分。
selectyear_now=datename(year,getdate()),
month_now=datename(month,getdate()),
weekday_now=datename(weekday,getdate()),date_now=getdate()
三.实验作业
1、用WHILE语句求1—100之间的累加和
declare@sumint,@countint
select@sum=0,@count=1
label:
select@sum=@sum+@count
select@count=@count+1
while@count<=100
gotolabel
select@sum,@count
2、使用PRINT语句在屏幕上输出2的10次方的值,输出的形式为“2的10次方为:
”
print'2的次方为'+rtrim(power(2,10))
3、用IF语句,求分段方程的值,方程如下:
X2(X<=0)
Y=X+10(0 X(X>5) declare@xint,@yint set@x=7 if@x<=0set@y=power(@x,2) else if@x>5set@y=@x elseset@y=@x+10 select@y,@x 4、设置在20秒钟以后进行一次返回当前系统日期时间的操作。 begin waitfordelay'00: 00: 20' selectgetdate() end 5、创建一个数据库NEW,并在数据库中创建一个表student,该表中有四个字段,表如下: 学号 姓名 性别 年龄 95001 李萍 女 22 95002 黄宏 男 20 95003 刘玲 女 21 95004 王丽 女 21 用case语句判断,如果表中有“黄宏”这个人,则显示“有这个人”,否则显示“查无此人! ” usenew select*,'显示的信息'=case姓名 when'黄宏'then'有这个人' else'查无此人' end fromstudent 三、实训作业: 本次实验的1,2,3,4四个题。 1、数据库的创建 1)使用SSMS创建一个只含一个数据文件和一个事务日志文件的数据库,数据库名为new,主数据库文件逻辑名称为new_data,数据文件初始大小为5MB,最大值为200MB,数据文件大小以3MB的增量增加。 日志逻辑文件名称new_log.ldf,日志文件初始大小为2MB,最大值100MB,日志文件以15%增量增加。 createdatabasenew2 on primary(name=new2_date, filename='C: \ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\new2.mdf', size=5MB, maxsize=200MB, filegrowth=3MB) logon (name=new2_log, filename='C: \ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\new2.ldf', size=2MB, maxsize=100MB, filegrowth=15%) go 2)创建一个具有多个数据文件和日志文件的数据库。 该数据库名称为old,有3个5MB的数据文件,名字自定,最大空间均为20MB,且均按10%的增量增长,其中第三个数据文件在old_group文件组里,主文件是列表中的第一个文件;还有1个10MB的事务日志文件,最大到30MB,按1MB增量增长,名字自定。 createdatabaseold on primary(name=old1_date, filename='C: \ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old1.mdf', size=5MB, maxsize=20MB, filegrowth=10%), (name=old2_date, filename='C: \ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old2.mdf', size=5MB, maxsize=20MB, filegrowth=10%), filegroupold_group (name=old3_date, filename='C: \ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old3.mdf', size=5MB, maxsize=20MB, filegrowth=10%) logon (name=old4_log, filename='C: \ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old4.ldf', size=10MB, maxsize=30MB, filegrowth=1MB) go 2、查看并修改数据库的属性 1)用SQL语句将NEW数据库中数据文件的最大值改为180MB,数据增长改为15%,将日志文件的初始大小改为3MB。 usenew2 go alterdatabasenew2 modifyfile(name=new2_date, maxsize=180MB, filegrowth=15%) alterdatabasenew2 modifyfile(name=new2_log, size=3MB) 2)执行系统存储过程sp_helpdb查看old数据库的信息。 execsp_helpdbnew2 3、数据库的缩小、更名及删除 dropdatabaseold 1)使用命令缩小数据库NEW的大小,收缩成原来大小的80%。 dbccshrinkdatabase(new2,80) 2)使用两种方法实验: 将NEW更名为NEWDATA,并删除该数据库。 createdatabasenew go sp_renamedbnew,newdata 1、数据库的创建 2、自定义数据类型的创建 使用对象资源管理器创建一个名为a0、数据长度为16,可变长字符,允许为空的自定义的数据类型 useSC go execsp_addtypea0,'varchar(16)','null' 3、表的创建与管理 1)用SQL语句在SC数据库中创建表student,有五个属性,分别为(学号nchar(5)notnull,姓名nvarchar(8)notnull,性别bit,年龄int) usesc go createtablestudent (学号nchar(5)notnull, 姓名nvarchar(8)notnull, 性别char (2), 年龄int) 2)使用SQL语句设置表student中学号为主键 usesc go altertablestudent5 addconstraintpk1_student_id primarykeyclustered(student_id) 3)使用SQL语句为表student设置检查约束,要求所输入的年龄必须在15—35岁(含15及35)之间 usesc go altertablestudent2 addconstraintcheck_student_年龄 check(student_年龄>=15andstudent_年龄<=35)。 4)使用SQL语句为student表再添加一个属性,名为“系部”,数据类型为a0,允许空 usesc go altertablestudent addstudent_xibua0notnull 5)使用SQL语句为student表添加记录(95001,李咏,false,20,计算机) usesc go insertintostudent5 (student_id,student_name,student_sex) values ('95001','李咏','男') 6)使用SQL语句将学号为95001这个学生的年龄改为19岁。 usesc go updatestudent2 setstudent_年龄='19' wherestudent_id='95001' 第二大题: 约束有哪6种类型,各是什么含义? (1)非空约束: 表中的某些列必须存在的有效值,不允许有空值出现。 (2)缺省约束: 当向数据库中的表插入数据时,如果用户没有明确给出某列的值,SQLSever自动为该列输入指定值。 (3)检查约束: 限制插入列中的值的范围。 (4)主键约束: 要求主键的列上没有两行具有相同值,也没有空值。 (5)唯一约束: 要求表中所有行在指定的列上没有完全相同的列值。 (6)外键约束: 要求正被插入或更新的列(外键)的新值,必须在被参照表(主表)的相应列(主键)中已经存在 1、默认值的创建及管理 使用SQL语句创建一个默认值abc1,该默认值为真值TRUE,将该默认值分别绑定到student表的性别列上,然后解除该默认值的绑定,并删除该默认值。 usesc go createdefaultabc1AS'TRUE' GO sp_bindfaultabc1,'student.性别' go sp_unbindfault'student.性别' dropdefaultabc1 go 2、规则的创建与管理 使用SQL语句创建一个规则abc2,该规则用于限定所取值在8至55之间,将该规则绑定到student表的年龄列上,以及xskc表的成绩列上,然后解除xskc表的成绩列上规则的绑定。 usesc go createruleabc2as@abc2>=8and@abc2<=55 go sp_bindruleabc2,'student.年龄' go sp_bindruleabc2,'xskc.成绩' go sp_unbindrule'xskc.成绩' 3、标识列的定义 使用SQL语句为xskc表添加一个标识列,列名为“标识列”,类型为int,初始值为1,步长也为1,注意观察xskc表的数据变化情况。 usesc go altertablexskc add标识列intidentity(1,1) (1)查询全体学生的学号及姓名 usesc go select*fromstudent select学号,姓名fromstudent (2)查询全体学生的姓名及出生年月(经过计算所得到的列),并将计算所得列的列名定义为出生年月。 usesc go select*fromstudent select姓名,2011-年龄fromstudent select'出生年月'=2011-年龄fromstudent (3)查询一下都有哪些系。 (4)查询所有20岁以下学生的姓名和年龄。 usesc go select姓名fromstudentwhere年龄<20 (5)查询至少有一科的成绩在85分以上的学生的学号。 usesc go select学号fromxskcwhere成绩>85 (6)查询年龄不在19到21之间的学生的姓名和他们的年龄 usesc go select姓名,年龄fromstudentwhere年龄notbetween19and21 (7)查询除了计算机系和机电系学生的学号和姓名 usesc go select学号,姓名fromstudent where系部notbetween'计算机'and'机电' (8)查询所有不姓刘的学生姓名 usesc go select姓名fromstudentwhere姓名notlike'刘%' (9)查询姓林的全名为两个字的学生的姓名及其所在系部 usesc go select*fromstudent where姓名like'林_' (10)查询前两个字是李红,第三个字符是“[”的学生姓名及其所在系 usesc go select姓名,系部fromstudent where姓名like'李红b[%' escape'b' (11)查询选修了课程但没有参加考试的学生学号。 usesc go select学号fromxskc where课程号isnotnulland成绩isnull (12)查询计算机系年龄小于21岁的学生姓名 usesc go select姓名fromstudent where年龄<21and系部='计算机' (13)查询全体学生信息,查询结果按所在系降序排序。 usesc go select*fromstudent orderby系部desc 1)从student和xskc表中查询成绩在80分以上的学生的学号、姓名、课程号和成绩 usesc go selectstudent.学号,student.姓名,xskc.课程号,xskc.成绩 fromstudent,xskc where成绩>80 2)查询成绩在80分以上的学生的学号,姓名,课程号,课程名,成绩。 usesc go selectstudent.学号,student.姓名,xskc.课程号,xskc.成绩,course.课程名 fromstudent,xskc,course where成绩>80 3)查询所有20岁以下学生的学号 usesc go selectstudent.学号 fromstudent,xskc,course where年龄<20 4)查询所有20岁以下学生选修课程的课程号 usesc go select课程号fromxskc where学号in(select学号fromstudent where年龄<20) groupby课程号 5)查询所有20岁以下学生选修课程的课程名及学分 usesc go select课程名,学分fromcourse where课程号in( select课程号fromxskc where学号in(select学号fromstudent where年龄<20)) 6)查询所有选过课学生的学号; usesc go selectstudent.学号 fromstudent,course where课程号isnotnull 7)查询所有选过课学生的姓名及所在系; usesc go selectstudent.学号,student.姓名,student.系部 fromstudent,course where课程号isnotnull 8)查询被选修课程中每门课程的最高分; usesc go selectxskc.学号,max(成绩)fromxskc groupby学号 9)查询每个系有多少个学生; USESC GO SELECT系部,count(姓名) fromstudent GROUPBY系部 10)用连接查询的方法查询所有20岁以下学生选修课程的课程号。 usesc go selectdistinctcourse.课程号 fromstudent,xskc,course where年龄<20 11)创建一个新的永久表computer,用来存放student表中所有计算机系学生有关信息。 usesc go select*intocomputer1fromstudent where系部='计算机' (一)创建视图 1、用两种方法创建view1视图,显示所有女同学的有关资料 usesc go createviewview2 as select*fromstudent where性别='false' 2、用T-SQL语句在SC数据库中创建一个名为view2,能输出成绩大于80分的学生选修成绩资料的加密的视图。 usesc go createviewview2 withencryption as select*fromxskc where成绩>80 3、用T-SQL语句创建view3视图,显示选修2学分这样的课程的学生的姓名,课程名,成绩 usesc go createviewview3 withencryption as select姓名,成绩,课程名 fromxskc,student,course where学分='2' andstudent.学号=xskc.学号 andxskc.课程号=course.课程号 4、用T-SQL语句创建view4视图,显示选修了C语言的学生的学号,姓名及成绩。 usesc go createviewview4 withencryption as select姓名,成绩,student.学号 fromxskc,student,course where课程名='C语言' andstudent.学号=xskc.学号 andxskc.课程号=course.课程号 (二)查看视图的创建信息 5、查看一下view3视图的定义脚本 sp_HELPTEXTVIEW3_1 思考: 能否使用该方法能否查看到view2的脚本 不能,因为已加密。 (三)修改视图的定义 6、用T-SQL语句将view3视图改为显示选修4学分课程的学生的姓名,课程名,成绩。 usesc go alterviewview3_1 as select姓名,成绩,课程名 fromxskc,student,course where学分='4' andstudent.学号=xskc.学号 andxskc.课程号=course.课程号 7、查询view1中年龄小于20岁的学生的有关信息 usesc go select* fromview1_2 where年龄<20 8、查询view3中成绩>75分的学生的姓名 usesc go select姓名 fromview3_1 where成绩>75 9、向view1中插入一个人,其信息为(95009,赵春梅,女,21,计算机) usesc go insertintoview1 (学号,姓名,性别,年龄,系部) values('95009','赵春梅','false','21','计算机') 10、利用view1将赵春梅的系部改为外语。 usesc go updateview1 set系部='外语' where姓名='赵春梅' 11、利用view1删除赵春梅这个人的信息。 usesc go deletefromview1 where姓名='赵春梅' (一)创建索引 在做下题之前请先想办法删除course表中已经存在的聚簇索引。 1、用T-SQL为course表创建一个惟一的聚簇索引aa,索引列为课程名,降序,填充因子为60%。 use sc createuniqueclusteredindexaa oncourse(课程名desc) with fillfactor=60 2、用T-SQL为student表创建一个唯一的非聚簇索引bb,索引列为姓名,升序,填充因子为40% USESC CREATENONCLUSTEREDINDEXbb ONstudent(姓名ASC) WITH FILLFACTOR=40 3、用T-SQL为xskc表创建一个非唯一的非聚簇索引cc,索引列为课程号,学号,降序,填充因子为50% usesc createuniqueclusteredindexcc onxskc(课程号desc,学号desc) with fillfactor=50 (二)重建索引 4、用T-SQL语句重建bb,填充因子改为60%。 use sc createuniqueclusteredindexbb onstudent(姓名asc) with fillfactor=50 (三)重命名索引 5、用T-SQL语句将索引aa的名字改为aaa。 usesc
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 题解