SQLServer练习题参考2-3.doc

SQL Server 练习题二CDBCDBDDCDCCDABBABDACDDBBBBCBA二、填空题1创建、修改、删除2Values select3Select from where 4Group by order by5 降序、升序6日期、时间，、bit7bigint Int smallint tinyint 84218数据库名称、数据库拥有者、数据表名 数据库名称、数据库拥有者9. nchar nvarchar ntext10Binary varbinary image 0x三、数据表基本操作1写出建立该数据表结构的T-SQL语句（数据表名：JBQK099）；要求学号、“姓名”、“身份证号”必须输入。create table jbqk099(学号 char(8) not null,姓名 nchar(4) not null,身份证号char(18) not null,性别 nchar(1),出生日期 smalldatetime,省份 nchar(3),所属单位 nchar(6),专业 nchar(10),数学 decimal(4,1),政治 decimal(4,1),英语 decimal(4,1),备注 text)go2按下列要求修改数据表结构1）添加“年龄”、“出生地”两个字段；Alter table jbqk099 add 年龄 tinyint,出生地 varchar(30)2）将“性别”的数据类型修改成bit；Alter table jbqk099 alter column 性别 bit3）将“专业”的数据类型改成Varchar(30)且不允许空；Alter table jbqk099 alter column 专业 Varchar(30) not null4）删除“备注”字段。Alter table jbqk099 drop column 备注3按下列要求写出插入数据的T-SQL语句（要求：以下操作中，所有的“学号”、“姓名”、“专业”均为自己的真实信息）1）插入1条完全记录；Insert into jbqk099 values (0902110101 , 张三 , 123456789012345678 , 1,03/09/1983,陕西省,管理学院,信管,69,92,56, 22,null)2）插入1条记录，只输入“学号”、“姓名”、“身份证号”的相关内容；Insert into jbqk099(学号,姓名, 身份证号) values(00001,张3,3）插入1条记录，只输入“学号”、“姓名”、“身份证号”、各门课程成绩等相关内容；Insert into jbqk099(学号,姓名, 身份证号,数学,政治,英语) values(080203001,王亚,93,96,87)4按下列要求修改数据表1）将表中第2条记录的“姓名”、“学号”、“专业”修改为自己的真实数据；Update jbqk099 Set 学号=0902110102,姓名=张三2,专业=电子商务 where 学号=403040602）将自己的数学成绩提高15%，英语成绩提高10分，政治成绩减少5分；Update jbqk099 Set 数学=数学*1.15,英语=英语+10,政治=政治-5 where 学号=09021101013）将没有数学成绩的记录，均修改为60分。Update jbqk099 Set 数学=60 where 数学 is NULL4）添加“总分”、“平均分”字段，并计算自己的总分和平均分。Alter table jbqk099 add 总分 int,平均分 realUpdate jbqk099 Set 总分=数学+英语+政治 ,平均分=(数学+英语+政治)/3where 学号=09021101015删除“性别”为空的所有记录。Delete from jbqk099 where 性别 is null四、SELECT查询（写出各查询要求相应的SELECT 语句）1显示表中前2条的学号、姓名、各门课程成绩的信息；Select top 2 学号,姓名,数学,政治,英语 from jbqk0992显示学号、姓名、各门课程成绩、总成绩、平均成绩信息；Select 学号,姓名,数学,政治,英语 ,(数学+政治+英语) as 总成绩, (数学+政治+英语)/3 as 平均成绩from jbqk0993显示学号是40开头，姓“张”、“李”的所有信息；Select * from jbqk099 where 学号 like 40% and 姓名 like 张李%或 Select * from jbqk099 where left(学号 ,2)=40 and (left(姓名 ,1)=张 or left(姓名 ,1)=李)4显示“管理学院”的不及格学生的信息；Select * from jbqk099 where 所属单位=管理学院 and (数学60 or 政治60 or 英语2009从JBQK、CJB中选出“陕西省”的学生的姓名、性别、所在系部、省份、总成绩，结果按总成绩降序排列，若总成绩相同则按姓名升序排列；Select jbqk.姓名 ,jbqk.性别,jbqk.所属单位,jbqk.省份,(cjb.数学+cjb.政治+cjb.英语) as 总成绩From jbqk inner join cjb on jbqk.学号=cjb.学号Order by 5 desc,姓名10选出与“王亚泽”同一个系的学生的姓名、性别、所在系部、平均成绩。Select 姓名,性别,所属单位,(数学+政治+英语)/3 as 平均成绩 from jbqk099 Where 所属单位 =(select 所属单位 from jbqk099 where 姓名=王亚泽)SQL Server 练习题三一、选择题1.如果将某一列设置为表的主键，则在表中此列的值（ C ）。A）可以出现重复值 B）允许为空值C）不允许为空值，也不能出现重复值 D）不允许为空值，但允许列值重复2.下面语句中，哪种语句用来修改视图（ D ）。A）alter table B）modify view C）drop viewD）alter view3.下列标识符可以作为局部变量使用（ C ）A) Myvar B) My var C) Myvar D) My var4创建存储过程的命令为（ C ）。A) CREATE VIEWB) CREATE INDEXC) CREATE PROCEDURE D) CREATE FUNCTION5将jbqk表复制到数据表jg_tab中，正确的语句是 （ B ）A) select * from jbqk into jg_tab B) select * into jg_tab from jbqk C) insert jg_tab from jbqk D)insert jg_tab select * from jbqk6print len(rtrim(str(245+100+0.5)的显示结果是（ C ）A) 345B) 345.5C) 10D)37.print ascii(12)+len(substring(1234,2,2)+day(2009-10-1)的结果为 CA)26 B) 35 C) 52D)出错8计算两个日期之间的差值的函数是（ D ）A) getdateB) dateaddC) datenameD)datediff9产生(0,1)之间随机数的函数是（ D ）A) sqrt()B) rnd()C) floor()D)rand()10判断“成绩”是否在0100之间的约束是（ D ）A) defaultB) constrainC) rangeD)check二、填空题1. 在SQL Server中, 编辑、运行Transact-SQL语句是在中完成。2Create database命令定义一个数据库，包括定义文件和文件部分。3在查询分析器中，打开一个数据库使之成为当前库，有种方法，其中之一使用的命令关键字为。4. Select语句可以实现多个数据表连接查询,其中外连接的方式分别有、和。5. Select语句可以实现模糊查询，其模式匹配有4种，它们分别是、。6.常用的聚合函数有：计算最大值的_，计算最小值的_，统计总和的_，统计记录总数的_和计算平均值的_。聚合函数的计算对象不仅是列而且可以是_。7.SQL Server 2000的数据库管理程序分为_和_两部分，两部分可以安装在同一台计算机上，也可以分别安装在不同的计算机上。8.SQL Server实例的身份验证模式有_和_两种。9.对表操作的数据定义语言（DDL）有创建表的_语句、修改表结构的_语句和删除表的_语句。10.对表中数据更新操作的数据操作语言（DML）有添加记录的_语句、修改记录的_语句和删除记录的_语句。11.主键是唯一能够区分表中每一行记录的_。一个表只能有_主键，主键不能为空值，并且可以强制表中的记录的_。主键的标志为_。12.存在两个表A和B，表A中的主键列在表B中也存在，但并不是表B的主键，仅作为表B的一个必要的属性，则称此属性为表B的_。13. 在一个已存在数据的表中增加一列，一定要保证所增加的列允许值。14CHECK约束被称为约束，UNIQUE约束被称为约束。当一个表带有约束后，执行对表的各种操作时，将自动检查相应的约束，只有符合约束条件的合法操作才能被真正执行。15定义局部变量的语句关键字为，局部变量的开始标记为。16创建和删除存储过程my_proc的命令为和。17任意写出5个常用字符函数的完整命令格式（即命令和参数）、。18任意写出5个常用日期函数的完整命令格式（即命令和参数）、。19select * from 学生基本情况 where len(rtrim(姓名)=2的功能是20select ascii(str(156)+ascii(ltrim(str(200)的结果是三、读程序，写功能假设存在名为AAA的数据库，包括Students(学号char(8)，姓名 varchar(8),身份证号char(18),年龄int,专业varchar(20),入学日期DateTime)和Score（学号char(8),课程名varchar(10),成绩numeric(5,2)）两张表。一）写出下列程序段的功能。 1 DECLARE MyNO CHAR(8)SET MyNO=40030001IF(SELECT 专业FROM Students WHERE 学号=MyNO)=信息管理 BEGIN SELECT AVG (成绩)AS 平均成绩FROM ScoreWHERE =MyNO ENDELSEPRINT 学号为+MyNO+的学生不存在或不属于信管专业GO-查询信息管理专业学号为40030001学生的平均成绩2 Declare a numberic(5,2),b numeric(5,2)Set a =(select max(成绩)from score)Set b=(select min (成绩)from score)Print a-b-计算成绩表中最高成绩与最低成绩之差3 Declare a char(8)Set a=计算机Select 计算机专业人数=count(*) From students Where left(专业，3)=a-查询计算机专业的学生人数4 Select month （入学日期）as入学月份， count（*） as 人数From studentsGroup by month（入学日期）统计不同月份入学的学生人数5 Create procedure xxk1xm varchar(10)=张 as select * from students x,score y where x学号=y学号 and 姓名 like xm+%go-创建存储过程xxk1，按给定姓氏查询students表与score表的相关信息exec xxk16Create procedure xxk3xh1 char(2)=2,xh2 char(2)=22as select 学号，avg(成绩) as平均成绩 from score where right(rtrim(学号),2) between xh1 and xh2 group by 学号go-创建存储过程xxk3，统计给定学号段的学生平均成绩exec xxk3 xh2=16,xh1=67create procedure xxk5(a char(8),b varchar(10),c numeric(5,2)as insert into score values(a,b,c)-创建存储过程xxk5，向数据表score插入一条记录二）按下列要求写出操作命令1根据students、score表生成数据表xs_cj的结构，字段包括学号、姓名、入学日期、专业、课程名、成绩；Select top 0 学号,姓名,入学日期,专业,课程名,成绩 into xs_cj from students,score2根据students、score表向数据表xs_cj插入“信息管理”和“电子商务”专业的学生信息；Insert xs_cj select a.学号,姓名,入学日期,专业,课程名,成绩 From students a inner join score on a.学号=score.学号Where 专业=”信息管理” or专业=”电子商务”3修改“学号”的宽度为10，并将本专业的学号前7位改为专业的编号、第8位为1或2（随机产生）、后两位不变；Alter table xs_cj alter column 学号 char(10)Update xs_cj set 学号=0902110+str(floor(1+rand()*2),1)+right(rtrim(学号),2)4显示学生的学号、姓名、入学日期和专业的信息，要求入学日期按“xx年xx月xx日 星期x”格式显示； Select 学号,姓名,datename(yy,入学日期)+年+ datename(mm,入学日期)+月+ datename(dd,入学日期)+日+ datename(dw,入学日期) as 入学日期 ，专业 From xs_cj5将“高等数学”为空的记录，修改为60,85)之间的随机数；Update xs_cj set 成绩=floor(60+rand()*26)Where 课程名=高等数学 and 成绩 is null6创建视图view_xscj，包括学号、姓名、年龄、入学日期、专业、课程名、成绩；Create view view_xscj asSelect 学号,姓名,年龄,入学日期,专业,课程名,成绩 From students a inner join score on a.学号=score.学号7. 查询“本专业99年入学年龄在19-25间”的学生信息；Select * from view_xscj Where 专业=信息管理 and year(入学日期)=1999 and 年龄 between 19 and 258查询“本专业学习成绩前5名”的学生信息；Select top 5 学号,avg(成绩) as 学习成绩 from view_xscjGroup by学号Order by 学习成绩 desc9创建视图view_xscj1，包括学号、姓名、专业、总成绩、平均成绩；Create view view_xscj1 asSelect 学号,姓名,专业,sum(成绩) as 总成绩 ,avg(成绩) as 平均成绩from view_xscjGroup by学号,姓名,专业10. 查询各专业学习的排序情况；Select 专业,avg(平均成绩) as 专业平均 from view_xscj1group by 专业 order by 2 desc11创建存储过程按给定姓氏（默认姓“李”）查询学生的姓名、专业、总成绩、平均成绩；Create proc proc_xmxm varchar(10)=张AsSelect 姓名,专业,总成绩,平均成绩 from view_xscj1 where 姓名 like xm+%12创建存储过程统计给定专业学生的平均成绩及人数；Create proc proc_zyzy varchar(30)AsSelect 专业=zy,count(*) as 人数,avg(平均成绩) as 专业平均 From view_xscj1 where 专业=xy13创建存储过程统计2006-2009年入学的学生的平均成绩、最高分、最低分；Create proc proc_rqrq1 int=2006,rq2 int=2009AsSelect avg(成绩) as 平均成绩, max(成绩) as 最高成绩, min(成绩) as 最低成绩From xscj where year(入学日期) between rq1 and rq2二）根据实验3提供的Employees、Departments、Salary数据表，写出下列查询语句的主要功能。1. SELECT Employees.*FROM Employees INNER JOIN Departments ON Employees.部门号 = Departments.部门号WHERE (Departments.部门名 = 财务部)-查询在财务部工作的员工情况2. SELECT Employees.*FROM Employees INNER JOIN Salary ON Employees.编号 = Salary.编号WHERE (Salary.收入 2500)-查找所有收入在2500以下的雇员情况3. SELECT Employees.*FROM Employees INNER JOIN Departments ON Employees.部门号 = Departments.部门号WHERE (Employees.出生日期 2500)-统计财务部收入在2500以上的雇员人数9. SELECT Employees.*, Salary.收入FROM Employees INNER JOIN Salary ON Employees.编号 = Salary.编号ORDER BY Salary.收入 DESC-按收入由高到低排列，查询雇员的情况和收入10. SELECT Departments.部门名, AVG(Salary.收入) AS 平均, MAX(Salary.收入) AS 最高, MIN(Salary.收入) AS 最低, COUNT(*) AS 人数FROM Emplo