---
tags: database
---
# 12/06
1
查詢住址相同的學生姓名及住址；
```
SELECT s_name ,s_address
FROM students as s
Where s_address IN
(
SELECT s.s_address
FROM students as s
GROUP BY s_address
HAVING COUNT(s.s_address)>=2
)
```
2
查詢姓「陳」的學生，男生、女生人數
```
SELECT s.s_sex,COUNT(s.s_no) as 男生女生人數
FROM students as s
WHERE s.s_name LIKE '%陳%'
GROUP BY s.s_sex
```
3
查詢課程名稱為「鋼琴課」，且分數低於 90 的學生姓名和分數；
```
SELECT s.s_name as 姓名,r.r_result as 分數
FROM students as s,results as r,classes as c
WHERE c.c_name='鋼琴課' and r.r_result<90 and s.s_no=r.s_no and r.c_no=c.c_no
```
4
查詢所有學生的選課情況，顯示學號、姓名及課程名稱；
```
SELECT s.s_no as 學號,s.s_name as 姓名,c.c_name as 課程名稱
FROM students as s,results as r,classes as c
WHERE s.s_no=r.s_no and r.c_no=c.c_no
```
5
. 查詢各科成績前三名的同學及科目、成績，不考慮成績並列情況；
```
SELECT s.s_no,s.s_name,a.c_no,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 < 4 and s.s_no = a.s_no and c.c_no = a.c_no
```
6
查詢至少有一門課與學號為“S080400011”的學生所學相同的學號和姓名；
```
SELECT DISTINCT s.s_no as 學號,s.s_name as 姓名
FROM students as s,results as r,classes as c
WHERE s.s_no=r.s_no and r.c_no=c.c_no and r.c_no IN
(
SELECT c.c_no
FROM results as r,classes as c ,students as s
WHERE r.s_no='S080400011' and s.s_no=r.s_no and r.c_no=c.c_no
) and r.s_no<>'S080400011'
```
Sign in with Wallet
Connect another wallet