SQL_SERVER_2005上机试卷的答案：create database stu2011517369

一、在当前服务器上创建数据库STUxxxx（其中xxxx为自己的学号）。如：CREA TE DA TABASE stu200800101。数据库初始大小3M，最大值500M，文件增长率2%

create database stu2011517369

on primary

(name='stu2011517369',

filename='d:\stu2011517369\stu.mdf',

size=3mb,

maxsize=500mb,

filegrowth=2%)

drop table xs_kc

drop table xs

drop table kc

二、在STUxxxx数据库中使用SQL语句创建如下的表结构

create table xs

(sno char(7)not null,

sname char(20)not null,

ssex bit not null default(1),

birth smalldatetime not null,

zy char(10)null,

sumxf tinyint null,

constraint sex check(ssex=1 or ssex=0),

primary key(sno))

create table kc

(cno char(3)not null,

cname char(20)not null,

xueqi tinyint not null,

xueshi tinyint not null,

xuefen tinyint not null,

primary key(cno))

create table xs_kc

(sno char(7)not null,

cno char(3)not null,

grade tinyint,

primary key(sno,cno),

foreign key(sno)references xs,

foreign key(cno)references kc)

insert into xs values('4102101','王林',1,'1983/1/23','计算机',40) insert into xs values('4202103','张强',1,'1981/11/19','电子',null) insert into xs values('4302101','刘明',1,'1982/10/18','自控',38)

insert into xs values('4402130','叶凡',1,'1983/11/18','数学',46)

insert into kc values('101','计算机基础',1,48,3)

insert into kc values('102','c语言',2,80,5)

insert into kc values('103','数据库',1,64,4)

insert into xs_kc values('4102101','101',80)

insert into xs_kc values('4102101','102',89)

insert into xs_kc values('4102101','103',78)

insert into xs_kc values('4202103','101',57)

insert into xs_kc values('4202103','102',67)

insert into xs_kc values('4202103','103',90)

insert into xs_kc values('4302101','101',85)

insert into xs_kc values('4402130','102',91)

1.查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。

select distinct zy

from xs

2.查询XS表中各个同学的姓名、专业名和总学分，只返回结果集的前行

select top 5 sname,zy,sumxf

from xs

3.查询XS表中每个学生的学号、姓名和年龄信息

select sno,sname,year(getdate())-year(birth) nl

from xs

4.查询XS表中专业名为“计算机”或“电子”或“数学”的学生的情况。

select*

from xs

where zy='计算机'or zy ='电子'or zy ='数学'

5、查询选修了课程号为的每个学生的姓名及成绩

select sname,grade

from xs,kc,xs_kc

where xs.sno=xs_kc.sno and https://www.360docs.net/doc/568716762.html,o=xs_https://www.360docs.net/doc/568716762.html,o

and https://www.360docs.net/doc/568716762.html,o='101'

6.查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。. select xs.sno,sname,grade

from xs,kc,xs_kc

where xs.sno=xs_kc.sno and https://www.360docs.net/doc/568716762.html,o=xs_https://www.360docs.net/doc/568716762.html,o and

zy ='计算机'and cname='计算机基础'

7、从XS表中查询学生的基本信息，要求按照总学分从高到低排序，

学分相同时，按学号由低到高排序。

select*

from xs

order by sumxf desc,sno

8、求选修了“”课程的学生的平均成绩。

select avg(grade) cj

from xs_kc

where cno='101'

9、求选修了任意一门课程的学生的人数

select count(*) num

from xs_kc

group by sno

10、统计各个专业的学生数。（按专业分组

select count(*) xss

from xs

group by zy

11..

update xs

set sumxf=sumxf+10

from xs

where zy ='计算机'

12、将XS1表中总学分小于分的学生数据删除。

delete from xs

where sumxf<10

13、使用SQL语句创建视图V_SCORE2，显示计算机专业每个学生的学号、姓名、选修的课程名称及其成绩

create view V_SCORE2

as

select xs.sno,sname,cname,grade

from xs,kc,xs_kc

where xs.sno=xs_kc.sno and https://www.360docs.net/doc/568716762.html,o=xs_https://www.360docs.net/doc/568716762.html,o and zy ='计算机' 14、通过V_SCORE2查看计算机专业每门课程的平均成绩。

select avg(grade) avgcj

from V_SCORE2

15、备份数据库STUxxxx到DISK上，设备逻辑名为STUBK，物理路径为D:\ STUxxxx

exec sp_addumpdevice'disk',stu,'d:\stu2011517369'

backup database stu2011517369 to

disk='d:\stu2011517369\stu.bak'

1、编程实现判断学号为，号课程的成绩等级，分以下不及格，-75分（含分）为中，75-90之间为良好（含分），分为优秀。

select sno,cno,jibie=

case

when grade<60 then'不及格'

when grade>60 and grade<75 then'中'

when grade>75 and grade<90 then'良好'

else'优秀'

end

,grade

from xs_kc

where sno='4102101'and cno='103'

2、使用游标实现如下功能：显示每个学生的学号、姓名、各门功课的成绩与平均成绩

declare a1 scroll cursor for

select xs.sno ,sname,grade,avg(grade)

from xs,xs_kc

where xs.sno=xs_kc.sno

group by xs.sno,sname,grade

open a1

fetch first from a1

while@@fetch_status=0

begin

fetch next from a1

end

3、创建一个内嵌表值函数：查询某门课程所有学生的成绩；

检索：所有修“C语言”这门学生的成绩；

create function f(@cname varchar(10))

returns table

as

return(select sno,cname,grade

from kc,xs_kc

where https://www.360docs.net/doc/568716762.html,o=xs_https://www.360docs.net/doc/568716762.html,o and

https://www.360docs.net/doc/568716762.html,o=(select cno

from kc

where cname=@cname))

select*from dbo.f('c语言')

4、创建一存储过程proc1，显示指定学生指定课程的成绩，然后执行该存储过程create proc proc1

@sno char(8),

@cno char(3),

@g smallint output

as

begin

select xs.sno,sname,https://www.360docs.net/doc/568716762.html,o,cname

from xs,kc,xs_kc

where https://www.360docs.net/doc/568716762.html,o=xs_https://www.360docs.net/doc/568716762.html,o and xs.sno=xs_kc.sno and xs.sno=@sno and https://www.360docs.net/doc/568716762.html,o=@cno

select @g=grade

from xs_kc

where sno=@sno and cno=@cno

end

declare @g smallint

exec proc1 '4102101','101',@g output

select @g cj