「達人に学ぶSQL徹底指南書」の演習問題 4-2を解いてブラウザのMySQLの実行環境で実行してみた #MySQL

この記事の概要

この記事は「達人に学ぶSQL徹底指南書/ミック著」の演習問題をブラウザのSQL実行環境(db-fiddle.com)で動かしてみたというものになります。
解説は書籍の方を参照してください。
カラム名がアルファベット表記になっていることに注意してください

db-fiddle.comの使い方

テーブルの作成: Scheme SQLの欄にCREATE TABLEを書いてテーブルを作成
データの挿入: Query SQLの欄にINSERT文を書いてデモデータを挿入する
データの検索: Query SQLの欄にSELCT文を書く

問題文

本文中のStudentsテーブルを使って、特性関数の練習を少しやっておきましょう。
それでは、「全員が9月中に提出済みの学部」を選択するSQLを考えてください。
答えは、「経済学部」ただ一つになります。
理学部は100版の学生が10月になってから提出しているので却下、
文学部と工学部は、そもそも未提出の学生がいる時点でこれも駄目です。

データセットの準備

Schema SQL

CREATE TABLE Students(
  student_id int NOT NUll,
  dpt varchar(255) NOT NULL,
  sbmt_date date
);

Query SQL

INSERT INTO Students VALUES 
('100', 'science', '2005-10-10'),
('101', 'science', '2005-09-22'),
('102', 'literature', NULL),
('103', 'literature', '2005-09-10'),
('200', 'literature', '2005-09-22'),
('201', 'engineering', NULL),
('202', 'economics', '2005-09-25');

解答

詳しい解説は書籍を参照してください。

# 解答パターン1: 学部ごとの行の合計と指定した期間に提出した学生の行の合計が等しい学部だけ抽出
SELECT dpt
FROM Students
GROUP BY dpt
HAVING 
	COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005-09-1' AND '2005-09-31'
                  						THEN  1 ELSE 0 END);

# 解答パターン2: 学部ごとの行の合計とsbmt_dateに2005と09という文字列が含まれている行の数の合計が一致する学部だけ抽出
SELECT dpt
FROM Students
GROUP BY dpt
HAVING
  COUNT(*) = SUM(CASE WHEN EXTRACT (YEAR FROM sbmt_date) = 2005
                      AND  EXTRACT (MONTH FROM sbmt_date) = 09
                      THEN 1 ELSE 0 END);