Upgrade to Pro — share decks privately, control downloads, hide ads and more …

データベース06: SQL (3/3) 副問い合わせ

データベース06: SQL (3/3) 副問い合わせ

1. 副問い合わせ
2. テーブルの定義と更新

講義ノートURL
https://dbnote.hontolab.org/content/sql/03.html

Y. Yamamoto

May 27, 2024
Tweet

More Decks by Y. Yamamoto

Other Decks in Technology

Transcript

  1. 副問い合わせ (1/2) SELECT * FROM ORDER BY DESC 平均⼤学⽣数 LIMIT

    10; population_avg これを求めるSQL⽂に 置き換える SQL文の中で別のSQL文の実行結果を参照 (別のSQL⽂の結果を⼀時的に保持しておき後で参照する)
  2. 副問い合わせ (2/2) SQL文の中で別のSQL文の実行結果を参照 (別のSQL⽂の結果を⼀時的に保持しておき後で参照する) SELECT * FROM ORDER BY DESC

    平均⼤学⽣数 LIMIT 10; カッコで包んだ別のSQLで置き換えた ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード )
  3. 副問い合わせ (2/2) SQL文の中で別のSQL文の実行結果を参照 (別のSQL⽂の結果を⼀時的に保持しておき後で参照する) SELECT * FROM ORDER BY DESC

    平均⼤学⽣数 LIMIT 10; ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード ) SELECT⽂の中に埋め込まれると読みづらい…
  4. WITH句 WITH句を使って副問い合わせを外出しできる SELECT * FROM ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数)

    AS 平均学⽣数 FROM population GROUP BY 地域コード ) ORDER BY DESC 平均⼤学⽣数 LIMIT 10; SELECT * FROM population_avg ORDER BY DESC 平均⼤学⽣数 LIMIT 10; = WITH句を使えば副問い合わせSQLの可読性UP WITH population_avg AS ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード )
  5. Q1: 副問い合わせ(1/2) Q. populationテーブルにおいて調査年度が2021時点 の⼩学校児童数上位20件のレコードを求め,それら とactivityテーブルの内容を結合した内容を表⽰す るSQL⽂を書け. WITH top20 AS

    ( SELECT 地域コード FROM population WHERE 調査年度 = 2021 ORDER BY ⼩学校児童数 DESC LIMIT 20 ) SELECT * FROM activity INNER JOIN top20 USING(地域コード); A.
  6. IN演算⼦ & NOT IN演算⼦ SELECT * FROM activity WHERE 都道府県

    IN ( “愛知県”, “岐⾩県”) ; IN演算子 リストアップした要素集合に値が含まれていればTRUE SELECT * FROM activity WHERE 都道府県 NOT IN ( “愛知県”, “岐⾩県”) ; NOT IN演算子 リストアップした要素集合に値が含まれていなければTRUE
  7. 副問い合わせをWHERE句で使う (1/2) SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity activity.都道府県 NOT IN

    (65歳以上⼈⼝数がTOP10の都道府県リスト) AND activity.旅⾏・⾏楽 >= 50; elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について,activityテーブルで過去1年以内に 「旅⾏・⾏楽」活動をしたことのある⼈の割合が 5割を超える都道府県と割合を表⽰するSQL⽂は?
  8. 副問い合わせをWHERE句で使う (2/2) SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity activity.都道府県 NOT IN

    ( SELECT 都道府県 FROM elderly_population_top10 ) AND activity.旅⾏・⾏楽 >= 50; elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について,activityテーブルで過去1年以内に 「旅⾏・⾏楽」活動をしたことのある⼈の割合が 5割を超える都道府県と割合を表⽰するSQL⽂は? 副問い合わせ
  9. Q1: 副問い合わせ(1/2) WITH top20 AS ( SELECT 地域コード FROM population

    WHERE 調査年度 = 2021 ORDER BY ⼩学校児童数 DESC LIMIT 20 ) SELECT * FROM activity WHERE activity.地域コード IN top20; A.
  10. Q2: 副問い合わせ(2/2) WITH top10 AS ( SELECT 地域コード FROM activity

    ORDER BY ボランティア DESC LIMIT 10 ) SELECT 都道府県, AVG(総⼈⼝) AS 平均総⼈⼝ FROM population JOIN top10 USING(地域コード) GROUP BY 都道府県; A.
  11. これまでに学んだデータ問い合わせのためのSQL lSELECT lWHERE lORDER BY lGROUP BY l集合演算 (UNION, INTERSECTIONなど)

    l結合演算 (直積, INNER JOIN, OUTER JOIN) l副問い合わせ lWITH句 lAS修飾句,IN述語
  12. データベースとテーブルの定義 CREATE DATABASE / TABLE⽂を⽤いて データベースとテーブルを定義. CREATE TABLE テーブル名 (

    ); ①テーブルが持つ属性とそのデータ型, ②主キー, ③(必要なら)外部キー ④(必要なら)その他制約 CREATE DATABASE データベース名;
  13. SQLでサポートしている代表的なデータ型 ⽂字列を扱う型 “⼭本祐輔” 421 10000 2024/4/29 令和6年4⽉29⽇ INTEGER型 VARCHAR型 DATETIME型

    “3.141592” 整数を扱う型 421.0 -3.141592 FLOAT型 実数を扱う型 TRUE FALSE BOOLEAN型 真偽値を扱う型 ⽇付・時間を 統⼀的に扱う型 …
  14. populationテーブルの場合 CREATE TABLE population ( 地域コード VARCHAR(10), 都道府県 VARCHAR(5), 調査年度

    INT, 総⼈⼝ INT, … PRIMARY KEY (地域コード, 調査年度) ); 属性と そのデータ型を定義 主キーを定義 RDBMSによって微妙に異なるのでドキュメントを⾒よう
  15. レコードの追加 INSERT⽂を⽤いる INSERT INTO テーブル名 ( 属性1, 属性2, …, 属性n

    ) VALUES (属性1の値, …, 属性nの値), (属性1の値, …, 属性nの値), …; 1⾏1⾏が追加する レコードに対応 ここに並べた列名に対応した値 をVALUES以下のカッコ内に書く
  16. レコード追加の例 INSERT INTO population ( 地域コード, 都道府県, 調査年度, 総⼈⼝, ⼩学校児童数,

    中学校⽣徒数, ⾼等学校⽣徒数, ⼤学学⽣数 ) VALUES ('R01000', '北海道', '2022', xxx, …, xxx), ('R01000', '北海道', '2023', xxx, …, xxx);
  17. レコードの修正 UPDATE テーブル名 SET 更新操作 WHERE 更新を⾏うデータの絞り込み条件; 構⽂ UPDATE population

    SET 総⼈⼝ = 総⼈⼝ + 100 WHERE 調査年度 = 2022; 例 SQL⽂はドキュメントを⾒ながら書ければOK
  18. 回 実施日 トピック 1 04/15 ガイダンス:データベースを使わない世界 2 04/22 データベースの概念 3

    04/29(祝) 関係データモデル 4 05/13 SQL (1/3) 5 05/20 SQL (2/3) 6 05/27 SQL (3/3) 7 06/03 SQL演習 – レポート課題1 8 06/10 実体関連モデル (1/3) 9 06/17 実体関連モデル (2/3) 10 06/24 実体関連モデル (3/3) 11 07/01 正規化 (1/2) 12 07/08 正規化 (2/2) 13 07/15(祝) データベース設計演習 – レポート課題2 14 07/22 索引付け 15 07/29 NoSQL 16 08/05 期末試験 今後の予定 38