---
tags: database
---
# 12/27

1
查詢每門功課成績最好的前兩名，顯示課程名稱、姓名、名次及分數；

SELECT c.c_name,s.s_name,a.r_result,a.num
FROM 
(
SELECT s_no,c_no,r_result,RANK() OVER (PARTITION BY c_no ORDER BY r_result DESC) AS num
FROM results
)a
,students as s
,classes as c
where a.NUM <= 2 and s.s_no = a.s_no and c.c_no = a.c_no

2
查詢跟「林進財」同學修完全相同課程的其他同學的學號、姓名；

SELECT s.s_no,s.s_name
FROM students as s,classes as c,results as r
WHERE s.s_name <>'林進財' and c.c_no in (SELECT r.c_no
FROM results as r,students as s
WHERE r.s_no=s.s_no and s.s_name='林進財'
) and s.s_no=r.s_no and c.c_no=r.c_no and r.s_no in (
SELECT r.s_no
FROM results as r ,students as s
WHERE s.s_no=r.s_no
GROUP BY r.s_no
HAVING COUNT(r.s_no)=(SELECT COUNT(r.c_no)
FROM results as r,students as s
WHERE r.s_no=s.s_no and s.s_name='林進財')
)
GROUP BY s.s_no,s.s_name
HAVING COUNT(s.s_no)=(SELECT COUNT(r.c_no)
FROM results as r,students as s
WHERE r.s_no=s.s_no and s.s_name='林進財') 

3
請計算男、女生的平均年齡，顯示性別、平均年齡。

SELECT s.s_sex,AVG(DATEDIFF(YEAR,s.s_birthday,GETDATE()))
FROM students as s
GROUP BY s.s_sex

4
請查詢 1980 年以前出生的學生名單。

SELECT *
FROM students as s
WHERE YEAR(s.s_birthday)<'1980'

5
查詢各課程分數大於該課程平均成績的所有學生的學號、姓名和課程、成績；

SELECT s.s_no,s.s_name,c.c_name,r.r_result
FROM students as s,
(SELECT AVG(r.r_result) as avgr,R.c_no
FROM results as r
GROUP BY r.c_no
) as a,results as r,classes as c
WHERE r.r_result>a.avgr and r.c_no=a.c_no and c.c_no=r.c_no and r.s_no=s.s_no