Share
Embed
Transcript
38 slides
-
Q0: 復習 Q. 2021年度と2020年度の調査結果が含まれる populationテーブルを⽤いて,都道府県別の ⼤学学⽣数の平均値を算出するSQL⽂を書け. SELECT 地域コード, 都道府県, AVG(⼤学学⽣数)
AS 平均学⽣数 FROM population GROUP BY 地域コード; A. -
結果がテーブルなら population_avgテーブル SELECT * FROM population_avg ORDER BY DESC 平均⼤学⽣数
LIMIT 10; population_avgテーブルがあればいいのに… -
副問い合わせ (1/2) SELECT * FROM ORDER BY DESC 平均⼤学⽣数 LIMIT
10; population_avg これを求めるSQL⽂に 置き換える SQL文の中で別のSQL文の実行結果を参照 (別のSQL⽂の結果を⼀時的に保持しておき後で参照する) -
副問い合わせ (2/2) SQL文の中で別のSQL文の実行結果を参照 (別のSQL⽂の結果を⼀時的に保持しておき後で参照する) SELECT * FROM ORDER BY DESC
平均⼤学⽣数 LIMIT 10; カッコで包んだ別のSQLで置き換えた ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード ) -
副問い合わせ (2/2) SQL文の中で別のSQL文の実行結果を参照 (別のSQL⽂の結果を⼀時的に保持しておき後で参照する) SELECT * FROM ORDER BY DESC
平均⼤学⽣数 LIMIT 10; ( SELECT 地域コード, 都道府県, AVG(⼤学学⽣数) AS 平均学⽣数 FROM population GROUP BY 地域コード ) SELECT⽂の中に埋め込まれると読みづらい… -
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 地域コード ) -
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. -
副問い合わせを使わなくても対応できる問題(3/3) elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っている」都道府県 について,activityテーブルで過去1年以内に「旅 ⾏・⾏楽」活動をしたことのある⼈の割合が5割を 超える都道府県と割合を表⽰するSQL⽂は? SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM
activity JOIN elderly_population_top10 ON activity.都道府県 = elderly_population_top10.都道府県 WHERE activity.旅⾏・⾏楽 >= 50; 「⼊っていない」をどうSQLで表現するか? -
IN演算⼦ & NOT IN演算⼦ SELECT * FROM activity WHERE 都道府県
IN ( “愛知県”, “岐⾩県”) ; IN演算子 リストアップした要素集合に値が含まれていればTRUE SELECT * FROM activity WHERE 都道府県 NOT IN ( “愛知県”, “岐⾩県”) ; NOT IN演算子 リストアップした要素集合に値が含まれていなければTRUE -
副問い合わせをWHERE句で使う (1/2) SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity WHERE activity.都道府県 NOT
IN (65歳以上⼈⼝数がTOP10の都道府県リスト) AND activity.旅⾏・⾏楽 >= 50; elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について,activityテーブルで過去1年以内に 「旅⾏・⾏楽」活動をしたことのある⼈の割合が 5割を超える都道府県と割合を表⽰するSQL⽂は? -
副問い合わせをWHERE句で使う (2/2) SELECT activity.都道府県, activity.旅⾏・⾏楽 FROM activity WHERE activity.都道府県 NOT
IN ( SELECT 都道府県 FROM elderly_population_top10 ) AND activity.旅⾏・⾏楽 >= 50; elderly_population_top10テーブルにおいて65歳 以上⼈⼝数の上位10件に「⼊っていない」都道府県 について,activityテーブルで過去1年以内に 「旅⾏・⾏楽」活動をしたことのある⼈の割合が 5割を超える都道府県と割合を表⽰するSQL⽂は? 副問い合わせ -
Q1: 副問い合わせ(1/2) WITH top20 AS ( SELECT 地域コード FROM population
WHERE 調査年度 = 2021 ORDER BY ⼩学校児童数 DESC LIMIT 20 ) SELECT * FROM activity WHERE activity.地域コード IN top20; A. -
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. -
これまでに学んだデータ問い合わせのためのSQL lSELECT lWHERE lORDER BY lGROUP BY l集合演算 (UNION, INTERSECTIONなど)
l結合演算 (直積, INNER JOIN, OUTER JOIN) l副問い合わせ lWITH句 lAS修飾句,IN述語 -
データベースとテーブルの定義 CREATE DATABASE / TABLE⽂を⽤いて データベースとテーブルを定義. CREATE TABLE テーブル名 (
); ①テーブルが持つ属性とそのデータ型, ②主キー, ③(必要なら)外部キー ④(必要なら)その他制約 CREATE DATABASE データベース名; -
SQLでサポートしている代表的なデータ型 ⽂字列を扱う型 “⼭本祐輔” 421 10000 2024/4/29 令和6年4⽉29⽇ INTEGER型 VARCHAR型 DATETIME型
“3.141592” 整数を扱う型 421.0 -3.141592 FLOAT型 実数を扱う型 TRUE FALSE BOOLEAN型 真偽値を扱う型 ⽇付・時間を 統⼀的に扱う型 … -
populationテーブルの場合 CREATE TABLE population ( 地域コード VARCHAR(10), 都道府県 VARCHAR(5), 調査年度
INT, 総⼈⼝ INT, … PRIMARY KEY (地域コード, 調査年度) ); 属性と そのデータ型を定義 主キーを定義 RDBMSによって微妙に異なるのでドキュメントを⾒よう -
レコードの追加 INSERT⽂を⽤いる INSERT INTO テーブル名 ( 属性1, 属性2, …, 属性n
) VALUES (属性1の値, …, 属性nの値), (属性1の値, …, 属性nの値), …; 1⾏1⾏が追加する レコードに対応 ここに並べた列名に対応した値 をVALUES以下のカッコ内に書く -
レコード追加の例 INSERT INTO population ( 地域コード, 都道府県, 調査年度, 総⼈⼝, ⼩学校児童数,
中学校⽣徒数, ⾼等学校⽣徒数, ⼤学学⽣数 ) VALUES ('R01000', '北海道', '2022', xxx, …, xxx), ('R01000', '北海道', '2023', xxx, …, xxx); -
レコードの削除 構⽂ DELETE FROM テーブル名 WHERE 削除条件; DELETE FROM population
WHERE 調査年度 = 2022 OR 調査年度 = 2023; 例 -
レコードの修正 UPDATE テーブル名 SET 更新操作 WHERE 更新を⾏うデータの絞り込み条件; 構⽂ UPDATE population
SET 総⼈⼝ = 総⼈⼝ + 100 WHERE 調査年度 = 2022; 例 SQL⽂はドキュメントを⾒ながら書ければOK -
回 実施日 トピック 1 04/13 ガイダンス:データベースを使わない世界 2 04/20 データベースの概念 3
04/27 関係データモデル 4 05/11 SQL (1/3) 5 05/18 SQL (2/3) 6 05/25 SQL (3/3) 7 06/01 SQL演習 – レポート課題1 8 06/08 実体関連モデル (1/3) 9 06/15 実体関連モデル (2/3) 10 06/22 実体関連モデル (3/3) 11 06/29 正規化 (1/2) 12 07/06 正規化 (2/2) 13 07/13 データベース設計演習 – レポート課題2 14 07/20 索引付け 15 07/27 授業の振り返り or 発展的話題 16 期末試験 今後の予定 38