SQL50题

SQL50题

#1.查询" 01 "课程比" 02 “课程成绩高的学生的信息及课程分数
select student.*,sc_01.CId,sc_01.score,sc_02.CId,sc_02.score
from student
LEFT JOIN
(select Sid,CId,score from sc where CId='01') as sc_01 on student.SId=sc_01.Sid
LEFT JOIN
(select Sid,CId,score from sc where CId='02') as sc_02 on sc_01.Sid=sc_02.Sid
where sc_01.score>sc_02.

#1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select student.*,sc_01.CId,sc_01.score,sc_02.CId,sc_02.score
from student
LEFT JOIN
(select Sid,CId,score from sc where CId='01') as sc_01 on student.SId=sc_01.Sid
INNER JOIN
(select Sid,CId,score from sc where CId='02') as sc_02 on sc_01.Sid=sc_02.Sid

#1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select student.*,sc_01.CId,sc_01.score,sc_02.CId,sc_02.score
from student
INNER JOIN
(select Sid,CId,score from sc where CId='01') as sc_01 on student.SId=sc_01.Sid
LEFT JOIN
(select Sid,CId,score from sc where CId='02') as sc_02 on sc_01.Sid=sc_02.Sid

#1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select student.*,sc_01.CId,sc_01.score,sc_02.CId,sc_02.score
from student
INNER JOIN
(select Sid,CId,score from sc where CId='02') as sc_02 on student.SId=sc_02.Sid
LEFT JOIN
(select Sid,CId,score from sc where CId='01') as sc_01 on sc_02.Sid=sc_01.Sid
where sc_01.score is null

#2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#方法一
select sc.SId,student.Sname,AVG(score) as avg
from sc
LEFT JOIN
student on sc.SId=student.SId
GROUP BY sc.SId
HAVING AVG(score)>=60

#方法二
select student.SId,student.Sname,a.avg
from student
LEFT JOIN
(select SId,avg(score) as avg from sc GROUP BY SId) as a
on a.SId=student.SId 
where a.avg>=60

#3.查询在 SC 表存在成绩的学生信息 
SELECT DISTINCT student.*
FROM student
INNER JOIN
sc on sc.SId=student.SId

#4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT student.SId,student.Sname,a.`数量`,a.`总分`
FROM student
LEFT JOIN
(SELECT SId,SUM(score) as '总分',COUNT(CId) as '数量' FROM sc GROUP BY SId) as a 
ON student.SId=a.SId

#4.1 查有成绩的学生信息
SELECT DISTINCT student.* 
FROM sc
LEFT JOIN
student ON sc.SId=student.SId

#5.查询「李」姓老师的数量
SELECT COUNT(Tname) as '李姓老师的数量' 
FROM teacher 
WHERE Tname LIKE '李%'

#6.查询学过「张三」老师授课的同学的信息
SELECT student.*,sc.CId,course.TId,teacher.Tname
FROM student 
LEFT JOIN
sc ON student.SId=sc.SId
LEFT JOIN
course ON sc.CId=course.CId
LEFT JOIN 
teacher ON course.TId=teacher.TId
WHERE teacher.Tname='张三'

#7.查询没有学全所有课程的同学的信息 
#方法一
SELECT Student.*,COUNT(CId) 
FROM Student 
LEFT JOIN sc 
ON Student.SId = sc.SId 
GROUP BY Student.SId
HAVING COUNT(CId)<(SELECT COUNT(CId) FROM Course)

#方法二
select student.*
from student
where student.SId not in 
(
select sc.SId
from sc 
group by SId
having count(CId)=(select count(CId) from course)
)

#8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select distinct student.*
from student
join sc on student.SId=sc.SId
where sc.CId in (select CId from sc where SId='01') 
and student.SId !='01'

#9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT DISTINCT SC.SId,Sname,Sage,Ssex,SUM_COUNT_CId,COUNT(CId) AS COUNT_CId_01 
FROM SC JOIN 
(
SELECT DISTINCT Student.*,COUNT(CId) AS SUM_COUNT_CId 
FROM SC 
JOIN Student ON SC.SId = Student.SId 
GROUP BY SId
HAVING COUNT(CId)=(SELECT COUNT(CId) FROM SC WHERE SId = "01")
) AS COUNT_SID_01 
/*COUNT_SID_01 的作⽤用是只取课程总数和01同学相同的同学进⾏行行下⼀一步判断*/ 
ON SC.SId =COUNT_SID_01.SId 
WHERE SC.CId IN (SELECT CId FROM SC WHERE SId = "01") /*只保留留和01同学课程相同的课程ID*/ 
GROUP BY SId
HAVING COUNT(CId)=(SELECT COUNT(CId) FROM SC WHERE SId = "01") 
/*这⼀一步的作⽤用是保留留和01同学课程相同之后的课程总数仍相等*/

#10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select *
from student
where SId not in 
(
select student.SId
from student
join sc on sc.SId=student.SId
join course on sc.CId=course.CId
join teacher on course.TId=teacher.TId
where teacher.Tname = '张三'
) 

#查询所有课程成绩均在70分以上的学生姓名，班级名称和成绩
select student.Sname,course.Cname,c.score
from student
join 
(
select sc.*
from sc
join 
(
select sc_70.SId
from 
(select * from sc where score>70) as sc_70
group by sc_70.SId
having count(sc_70.CId)=(select count(CId) from course)
) as b on sc.SId = b.SId
) as c on student.SId=c.SId
join 
course on course.CId=c.CId

#11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
#方法一
select student.SId,student.Sname,c.avg_score
from student
join 
(select a.SId
from 
(select * from sc where score<60) as a
group by a.SId
having count(CId)>=2) as b
on student.SId=b.SId
join 
(select sc.SId,avg(score) as avg_score from sc group by SId) as c
on b.SId=c.SId

#方法二
SELECT Student.SId,Sname,avg_score,count(CId) as fail_CId 
FROM Student  
JOIN SC ON Student.SId=SC.SId
JOIN 
(SELECT SId,AVG(score) as avg_score 
FROM SC 
GROUP BY SId ) AS fail_SC ON fail_SC.SId=SC.SId 
#这⾥里里增加fail_SC这个表是因为这样计算的AVG(score)才是所有科⽬目的平均成绩， 
#如果直接算AVG(score)的话得到的是score < 60条件下的平均成绩 
WHERE score < 60 
GROUP BY Student.SId
HAVING fail_CId >= 2

#12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*
from student
join sc on sc.SId=student.SId
where sc.CId=01 and sc.score<60
order by sc.score desc

#13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student.*,
sc_01.CId as '语文',sc_01.score as '语文成绩',
sc_02.CId as '数学',sc_02.score as '数学成绩',
sc_03.CId as '英语',sc_03.score as '英语成绩',
sc_avg.avg_score as '平均成绩'
from student
left join 
(select * from sc where CId=01) as sc_01 
on sc_01.SId=student.SId
left join 
(select * from sc where CId=02) as sc_02
on student.SId=sc_02.SId
left join 
(select * from sc where CId=03) as sc_03
on student.SId=sc_03.SId
left join 
(select SId,avg(score) as avg_score
from sc 
group by SId) as sc_avg
on student.SId=sc_avg.SId
order by sc_avg.avg_score desc