sql查询，学生成绩举例sql

sql查询，学生成绩举例sql
学生表：student field:stuID,chinesename,sex,scholarship(奖学金)
成绩表：grade field:graID,stuID,courceID,grade
课程表：cource field:courceID,courceName,xuefen

1.查询分数都在90分以上的学生的stuID,chinesename
方法一：
select * from student where stuID not in(select stuID from grade where grade<=90)
方法二：
select * from student where stuID in
(select stuID from grade where grade >=90 group by stuID having count(*)=(select count(*) from cource) )
方法三：
select s.stuID,s.chinesename from student s join grade g on s.stuID=g.stuID join cource c on g.courceID=c.courceID
where grade>=90 group by s.stuID,s.chinesename having count(g.courceID) = (select count(*) from cource)

2.查询至少有一门分数大于80分学生的stuID,chinesename

方法一：
select distinct s.* from student s join grade g on s.stuID=g.stuID
join cource c on g.courceID=c.courceID where g.grade>=80

方法二：

select * from student where stuID in (select stuID from grade where grade >80)

use test
create table student(stuID int,chinesename nvarchar(20),sex int,scholarship money)
insert into student select 1,'allen',1,500 union all
select 2,'tom',1,0 union all
select 3,'jordan',1,1000 union all
select 4,'kobe',1,0
go
create table grade(graID int,stuID int,courceID int,grade decimal(4,1))
insert into grade select 1,4,1,80.8 union all
select 2,4,2,90 union all
select 3,4,3,100 union all
select 4,3,1,55 union all
select 5,3,2,86 union all
select 6,3,3,90.5 union all
select 7,1,1,95 union all
select 8,1,2,99.5 union all
select 9,1,3,96
go
create table cource(courceID int,courceName nvarchar(20),xuefen int)
insert into cource select 1,'语文',5 union all
select 2,'数学',6 union all
select 3,'外语',6