create database stuinfo

create table student

(

   mid char(10) not null primary key,

   mname char(50) not null

)

create table  course

(

  fid char(10) not null primary key,

  fname char(50) not null

)

create table score

(

   sid int identity(1,1) primary key,

   fid char(10) not null,

   mid char(10) not null,

   score int,

   foreign key(fid) references course(fid),

   foreign key(mid) references student(mid)

)

INSERT INTO course(FID,FName)VALUES('F001','语文')

INSERT INTO course(FID,FName)VALUES('F002','数学')

INSERT INTO course(FID,FName)VALUES('F003','英语')

INSERT INTO course(FID,FName)VALUES('F004','历史')

--学生表中插入数据--

INSERT INTO student(MID,MName)VALUES('M001','张萨')

INSERT INTO student(MID,MName)VALUES('M002','王强')

INSERT INTO student(MID,MName)VALUES('M003','李三')

INSERT INTO student(MID,MName)VALUES('M004','李四')

INSERT INTO student(MID,MName)VALUES('M005','阳阳')

INSERT INTO student(MID,MName)VALUES('M006','虎子')

iNSERT INTO student(MID,MName)VALUES('M007','夏雪')

INSERT INTO student(MID,MName)VALUES('M008','璐璐')

INSERT INTO student(MID,MName)VALUES('M009','珊珊')

INSERT INTO student(MID,MName)VALUES('M010','香奈儿')

INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',78)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M001',67)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M001',89)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M001',76)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M002',89)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M003',70)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M003',87)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M003',92)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M003',56)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M004',80)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M004',78)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M004',97)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M004',66)

INSERT INTO Score(FID,MID,Score)VALUES('F001','M006',88)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M006',86)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M006',79)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M007',77)

INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48)

INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75)

INSERT INTO Score(FID,MID,Score)VALUES('F002','M009',88)

select * from score

select mname,语文=

             max(case

               when course.fname='语文' then score.score

             end)

            ,数学=

             max(case

               when course.fname='数学' then score

             end)

            ,英语=max(case

               when course.fname='英语' then (score)

             end)

            ,历史=max(case

               when course.fname='历史' then (score)

             end)

from student,score,course

where student.mid=score.mid and score.fid=course.fid

group by mname

select   姓名=mname,课程=fname,成绩=score  from student,course,score where score<70 and student.mid=score.mid and score.fid=course.fid

select 姓名=(select mname from student where mid=score.mid ),

       课程=(select fname from course where fid=score.fid),

       成绩=score

from score where score<70

--select * from score where score<70

select 姓名=(select mname from student where mid=score.mid),平均分=avg(score)  from score group by mid order by 平均分 desc

select distinct mid from score

select mid from student