SQL 基礎練習：建表、查詢、排序與聚合

SQL 基礎練習：建表、查詢、排序與聚合

記錄基礎 SQL 練習題，涵蓋建表約束、條件查詢、多欄位排序、聚合函數與外鍵關聯。

1. 建立學生表

CREATE TABLE student_masen (
  sno CHAR(4) NOT NULL PRIMARY KEY,
  sname NCHAR(10) NOT NULL,
  sex CHAR(1) NULL CHECK (sex IN ('M','F')),
  age SMALLINT NULL CHECK (age > 15 AND age < 25)
);

2. 建立課程表

CREATE TABLE course_masen (
  cno CHAR(4) NOT NULL PRIMARY KEY,
  cname CHAR(10) NOT NULL,
  credit SMALLINT NULL CHECK (credit > 0 AND credit < 10)
);

3. 建立選課表（含外鍵）

CREATE TABLE S_C_masen (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  PRIMARY KEY (sno, cno),
  grade SMALLINT CHECK (grade >= 0 AND grade <= 100)
);

ALTER TABLE S_C_masen
  ADD CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES student_masen(sno),
  ADD CONSTRAINT fk_cno FOREIGN KEY (cno) REFERENCES course_masen(cno);

修正：原文外鍵參考 sutdent_masen(cno) 拼寫錯誤，已更正為 course_masen(cno)。

4. 查詢練習

4.1 查詢性別非男的學生姓名

SELECT sname FROM student_masen WHERE sex != 'M';

4.2 查詢男性且年齡 > 20 的學生姓名

SELECT sname FROM student_masen WHERE sex = 'M' AND age > 20;

4.3 課程號降序、課程名升序排列

SELECT * FROM course_masen ORDER BY cno DESC, cname ASC;

4.4 統計學號 S1 修了幾門課

SELECT COUNT(cno) FROM S_C_masen WHERE sno = 'S1';

重點知識回顧

• PRIMARY KEY：主鍵約束，隱含 NOT NULL + UNIQUE。
• CHECK：MySQL 8.0.16 前僅語法解析，不強制執行；建議應用層或觸發器驗證。
• FOREIGN KEY：需參考表有索引，InnoDB 支援，MyISAM 不支援。
• ORDER BY 多欄位：按宣告順序依優先級排序。
• COUNT(column) 忽略 NULL 值；COUNT(*) 計算所有行。