sqlServer习题与答案.docx

1、sqlServer习题与答案sqlServer2005习题与答案 从学生表Student(Sno,Sname,Ssex,Sage,Sdept)中查询出全体学生的学号与姓名1. 查询全体学生的详细记录2. 显示前 5条纪录3. 显示前 50%条纪录4. 查询所有年龄在17岁以下的学生姓名及其年龄。5. 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。（成绩为null）6. 查所有有成绩的学生学号和课程号7. 查询学生的所有信息，按学号的降序排列 1.select * from student 2.select top 5 * from st

2、udent 3.select top 50 percent * from student 4.select sname,sage from student where sage<17 5.select sno,cno from sc where score is NULL 6.select sno,cno from sc where score is not NULL 7.select * from student order by sno desc8 查询选修了课程的学生学号9. 查全体学生的姓名及其出生年份，显示两列：姓名、出生年份10. 查询年龄在1517岁（包括15岁和17岁）之

3、间的学生的姓名、年龄。11. 查询年龄不在1517岁之间的学生姓名、系别和年龄。12. 查询年龄不在1517岁之间的男生姓名、系别和年龄。13. 将上题查询的结果插入一个新表中。 8.select distinct sno from sc 9.select sname,2010-sage as 出生年份 from student 10.select sname,sage from student where sage between 15 and 17 11.select sname,sdept,sage from student where sage is not between 15 an

4、d 17 12.select sname,sdept,sage from student where ssex=男 and sage is not between 15 and 17 13.select sname,sdept,sage into newtable from student where ssex=男 and sage is not between 15 and 171. 查询学生总人数。2. 查询选修了课程的学生人数。3. 计算1001号课程的学生平均成绩。4. 查询选修1号课程的学生最高分数。5. 求各个课程号及相应的选课人数。（group by）6. 查询选修了1门以上课程

5、的学生学号。（having）7. 请说明union的作用。 1.select count(*) from student 2.select count(distinct sno) as 人数 from sc 3.select avg(score) as 平均成绩 from sc where cno =1001 4.select max(score) as 最高分数 from sc where cno =1 5.select cno,count(*) as 选课人数 from sc group by cno 6.select cno, count(*) as 选课人数 from sc group

6、 by cno having count(*)>11. 查询学生总人数。2. 查询选修了课程的学生人数。3. 计算1001号课程的学生平均成绩。4. 查询选修1001号课程的学生最高分数。5. 求各个课程号及相应的选课人数。（group by）6. 查询选修了1门以上课程的学生学号。（having）7. 请说明union的作用。1.select count(*) as 总人数 from student >2.select count(distinct sno) as 总人数 from sc >3.select avg(score) as 平均成绩from sc where c

7、no=1001 >4.select max(score) from sc where cno=1001>5.select cno,count(*)as 人数,max(score ) from sc group by cno>6.select sno from sc group by sno having count(cno)>1>7.在列数和列的顺序相同且数据类型相同的前提下，将多个select语句返回的结果组合到同一个结果当中。>请举例说明With cube和With rollup的作用。select cno,cname,count(cno)as 人数 f

8、rom course group by cno,cname with cube说明每一个分组统计的总数select cno,cname,count(cno)as 人数from course group by cno,cname with rollup说明每一个小分组的统计总数>3. 使用compute 汇总所有学生的成绩平均分。select sno,cno,score from sc compute avg(score) 统计所有内容，求出平均成绩 >4. 使用compute by汇总每个人的选修课程数。select * from sc order by sno,cno compu

9、te count(cno) by sno按SNO，CNO分组进行统计>使用ANSI连接和sql server 连接两种方式完成：>1. 查询每个学生的学号、姓名及其选修课程的课程号、成绩。 使用ANSI：select student.sno,sname,sc.sno,cno from student inner join sc on student.sno=sc.sno 使用sql server：select student.sno,sname,sc.sno,cno from student,sc where student.sno=sc.sno>2. 查询出101号学生选修

10、的课程的课程名称和学分 使用ANSI：select cname,ccredit from course inner join sc on o=o 使用sql server：select cname,ccredit from course,sc where o=o and sc.sno=101 查询出选修1002号课程的学生的学号、姓名。 使用ANSI select student.sno,sname from student inner join sc on student.sno=sc.sno and o=1002 使用sql server：select student.sno,sname

11、from student,sc where student.sno=sc.sno and o=1002 -查询与“name2”在同一个系学习的学生信息。 select *from student where sdept in (select sdept from student where sname=name2)and sname!=name2查男女各有多少人 select ssex ,count(*)as 人数 from student group by ssex 按降序排列：group by是分组 order by 是排序 select ssex ,count(*)as 人数 from

12、student group by ssex order by ssex desc选课多余2的人数select cno,count(*)from sc group by cno having count(*)>2查询出101号学生选修的课程的课程名称和学分。使用sql server：select cname,ccredit from sc,course where sno=101 and o=o使用ANSI：select cname,ccredit from course inner join sc on o=o and sno=101嵌套查询：select cname,ccredit f

13、rom course where cno in (select cno from sc where sno=101)exists查询：select cname,ccredit from course where exists(select * from sc where cno=o and sno=101)-查询选修课程号为“1001”的所有男生的姓名和该科成绩。sql server：select sname, score from student,sc where student.sno=sc.sno and o=1001and student.ssex=男ANSI：select sname

14、,score from student inner join sc on student.sno=sc.sno and student.ssex=男and o=1001-查询出101号学生选修的课程的学分总和。-使用sql server：select sum(ccredit) as 总学分from sc,course where sc.sno=101and o=o-使用ANSI：select sum(ccredit) as 总学分 from course inner join sc on sc.sno=101and o=o-嵌套查询：select sum(ccredit) as 总学分 fro

15、m course where cno in(select cno from sc where sno=101)-exists：select sum(ccredit) from coursewhere exists(select cno from sc where sno=101 and o=o)-查询出每个学生已经修过的总学分。sql server：select sno,sum(ccredit) as 总学分 from sc,course where o=o group by sc.sno order by sum(ccredit) desc-使用ANSI：select sno,sum(ccr

16、edit) as 总学分 from scinner join course on o=o group by sc.sno order by sum(ccredit) desc-查询出选修c语言的学生的学号、姓名和成绩-使用sql server： select student.sno,sname,score from student,sc,course where ame=c and student.sno=sc.sno and o=o-查询出选修了学分是4的课程的学号-使用sql server： select o,sno from sc,course where o=o and course.

17、ccredit=4 -查询出选修了学分是4的课程的姓名-使用sql server： select cname,sno from sc,course where o=o and course.ccredit=4 -查询出没有选修学分是4的课程的学号-使用sql server： select sno ,o from sc,course where o=o and course.ccredit<>41.将一个新生记录（学号：111；姓名：陈冬；性别：男；所在系：IS;年龄：18岁）插入到Student表中。 insert into student (sno,sname,ssex,sage

18、,sdept) values (111,陈冬,男,18,IS)2.插入一条选课记录(sno： 111，cno：1111 )，新插入的记录在score列上将会取空值。能插入吗？ 存在外键则不能3.student数据库中，有一个表Deptage(Sdept，Avgage)用来存放每个系的学生平均成绩，但还没有数据。请你对每一个系求学生的平均年龄，并把结果存入表Deptage。 select sdept,avg(sage)as avgage into avger from student group by sdept任务2（update）：1.将学生101的年龄改为19岁。 update stude

19、nt set sage=19 where sno=1012.将所有学生的年龄增加1岁。 update student set sage=sage+1 3.将信息系全体学生的成绩置零。 update sc set score=0 where sno in ( select sno from student where sdept=信息)任务3（delete）：1.删除学号为102的学生选课记录。 delete sc where sno=1022.删除所有的学生选课记录。 delete sc3.删除信息系所有学生的选课记录。 delete sc where sno in( select sno f

20、rom student where sdept=信息) 4.你能删除student表中学号为101的学生记录吗？问什么？ 不能删除 DELETE 语句与 REFERENCE 约 束FK_sc_student1冲突。该冲突发生于数据 库students，表dbo.sc, column sno。任务1：回答以下问题：1. 什么是视图2. 使用视图的优点3. 创建视图的注意事项步骤1：每个人独立完成，15分钟。步骤2：提问学生任务完成情况，5分钟步骤3：教师补充点评，5分钟。任务2：创建一个视图view1，查询选修课程号为1001的所有女生的姓名和该科成绩。create view view1asse

21、lect sname,score from student inner join sc on student.sno=sc.snowhere ssex=女 and cno=1001任务3：完成以下操作：创建一个视图v1，在视图中包含sc表中及格的选课信息。create view v1as select *from scwhere score>=601. 插入数据记录：将这两条记录插入视图v1 中(105,1001,69)(105,1002,50)。观察视图和表的记录变化。insert into v1values (105,1001,69)insert into v1values (105

22、,1002,50)2. 修改数据记录：修改视图v1，将(105,1001,69)的成绩改为99UPDATE v1set score=99where sno=105 and cno=1001。3. 删除数据记录 ：修改视图v1，删除105号学生的选课记录。delete from v1where sno=105步骤1：每个人独立完成，5分钟。步骤2：与你的同组搭档得出一个小组结果，5分钟。步骤3：学生介绍任务完成情况，3分钟。步骤4：教师补充点评，2分钟。任务4：创建视图v1，create view v1 asselect student.sno,sname,cno,score from stud

23、ent inner join scon student.sno=sc.sno完成：1. 将满足sno=101 and cno=1003的记录sname改为n1.UPDATE v2set sname=n1where sno=101 and cno=10032. 将满足sno=101 and cno=1004的记录sname改为n1，cno改为1002.不行3. 将sno=109,sname=name9的记录插入v1.4. 将sno=110,sname=nam10,score=99的记录插入v1，能够正确执行吗？5. 删除sno=101 and cno=1004的记录，可以吗？不可以，因为关系到多

24、个基表任务1：通过学习教材及课件回答以下问题：1. 标识符命名规则 答：标识符分为标准标识符和分隔标识符两大类2. 如何注释3. 局部变量的声明、赋值方式4. 全局变量的特点，使用。5. 声明一个变量x并为x赋值，查询成绩大于x(x是局部变量)的学生学号和选修的课程号.declare x int set x=90select sno,cno,score from sc where score>=x6. 声明一个变量avgscore,并将sc表中成绩的平均分赋值给变量avgscore。你有几种赋值方式？declare avgscore int set avgscore=(select av

25、g(score) from sc)select avgscore 平均成绩go5. 声明一个变量x并为x赋值，查询成绩大于x(x是局部变量)的学生学号和选修的课程号.declare x intset x=60select sno,cno from sc where score>x6. 声明一个变量avgscore,并将sc表中成绩的平均分赋值给变量avgscore。你有几种赋值方式？declare avgscore intset avgscore=(select avg(score) from sc)print avgscoreselect avgscore=avg(score) fro

26、m scprint avgscore>>-no是student表的identity列>>insert into student(sno,sname) values(110,name10)>>select identity identity1>>go>> >> >>select * from student where sno=101 or sno=102>>go>>select rowcount>>go>> >> >> >>us

27、e students>>go>>select trancount as trancount1,servername servername1,version version1,identity identity1,language language1>>go>>-no是student表的identity列>>insert into student(sno,sname) values(110,name10)>>select identity identity11.(if)如果sc表中所有学生的平均成绩大于80，显示成绩优异，并

28、显示出成绩大于80的选课记录。否则显示学生成绩一般。declare avgscore int set avgscore=(select avg(score) from sc where score is not NULL) if avgscore>=80 begin print 成绩优异 select * from sc where score>=80 end else begin print 成绩一般 end-在student中查询学生信息，如果存在此生，则将学生的信息输出；如果不存在此生，则输出数据库中没有该生信息declare name nchar(8)set name=na

29、me1if exists(select * from student where sname=name)beginprint select *from student where sname=nameendelsebeginprint数据库中没有该生信息。end-3.(while)使用while循环，对course表中的学分总和进行检查，-若学分总和小于50，对每门课程学分加1，直到学分总和不小于50为止。while (select sum(ccredit) from course)<50)beginupdate courseset ccredit=ccredit+1if(select sum(ccredit) from course)>=50breakend/使用while循环，对course表中的学分总和进行检查，若学分总和小于50，/对每门课程学分加1，直到学分总和不小于50为止。declare sumc intset sumc=(select sum(ccredit) from course)WHILE sumc<50BEGIN