SQL-SERVER-2005上机试卷的答案create-database-stu2011517369

1、一、在当前服务器上创建数据库STUxxxx（其中xxxx为自己的学号）。如：CREATE DATABASE stu200800101。数据库初始大小3M，最大值500M，文件增长率2%create database stu2011517369on primary(name='stu2011517369', filename='d:stu2011517369stu.mdf', size=3mb, maxsize=500mb, filegrowth=2%)drop table xs_kcdrop table xsdrop table kc二、在STUxxxx数据库中

2、使用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

3、 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/

4、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','数学'

5、;,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) i

6、nsert 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)i

7、nsert into xs_kc values('4402130','102',91)1.查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。select distinct zy from xs2.查询XS表中各个同学的姓名、专业名和总学分，只返回结果集的前行select top 5 sname,zy,sumxffrom xs3.查询XS表中每个学生的学号、姓名和年龄信息select sno,sname,year(getdate()-year(birth) nlfrom xs4.查询XS表中专业名为“计算机”或“电子”或“数学”的

8、学生的情况。select *from xswhere zy='计算机' or zy ='电子' or zy ='数学' 5、查询选修了课程号为的每个学生的姓名及成绩select sname,gradefrom xs,kc,xs_kcwhere xs.sno=xs_kc.sno and o=xs_oand o='101'6.查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。. select xs.sno,sname,gradefrom xs,kc,xs_kcwhere xs.sno=xs_kc.sno and o

9、=xs_o and zy ='计算机'and cname='计算机基础'7、从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。select *from xsorder by sumxf desc,sno8、求选修了“”课程的学生的平均成绩。select avg(grade) cjfrom xs_kcwhere cno='101'9、求选修了任意一门课程的学生的人数select count(*) numfrom xs_kcgroup by sno10、统计各个专业的学生数。（按专业分组select count

10、(*) xssfrom xsgroup by zy11.update xsset sumxf=sumxf+10from xswhere zy ='计算机' 12、将XS1表中总学分小于分的学生数据删除。delete from xswhere sumxf<1013、使用SQL语句创建视图V_SCORE2，显示计算机专业每个学生的学号、姓名、选修的课程名称及其成绩create view V_SCORE2as select xs.sno,sname,cname,grade from xs,kc,xs_kc where xs.sno=xs_kc.sno and o=xs_o an

11、d zy ='计算机'14、通过V_SCORE2查看计算机专业每门课程的平均成绩。select avg(grade) avgcjfrom V_SCORE215、备份数据库STUxxxx到DISK上，设备逻辑名为STUBK，物理路径为D: STUxxxxexec sp_addumpdevice 'disk',stu,'d:stu2011517369'backup database stu2011517369 to disk='d:stu2011517369stu.bak'1、编程实现判断学号为，号课程的成绩等级，分以下不及格，-75

12、分（含分）为中，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_kcwhere sno='4102101' and cno='103'2、使用游标实现如下功能：显示每个学生的学号、

13、姓名、各门功课的成绩与平均成绩declare a1 scroll cursor forselect xs.sno ,sname,grade,avg(grade)from xs,xs_kcwhere xs.sno=xs_kc.sno group by xs.sno,sname,gradeopen a1 fetch first from a1while fetch_status=0 begin fetch next from a1 end3、创建一个内嵌表值函数：查询某门课程所有学生的成绩； 检索：所有修“C语言”这门学生的成绩；create function f(cname varchar(10)returns tableasreturn (select sno,cname,grade from kc,xs_kc where o=xs_o and o=(select cno from kc where cname=cname)select * from dbo.f('c语言')4、创建一存储过程proc1，显示指定学生指定课程的成绩，然后执行该存储过程create proc proc1sno char(8),cno char(3),g smallint outputasbegin select xs.sno,sname,