---
tags: database
---
# 12/13

### 1
在「學生資料表」中查詢姓名開頭姓「李」或「林」的學生基本資料。

SELECT * 
FROM students AS s
WHERE s.s_name LIKE '李%' OR s.s_name LIKE '林%'

### 2
查詢各課程成績相同的學生的學號、課程編號、學生成績

SELECT r.s_no,r.c_no,r.r_result
FROM results AS r,students AS s,results AS a
WHERE r.s_no=s.s_no AND a.s_no<>r.s_no AND r.c_no=a.c_no AND r.r_result=a.r_result
ORDER BY r.r_result 

### 3
統計列印各科成績、各分數段人數，顯示：課程編號、課程名稱、[100-86]、[85-70]、
[69-60]、[ <60]；

SELECT c.c_no,c.c_name,
COUNT(IIF(r.r_result<=100 and r.r_result>=86,1,null)) AS [100-86],
COUNT(IIF(r.r_result<=85 and r.r_result>=70,1,null)) AS [85-70],
COUNT(IIF(r.r_result<=69 and r.r_result>=60,1,null)) AS [69-60],
COUNT(IIF(r.r_result<60,1,null)) AS [ <60]
FROM results AS r,classes AS c
WHERE r.c_no=c.c_no
GROUP BY c.c_no,c.c_name

### 4
請計算每位學生所得到的學分數，顯示學號、姓名及學分數等三個欄位。

SELECT a.s_no,s.s_name,SUM	(a.c_points) as 學分數
FROM students AS s,(
SELECT r.c_no,r.s_no,c.c_points
FROM results as r,classes as c
WHERE r.r_result>=60 AND c.c_no=r.c_no
) as a
Where a.s_no=s.s_no 
GROUP BY a.s_no,s.s_name

### 5
請問數學課男生和女生的平均成績各多少，顯示姓別、平均成績等二個欄位。

SELECT s.s_sex,AVG(r.r_result) AS 平均成績
FROM students AS s,results AS r
WHERE s.s_no=r.s_no AND r.c_no='C0001'
GROUP BY s.s_sex

### 6
請將此資料庫備份到到 D 槽「Backup」目錄，並寫出完整備份的指令。

BACKUP DATABASE [s1311034027] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\s1311034027.bak',  DISK = N'D:\backup\12.13' WITH NOFORMAT, NOINIT,  NAME = N's1311034027-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO