新SQL——SERVER实验练习答案(3)

SQL-Server实验讲义

三 思考：如何把索引 IX_student_sname 修改为唯一性索引？ 可以使用企业管理器

或先删除索引，再重新建立。 *四 思考建立索引的目的

1 输入下列存储过程，该程序生成大量数据供测试： create procedure usp_makedata as

declare @nCnt int , @sNo varchar(6) , @sname varchar(8) set @nCnt =12000 --计数器 while @nCnt<999999 begin

set @nCnt = @nCnt + 1

set @sNo = convert(varchar(6) ,@nCnt) set @sName = '张'+@sno

insert into student (sno,sname,ssex,sage) values ( @sno,@sname,'男',20)

end return

2 exec usp_makedata --生成测试数据 3 输入下述测试程序：

create procedure usp_test as declare @nCount int ,@data int set @nCount=0

while @nCount<100 begin

select @data=count(*) from student where sname <'张3800' or sname>'张8800' set @nCount =@nCount + 1

end

4 测试

1）建立姓名的索引，查看运行时间(8秒).

create index ix_student_sname on student(sname) --建立索引 exec usp_test

2) 删除姓名索引，查看运行时间（2分11秒），比较与1）的时间长短。

drop index student.ix_student_sname --删除索引

exec usp_test

试验六 更新数据

目的：掌握insert,update ,delete 语句的使用。

一 insert

1 写出把下述学生的信息添加到student表中的命令。 学号 4001 4002 姓名 赵茵 杨华 性别 男 女 20 21 年龄 SX 系科 Insert into student (sno,sname,ssex,sage,sdept) values (?4001 ?,?赵茵?,?男?,20,?SX?) Insert into student (sno,sname,ssex,sage) values (?4002 ?,?杨华?,?女?,21)

2 批量插入数据

1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade 。 CREATE TABLE sc_name (

11

SQL-Server实验讲义

Sno char(6) ,

Sname varchar(20), Ssex char(2) , cno char(4) , grade int )

2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。 Insert into sc_name (sno,sname,ssex,cno , grade)

select student.sno,sname , ssex,cno,grade from student,sc where student.sno=sc.sno and sdept=?SX?

3） 察看 sc_name 表的数据 select * from sc_name

二 Update

1 修改 0001 学生的系科为: JSJ

Update student set sdept=?JSJ? where sno=?0001?

2 把陈小明的年龄加1岁，性别改为女。

Update student set sage=sage+1 , ssex=?女? where sname=? 陈小明? 3 修改李文庆的1001课程的成绩为 93 分

update sc set grade=93 where cno=?1001? and sno in (

select sno from student where sname=? 李文庆?)

4 把“数据库原理”课的成绩减去1分

update sc set grade=grade - 1 where cno in (

select cno from course where cname=?数据库原理? )

三 Delete

1 删除所有 JSJ 系的男生 delete from student where sdept=?JSJ?

2 删除“数据库原理”的课的选课纪录

Delete from sc where cno in (select cno from course where cname=?数据库原理? )

思考：修改数据的命令与修改表结构的命令有何区别？

试验七 Sql 查询语句

目的: 掌握 Select 查询语句。

一 单表

1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 select sno,sname,sage from student

where sage between 19 and 21 and ssex=’女’ order by sage desc 2查询姓名中第戎2个字为“明”字的学生学号、性别。

select sname ,ssex from student where sname like ‘_明%’ 3查询 1001课程没有成绩的学生学号、课程号

select sno,cno from sc where grade is null and cno=’1001’ 4查询JSJ 、SX、WL 系的学生学号,姓名，结果按系及学号排列

select sno,sname from student where sdept in (‘JSJ’,’SX’,’WL’)

12

SQL-Server实验讲义

order by sdept,sno

5按10分制查询学生的sno,cno,10分制成绩

（1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） select sno , cno , grade/10.0+1 as level from sc

6查询 student 表中的学生共分布在那几个系中。（distinct） select distinct sdept from student

7查询0001号学生1001，1002课程的成绩。

Select cno from sc where sno=’0001’ and (cno=’1001’ or cno=’1002’)

二 统计

1查询姓名中有“明”字的学生人数。

select count(*) from student where sname like ‘%明%’ 2计算‘JSJ’系的平均年龄及最大年龄。

Select avg(sage) , max(sage) from student Where sdept=’JSJ’ 3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno

order by avg(grade) desc

4 计算 1001,1002 课程的平均分。

Select cno , avg(grade) from sc where cno in (‘1001’,’1002’) Group by cno

5 查询平均分大于80分的学生学号及平均分 select sc.sno , avg(grade) from sc group by sc.sno

having avg(grade)>80

6 统计选修课程超过 2 门的学生学号

select sno from sc group by sno having count(*)>2 7 统计有10位成绩大于85分以上的课程号。 Select cno from sc where grade>85

group by cno having count(*) =10 8 统计平均分不及格的学生学号

select sno from sc group by sno having avg(grade)<60

9 统计有大于两门课不及格的学生学号 select sno from sc where grade<60 group by sno having count(*) >2

三 连接

1查询 JSJ 系的学生选修的课程号

select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’ 2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）

a: select sname from student,sc where student.sno = sc.sno and cno=’1002’ b: select sname from student where sno in (select sno from sc where cno=’1002’) 3查询数据库原理不及格的学生学号及成绩 select sno,grade from sc ,course

where sc.cno=course.cno and cname=’数据库原理’

13

SQL-Server实验讲义

4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法） a: select sname from student , sc , course

where student.sno=sc.sno and sc.cno = course.cno and

grade>80 and cname=’数据库原理’

b: select sname from student where sno in ( select sno from sc where grade>80 and cno in ( select cno from course where cname=’数据库原理’) ) 5查询平均分不及格的学生的学号，姓名,平均分。

select sno, max(sname) , avg(grade) as avggrade from sc , student where student.sno=sc.sno group by student.sno having avg(grade) <60

6查询女学生平均分高于75分的学生姓名。

A: Select sname from student where ssex=’女’ and sno in ( Select sno from sc group by sno having avg(grade)>75)

B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=’女’ Group by student.sno having avg(grade)>75

7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)

select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=’男’

四 嵌套、相关及其他

1 查询平均分不及格的学生人数

select count(*) from student where sno in (

select sno from sc group by sno having avg(grade)<60 ) 2 查询没有选修1002 课程的学生的学生姓名 select sname from student where sno not in( select sno from sc where cno=’1002’) student 0001 aa X 0002 bb ?0003 cc X sc

0001 1001 0001 1002 0002 1001 0003 1002

select sname from student where not exists (

select * from sc where cno=’1002’ and sc.sno=student.sno)

3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）

a: select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc

B: select sno,avg(grade) from sc group by sno

having avg(grade) = (select top 1 avg(grade) from sc

group by sno order by avg(grade) desc )

c: select sno,avg(grade) from sc group by sno

having avg(grade) >=all ( select avg(grade) from sc group by sno )

14

SQL-Server实验讲义

*4 查询没有选修1001，1002课程的学生姓名。 Select sname from student where not exists (

Select * from course where cno in (‘1001’,’1002’) and

Not exists ( select * from sc where sno=student.sno and cno=course.cno ) )

5 查询1002课程第一名的学生学号（2种方法）

a: select top 1 sno from sc cno=’1002’ order by grade desc

b: select sno from sc where cno=’1002’ and

grade >=all (select grade from sc where cno=’1002’)

6 查询平均分前三名的学生学号

select top 3 sno from sc group by sno order by avg(grade) desc 7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集

a: select * from student where sdept=’JSJ’ and sage>19 b: select * from student where sdept=’JSJ’ except select * from student where sage<19

8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 select student.sno,sname from student,sc where cno=?1001? and grade>90 union

select sno,sname from student where sno in (

select sno from sc group by sno having avg(grade)>85 ) 9 查询每门课程成绩都高于该门课程平均分的学生学号 select sno from student where sno not in ( select sno from sc X where grade<(

select avg(grade) from sc Y where Y.sno=X.sno) )

select sno from student where sno not in (

select sno from sc X where grade < (

select avg(grade) from sc where cno=X.cno ) )

10 查询大于本系科平均年龄的学生姓名 select sname from student X where sage > (

select avg(sage) from student y where sdept=x.sdept)

试验八 视图

目的: 掌握视图的建立、使用。

1建立学生学号、姓名、性别、课程号、成绩的视图 v_sc 查看V_sc中的数据。

Create view v_sc (sno , sname,ssex , cno, grade ) as

Select student.sno , sname,ssex , cno , grade from student , sc Where student.sno=sc.sno

Select * from v_sc

1 建立学生学号、姓名、出生年月的视图 v_age 查看V_age中的数据。

Create view v_age (sno,sname, sbirth) as Select sno , sname , 2008 – sage from student

15

百度搜索“70edu”或“70教育网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，70教育网，提供经典综合文库新SQL——SERVER实验练习答案(3)在线全文阅读。