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