SQL server 数据库练习题及答案（练习4）

一、编程题

班级表(clazz)

字段名称	数据类型	约束等	字段描述
id	int	主键,自增	班级序号
name	varchar(32)	唯一键,不能为空	班级名称
teacher_name	varchar(32)	可空	班主任姓名
description	varchar(1024)	可空	班级简介

学生表(student)

字段名称	数据类型	约束等	字段描述
id	int	主键,自增	学生序号
name	varchar(32)	不能为空	学生姓名
sex	varchar(8)	不能为空,默认男	学生性别
age	int	不为空,年龄在[16,50]之间	学生年龄
clazz_id	int	外键(关联clazz表中的id字段)	所在班级序号
stu_num	varchar(32)	唯一键	学号

课程表(course)

字段名称	数据类型	约束等	字段描述
id	int	主键.自增	课程序号
name	varchar(32)	非空	课程名称
grade_point	float	非空	课程的学分绩点
description	varchar(1024)	非空	课程简介

选课表(selection)

字段名称	数据类型	约束等	字段描述
id	int	主键.自增	选课序号
course_id	int	外键(关联course表中的id字段)	外键,课程序号
student_id	int	外键(关联student表中的id字段)	外键,学生序号
score	float	成绩大于0	成绩

问题要求:

1：具备以上数据库、表、数据完成以下内容

2：查询所有学生信息

3：查询所有班级信息

4：查询所有课程信息

5：查询所有选课信息

6：查询班级序号为2 并且性别为女 并且年龄在16-17之间的学生信息

7：查询班级序号为3的学生序号

8：查询班级序号为3的课程编号及成绩

9：查询选课表中成绩小于60的学生序号

10：查询选课表中成绩小于60的学生所有信息

11：查询所有不及格的学生姓名

12：将成绩小于60的同学姓名添加内容，例如“张三（不及格）”

13：将选课表中成绩小于60的成绩全部修改为60

14：将班级序号为3的成绩按照倒序输出

15：查询出班级序号为3、课程序号为3的第一名的学生序号

16：查询出班级序号为3、课程序号为3的最后一名的学生序号

17：将班级序号为3、课程序号为3的成绩按照分数倒序、学生序号正序输出

18：分组统计每门课程的平均分

19：分组统计每门课程的总分

20：查询课程序号为1的最高分

21：查询课程序号为2的最低分

22：查询参加课程序号为3的考试人数

23：统计平均分中大于60的课程序号，并显示出平均成绩

答案：

/*创建数据库*/
create database gao2
on primary 
(name="gao2_data",
filename="c:\gao\gao2_data.mdf", 
size=8MB,
maxsize=100MB, 
filegrowth=10%) 
log on 
(name="gao2_log.ldf", 
filename="c:\gao\gao2_log.ldf", 
size=1MB, 
filegrowth=10%)

/*切换数据库*/
use gao2

/*班级表*/
create table clazz(
cla_id int identity(1,1) primary key not null,   --identity(1,1)自增 primary key设置主键 not null不能为空
cla_name varchar(32) unique not null,            --unique 唯一值
cla_teacher_name varchar(32) ,
description varchar(1024)
)
/*学生表*/
create table student(
stu_id int identity(10000,1) primary key not null,        --identity(10000,1）从10000开始自增
stu_name varchar(32) not null,
stu_sex varchar(8) default('男') not null,                --default('男') 默认值
stu_age int check(stu_age>=16 and stu_age<=50) not null,  --check(stu_age>=16 and stu_age<=50) 条件判断语句
cla_id int references clazz(cla_id),                      --references 关联外键
stu_num varchar(32) unique 
)
/*课程表*/
create table course(
course_id int identity(1,1) primary key not null,
cou_name varchar(32) not null,
grade_point float not null,
descripion varchar(1024) not null
)
/*选课表*/
create table selection(
sele_id int identity(1,1) primary key not null,
course_id int references course(course_id),
stu_id int references student(stu_id),
score float check(score>0)
)
/*插入数据*/
select * from clazz/*查询语句*/
insert clazz values
('班级一','张三','一班'),
('班级二','李四','二班'),
('班级三','王五','三班'),
('班级四','赵六','四班'),
('班级五','赵云','五班')

select * from student/*查询语句*/
insert student values
('李三','男','21','1','s001'),
('小明','男','20','1','s002'),
('小红','女','19','1','s003'),
('关羽','女','22','1','s004'),
('刘七','女','21','1','s005'),
('陈八','男','20','2','s006'),
('政九','女','19','2','s007'),
('孙十','女','22','2','s008'),
('冯一','女','21','2','s009'),
('吴三','男','22','3','s010'),
('小丽','女','16','3','s011'),
('小雪','女','16','2','s012')

select * from course/*查询语句*/
insert course values
('数学','10','数学课程'),
('英语','8','英语课程'),
('语文','10','语文课程'),
('法语','5','法文课程'),
('日语','5','日文课程')

select * from selection/*查询语句*/
insert selection(stu_id,course_id,score) values
(10000,1,'78'),
(10000,2,'89'),
(10000,3,'90'),
(10001,1,'45'),
(10001,2,'67'),
(10001,3,'90'),
(10002,1,'98'),
(10002,2,'99'),
(10002,3,'100'),
(10003,1,'98'),
(10003,2,'92'),
(10003,3,'90'),
(10004,1,'11'),
(10004,2,'24'),
(10004,3,'16'),
(10005,1,'67'),
(10005,2,'56'),
(10005,3,'66'),
(10006,1,'45'),
(10006,2,'67'),
(10006,3,'89'),
(10007,1,'90'),
(10007,2,'89'),
(10007,3,'97'),
(10008,1,'76'),
(10008,2,'87'),
(10008,3,'88'),
(10009,1,'54'),
(10009,2,'62'),
(10009,3,'61')

--2、
select * from student
--3、
select * from clazz
--4、
select * from course
--5、
select * from selection
--6、
select * from student where cla_id = 2 and stu_sex= '女' and stu_age between 16 and 17
--7、
select stu_id from student where cla_id = 3
--8、
select * from student stu join selection se on stu.stu_id=se.stu_id
where cla_id=3
--9、
select distinct stu_id from selection where score<60
--10、
select * from selection se join student st on se.stu_id=st.stu_id where score<60
--11、
select distinct stu_name from student st join selection se on st.stu_id=se.stu_id where score<60
--12、
update student set stu_name=stu_name+'不及格' where stu_name in(select distinct stu_name from student st join selection se on st.stu_id=se.stu_id where score<60)
select * from student
--13、
update selection set score=60 where score<60
select * from selection
--14、
select * from selection se join student st on se.stu_id=st.stu_id join clazz cl on st.cla_id=cl.cla_id where cl.cla_id=3 order by cl.cla_id desc
--15、
select stu_id,score from selection                --查询成绩表中的选课序号和分数
where sele_id in(                                    --选课序号的条件
select sele_id from student st 
join clazz cl on st.cla_id=cl.cla_id 
join selection se on st.stu_id=se.stu_id 
where st.cla_id=2
)
and 
score=(                                            --分数的条件
select max(score) from selection se
join student st on st.stu_id=se.stu_id 
join clazz cl on st.cla_id=cl.cla_id 
where st.cla_id=2
)
go
--16、
select stu_id,score from selection                --查询成绩表中的选课序号和分数
where sele_id in(                                    --选课序号的条件
select sele_id from student st 
join clazz cl on st.cla_id=cl.cla_id 
join selection se on st.stu_id=se.stu_id 
where st.cla_id=2
)
and 
score=(                                            --分数的条件
select min(score) from selection se
join student st on st.stu_id=se.stu_id 
join clazz cl on st.cla_id=cl.cla_id 
where st.cla_id=2
)
go
--17、
select * from student st 
join clazz cl on st.cla_id=cl.cla_id
join selection se on st.stu_id=se.stu_id
where cl.cla_id=2 and course_id=2
order by score desc,se.stu_id asc
--18、
select course_id, avg(score) from selection group by course_id
--19、
select course_id,sum(score) from selection group by course_id
--20、
select course_id,max(score) from selection group by course_id having course_id=1
--21、
select course_id,min(score) from selection group by course_id having course_id=2
--22、
select course_id,count(score) from selection group by course_id having course_id=3
--23、
select course_id,avg(score) from selection group by course_id having avg(score)>=60