数据库开发基础-授课-2

create database xuexiao
go

use xuexiao
go

--表一:学生表   student
--学号:code   int        （主键）从1开始
--姓名:name  varchar(50)
--性别:sex        char(10)
--班级:banji    char(10)
--语文教师编号:yujiao        int    
--数学教师编号:yujiao        int    
--英语教师编号:yujiao        int    
create table student
(
 scode int primary key identity(10001,1)not null,--主键
 name varchar(50),
 sex        char(10),
 banji     char(10),
 yujiao        int    ,--外键
 shujiao    int,--外键
 yingjiao   int,--外键
)
--插入学生表数据
--一班数据
insert into student values('王红','女','一班',1001,1004,1007)
insert into student values('周瑜','男','一班',1001,1004,1007)
insert into student values('张飞','男','一班',1001,1004,1007)
insert into student values('乔雨','女','一班',1001,1004,1007)
insert into student values('王发','女','一班',1001,1004,1007)
--二班数据
insert into student values('杜鹃','女','二班',1002,1005,1008)
insert into student values('白小飞','男','二班',1002,1005,1008)
insert into student values('刘小峰','男','二班',1002,1005,1008)
insert into student values('张强','男','二班',1002,1005,1008)
insert into student values('周莹莹','女','二班',1002,1005,1008)
--三班数据
insert into student values('赵晓红','女','三班',1003,1006,1009)
insert into student values('王国章','男','三班',1003,1006,1009)
insert into student values('张三更','男','三班',1003,1006,1009)
insert into student values('潘晓丽','女','三班',1003,1006,1009)
insert into student values('楚红红','女','三班',1003,1006,1009)

--表二:教师表        teacher
--教师编号:code        int    （主键）            从1001开始
--负责课程:lesson        char(10)（语文、数学、英语）
--年龄:age            int
--生日:birth        datetime
create table teacher
(
  tcode        int primary key identity(1001,1)not null,--主键
  tname      varchar(20),
  lesson        char(10),
  age            int,
  birth        datetime,
)
insert into teacher values('张斌','语文',30,1986-3-30)
insert into teacher values('齐红','语文',40,1976-9-20)
insert into teacher values('王雪丽','语文',25,1991-2-3)

insert into teacher values('崔刚','数学',36,1980-2-10)
insert into teacher values('余兵','数学',30,1986-6-30)
insert into teacher values('刘备','数学',50,1966-7-20)

insert into teacher values('张灿灿','英语',30,1986-5-5)
insert into teacher values('王丽丽','英语',26,1990-7-7)
insert into teacher values('张婷婷','英语',35,1981-6-3)

--表三：分数表        score
--学号：code        int        （学生学号的外键）
--语文分数：yufen        decimal（18,2）
--数学分数：shufen        decimal（18,2）
--英语分数：yingfen        decimal（18,2）

  create table    score
  (
    fcode    int    ,--外键
    yufen   decimal(18,2),
    shufen    decimal(18,2),
    yingfen    decimal(18,2),
)
--插入分数数据
--一班数据
insert into score values(10001,80,90,70)
insert into score values(10002,50,80,60)
insert into score values(10003,60,55,80)
insert into score values(10004,70,90,55)
insert into score values(10005,50,70,80)
--二班数据
insert into score values(10006,90,60,80)
insert into score values(10007,60,50,66)
insert into score values(10008,70,82,59)
insert into score values(10009,63,87,85)
insert into score values(10010,45,55,64)
--三班分数
insert into score values(10011,90,90,70)
insert into score values(10012,60,80,70)
insert into score values(10013,55,70,56)
insert into score values(10014,70,80,70)
insert into score values(10015,85,55,70)


--查询此次语文成绩最高的学生的信息
select * from student where scode=
(
    select top 1 fcode from score order by yufen desc
)
--查询此次数学成绩最高的学生的信息
select * from student where scode=
(select top 1 fcode from score order by shufen desc)

--查询此次英语成绩最高的学生的信息
select *from student where scode=
(select top 1 fcode from score order by yingfen desc)

--查询此次语文成绩最低的学生的信息
select *from student where scode=
(
select top 1 fcode from score order by yufen 
)
--查询此次数学成绩最低的学生的信息
select * from Student where scode=
(select top 1 fcode from score order by shufen)
--查询此次英语成绩最低的学生的信息
select * from student where scode =
(select top 1 fcode from score order by yingfen )
--查询此次语文成绩最低的学生所任课教师的信息
select * from teacher where tcode=
(select  yujiao from student where scode=
(select top 1 fcode from score order by yufen)
)

--查询此次数学成绩最低的学生所任课教师的信息
select * from teacher where tcode=
(select  shujiao from student where scode=
(select top 1 fcode from score order by shufen)
)


--查询此次英语成绩最低的学生所任课教师的信息
select * from teacher where tcode=
(select  yingjiao from student where scode=
(select top 1 fcode from score order by yingfen)
)

--查询此次语文成绩最高的学生所任课教师的信息
select * from teacher where tcode=
(select  yujiao from student where scode=
(select top 1 fcode from score order by yufen desc)
)
--查询此次数学成绩最高的学生所任课教师的信息
select * from teacher where tcode=
(select  shujiao from student where scode=
(select top 1 fcode from score order by shufen desc)
)
--查询此次英语成绩最高的学生所任课教师的信息
select * from teacher where tcode=
(select  yingjiao from student where scode=
(select top 1 fcode from score order by yingfen desc)
)
--查询学生信息，将所有语文任课教师编号改为该科目的任课教师名字显示
select student.scode,name,sex,banji,teacher.tname from student
 join teacher on student.yujiao=teacher.tcode

--查询学生信息，将所有任课教师编号改为该科目的任课教师名字显示
select scode,name,sex,banji,
(select tname from teacher where student.yujiao=teacher.tcode) as 语文,
(select tname from teacher where student.shujiao=teacher.tcode) as 数学,
(select tname from teacher where student.yingjiao=teacher.tcode) as 英语
from student


select student.scode,name,sex,banji,teacher.tname,
(select tname from teacher where student.shujiao=teacher.tcode) as 数学,
(select tname from teacher where student.yingjiao=teacher.tcode) as 英语
from student 
join teacher on student.yujiao=teacher.tcode



--查询各个学生的学号,姓名,语文分数,数学分数,英语分数,以及三门课里面每一门课的任课教师姓名
select scode,name,banji,
(select yufen from score where student.scode=score.fcode) as 语文分数,
(select tname from teacher where student.yujiao=teacher.tcode) as 语文教师,
(select shufen from score where student.scode=score.fcode) as 数学分数,
(select tname from teacher where student.shujiao=teacher.tcode) as 数学教师,
(select yingfen from score where student.scode=score.fcode) as 英语分数,
(select tname from teacher where student.yingjiao=teacher.tcode) as 英语教师
from student

--查询每个班级里的语文最高分
--一班
select top 1 yufen from score where fcode in
(select scode from student where banji ='一班')order by yufen desc
--二班
select top 1 yufen from score where fcode in
(select scode from student where banji ='二班班')order by yufen desc
--三班
select top 1 yufen from score where fcode in
(select scode from student where banji ='三班')order by yufen desc

--查询每个班级里的数学最高分
--一班
select top 1 shufen from score where fcode in
(select scode from student where banji ='一班')order by shufen desc
--二班
select top 1 shufen from score where fcode in
(select scode from student where banji ='二班')order by shufen desc
--三班
select top 1 shufen from score where fcode in
(select scode from student where banji ='三班')order by shufen desc


--查询每个班级里的英语最高分
--一班
select top 1 yingfen from score where fcode in
(select scode from student where banji ='一班')order by yingfen desc
--二班
select top 1 yingfen from score where fcode in
(select scode from student where banji ='二班')order by yingfen desc
--三班
select top 1 yingfen from score where fcode in
(select scode from student where banji ='三班')order by yingfen desc

--查看每个班的语文平均分
select AVG(yufen) as 一班 from score where fcode in
(select scode from student where banji='一班')

select AVG(yufen) as 二班 from score where fcode in
(select scode from student where banji='二班')

select AVG(yufen)  as 三班 from score where fcode in
(select scode from student where banji='三班')
--查询语文课程平均分最高的班级的语文教师的信息
declare @a decimal(18,2)
select @a=AVG(yufen) from score where fcode in
(select scode from student where banji='一班')
declare @b decimal(18,2)
select @b=AVG(yufen)  from score where fcode in
(select scode from student where banji='二班')
declare @c decimal(18,2)
select @c =AVG(yufen)   from score where fcode in
(select scode from student where banji='三班')
declare @jie varchar(20)
if @a>@b and @a>@c
      set @jie='一班'
 else   if @b>@a and @b>@c
      set @jie='二班'
 else   if @c>@a and @c>@b
      set @jie='三班'
      
select * from teacher where tcode in
(select yujiao from student where banji=@jie)

--查询数学课程平均分最高的班级的数学教师的信息
declare @x decimal(18,2)
select @x=AVG(shufen) from score where fcode in
(select scode from student where banji='一班')
declare @y decimal(18,2)
select @y=AVG(shufen)  from score where fcode in
(select scode from student where banji='二班')
declare @z decimal(18,2)
select @z =AVG(shufen)   from score where fcode in
(select scode from student where banji='三班')
declare @guo varchar(20)
if @x>@y and @x>@z
      set @guo ='一班'
 else   if @y>@x and @y>@z
      set @guo ='二班'
 else   if @z>@x and @z>@y
      set @guo ='三班'
      
select * from teacher where tcode in
(select shujiao from student where banji=@guo)

--查询英语课程平均分最高的班级的英语教师的信息
declare @x decimal(18,2)
select @x=AVG(yingfen) from score where fcode in
(select scode from student where banji='一班')
declare @y decimal(18,2)
select @y=AVG(yingfen)  from score where fcode in
(select scode from student where banji='二班')
declare @z decimal(18,2)
select @z =AVG(yingfen)   from score where fcode in
(select scode from student where banji='三班')
declare @guo varchar(20)
if @x>@y and @x>@z
      set @guo ='一班'
 else   if @y>@x and @y>@z
      set @guo ='二班'
 else   if @z>@x and @z>@y
      set @guo ='三班'
      
select * from teacher where tcode in
(select yingjiao from student where banji=@guo)