第6章 数据查询与索引_上机训练

有一个“学生—课程—成绩”数据库，数据库中包括三个表：“学生”表 Student、“课程”表 Course、“成绩”表SC。

列名	说明	数据类型	约束
sno	学号	Char（8）	主键
sname	姓名	Char（8）	NOT Null
sex	性别	Char（2）	取值为“男”或“女”，默认为“男”
brithday	出生日期	datetime
Sdept	所在系	Char（8）

列名	说明	数据类型	约束
Cno	课程号	Char（8）	主键
Cname	课程名	Char（8）
Cpno	先修课程	Char（8）
Ccredit	学分	int

列名	说明	数据类型	约束
sno	学号	Char（8）	主码，引用student的外码
cno	课程号	Char（8）	主码，引用course的外码
Score	分数	int	不为空

要求：
首先建立“学生一课程—成绩”数据库，在该数据库中建立以上三个表，在各表中输入一些记录。

create database 教学管理

--设置数据库为当前数据库
use 教学管理

--创建学生表
create table student
(
	sno char(8) primary key,	--学生学号为主键
	sname char(8) not null,		--学生姓名不能为空
	sex char(2) check(sex in ('男','女')) default '男',	--默认值为“男”，只能从“男”“女”中选填
	birthday date,				--学生出生日期
	Sdept char(8)				--学生所在院系
)

--创建课程表
create table course
(
	cno char(8) primary key,	--课程编号为主键
	cname char(8),				--课程名称
	cpon char(8),				--先修课号
	ccredit smallint			--课程学分
)

--创建成绩表
create table sc
(
	sno char(8),
	cno char(8),
	Ssore int not null,
	primary key (sno,cno),		--主键是复合列，定义在表级约束上
	
	foreign key (sno) references student (sno)
		on update cascade		--级联更新
		on delete cascade,		--级联删除
	
	foreign key (cno) references course (cno)
		on update no action		--不执行任何操作
		on delete no action		--不执行任何操作
)


insert into student values ('20101001','王皓','男','2001-10-20','计算机系')
insert into student values ('20101002','张丽萍','女','1999-8-24','计算机系')
insert into student values ('20101003','王军','男','2000-4-15','计算机系')
insert into student values ('20101004','李建伟','男','2000-7-8','信息系')
insert into student values ('20101005','程爽','女','2002-1-30','信息系')
insert into student values ('20101006','李纹','女','1999-8-14','信息系')

insert into course values ('c01','数据库','c04',4)
insert into course values ('c02','操作系统',null,3)
insert into course values ('c03','高等数学',null,2)
insert into course values ('c04','数据结构',null,4)

insert into sc values ('20101001','c02',84)
insert into sc values ('20101001','c03',74)
insert into sc values ('20101002','c01',86)
insert into sc values ('20101002','c02',89)
insert into sc values ('20101002','c03',92)
insert into sc values ('20101002','c04',78)
insert into sc values ('20101003','c03',91)
insert into sc values ('20101003','c04',95)
insert into sc values ('20101004','c01',81)
insert into sc values ('20101004','c04',86)
insert into sc values ('20101005','c03',78)
insert into sc values ('20101005','c02',85)
insert into sc values ('20101005','c04',91)

1.查询全体学生的详细信息

select * from student

2.查询计算机年龄在18~20岁男生的姓名、年龄和系别

select sname as 姓名,(YEAR(GETDATE())-YEAR(birthday)) as 年龄,Sdept as 系别 
from student 
where YEAR(GETDATE())-YEAR(birthday) in (18,19,20)

3.查询所有姓李且姓名为三个汉字的学生的姓名、性别、出生日期和系别,并按出生日期降序排序

select sname as 姓名,sex as 性别,birthday as 出生日期,Sdept as 系别 
from student
where sname like '李__'
order by birthday desc

4.查询所有选修了“数据库”课程的学生学号、姓名和系

select sno as 学号,sname as 姓名,Sdept as 系别 from student where sno in
(select sno from sc where cno = 
(select cno from course where cname='数据库'))

5.查询选修了两门以上课程的学生学号

select sno from sc
group by sno
having COUNT(cno)>2

6.统计每个系的学生人数

select Sdept as 系名,COUNT(sno) as 人数  
from student
group by Sdept

7.统计每门课程的修课人数和考试最高分

select cno as 课程编号, COUNT(sno) as 选课人数, MAX(Ssore) as 课程最高分
from sc
group by cno

8.统计每个学生的选课门数和考试总成绩

select sno as 学号, COUNT(cno) as 选课门数, SUM(Ssore) as 考试总成绩
from sc
group by sno

9.查询选修了c02号课程的学生姓名和所在系

select sname as 姓名,Sdept as 系别 
from student
where sno in
(select sno from sc where cno='c02')

10.查询数学成绩在80分以上的学生学号和姓名

select sno as 学号, sname as 姓名
from student
where sno in
(select sno from sc where cno=
(select cno from course where cname='高等数学')
and Ssore>80)

11.将所有选修了c01号课程的成绩加5分

update sc set Ssore+=5
where sno in
(select sno from sc where cno='c01')

12.删除所有选择“高等数学”的同学的选课记录

delete from sc where sno in 
(select sno from sc where cno =
(select cno from course where cname='高等数学'))