MySQL数据库习题练习（二）

-- 21、查询不同老师所教不同课程平均分从高到低显示 

select avg(s_score) from score GROUP BY c_id ORDER BY avg(s_score) desc

---

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select sum(s_score), s_id from score GROUP BY s_id ORDER BY sum(s_score) desc

select s.*, sumscore from student s, (select sum(s_score) sumscore, s_id from score GROUP BY s_id ORDER BY sum(s_score) desc) a where s.s_id=a.s_id limit 1, 2

公式：pageNo, pageSize
          LIMIT (pageNo - 1) * pageSize, pageSize

---

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]
select c.*,
(select count(*) from score where s_score>=85 and s_score<=100 and c_id=c.c_id GROUP BY c_id) 优秀,
(select count(*) from score where s_score>=70 and s_score<85 and c_id=c.c_id GROUP BY c_id) 良,
(select count(*) from score where s_score>=60 and s_score<70 and c_id=c.c_id GROUP BY c_id) 及格,
(select count(*) from score where s_score>=0 and s_score<60 and c_id=c.c_id GROUP BY c_id) 不及格

from course c

-- 24、查询学生平均成绩及并降序排列

SELECT avg(s_score) from score GROUP BY s_id ORDER BY avg(s_score) desc;

---

-- 25、查询选修了全部课程的学生信息 

select s_id from score GROUP BY s_id having count(*)=(select count(*) from course)

select stu.* from student stu where stu.s_id in (select s_id from score GROUP BY s_id having count(*)=(select count(*) from course))

-- 26、查询每门课程被选修的学生数 

select count(*) from score GROUP BY c_id

-- 27、查询出只有两门课程的全部学生的学号和姓名 

select s_id from score GROUP BY s_id having count(*)=2

select * from student where s_id in(select s_id from score GROUP BY s_id having count(*)=2)

---

-- 28、查询男生、女生人数 

select count(*) from student GROUP BY s_sex

---

-- 29、查询名字中含有"风"字的学生信息

select * from student where s_name like '%风%';

---

-- 30、查询同名同性学生名单，并统计同名人数 

select s1.s_name s1name, s2.s_name s2name from student s1, student s2 where s1.s_id<>s2.s_id and s1.s_name=s2.s_name;

select count(*) from (select s1.s_name s1name, s2.s_name s2name from student s1, student s2 where s1.s_id<>s2.s_id and s1.s_name=s2.s_name) a GROUP BY a.s1name

---

-- 31、查询1990年出生的学生名单

select * from student where s_birth like '1990%'

---

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 

select avg(s_score) from score GROUP BY c_id ORDER BY avg(s_score) desc, c_id

---

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 

select avg(s_score) avgscore, s_id from score GROUP BY s_id having avg(s_score)>85

select s.s_id, s.s_name, a.avgscore from student s, (select avg(s_score) avgscore, s_id from score GROUP BY s_id having avg(s_score)>85) a where s.s_id=a.s_id

---

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
select c_id from course where c_name='数学'

select s.s_name, s1.s_score from student s, score s1 where s.s_id=s1.s_id and s1.s_score<60 and s1.c_id=(select c_id from course where c_name='数学')

---

-- 35、查询所有学生的课程及分数情况；

select s.*,s1.s_score,c.c_name from student s,score s1,course c where s.s_id=s1.s_id and s1.c_id=c.c_id

---

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select s.s_name, s1.s_score, c.c_name from student s, score s1, course c where s.s_id=s1.s_id and s1.c_id=c.c_id and s1.s_score>70 

---

-- 37、查询不及格的课程

select c-id from score where s_score<60

select * from course where c_id in (select c_id from score where s_score<60)

---

-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select * from student where s_id=any(select s_id from score where c_id='01' and s_score>=80)

---

-- 39、求每门课程的学生人数 

select count(*),c_id from score GROUP BY c_id

---

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select * from score s1, student s where s1.s_id=s.s_id and s1.c_id=any(select c.c_id from course c, teacher t where c.t_id=t.t_id and t.t_name='张三') ORDER BY s_score desc LIMIT 0, 1

---

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select *from sc as t1where exists(select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score ) 

---

-- 42、查询各学生的年龄

select DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y')+(DATE_FORMAT(NOW(),'%m')>DATE_FORMAT(s_birth,'%m')) AS 年龄 from student

---

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,count(*) from score group by c_id having count(*)>5 ORDER BY count(*) desc,c_id