MySQL试题2

##二、题目：
#01）查询" 1 "课程比" 2 "课程成绩高的学生的信息及课程分数
#方法一
  select s.*,t1.score,t2.score from
  (select sid,score from t_score where cid=1) t1,
  (select sid,score from t_score where cid=2) t2,
  t_student s
  where t1.sid=t2.sid and t1.sid=s.sid 
  and t1.score>t2.score
#方法二
select stu.*,t1.score,t2.score from t_student stu
INNER JOIN
(select * from t_score where cid = 1) t1
on stu.sid = t1.sid
INNER JOIN
(select * from t_score where cid = 2) t2
on t1.sid = t2.sid and t1.score > t2.score
#02）查询同时存在" 01 "课程和" 02 "课程的情况
#方法一
  select * from 
    (select * from t_score as sc where sc.cid=1) as t1,
    (select * from t_score as sc where sc.cid=2) as t2
  where t1.sid = t2.sid;  
#方法二
select stu.*,t1.score,t2.score from t_student stu
INNER JOIN
(select * from t_score where cid = 1) t1
on stu.sid = t1.sid
INNER JOIN
(select * from t_score where cid = 2) t2
on t1.sid = t2.sid 
#03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select stu.sname,tmp.c1,tmp.c2 from t_student stu inner join
  (select sid,
    SUM(case when cid = 1 then score else null end) c1,
    SUM(case when cid = 2 then score else null end) c2
  from t_score GROUP BY sid
  HAVING c1 is not null and c1>=0) tmp on stu.sid = tmp.sid
#04）查询不存在" 01 "课程但存在" 02 "课程的情况
select stu.sname,tmp.c1,tmp.c2 from t_student stu inner join
  (select sid,
    SUM(case when cid = 1 then score else null end) c1,
    SUM(case when cid = 2 then score else null end) c2
  from t_score GROUP BY sid
  HAVING c1 is null and c2 is not null) tmp on stu.sid = tmp.sid
#05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#子查询+内连接
  select stu.sid,sname,avgScore from t_student as stu inner join(
    select sid,ROUND(avg(score),2) as avgScore from t_score
    GROUP BY sid
    HAVING avg(score) >=60
  )b
  on stu.sid = b.sid
#联表查询
select s.sid,s.sname,ROUND(avg(sc.score),2) as avgScore 
from t_student s,t_score sc
where s.sid = sc.sid
group by s.sid,s.sname
having avgScore>=60;
#内连接
  select stu.sid,sname,ROUND(avg(score),2) as avgScore 
  from t_student as stu 
  inner join 
  t_score as sc
  on stu.sid = sc.sid 
  GROUP BY 
  stu.sid
  HAVING avgScore>=60
#4
select stu.sid,stu.sname,tmp.a from t_student stu inner join
  (select sid,ROUND(avg(score),2) as avgScore from t_score group by sid ) tmp 
  on stu.sid = tmp.sid 
  where tmp.avgScore>=60
#06）查询在t_score表存在成绩的学生信息
#方法一
select DISTINCT s.* from t_student s,t_score sc where s.sid=sc.sid
#方法二
select DISTINCT stu.* from t_student as stu
inner join
t_score as sc
on stu.sid = sc.sid
#07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select stu.sid,sname,COUNT(score)
as 选课总数,IF(SUM(score)!=0,SUM(score),NULL) as 总成绩 from t_student as stu
inner join 
t_score as sc 
on stu.sid = sc.sid
GROUP BY stu.sid
#08）查询「李」姓老师的数量
select tid,tname,COUNT(*) as 数量 from t_teacher 
where tname like '李%'
group by tid
select COUNT(*) from t_teacher 
  where tname like'李%'
  GROUP BY tid
#09）查询学过「张五哥」老师授课的同学的信息
SELECT stu.* from t_student stu where sid in
(select sid from t_score where cid  in
(select cid from t_course where tid in
(select tid from t_teacher where tname ='张五哥')))
#10）查询没有学全所有课程的同学的信息
select * from t_student where sid not in(
  select sid from t_score 
  group by sid 
  having count(cid)=3);
  select sid,cid from t_score 
  group by sid 
  having count(cid)>=2
select stu.*,COUNT(*) as course from t_student as stu 
  LEFT JOIN t_score as sc
  on stu.sid = sc.sid
  GROUP BY stu.sid
  HAVING course<3
#11）查询没学过"张五哥"老师讲授的任一门课程的学生姓名
SELECT stu.* from t_student stu where sid not in
(select sid from t_score where cid  in
(select cid from t_course where tid in
(select tid from t_teacher where tname ='张五哥')))
#12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
  select stu.sid,stu.sname,tmp.a as 平均成绩 from t_student stu 
  inner join
  (select sid,
  ROUND(avg(score),2) as a,
  sum(case when cid = 1 then (case when score>60 then score else 0 end) else 0 end) c1,
  sum(case when cid = 2 then (case when score>60 then score else 0 end) else 0 end) c2,
  sum(case when cid = 3 then (case when score>60 then score else 0 end) else 0 end) c3
  from t_score 
  group by sid 
  having
  c1=0 and c2=0 or c1=0 and c3=0 or c2=0 and c3=0) tmp
  on stu.sid = tmp.sid 
          SELECT t.sid, COUNT(*) num FROM (SELECT sid, cid FROM t_score WHERE score < 60) t GROUP BY t.sid HAVING num >= 2
  select sid,count(score) from t_score where score < 60  group by sid
#13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select stu.*,emp.score from t_student stu inner join(
  select * from t_score where cid = 1 and score < 60) emp
  on stu.sid = emp.sid
  ORDER BY 
  emp.score desc
#14）按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
SELECT stu.sid,stu.sname, 
  SUM(CASE WHEN sc.cid = 1 THEN sc.score ELSE 0 END) as c1,
  SUM(CASE WHEN sc.cid = 2 THEN sc.score ELSE 0 END) as c2,
  SUM(CASE WHEN sc.cid = 3 THEN sc.score ELSE 0 END) as c3,
  ROUND(AVG(sc.score), 2) as avgScore
FROM t_score as sc 
INNER JOIN t_student as stu ON sc.sid = stu.sid
 GROUP BY stu.sid, stu.sname
 ORDER BY avgScore DESC;
#15）查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select 
cid as '课程编号',
count(cid) as'选修人数',
max(score)as'最高分',
min(score)as'最低分',
ROUND(avg(score),2)as'平均分' ,
CONCAT(ROUND((select count(*) from t_score where score>=60 and cid=sc.cid group by cid)/count(cid)*100,2),'%')as'及格率',
CONCAT(ROUND((select count(*) from t_score where score between 70 and 80 and cid =sc.cid group by cid)/count(cid)*100,2),'%')as'中等率',
CONCAT(ROUND((select count(*) from t_score where score between 80 and 90 and cid =sc.cid group by cid)/count(cid)*100,2),'%')as'优良率',
CONCAT(ROUND((select count(*) from t_score where score>=90 and cid =sc.cid group by cid)/count(cid)*100,2),'%')as'优秀率'
from t_score as sc 
group by cid
order by count(cid) desc, cid