主外键

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)


--学生表数据：插入三个班的学生数据，每个班的相同科目的教师都是相同的
--至少每个班5个人

--教师表数据：语文教师，数学教师，英语教师分别三个，每一个对应一个班级

--分数表数据：学号对应的语文、数学、英语成绩
select * from student
select * from teacher
select * from score








--查询此次语文成绩最高的学生的信息
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 scode,name, sex, banji, (select tname from teacher where teacher.tcode=student.yujiao),shujiao,yingjiao from student


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

select scode, name,sex ,banji,yujiao,shujiao,yingjiao from student
join teacher on student.yujiao=teacher.tname
or student.yujiao=teacher.tname
or student.yujiao=teacher.tname

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

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

select AVG(yufen) 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 @a decimal(18,2)
select @a=AVG(shufen) from score where fcode in
(select scode from student where banji='一班')
declare @b decimal(18,2)
select @b=AVG(shufen) from score where fcode in
(select scode from student where banji='二班')
declare @c decimal(18,2)
select @c=AVG(shufen) 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 shujiao from student where banji=@jie)

--查询英语课程平均分最高的班级的英语教师的信息
declare @a decimal(18,2)
select @a=AVG(yingfen) from score where fcode in
(select scode from student where banji='一班')
declare @b decimal(18,2)
select @b=AVG(yingfen) from score where fcode in
(select scode from student where banji='二班')
declare @c decimal(18,2)
select @c=AVG(yingfen) 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 yingjiao from student where banji=@jie)
    
--求某一个教师教学水平是否达标，学生成绩大于等于80人数超过3个为达标
--刘备

  
  declare @laoshi decimal(18,2)
   select @laoshi=tcode from teacher where tname='刘备'
   declare @renshu int
  select @renshu=COUNT(*) from score where fcode in(select scode from student where shujiao=@laoshi)
  and shufen>=80
  if @renshu>=3
     print '教学水平达标'
  else 
     print  '教学水平不达标'