---
tags: database
---
# 11/29

1
查詢各科平均成績與及格率，並從低到高和及格率的百分數從高到低排序

SELECT r.c_no AS 課程編號,AVG(r.r_result) AS 平均成績
,COUNT(case when r.r_result>=60 then 1 end)*1.0/COUNT(r.r_result)*100.0 AS 及格率
FROM dbo.results as r
GROUP BY r.c_no
ORDER BY 平均成績,及格率 DESC

2
查詢「英文課」比「數學課」成績高的所有學生的學號、姓名；

SELECT s.s_no,s_name
FROM students AS s
,(SELECT * FROM results as a WHERE a.c_no='C0001' ) as aa
,(SELECT * FROM results as b WHERE b.c_no='C0003' ) as bb
WHERE aa.r_result<bb.r_result and aa.s_no=bb.s_no and aa.s_no=s.s_no and aa.s_no=bb.s_no

3
查詢平均成績大於 60 分的同學學號和平均成績；

SELECT r.s_no,AVG(r.r_result) AS 平均成績
FROM dbo.results as r
GROUP BY r.s_no
HAVING AVG(r.r_result)>60

4
查詢所有同學的學號、姓名、選課數、總成績；

SELECT r.s_no,s_name,COUNT(r.r_result) AS 選課數,SUM(r.r_result) AS 總成績
FROM dbo.results AS r
LEFT JOIN dbo.students as s ON r.s_no=s.s_no
GROUP BY r.s_no,s_name
ORDER BY r.s_no ASC

5
查詢沒有學所有課的同學的學號、姓名；

SELECT r.s_no,s_name 
FROM dbo.students AS s
LEFT JOIN dbo.results AS r ON s.s_no=r.s_no
GROUP BY r.s_no,s_name
HAVING COUNT(r.s_no)<(
SELECT COUNT(c.c_no)
FROM dbo.classes as c
)
ORDER BY r.s_no ASC

6
查詢修課人數少於 10 人的課程編號和課程名稱

SELECT c.c_no,c.c_name
FROM dbo.results AS r
LEFT JOIN dbo.classes AS c ON c.c_no=r.c_no
GROUP BY c.c_no,c.c_name
HAVING COUNT(r.c_no)<10