SQLserver基础知识例题
一表结构操作 （2*3=6分）
1 在平时使用的数据库中，建立一个“考勤表”，用于记录学生考勤情况，表包括的属性有学号，类别，日期，备注。其中学号与“学生表”中的学号属性外键约束，类别只能取“迟到”，“早退”，“旷课”，“事假”四值之一。备注最多填写40个可变长字符。
答：
create table 考勤表( 学号 char(8) not null ,类别 nchar(2) not null check(类别='迟到'or 类别='早退'or 类别='旷课'or 类别='事假'),
日期 smalldatetime not null ,备注 nvarchar(40),
foreign key(学号)references 学生表(学号)
)
1 在学生表中添加新字段:婚否 bit , 籍贯 字符(12), 高考成绩 (数值 5,1)
答：
alter table 学生表
add 婚否 bit,籍贯 nvarchar(12),高考成绩 numeric(5,1)
2 修改高考成绩字段为 数值 6,2
答：
alter table 学生表
alter column 高考成绩 numeric(6,1)
二 简单查询操作（2*10=20分）
1从学生表中查询出所有学生的学号和生日
答：
select 学号 ,出生日期 as 生日 from 学生表
2从课程表中查询出所有记录的所有字段的数据
答：
select * from 课程表
3从成绩表中查询出101号课程的成绩在>=60的记录情况
答：select * from 成绩表 where 成绩>60 and 课程号='101'
4 从学生表中查出所有1989年以后出生的男生情况
答：select *from 学生表 where 性别='男' and (datepart(yy,出生日期)>1989)
5 从学生表中查出学号,姓名,身高, 且结果中的身高 列重命列名为”高度”
答：select 学号,姓名,身高 as 高度 from 学生表
6 查出成绩表中有哪些同学选了课
答：select distinct 姓名 from 成绩表 inner join 学生表 on 成绩表.学号=学生表.学号
7在学生表中把李四方的生日提前10天,且身高增加10厘米
答：update 学生表 set 出生日期=DATEADD(dd,-10,出生日期),身高=身高+0.1 where 姓名='李四方'
8 查出成绩表中101和103课程成绩在70以上的情况
答：select *from 成绩表 where 成绩>70 and 课程号 in('101','103')
9 从课程表中查询出课程名中含有“计算机”三字的情况
答：select * from 课程表 where 课程名称 like '%计算机%'
10 查出成绩表中的学号和每个人的所有课程的平均分,单人最高成绩
答：select 学号 , avg(成绩)as 科平均分 ,max(成绩) as 最高成绩 from 成绩表 group by(学号)
三 复杂查询（2*20=40分）
1 查出学生表中语文成绩最高前四名
答：select top 4 a.* from 学生表 a inner join 成绩表 b on a.学号=b.学号 inner join 课程表 c on b.课程号=c.课程号
where 课程名称='语文' order by 成绩 desc
2 查出学生表中语文成绩较差的50%
答：select top 50 percent a.* from 学生表 a inner join 成绩表 b on a.学号=b.学号 inner join 课程表 c on b.课程号=c.课程号
where 课程名称='大学英语' order by 成绩 asc
3 查询出所有成都地区的学生的学号,姓名,性别,生日
答：select 学号,姓名,性别,出生日期 from 学生表 where 籍贯 like '%成都%'
4 查出”大学英语”和”高等数学”两门课程的成绩情况:学号,课程号,成绩(注意课程表中的高等数学要分上下,如何处理?)
答：select 学号,课程号,成绩 from 成绩表 where 课程号 in(select 课程号 from 课程表
where 课程名称 in('大学英语','高等数学上','高等数学下'))
5从成绩表中查询出成绩在85和95之间的人的情况（between … and …的用法）
答：select * from 成绩表 where 成绩 between 85 and 95
6 查询成绩表,结果先按课程号升序排序，同一门课程的内部再按成绩降序排列
答：
select *from 成绩表 order by 课程号 asc,成绩 desc
7 在 学生表中根据性别进行分类,分别求男女生各自语文和身高的平均值
答：select avg(成绩)as 语文成绩平均值,avg(身高) as 身高平均值 from 学生表 a inner join 成绩表 b on a.学号=b.学号 inner join 课程表 c on b.课程号=c.课程号
where 课程名称='语文' group by 性别
8 查出成绩表中哪些课程选课人数>=5
答：select 课程号,count(课程号) as 选课人数 from 成绩表 group by 课程号 having count(课程号)>=5
9 统计成绩表中各个学生选课门数
答：select 学号,count(学号) as 选课门数 from 成绩表 group by 学号
10学生表中有哪几种姓氏?
答：select distinct substring(姓名,1,1) as 姓氏 from 学生表
11统计学生表中团员和非团员各自人数
答：
select 是否团员,COUNT(学号) as 人数 from 学生表 group by 是否团员
12查出学生表中所有姓王,张,杨的人
答：select *from 学生表 where 姓名 like '[王张李]%'
13 查出成绩表中所有选了101,103,105课程的各自人数
答：select count(课程号) from 课程表 where 课程号 in('101','103','105') group by 课程号 order by 课程号 desc
14 查出成绩表中选课门数在3门以上的学生姓名有哪能些?
答：select 姓名,count(b.学号) as 选课门数 from 学生表 a inner join 成绩表 b on a.学号=b.学号
group by b.学号,姓名 having count(b.学号)>3
15 在学生表中,求男生团员占全班人数的百分比是多大
答：declare @Nty int ,@zty int
select @Nty=count(*) from 学生表 where 性别='男' and 是否团员='是'
select @zty=count(*) from 学生表
select (@Nty*0.1/@zty)
16 求李大方同学的所有选修课程的平均成绩
答：select avg(成绩) from 学生表 a inner join 成绩表 b on a.学号=b.学号 where 姓名='李大方'
17 求出”大学英语”课程的最高分
答：select max(成绩) from 成绩表 where 课程号 in(select 课程号 from 课程表 where 课程名称='大学英语')
18 求出成绩表中平均成绩在90以上的有哪些课(查询结果包括课程号,课程名称,学分)
答：select 课程号,课程名称,学分 from 课程表 where 课程号 in (select 课程号 from 成绩表 group by 课程号 having avg(成绩)>90)
19 从学生表中无顺序地随机选了四个学生数据（学号，姓名，性别）显示
答：select top 5 学号,姓名,性别 from 学生表 order by NEWID()
20 对课程表的课程号建立一名为“indexkch”的非聚集索引
答：create index indexkch on 课程表(课程号)
四 程序设计及综合应用 (34分)
1、根据常识和所给标注约束，建立以下三张表,且每张表有适当数据内容 （9分）
人事表（员工号PK ，姓名，性别，部门号FK）
部门表（部门号PK, 部门名称，负责人）
工资表（员工号FK， 基本工资，津贴，奖金）
答：代码和表内容截图
create table 人事表 (
员工号 char(6) primary key not null ,姓名 nvarchar(10)not null,性别 nchar(2)not null DEFAULT '男' check(性别='男'or 性别='女')
部门号 char(4) not null
foreign key(部门号)references 部门表(部门号)
)
create table 部门表(
部门号 char(4) primary key not null , 部门名称 nvarchar(10)not null,负责人 nvarchar(10)not null
)
create table 工资表 (
员工号 char(6) not null , 基本工资 decimal(18,2) not null, 津贴 decimal(18,2) ,奖金 decimal(18,2)
foreign key(员工号)references 员工表(员工号)
)
2、 为人事表，部门表，工资表建立一个视图（view1），内容为： 员工号，姓名，性别，部门名称，负责人，基本工资，津贴，奖金 （5分）
答：
create view view1 as
select 人事表.员工号,姓名,性别,部门名称,负责人,基本工资,津贴,奖金
from 人事表
inner join 部门表 on 人事表.部门号=部门表.部门号
inner join 工资表 on 人事表.员工号=工资表.员工号
3、建立一个存储过程 ( mypro ),完成功能: 输入一个部门名称,使用变量返回该部门的总人数,男性人数,女性人数（5分）
答：
create procedure mypro @depName nvarchar(5),@tot int output,@male int output,@famale int output
as
select @tot=COUNT(员工号) from 部门表
inner join 人事表 on 部门表.部门号=人事表.部门号
where 部门表.部门名称=@depName
select @male=COUNT(员工号) from 部门表
inner join 人事表 on 部门表.部门号=人事表.部门号
where 性别='男' and 部门表.部门名称=@depName
select @famale=COUNT(员工号) from 部门表
inner join 人事表 on 部门表.部门号=人事表.部门号
where 性别='女' and 部门表.部门名称=@depName
4、编写一个函数myfunc( ) ,功能: 输入一个部门名称, 计算该部门的所有人的总工资的平均值, 如果平均值在20000以上则返回“小资”，在[10000-20000）返回“小康”，在[5000，10000）返回“温饱”，5000以下返回“拖祖国后腿”.。 通过“部门表”调用myfunc函数统计出各部门平均工资情况. （10分）
答：
create function myfunc(@depName nvarchar(5)) returns nvarchar(5)
begin
declare @avg_salary numeric(8,2)
select @avg_salary=AVG(基本工资)
from 人事表
inner join 部门表 on 人事表.部门号=部门表.部门号
inner join 工资表 on 人事表.员工号=工资表.员工号
where 部门表.部门名称=@depName
group by 部门表.部门号
declare @res nvarchar(5)
if @avg_salary>=20000 set @res='小资'
else if 10000<=@avg_salary and @avg_salary<20000 set @res='小康'
else if 5000<=@avg_salary and @avg_salary<10000 set @res='温饱'
else set @res='拖祖国后腿'
return @res
end
5、编写一个名为“RsDel”触发器，功能：当用户要对人事表中的记录删除时，先检查工资表中的是否有该人工资数据记录，如果有则事先删除工资表中的该人的记录（5分）
答：
create trigger RsDel on 人事表 after
delete
as
begin
declare @ygh nchar(6)
select @ygh=员工号 from inserted
if exists(select 员工号 from 工资表 where 员工号=@ygh)
begin
rollback
end
end
