SQL 50題

SQL 50題

#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