Embedded Files
SQLZOOが障害で利用できない場合はPaizaラーニングのSQL講座を受講して演習をする。
復習１
復習２
既出キーワードと記号 こちらを見てクエリの機能を思い出せますか？
ノーベル賞テーブルでSUMとCOUNT関数を練習
のSQL入力欄を利用して以下を復習する
insert into values
update set where
select a,b,c
select a/b,a*b,a+b,a-b
select a as x
select *
select count(*)
select sum(a)
select distinct a,b,c
from
where
X in （a,b,c)
X and Y
X or Y
not X
X between A and B
X like
(order by)
,
*
'
<>
=
>
<
>=
<=
%
_
SQLZOOの設定
SQLZOOの設定
前回と同様に歯車アイコンから設定を変更する。
DBMSを MySQL に切り替える
今回の内容：
SQLの selectにおける 集計関数 サブクエリ― グループ化 並べ替え について学ぶ
新出キーワード
集計関数 count sum avg max min
group by
having
all
any
order by
関数とSELECT文の句（GROUP BY の部分）
表の結合とサブクエリー （サブクエリーの部分。表の結合は次回扱う）
サブクエリーと ALLとANY の組み合わせ http://www.techscore.com/tech/sql/SQL7/07_02.html/
HAVING と WHERE の差 https://codezine.jp/article/detail/652
SQLZOOに用意された解説記事
SELECT文の評価順序
SELECT文の評価順序
SQLでは
select distinct from join on where group by having order by
の順にキーワードを書く。
しかし、データベース内部では書いた順ではなく以下の様に処理が進む。
覚えておいてSQLを読み書きしたい。
FROM レコードを取得するテーブル
JOIN 結合するテーブル
ON テーブルの結合条件
WHERE レコードの選択条件
GROUP BY グループ化するフィールド
HAVING グループ化の結果を出力する条件
SELECT 表示するフィールドや式
DISTINCT 重複の除去
ORDER BY 出力の並べ替え
TOP（LIMIT） 出力するレコード数の上限指定
参照記事： https://qiita.com/suzukito/items/edcd00e680186f2930a
（余談） SQLは大規模言語モデルなど生成系AIのプロンプトに近い。ただし、自然言語の様な表記の曖昧性は無く厳密な文法に従った記述が必要。SQLの記述に従ってDBMSがテーブルへのアクセスやフィールドの処理手順のアルゴリズムを組み手立てて実行する。その点では手続き型のプログラム言語とも異なり、関数スタイルのコード記述に近いものがある。
課題
課題
SQLZOO（日本語のチュートリアルページ）
を参考にして自作の問題を作成して自分で回答する。
提出方法
提出方法
SQLZOOのSQL入力欄を利用する
「#」記号に続けて【自作の問題文】を記入する
自作問題の【解答SQL】を記入して Submit SQLで実行する
問題とSQLと実行結果をスクリーンショットに撮る
複数の問題を考えた場合は1枚の画像にまとめて提出してよい
（自作の問題を実行しても正解判定のアイコンは表示されない）
配点： 問題の難易度に応じて点数を与える
学習した内容をなるべく多く反映するように工夫するとよい
新しく登場したキーワードやテクニック、または復習となるような問題でもよい
解答例
SQLZOOのヒントなど
SQLZOOのヒントなど
The nobel table can be used to practice more SUM and COUNT functions
1-1 全受賞者数を表示する
1-2 各分野subjectが1回だけ登場するリストを表示する
1-3 物理学賞Physicsの受賞トータル数を表示する
1-4 各分野ごとに分野と受賞数を表示する
1-5 各分野の初受賞の年を表示する
1-6 2000年の各分野の受賞数を表示する
1-7 各分野の異なる受賞者の人数を表示する（同一人物の複数回受賞は1名と数える）
（ヒント）distinct を付けてフィールドを集計する
1-8 各分野に何年間受賞者がいたか表示する （修正2023.5.24）→ 各分野ごとに受賞が有った年数を表示する
（ヒント）nobelテーブルは受賞者のリストである。ある年度で1つの分野に複数の受賞者がいる場合がある。また平和賞などは受賞者がいない年度もある。
1-9 物理学賞Physicsが3人の年を表示する
1-10受賞回数が2回以上の受賞者を表示する
(以下2問は応用課題）
1-11 複数の分野で受賞した人を表示する
1-12 2000年以降で受賞者が3人いる分野の年と分野を表示する（訳注：GROUP BYは並び順に影響することに注意）
4.SELECT within SELECT Tutorial
このチュートリアルではさらに複雑なクエリ―を実行するためにselect文の中で select 文を利用する方法を示す。
4-1 ロシア（Russia）よりも人口(population)が多い各国を表示する
4-2 国民一人当たりの国内総生産(per Capita GDP)がイギリスよりも大きなヨーロッパの国を表示する。
4-3 'Argentina'または'Australia' を含む大陸にある国の、国名と大陸を表示する。国名順に表示する。
4-4 人口がカナダ Canadaよりも多く、ポーランドPolandよりも少ない国の、国名と人口を表示する。
4-5 ドイツは（人口 80000000人)とヨーロッパで最大の国である。オーストリア（8500000人）はドイツの人口の１１％である。
ヨーロッパの各国について 国名と人口を表示する。ドイツ人口の何％かで表示する。 パーセント表示には、 % 記号を使え。
（ヒント） ROUND と CONCAT 関数を使う
4-6 ヨーロッパのどの国のGDPよりも大きなGDPを持つ国の国名だけを表示する。（GDPがNULL の国も有る）
（ヒント） WHEREの条件で ALL を利用するとよい。加えて、GDPが null である国を除外する条件も付ける。
値 null が比較演算に含まれる場合の扱いについて、調べて見よう。
（ヒント）サブクエリ―でグループ化を使う方法もある
4-7 （ちょこっと予習） テーブルとフィールドについて、テーブルを指定したフィールドの指定が可能。
表の結合条件にテーブルを指定したフィールドを用いる。この問題ではサブクエリーの表と外側のクエリ―の表を区別するためにテーブルの名前を付け替えている。
各大陸のもっとも大きな国（面積で）の大陸、国名、面積を表示する。
※自己相関クエリー というテクニックを利用して解きます。
4-8 各大陸を表示する。その大陸で、アルファベット順で先頭の国名も表示する。
（ヒント）min関数は文字列も処理できます。
ここから先は難問。テクニックを駆使して解く必要がある。
4-9 その大陸の全ての国の人口が250000000 より少ない大陸にある国の名前と大陸と人口を表示する。
（ヒント）自己相関クエリを使う。
（ヒント）サブクエリ―でグループ化を使えば自己相関クエリを使わなくて済む
4-10 ある国は、おなじ大陸内の他のどの国よりも３倍大きな人口を持っている。国名と大陸を表示する。
（ヒント）条件「その国の population/3 の値が、その国の他の国の population の最大値よりも大きい」
（補足）
4-7 各大陸のもっとも大きな国（面積で）の大陸、国名、面積を表示する。
※自己相関クエリー というテクニックを利用して解きます。
SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y WHERE y.continent=x.continent)
回答欄には上記のＳＱＬがあらかじめ入力されている。修正して問題を解くことができる。上のSQLは解答例。
回答欄には上記のＳＱＬがあらかじめ入力されている。修正して問題を解くことができる。上のSQLは解答例。
解説：
解説：
このSQLのサブクエリ（カッコ内の部分）は、外側（メイン）のＳＱＬのテーブルのフィールド（x.continent）を参照している。
そのため、サブクエリを単体で実行しても実行結果（値）を求めることができない（メインのSQLと切り離して実行できない）。
プログラム言語の2重ループの内側のループと外側のループのような関係になっている。
このSQLのサブクエリ（カッコ内の部分）は、外側（メイン）のＳＱＬのテーブルのフィールド（x.continent）を参照している。
そのため、サブクエリを単体で実行しても実行結果（値）を求めることができない（メインのSQLと切り離して実行できない）。
プログラム言語の2重ループの内側のループと外側のループのような関係になっている。
テーブル名 world を別名 x と y で参照している。 テーブル名 as 別名 の構文で as は省略可能。
条件式 >= ALL について
内側のサブクエリの検索結果が テーブル x で処理中のレコード（国）と同じ大陸にある各国の人口のという複数の値になる。ALLを指定するとそれら全ての値以上（自分自身の人口を含むので＝が必要）かどうかを判定する。つまり最大の人口を判定できる。
5.SUM and COUNT
5-1 ～ 5-5 は前回取り組んだ。
group by と having を利用
（ヒント）Using GROUP BY and HAVING.
5-6 各大陸ごとに大陸名とそこにある国の数を表示する。
5-7 人口が10000000 人以上の各大陸の国の数を表示する。
5-8 各大陸の人口（大陸内の各国の合計）で１00000000 以上の大陸を表示 （ヒント） having で集計関数を利用した条件を指定する。
Page updated
Google Sites
Report abuse