11. SQL の IN，副問い合わせ

11. SQL の IN，副問い合わせ URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞：この資料では「いらすとや」のイラストを使用しています 1

• https://www.kkaneko.jp/de/ds/index.html

複数の値との比較（IN），副問い合わせ • 複数の値との比較（IN） SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); 一階または二階 • IN は，問い合わせの結果を別の問い合わせで使う（副問い 合わせ）ときでも便利 SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 2

アウトライン 番号 項目 復習 11-1 11-2 11-3 11-4 11-5 SQL の IN Access を用いた IN の演習 SQL の副問い合わせ Accessを用いた副問い合わせの演習 Paiza.IO による演習 3

リレーショナルデータベースシステム データベースシステムの一種 コンピュータ 記憶 装置 リレーショナル リレーショナル データベース データベース 管理システム たくさんのテーブルが格納される あわせて リレーショナルデータベースシステム 4

SQL • SQL は、リレーショナルデータベースシステムの さまざまな機能を使える言語 問い合わせ（クエリ）、 テーブル定義、 その他の操作 5

SQL による問い合わせ（クエリ）の例 ① SELECT * FROM 商品; ② SELECT 名前, 単価 FROM 商品; ③ SELECT 名前, 単価 FROM 商品 WHERE 単価 > 80; ④ SELECT 受講者, COUNT(*) FROM 成績 GROUP BY 受講者; ⑤ SELECT * FROM 米国成人調査データ ORDER BY 年齢; ⑥ SELECT * FROM T, S; ⑦ SELECT * FROM T, S WHERE a = b; 6

Access で SQLビューを開く． ①「作成」タブで、 「クエリデザイン」 をクリック このような 表示が出た ときは 「閉じる」を クリック ②「デザイン」タブで、 「表示」を展開し「SQL ビュー」を選ぶ 7

11-1. SQL の IN 8

SQL での条件 • 比較 (= など） SELECT * FROM 授業 WHERE 教室名 = '一階'; • パターンマッチ (LIKE) SELECT * FROM 授業 WHERE 教室名 LIKE '%階'; Access では % でなく * • 複数の値との比較（IN） SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); • 条件は、複数を AND や OR でつなげることができる SELECT * FROM 授業 WHERE 教室名 = '一階' OR 教室名 = '二階'; 9

SQL の IN 複数の値と比較． そのうち１つの値でも一致するものを結果とする SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); 半角丸かっこ で囲む 半角の カンマ 半角丸かっこ で囲む 10

11-2. Access を用いた IN の演習 11

演習用のデータベースファイル • 演習用の Access データベースファイル セレッソの利用者は，セレッソからもダウンロード可能 ファイル名: db11.accdb • 「コンテンツの有効化」のメッセージが出たときは、確認 のうえ、次にすすむ • つぎのような表示が出たときは、確認のうえ、「はい」 12

演習で使うデータベース 米国成人調査データ （１９９４年、米国における統計調査データのうち 32561 人分） ※ このデータを使います （演習では、特定の職業、学歴、性別、母国を差別的に見ないようにしてください） データの出典：Lichman, M. (). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science （米国） 13

米国成人調査データ 「成績」はあとで使う 14

SQL ビュー Access で、SQL ビューを開くときは、「表示」→ 「SQLビュー」と操作する 表示の下に「SQLビュー」が ないときは、 「作成」，「クエリデザイン」 と操作 15

問い合わせ（クエリ）での、２つのビュー 実行 SQL ビュー 問い合わせ（クエリ）の 作成、編集 表示 + SQL ビュー データシートビュー 問い合わせ（クエリ）の 結果 マウス操作でビューを切り替え 16

SELECT * FROM 米国成人調査データ WHERE 教育 IN ('10th', '11th'); ・空白文字は半角 ・「;」は最後にだけ 選択 10th, 11th だけが選ばれている 17

SELECT * FROM 米国成人調査データ WHERE 母国 IN ('インド', 'メキシコ'); ・空白文字は半角 選択 ・「;」は最後にだけ インド，メキシコだけが選ばれている 18

11-3. SQL の副問い合わせ 19

単純な問い合わせ 成績テーブル 学生番号 101 の科目名を得る SQL SELECT 科目名 FROM 成績 WHERE 学生番号 = 101; 科目名 データベース 数学 結果は１つのテーブル， テーブルの本体は「データベース」と「数学」 20

副問い合わせ 問い合わせの結果を、別の問い合わせで使う 成績テーブル 学生番号 101 の科目名を得る SQL SELECT 科目名 FROM 成績 WHERE 学生番号 = 101; 科目名 データベース 数学 「データベース」と「数学」 学生番号 101 の科目名のものを（１つでもよいので）受講している 学生番号を全て得る SQL SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 学生番号 101 102 103 「101」と「102」と「103」 21

副問い合わせの書き方例 次の書き方が便利 SELECT ... FROM ... WEHRE ... IN (SELECT ... FROM ... WHERE ...); 学生番号 101 の科目名のものを（１つでもよいので）受講している 学生番号を全て得る SQL SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 22

11-4. Access を用いた 副問い合わせの 演習 23

副問い合わせ 副問い合わせでは、 問い合わせの結果を、別の問い合わせで使う 単純な問い問い合わせ 全ての中の最高点 SELECT MAX (得点) FROM 成績; 副問い合わせ 最高点を取ったのはだれか SELECT 学生番号 FROM 成績 WHERE 得点 IN (SELECT MAX (得点) FROM 成績); 24

成績テーブル 学生番号 101 101 102 102 103 201 202 科目名 データベース 数学 データベース プログラミング データベース 歴史 地理 演習では， 11-2. Access を用いた IN の演習 で用いた db11.accdb を使用 得点 90 80 85 100 95 90 90 25

SELECT 科目名 FROM 成績 WHERE 学生番号 = 101; 単純な問い合わせ 学生番号 101 の科目名 26

SELECT MAX (得点) FROM 成績; 単純な問い合わせ 全ての中の最高点 27

SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN ('データベース', '数学'); 単純な問い合わせ データベースや数学が科目名にある学生番号 28

SELECT 学生番号 FROM 成績 WHERE 得点 IN (SELECT MAX (得点) FROM 成績); 副問い合わせ 最高点をとった学生 29

SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 副問い合わせ 学生番号 101 の科目名のものを（１つでもよいの で）受講している学生番号 30

この授業では「書き方２」をおススメします • 書き方１ （副問い合わせの答えが必ず１つの場合） SELECT ・・・ FROM ・・・ WHERE ・・・ = ( SELECT ・・・ FROM ・・・ WHERE ・・・); • 書き方２ （副問い合わせの答えが２つ以上になる可能性がある場合） SELECT ・・・ FROM ・・・ WHERE ・・・ IN ( SELECT ・・・ FROM ・・・ WHERE ・・・); 31

11-5. Paiza.IO による演習 32

Paiza.IO の使い方 ① ウェブブラウザを起動する ② 次の URL を開く https://paiza.io/ ③ もし，表示が英語になっていたら，日本語に切り 替える

• https://paiza.io/

④ 「コード作成を試してみる」をクリック ⑤ 「MySQL」を選ぶ（左上のボタンをクリックするとメニューが出る）

プログラムの 編集画面 プログラムを 書き換えること ができる 実行ボタン 35

編集画面を確認する。 すでに、SQL が入っているが、使わないので消す。

• 次の SQL を入れ，実行結果を確認 使用している Web ブラウザの設定等によっては，結果が表示されない場合 がある．そのときは，Webブラウザを別のものに変えて実行 37

• 次の SQL を入れる。次のページに続く 38

• 次の SQL を入れる。次のページに続く 39

• 実行結果を確認 使用している Web ブラウザの設定等によっては，結果が表示されない場合 がある．そのときは，Webブラウザを別のものに変えて実行 40

複数の値との比較（IN），副問い合わせ • 複数の値との比較（IN） SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); 一階または二階 • IN は，問い合わせの結果を別の問い合わせで使う（副問い 合わせ）ときでも便利 SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 41