Problem 1
查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select * from
(select g1.GId, g1.Gradeore from
(select * from grade where CId = '01') as g1,
(select * from grade where CId = '02') as g2
where (g1.GId = g2.GId)
and g1.Gradeore > g2.Gradeore) as tbl1
join student s1 on tbl1.GId = s1.SId;
Problem 1.1
查询同时存在" 01 "课程和" 02 "课程的情况
select * from
(select * from grade where CId = '01') as g1 join
(select * from grade where CId = '02') as g2
on g1.GId = g2.GId;
Problem 1.2
select * from
(select * from grade where CId = '01') as g1 left join
(select * from grade where CId = '02') as g2
on g1.GId = g2.GId;
Problem 1.3
查询不存在" 01 "课程但存在" 02 "课程的情况
select GId, cid2 from(
select g2.GId, g1.CId as cid1, g2.CId as cid2 from
(select * from grade where CId = '01') as g1 right join
(select * from grade where CId = '02') as g2
on g1.GId = g2.GId) as tbl1
where tbl1.cid1 is null;
Easier Version
select * from grade
where CId = '02'
and GId not in
(select GId from grade where CId = '01');
Problem 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select SId, Sname, AveScore
from (select GId, avg(Gradeore) as AveScore from grade
group by GId
having AveScore >= 60) as tbl1 join student on tbl1.GId = student.SId
order by AveScore DESC;
3. 查询在 grade 表存在成绩的学生信息
select * from student where SId in
(select distinct GId from grade);
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select s1.*, total_courses, total_scores from
(select GId, count(Gradeore) as total_courses, sum(Gradeore) as total_scores
from grade
group by GId) as tbl1 right join student as s1
on (tbl1.GId = s1.SId);
4.1 查有成绩的学生信息
select s1.*, total_courses, total_scores from
(select GId, count(Gradeore) as total_courses, sum(Gradeore) as total_scores
from grade
group by GId) as tbl1 join student as s1
on (tbl1.GId = s1.SId);
5. 查询「李」姓老师的数量
select count(TId)
from teacher
where Tname like 'li%'
6. 查询学过「张三」老师授课的同学的信息
select * from student where
SId in(
select GId from grade where CId =
(select CId from course where TId =
(select TId from teacher
where Tname = 'zhangsan')
));
7. 查询没有学全所有课程的同学的信息
select * from student
where SId not in
(select GId from grade
group by GId
having count(*) = (select count(CId) from course));
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student where SId in (
select distinct grade.GId from grade join
(select * from grade where GId = '01') as tmp
on (grade.CId = tmp.CId)
where grade.GId != '01'
);
9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select GId, sum(CId) from grade
where CId in (select CId from grade where GId = '01')
and GId != '01'
group by GId
having sum(CId) = (select sum(CId) from grade where GId = '01');
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select SName from student
where SId not in
(select GId from grade where CId in (
select CId from course where TId in
(select TId from teacher where Tname = 'zhangsan'))
);
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select grade.GId, SName,
avg(Gradeore) as average
from grade join student where grade.GId = student.SId
group by grade.GId
having sum(
case
when Gradeore < 60 then 1
else 0
end ) >= 2
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select * from
(select GId, avg(Gradeore) as average
from grade
group by GId
order by average DESC) as tbl1 right join grade on (tbl1.GId = grade.GId)
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from
(select GId, avg(Gradeore) as average
from grade
group by GId
order by average DESC) as tbl1
right join grade on (tbl1.GId = grade.GId)
14. 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select g1.CId as courseID,
c1.CName as courseName,
MAX(g1.Gradeore)as max_score,
MIN(g1.Gradeore)as min_score,
AVG(g1.Gradeore)as ave_score,
count(*)as register_num,
sum(case when g1.Gradeore>=60 then 1 else 0 end )/count(*)as pass_rate,
sum(case when g1.Gradeore between 70 and 80 then 1 else 0 end )/count(*)as 'C',
sum(case when g1.Gradeore between 80 and 90 then 1 else 0 end )/count(*)as 'B',
sum(case when g1.Gradeore>=90 then 1 else 0 end )/count(*)as 'A'
from grade as g1 join course c1 on (g1.CId = c1.CId)
GROUP BY g1.CId
ORDER BY count(*) DESC, g1.CId
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select GId,
Gradeore,
rank() over (partition by CId order by Gradeore DESC)
from grade;
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select GId,
Gradeore,
dense_rank() over (partition by CId order by Gradeore DESC)
from grade;
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select GId,
sum(Gradeore),
rank() OVER (ORDER BY sum(Gradeore) DESC)
from grade
GROUP BY GId
ORDER BY sum(Gradeore) desc;
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select GId,
sum(Gradeore),
dense_rank() OVER (ORDER BY sum(Gradeore) DESC)
from grade
GROUP BY GId
ORDER BY sum(Gradeore) desc;
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select course.Cname,t1.*
from course LEFT JOIN (
select grade.CId,
CONCAT(round(sum(case when grade.Gradeore between 85 and 100 then 1 else 0 end )/count(*)*100,2),'%')
as '[85-100]',
CONCAT(round(sum(case when grade.Gradeore between 70 and 85 then 1 else 0 end )/count(*)*100,2),'%')
as '[70-85)',
CONCAT(round(sum(case when grade.Gradeore between 60 and 70 then 1 else 0 end )/count(*)*100,2),'%')
as '[60-70)',
CONCAT(round(sum(case when grade.Gradeore between 0 and 60 then 1 else 0 end )/count(*)*100,2),'%')
as '[0-60)'
from grade
GROUP BY grade.CId) as t1 on course.CId=t1.CId
18. 查询各科成绩前三名的记录
select GId, grade_rank, Gradeore from
(select GId,
CId,
Gradeore,
row_number() over (partition by CId order by Gradeore DESC) AS grade_rank
from grade) as temp
where grade_rank <= 3
19. 查询每门课程被选修的学生数
select CId, count(GId)
from grade
group by CId;
20. 查询出只选修两门课程的学生学号和姓名
select SId, Sname from student
where SId in
(select GId
from grade
group by GId
having count(CId) = 2);
21. 查询男生、女生人数
select Sgender, count(*) from student group by Sgender
22. 查询名字中含有「风」字的学生信息
select * from student where SName like '%feng%'
23. 查询同名同性学生名单，并统计同名人数
select * from student s1, student s2
where s1.SId != s2.SId and s1.SName = s2.SName
24. 查询 1990 年出生的学生名单
select SId, SName, year(Sage) as born_year
from student
having born_year = '1990'
25. 查询每门课程的平均成绩�
