SQL 50题

SQL 50题

--Q1,Q2
select st.* ,sc.s_score as s1,sc2.s_score as s2
from student st
left join score sc on sc.s_id =st.s_id  and sc.c_id ="01"
left join score sc2 on sc2.s_id =st.s_id  and sc2.c_id ="02"
where s1>s2

--Q3
SELECT s.s_id,s.s_name,AVG(sc.s_score) 
from student s
left join score sc on sc.s_id =s.s_id
group by s.s_id 
having AVG(sc.s_score) >60

--Q4
SELECT s.s_id,s.s_name,AVG(sc.s_score) 
from student s
left join score sc on sc.s_id =s.s_id
group by s.s_id 
having AVG(sc.s_score) <60 or AVG(sc.s_score) is NULL 

--Q5
SELECT s2.s_id ,s2.s_name ,count(s.c_id ),SUM(s.s_score )
from student s2 left join Score s 
on s2.s_id =s.s_id 
group by s2.s_id 

--Q6
SELECT count(t2.t_id )
from Teacher t2 
group by t2.t_id 
having t2.t_name LIKE "李%"

--Q7 
select *
from student s 
where s.s_id in(
select DISTINCT s_id from Score s2 
where c_id in(
select  c_id from Course c2 ,Teacher t 
where c2.t_id =t.t_id and t.t_name like "张三")
)

SELECT s.s_id ,s.s_birth ,s.s_name ,s.s_sex 
from student s 
join score sc on s.s_id =sc.s_id 
join Course c on sc.c_id =c.c_id 
join Teacher t on c.t_id =t.t_id 
where t.t_name like "张三"

--Q8
SELECT *
from student s 
where s.s_id  not in (
SELECT s2.s_id 
from Score s2,Course c ,Teacher t 
where s2.c_id =c.c_id and c.t_id =t.t_id and t.t_name like "张三"
)

--Q9 还可以一口气写一起然后where，但不规范就算了。
select s.*
from (student s
inner join score sc on s.s_id =sc.s_id 
)
inner join score sc2 on s.s_id=sc2.s_id 
where sc.c_id ="01" and sc2.c_id ="02"

--Q10
SELECT s.*
from student s,Score  sc 
where s.s_id =sc.s_id and sc.c_id ="01" and s.s_id not in (
SELECT s2.s_id from Score s2 where s2.c_id ="02")

--Q11
SELECT s.*
from student s
where s.s_id in(
SELECT s2.s_id 
from Score s2 group by s2.s_id having COUNT(s2.c_id ) <3)

--Q12
SELECT DISTINCT s.*
from student s,Score s2 
where s.s_id=s2.s_id  and s2.c_id in (
SELECT s3.c_id 
from Score s3 where s3.s_id ="01")

--Q13
SELECT s2.*
from student s2
left join Score sc on s2.s_id =sc.s_id 
group by s2.s_id 
having group_concat(sc.c_id )=
( SELECT group_concat(sc2.c_id) from student s3 
left join score sc2 on sc2.s_id =s3.s_id 
where s3.s_id ="01"
) and s2.s_id !="01"

PART2

14.用一下EXISTS

--Q14
select s.s_name 
from student s 
where not EXISTS (
select s2.s_id 
from score s2, Course c2 ,Teacher t 
where s2.c_id =c2.c_id  and c2.t_id =t.t_id and t.t_name ="张三" and s2.s_id =s.s_id )

15\16.用round()统一小数，结果会好看点

--Q15
SELECT s2.s_id ,s2.s_name ,round(AVG(s.s_score ) ,2)
from student s2 ,Score s 
where s.s_score <60 and s2.s_id =s.s_id 
group by s2.s_id 
HAVING count(*) >=2

--Q16
SELECT s2.*, s.s_score
from student s2 ,Score s 
where s.s_id =s2.s_id and s.c_id ="01" and s.s_score <60
order by s.s_score desc

17. 我觉得这个算简单的写法了吧，先把三个课程分列，然后用max（）合并行，最后加上平均值。其实我就是想用case when 了…
    

--Q17
select t1.s_name ,max(t1.C1),max(t1.C2),max(t1.C3),avg(s3.s_score )
from
(select s2.s_id ,s2.s_name,
case when s1.c_id ="01" then s1.s_score end as C1, 
case when s1.c_id ="02" then s1.s_score end as C2,
case when s1.c_id ="03" then s1.s_score end as C3
from student s2 , Score s1
where s1.s_id =s2.s_id )t1,score s3
where s3.s_id =t1.s_id
group by t1.s_name
ORDER by t1.s_id