---
tags: database
---
# 11/22

1
查詢沒學過「機器人初階」 課程同學的學號、姓名

SELECT s_no,s_name 
FROM dbo.students
WHERE s_no NOT IN(
	SELECT s_no
	FROM dbo.results 
	LEFT JOIN dbo.classes ON classes.c_no=results.c_no
	WHERE c_name='機器人初階'
)

2
查詢“C0006”課程分數小於 80，並按分數降冪排列的學號

SELECT s_no 
FROM dbo.results 
WHERE c_no='C0006' AND r_result<80
ORDER BY r_result DESC

3
查詢住在「汐止鎮」的學生學號、姓名及住址；

SELECT s_no,s_name,s_address
FROM dbo.students
WHERE s_address LIKE '%汐止鎮%'

4
查詢各科成績最高分、最低分及平均分數，顯示：課程編號、課程名稱、最高分、最低
分及平均分數；

SELECT r.c_no AS 課程編號,c.c_name AS 課程名稱,MAX(r_result) as 最高分,MIN(r_result) as 最低分,AVG(r_result) as 平均分數
FROM dbo.results as r
LEFT JOIN dbo.classes as c ON r.c_no=c.c_no
GROUP BY r.c_no,c.c_name
ORDER BY r.c_no

5
查詢每門課程被選修的學生數；

SELECT c.c_name AS 課程名稱,COUNT(r.s_no) AS 學生數
FROM dbo.classes as c
LEFT JOIN dbo.results as r ON r.c_no=c.c_no
GROUP BY r.c_no,c.c_name
ORDER BY r.c_no

6
查詢住在台北市學生的平均成績，課程編號、平均成績

SELECT r.c_no AS 課程編號,AVG(r.r_result) AS 平均成績
FROM dbo.results as r
LEFT JOIN dbo.students as s ON s.s_no=r.s_no
WHERE s.s_address LIKE '%台北市%'
GROUP BY r.c_no

7
請查詢住在「台北縣」的同學，並將其修改成「新北市」；

UPDATE dbo.students
SET s_address=REPLACE(s_address,'台北縣','新北市')
SELECT s.s_no,s.s_address
FROM dbo.students AS s
WHERE s_address LIKE '%新北市%'

8
刪除“S080400001”同學的“C0001”課程的成績

DELETE FROM dbo.results
WHERE s_no='S080400001' AND c_no='C0001'
SELECT * 
FROM dbo.results
WHERE s_no='S080400001' AND c_no='C0001'