SQL语句50条

一、SQL语句50条

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select st.*,sc1.s_score '01score' ,sc2.s_score '02score' from student st 
left JOIN score sc1
on st.s_id = sc1.s_id
left join score sc2
on st.s_id = sc2.s_id
WHERE sc1.c_id = 01 and sc2.c_id = 02
and sc1.s_score >= sc2.s_score 

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select st.*,sc1.s_score '01score' ,sc2.s_score '02score' from student st 
left JOIN score sc1
on st.s_id = sc1.s_id
left join score sc2
on st.s_id = sc2.s_id
WHERE sc1.c_id = 01 and sc2.c_id = 02
and sc1.s_score < sc2.s_score 

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select st.s_id,st.s_name, round(AVG(sc.s_score),2) as avgScore 
from student st 
JOIN score sc
on   st.s_id = sc.s_id 
group by st.s_id,st.s_name
having  AVG(sc.s_score) >= 60

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-- (包括有成绩的和无成绩的)

select st.s_id,st.s_name, round(AVG(sc.s_score),2) as avgScore 
from student st 
left JOIN score sc
on   st.s_id = sc.s_id 
group by st.s_id,st.s_name
having  AVG(sc.s_score) <= 60
union
select st1.s_id,st1.s_name, 0.00 as avgScore 
from student st1 
where st1.s_id not in (select s_id from score)

5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select st.s_id,st.s_name , sum(sc.s_score) as totalScore 
from student st 
left JOIN score sc
on   st.s_id = sc.s_id 
group by st.s_id,st.s_name

6. 查询"李"姓老师的数量

select count(t_id) from teacher  where t_name like '李%'

7. 查询学过"张三"老师授课的同学的信息

select st.*
from student st
left JOIN score sc on st.s_id = sc.s_id 
left JOIN course co on sc.c_id = co.c_id
left JOIN teacher te on co.t_id = te.t_id 
WHERE te.t_name = '张三'

select st.* from 
student st 
join score sc on st.s_id=sc.s_id where sc.c_id in
(select c_id from course where t_id = (select t_id from teacher where t_name = '张三'));

8. 查询没学过"张三"老师授课的同学的信息

在这里插入代码片

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select  st.* from student st, score sc1,score sc2
where  st.s_id = sc1.s_id and st.s_id= sc2.s_id
and sc1.c_id= 01 and sc2.c_id = 02

10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

在这里插入代码片

二、建表语句