数据库原理与应用半期测验

最新推荐文章于 2024-11-12 00:01:05 发布

扬帆起航136

最新推荐文章于 2024-11-12 00:01:05 发布

阅读量302

点赞数 1

分类专栏： SQLserver 文章标签： sql 数据库开发语言

本文链接：https://blog.csdn.net/qq_62994974/article/details/126537254

版权

SQLserver 专栏收录该内容

6 篇文章 2 订阅

订阅专栏

一表结构操作 （2*3=6分）

1 在平时使用的数据库中，建立一个“考勤表”，用于记录学生考勤情况，表包括的属性有学号，类别，日期，备注。其中学号与“学生表”中的学号属性外键约束，类别只能取“迟到”，“早退”，“旷课”，“事假”四值之一。备注最多填写40个可变长字符。

答：

create table 考勤表 (

学号 nchar(10) primary key,

类别 nchar(2) not null check (类别='迟到'or 类别='早退'or 类别='旷课'or 类别='事假') ,

日期 smalldatetime not null,

备注 nvarchar(40) not null,

foreign key(学号) references 学生表(学号)

)

1 在学生表中添加新字段:婚否 bit , 籍贯字符(12), 高考成绩 (数值 5,1)

答：alter table 学生表 add 婚否 bit,籍贯 nchar(12),高考成绩 numeric(5,1)

2 修改高考成绩字段为 数值 6,2

答：alter table 学生表 alter column 高考成绩 numeric(6,2)

二 简单查询操作（2*10=20分）

1从学生表中查询出所有学生的学号和生日

答：select 学号,生日 from 学生表

2从课程表中查询出所有记录的所有字段的数据

答：select *from 课程表 where 2<1

3从成绩表中查询出101号课程的成绩在>=60的记录情况

答：select *from 成绩表 where 课程号='101' and 成绩>=60

4 从学生表中查出所有1989年以后出生的男生情况

答：select *from 学生表 where year(出生日期)>1989 and 性别='男'

5 从学生表中查出学号,姓名,身高, 且结果中的身高 列重命列名为”高度”

答：select 学号,姓名,身高 as 高度 from 学生表

6 查出成绩表中有哪些同学选了课

答：select distinct 学号 from 成绩表

7在学生表中把李四方的生日提前10天,且身高增加10厘米

答：update 学生表 set 出生日期=出生日期-10,身高=身高+10 where 姓名='李四方'

8 查出成绩表中101和103课程成绩在70以上的情况

答：select *from 成绩表 where 课程号 in ('101','103') and 成绩>=70

9 从课程表中查询出课程名中含有“计算机”三字的情况

答：select * from 课程表 where 课程名称 like '%计算机%'

10 查出成绩表中的学号和每个人的所有课程的平均分,单人最高成绩

答：select 学号 ,AVG(成绩) as 平均分,max(成绩) as 个人最高成绩 from 成绩表 group by 学号

三 复杂查询（2*20=40分）

1 查出学生表中语文成绩最高前四名

答：select top 4 学生表.* from 学生表 inner join 成绩表 on 成绩表.学号=学生表.学号 inner join 课程表 on 课程表.课程号=成绩表.课程号 where 课程名称='语文'

2 查出学生表中语文成绩较差的50%

答：select top 50 percent 学生表.* from 学生表 inner join 成绩表 on 成绩表.学号=学生表.学号 inner join 课程表 on 课程表.课程号=成绩表.课程号 where 课程名称='语文' order by 成绩 asc

3 查询出所有成都地区的学生的学号,姓名,性别,生日

答：select 学号,姓名,性别,出生日期 from 学生表 where 家庭住址 like '%成都%'

4 查出”大学英语”和”高等数学”两门课程的成绩情况:学号,课程号,成绩(注意课程表中的高等数学要分上下,如何处理?)

答：select 学号,课程号,成绩 from 成绩表 where 课程号 in (select 课程号 from 课程表 where 课程名称 ='大学英语' or 课程名称 like '高等数学_')

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 学生表 inner join 成绩表 on 成绩表.学号=学生表.学号 inner join 课程表 on 课程表.课程号=成绩表.课程号 where 课程名称='语文' group by 性别

8 查出成绩表中哪些课程选课人数>=5

答：select 课程号,count(*) as 选课人数 from 成绩表 group by 课程号 having count(*)>=5

9 统计成绩表中各个学生选课门数

答：select 课程号,count(*) as 选课人数 from 成绩表 group by 课程号

10学生表中有哪几种姓氏?

答： select count(distinct(left(姓名,1))) as 姓氏数量 from 学生表

11统计学生表中团员和非团员各自人数

答： select 是否团员,count(*) as 人数 from 学生表 group by 是否团员

12查出学生表中所有姓王,张,杨的人

答：select *from 学生表 where left(姓名 ,1) in ('王','张','李')

13 查出成绩表中所有选了101,103,105课程的各自人数

答：select 课程号,count(*) as 选课人数 from 成绩表 where 课程号 in ('101','103','105') group by 课程号

14 查出成绩表中选课门数在3门以上的学生姓名有哪能些?

答：select 学号,姓名 from 学生表 where 学号 in (select 学号 from 成绩表 group by 学号 having count(*)>3)

15 在学生表中,求男生团员占全班人数的百分比是多大

答：select'男生团员占全班人数的百分比是'+ convert(nvarchar(6),convert(numeric(6,2),((select count(*)from 学生表 where 性别='男' and 是否团员='是' )*1.0/(select count(*) from 学生表))*100))+'%'

16 求李大方同学的所有选修课程的平均成绩

答：select avg(成绩) as 所有课程平均成绩 from 学生表 inner join 成绩表 on 成绩表.学号=学生表.学号 where 姓名='李大方' group by 成绩表.学号

17 求出”大学英语”课程的最高分

答：select max(成绩) as 最高分 from 成绩表 inner join 课程表 on 课程表.课程号=成绩表.课程号 where 课程名称='大学英语'

18 求出成绩表中平均成绩在90以上的有哪些课(查询结果包括课程号,课程名称,学分)

答：select 课程表.课程号,课程表.课程名称,学分 from 课程表 where 课程号 in (select 课程号 from 成绩表 group by 课程号 having AVG(成绩)>=90)

19 从学生表中无顺序地随机选了四个学生数据（学号，姓名，性别）显示

答：select top 4 学号,姓名,性别 from 学生表 order by NEWID()

20 对课程表的课程号建立一名为“indexkch”的非聚集索引

答：create index indexch on 课程表(课程号)

四 程序设计及综合应用 (34分)

1、根据常识和所给标注约束，建立以下三张表,且每张表有适当数据内容 （9分）

人事表（员工号PK ，姓名，性别，部门号FK）

部门表（部门号PK, 部门名称，负责人）

工资表（员工号FK， 基本工资，津贴，奖金）

答：代码和表内容截图

create table 人事表

(员工号 nchar(4) primary key,

姓名 nvarchar(4) not null,

性别 nchar(1) not null,

部门号 nchar(4) not null,

foreign key (部门号) references 部门表(部门号)

)

create table 部门表(

部门号 nchar(4) primary key,

部门名称 nchar(4) not null ,

负责人 nchar(4) not null

)

create table 工资表(

员工号 nchar(4) foreign key(员工号) references 人事表(员工号),

基本工资 smallint not null,

津贴 smallint not null,

奖金 smallint not null

)

insert into 部门表(部门号,部门名称,负责人)

values('101','学习部','小明'),

('102','组织部','李华')

insert into 人事表(员工号,姓名,性别,部门号)

values('D001','库里','男','101'),

('D002','张华','女','102')

insert into 工资表(员工号,基本工资,津贴 ,奖金)

values

('D001',5000,500,50),

('D002',9000,400,100)

2、 为人事表，部门表，工资表建立一个视图（view1），内容为： 员工号，姓名，性别，部门名称，负责人，基本工资，津贴，奖金 （5分）

答：

create view view1 as

select 人事表.员工号,姓名,性别,部门名称,负责人,基本工资,津贴,奖金 from 部门表 inner join 人事表 on 部门表.部门号=人事表.部门号 inner join 工资表 on 工资表.员工号=人事表.员工号

3、建立一个存储过程 ( mypro ),完成功能: 输入一个部门名称,使用变量返回该部门的总人数,男性人数,女性人数（5分）

答：

create procedure mypro @bmh nchar(4),@res nvarchar(20) output as

begin

declare @zrs nchar(2),@nan nchar(2),@nv nchar(2)

select @zrs= count(*) from 部门表 where 部门名称=@bmh

select @nan= count(*) from 部门表 inner join 人事表 on 人事表.部门号=部门表.部门号 where 部门名称=@bmh and 性别='女'

select @nv= count(*) from 部门表 inner join 人事表 on 人事表.部门号=部门表.部门号 where 部门名称=@bmh and 性别='男'

set @res=@zrs+'|'+@nan+'|'+@nv

end

4、编写一个函数myfunc( ) ,功能: 输入一个部门名称, 计算该部门的所有人的总工资的平均值, 如果平均值在20000以上则返回“小资”，在[10000-20000）返回“小康”，在[5000，10000）返回“温饱”，5000以下返回“拖祖国后腿”.。 通过“部门表”调用myfunc函数统计出各部门平均工资情况. （10分）