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

一、在当前服务器上创建数据库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数据库中使用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 xs2.查询XS表中各个同学的姓名、专业名和总学分，只返回结果集的前行select top 5 sname,zy,sumxffrom xs3.查询XS表中每个学生的学号、姓名和年龄信息select sno,sname,year(getdate()-year(birth) nlfrom xs4.查询XS表中专业名为“计算机”或“电子”或“数学”的学生的情况。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=1016.查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。. select xs.sno,sname,gradefrom xs,kc,xs_kcwhere xs.sno=xs_kc.sno and o=xs_o and zy =计算机and cname=计算机基础7、从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。select *from xsorder by sumxf desc,sno8、求选修了“”课程的学生的平均成绩。select avg(grade) cjfrom xs_kcwhere cno=1019、求选修了任意一门课程的学生的人数select count(*) numfrom xs_kcgroup by sno10、统计各个专业的学生数。（按专业分组select count(*) xssfrom xsgroup by zy11.update xsset sumxf=sumxf+10from xswhere zy =计算机 12、将XS1表中总学分小于分的学生数据删除。delete from xswhere sumxf1013、使用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 and zy =计算机14、通过V_SCORE2查看计算机专业每门课程的平均成绩。select avg(grade) avgcjfrom V_SCORE215、备份数据库STUxxxx到DISK上，设备逻辑名为STUBK，物理路径为D: STUxxxxexec sp_addumpdevice disk,stu,d:stu2011517369backup database stu2011517369 to disk=d:stu2011517369stu.bak1、编程实现判断学号为，号课程的成绩等级，分以下不及格，-75分（含分）为中，75-90之间为良好（含分），分为优秀。select sno,cno,jibie=case when grade60 and grade75 and grade90 then 良好 else 优秀end,grade from xs_kcwhere sno=4102101 and cno=1032、使用游标实现如下功能：显示每个学生的学号、姓名、各门功课的成绩与平均成绩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,o,cname from xs,kc,xs_kc