目录
表字段如图所示:
21. 查询男生、女生人数
select Student.Ssex, count(Student.Ssex)
from student
group by Student.Ssex;
22. 查询名字中含有「风」字的学生信息
select Student.*
from student
where Student.Sname like '%风%';
23. 查询同名同性学生名单,并统计同名人数
select Sname, count(Sname)
from student
group by Student.Sname
having count(Student.Sname)>1;
24. 查询 1990 年出生的学生名单
select Student.*
from Student
where Student.Sage like '1990%';
# 写法二
select Student.*
from Student
where year(Student.Sage) like '1990';
25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select sc.CId,
avg(score)
from sc
group by sc.CId
order by avg(score) desc,CId asc ;
26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select Student.SId, Student.Sname, avg(score)
from student inner join SC S on Student.SId = S.SId
group by Student.SId
having avg(score)>85;
27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select Student.Sname, score
from student inner join SC S on Student.SId = S.SId
inner join Course C on S.CId = C.CId
where c.Cname='数学' and s.score<60;
28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select Student.Sname, score
from student left join SC S on Student.SId = S.SId;
29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Student.Sname, c.Cname, score
from student inner join SC S on Student.SId = S.SId
inner join Course C on S.CId = C.CId
where score>70;
30. 查询不及格的课程
select sc.CId
from sc
where sc.score<60;