select Cid from sc order by Sid HAVING score >
#查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select s.,a.,b.* from student s,
(select Sid,score from sc where Cid =‘01’ ) a,
(select Sid,score from sc where Cid =‘02’ ) b
WHERE a.Sid = b.Sid and a.score > b.score and s.Sid = a.Sid

#查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select s.,c. ,AVG(score) as se from student s, sc c
WHERE s.Sid = c.Sid
GROUP BY s.Sid HAVING se >60

#查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) where 返回得值 不会是NULL
select * from student s LEFT JOIN sc on sc.Sid = s.Sid GROUP BY s.Sid – 返回得值 会包括NULL
select * from student s ,sc WHERE sc.Sid = s.Sid GROUP BY s.Sid – 返回得值 不会是NULL

select s.sid, s.sname, count(cid) as 选课总数, sum(score) as 总成绩
from student as s left join sc
on s.sid = sc.sid
group by s.sid

#查有成绩的学生信息
select s.sid, s.sname, count(*) as 选课总数, sum(score) as 总成绩,
sum(case when cid = 01 then score else null end) as score_01,
sum(case when cid = 02 then score else null end) as score_02,
sum(case when cid = 03 then score else null end) as score_03
from student as s, sc
where s.sid = sc.sid
group by s.sid

#查询「李」姓老师的数量
select count(*)from teacher where Tname like ‘李%’

#查询学过「张三」老师授课的同学的信息
SELECT
*
FROM
student s
WHERE sid IN (
SELECT sid FROM sc WHERE cid IN (
SELECT cid FROM course WHERE tid IN (
SELECT tid FROM teacher WHERE tname = ‘张三’
)
)
)
select * from student where sid in (
select sid from sc, course, teacher
where sc.cid = course.cid
and course.tid = teacher.tid
and tname = ‘张三’
)

#查询没有学全所有课程的同学的信息
select * from student WHERE sid not in (
select sid from sc group by sid HAVING COUNT(cid) = (select COUNT(*) from course)
)

select * from student where sid in (select sid from sc group by sid having count(cid) < 3)

#查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from student WHERE sid not in (
select sid from sc group by sid HAVING COUNT(cid) = (select COUNT(*) from course WHERE sid = ‘01’)
)

select * from student WHERE sid in(
select sid from sc where cid in(
select cid from sc WHERE sid = ‘01’
) group by sid HAVING count(1) = (select count(1) from course) and sid != ‘01’
)

#查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student WHERE sid in(
select sid from sc WHERE cid in (
select cid from sc WHERE sid = ‘01’
)group by sid HAVING count(1) <= (select count(1) from course) and sid != ‘01’
)

#查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student WHERE sid in (
select sid from sc WHERE cid not in (
select cid from course WHERE tid in (
select tid from teacher WHERE tname = ‘张三’
)
)
)

select sname from student
where sname not in (
select s.sname
from student as s, course as c, teacher as t, sc
where s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid
and t.tname = ‘张三’
)

#查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.sid ,s.sname,AVG(score) from student s,sc
WHERE s.sid = sc.sid and s.sid in ( select sid from sc WHERE score <60)
group by s.sid HAVING count(score)>=2

select s.sid ,s.sname,AVG(score) from student s,sc
WHERE s.sid = sc.sid and score <60
GROUP BY s.sid HAVING count(score)>=2\

#检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select * from student s ,sc
WHERE s.sid = sc.sid and score <60 and cid = ‘01’ ORDER BY score DESC

#按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s.*,
sum(case when sc.cid = ‘01’ then sc.score else null end ) as 001_cid,
sum(case when sc.cid = ‘02’ then sc.score else null end ) as 002_cid,
sum(case when sc.cid = ‘03’ then sc.score else null end ) as 003_cid,
AVG(score) as avscore from student s,sc
WHERE s.sid = sc.sid
GROUP BY s.sid ORDER BY avscore DESC

#查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，
#及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。

select c.cid,c.cname, MAX(score),MIN(score),AVG(score) ,
sum(case WHEN score >=60 then 1 else 0 END)/count() as 及格,
sum(case when score >=70 and score <80 then 1 ELSE 0 END)/COUNT() as 中等,
sum(case when score >=80 and score <90 then 1 ELSE 0 END)/COUNT() as 良好,
sum(case when score >=90 and score <100 then 1 ELSE 0 END)/COUNT() as 优秀
from sc ,course c
WHERE sc.cid = c.cid GROUP BY sc.cid 、

#查询各科成绩前三名的记录
select * from (select *, rank() over(partition by cid order by sc desc) as graderank from sc) A
where A.graderank <= 3

select s.* from (select distinct student.*,a.cid, a.score, count( distinct b.score)+1 as rank
from sc a
left join sc b on a.cid=b.cid and a.score<b.score
left join student on(a.sid=student.sid)
group by a.cid, a.sid
order by a.cid, a.score desc) s
where s.rank between 1 and 3;

#查询出只选修两门课程的学生学号和姓名
select * from student s,sc
WHERE s.sid = sc.sid
GROUP BY s.sid HAVING count(cid)= 2

#查询名字中含有「风」字的学生信息
select * from student WHERE sname LIKE ‘%风’ or ‘%风%’

#查询 1990 年出生的学生名单
select * from student WHERE year(sage) = ‘1990’