一、建立資料庫與資料表 (DDL)

CREATE DATABASE Mf2011;
GO
USE Mf2011;
GO

CREATE TABLE Dept (
    DeptNo INT PRIMARY KEY,
    DName NVARCHAR(30),
    Loc NVARCHAR(30)
);

CREATE TABLE Emp (
    EmpNo INT PRIMARY KEY,
    EName NVARCHAR(30),
    Job NVARCHAR(30),
    Mgr INT,
    HireDate DATETIME,
    Sal NUMERIC(10,2),
    Comm NUMERIC(10,2),
    DeptNo INT FOREIGN KEY REFERENCES Dept(DeptNo)
);

二、初始化測試資料 (DML)

INSERT INTO Dept VALUES (10, N'會計', N'紐約'), (20, N'研發', N'達拉斯'), (30, N'業務', N'芝加哥'), (40, N'營運', N'波士頓');

INSERT INTO Emp (EmpNo, EName, Job, Mgr, HireDate, Sal, Comm, DeptNo) VALUES
(7369, N'邁克', N'文員', 7902, '2010-01-12', 675.23, 300, 20),
(7499, N'艾倫', N'業務', 7698, '2009-01-23', 1675.23, 322.50, 30),
(7521, N'沃德', N'業務', 7698, '2008-01-03', 12675.99, 399.50, 30),
(7566, N'瓊斯', N'經理', 7839, '2000-01-01', 8675.99, NULL, 20),
(7654, N'馬丁', N'業務', 7698, '2007-12-31', 1275.99, 999.00, 30),
(7782, N'布雷克', N'經理', 7839, '2007-12-20', 1275.99, NULL, 30),
(7788, N'斯科特', N'分析師', 7566, '2003-01-22', 1275.99, NULL, 10),
(7839, N'金恩', N'總裁', NULL, '2000-01-01', 100244.00, NULL, 10);

三、核心查詢練習 (30+ 實例)

基本查詢

-- 1. 全表
SELECT * FROM Emp;

-- 2. 指定欄位
SELECT EName, Sal FROM Emp;

-- 3. 去重
SELECT DISTINCT DeptNo FROM Emp;

-- 4. 條件篩選
SELECT Sal, Job, DeptNo FROM Emp WHERE EName = N'金恩';

-- 5. 統計部門數
SELECT COUNT(*) FROM Dept;
SELECT COUNT(DISTINCT DeptNo) AS 部門數 FROM Emp;

運算與函數

-- 6. 年薪 = 月薪*12 + 年終獎 (處理 NULL)
SELECT ISNULL(Sal,0)*12 + ISNULL(Comm,0) AS 年薪 FROM Emp;

-- 7. 範圍查詢
SELECT * FROM Emp WHERE Sal BETWEEN 2000 AND 25000;

-- 8. 模糊查詢
SELECT EName, Sal FROM Emp WHERE EName LIKE N'J%';      -- J 開頭
SELECT EName, Sal FROM Emp WHERE EName LIKE N'__O%';    -- 第 3 字為 O

集合運算

-- 9. IN 列表
SELECT * FROM Emp WHERE EmpNo IN (123, 345, 1999, 7744);

-- 10. IS NULL
SELECT * FROM Emp WHERE Mgr IS NULL;  -- 無主管 (總裁)

排序

-- 11. 多欄位排序
SELECT * FROM Emp ORDER BY DeptNo ASC, Sal DESC;

-- 12. 別名排序
SELECT ISNULL(Sal,0)*12+ISNULL(Comm,0) AS 年薪 FROM Emp ORDER BY 年薪;

聚合與分組

-- 13. 最高/最低薪
SELECT MAX(Sal) AS 最高薪, MIN(Sal) AS 最低薪 FROM Emp;

-- 14. 子查詢找最低薪員工
SELECT * FROM Emp WHERE Sal = (SELECT MIN(Sal) FROM Emp);

-- 15. 平均與總和
SELECT AVG(Sal) AS 平均薪, SUM(Sal) AS 薪資總和 FROM Emp;

-- 16. 高於平均薪員工
SELECT EName, Sal FROM Emp WHERE Sal > (SELECT AVG(Sal) FROM Emp);

-- 17. 分組統計
SELECT DeptNo, AVG(Sal) AS 平均薪, MAX(Sal) AS 最高薪 FROM Emp GROUP BY DeptNo;

-- 18. HAVING 過濾分組
SELECT DeptNo, AVG(Sal) FROM Emp GROUP BY DeptNo HAVING AVG(Sal) < 2000;

多表連接

-- 19. 內連接：業務部門位置與員工
SELECT D.Loc, E.EName FROM Emp E JOIN Dept D ON E.DeptNo = D.DeptNo WHERE D.DName = N'業務';

-- 20. 別名消除歧義
SELECT E.EName, E.Sal, D.DName FROM Emp E JOIN Dept D ON E.DeptNo = D.DeptNo;

-- 21. 自連接：員工與主管
SELECT E.EName AS 員工, M.EName AS 主管
FROM Emp E JOIN Emp M ON E.Mgr = M.EmpNo;

四、備份與還原

-- 備份
BACKUP DATABASE Mf2011 TO DISK = 'F:\Mf2011.bak' WITH COMPRESSION, CHECKSUM;

-- 刪除
DROP DATABASE Mf2011;

-- 還原
RESTORE DATABASE Mf2011 FROM DISK = 'F:\Mf2011.bak' WITH REPLACE, STATS = 10;

五、主鍵/外鍵/約束重點提醒

• 主鍵：唯一 + 非空
• 外鍵：只能參考主鍵/唯一鍵，資料型別必須完全一致
• ON DELETE CASCADE / ON UPDATE CASCADE 實現級聯