# 2020/07/01 upper layer progress

## SQL勉強会
今回は、SQL zoo(https://sqlzoo.net/) にて[SUM and COUNT]のquizから、
[JOIN]まで取り掛かった。
以下問題と答え
- [SUM and COUNT quiz](#SUM-and-COUNT-quiz)
- [1. ヨーロッパの全ての国の人口を集計する文を選べ](#1-ヨーロッパの全ての国の人口を集計する文を選べ)
- [2. 150000人よりも少ない人口の国の数を表示する文を選べ](#2-150000人よりも少ない人口の国の数を表示する文を選べ)
- [3. SQL の主要な集計関数のリストを選べ](#3-SQLの主要な集計関数のリストを選べ)
- [4. 次のコードの実行結果を選べ](#4-次のコードの実行結果を選べ)
- [5. 'Poland', 'Germany', 'Denmark' の人口の平均を表示するコードを選べ](#5-‘Poland’-‘Germany’-‘Denmark’-の人口の平均を表示するコードを選べ)
- [6. 各地域の地域と人口密度を表示するコードを選べ](#6-各地域の地域と人口密度を表示するコードを選べ)
- [7. 人口が最大の国の国名と人口密度を表示するコードを選べ](#7-人口が最大の国の国名と人口密度を表示するコードを選べ)
- [8. 次のコードの実行結果を選べ](#8-次のコードの実行結果を選べ)

### SUM and COUNT quiz

　　　　　　　　　　　　　　　　**bbc**
| name | region | area | population | gdp |
|---------------|---------------|:--------:|------------|---------------|
| Afghanistan | South Asia | 652225 | 26000000 | |
| Albania | Europe | 28728 | 3200000 | 6656000000 |
| Algeria | Middle East | 2400000 | 32900000 | 75012000000 |
| Andorra | Europe | 468 | 64000 | |

#### 1. ヨーロッパの全ての国の人口を集計する文を選べ

SELECT SUM(population) FROM bbc WHERE region = 'Europe'

カラムに関して集計関数SUMの適用。

#### 2. 150000人よりも少ない人口の国の数を表示する文を選べ

SELECT COUNT(name) FROM bbc WHERE population < 150000

カラムに関して集計関数COUNTの適用。
#### 3. SQL の主要な集計関数のリストを選べ

AVG(), COUNT(), MAX(), MIN(), SUM()

覚えとく。
#### 4. 次のコードの実行結果を選べ:

SELECT region, SUM(area)
  FROM bbc 
  WHERE SUM(area) > 15000000 
  GROUP BY region

``WHERE の用法が正しくないため結果は無し``
WHERE句で集計関数の結果は条件にできないみたい？

#### 5. 'Poland', 'Germany', 'Denmark' の人口の平均を表示するコードを選べ

SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')

INで指定した要素のみに対して、AVG()。
#### 6. 各地域の地域と人口密度を表示するコードを選べ

SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region

GROUP BYで指定したカラムの要素ごとに、別のカラムのSUM。

#### 7. 人口が最大の国の国名と人口密度を表示するコードを選べ

SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc)

態々``WHERE population = (SELECT MAX(population) FROM bbc)``で指定する必要があるんですね

#### 8. 次のコードの実行結果を選べ:

SELECT region, SUM(area) 
  FROM bbc 
  GROUP BY region 
  HAVING SUM(area)<= 20000000

　　　　**Table D**
| | |
|---------------|----------|
| Americas | 732240 |
| Middle East | 13403102 |
| South America | 17740392 |
| South Asia | 9437710 |

カラム``region``の要素ごとに``SUM(area)``で場合分け

### The JOIN operation

``
game ゲームテーブル 試合日や参加チームなどを記録

id 試合id
mdate 試合日
stadium スタジアム
team1 チーム１（アルファベット３文字）
team2 チーム２（アルファベット３文字）

goal ゴールテーブル　ゴールの記録

matchid 試合id
teamid チームid（アルファベット３文字）
player プレイヤー
gtime ゴール時間

eteam 参加チームテーブル

id チームid（アルファベット３文字）
teamname チーム名前
coach コーチ

PK 主キー
FK 外部キー
``

#### 1
最初の例としてラストネームが Bender である選手のゴール記録が示されている。

「*」でテーブルの全フィールド（カラム）を宣言する。これは matchid, teamid, player, gtime を短く書く方法である。

matchid と player 名をドイツ(Germany)チームの全ゴールについて表示する。ドイツプレイヤーを識別するには、次を確認: ``teamid = 'GER'``

SELECT matchid,player FROM goal 
  WHERE teamid = 'GER'

「*」使わないんかい。

#### 2
上記のクエリ―では、Lars Benderのゴールは ゲーム 1012 で確認できる。さて、この試合でどのチーム達がプレイしていたか知りたい。

``goal``テーブルで試合番号は`` matchid ``だが、``game``テーブルでは`` id ``となっていることに注意する。 試合 2012 の情報を知るには`` game`` の該当する行を参照する。

試合 1012 の`` id, stadium, team1, team2 ``を表示する。

SELECT id,stadium,team1,team2
  FROM game
  WHERE id = "1012"

#### 3
``JOIN ``を利用して、２つのステップを組み合わせて単独のクエリ―にすることができる。

``game``と``goal``の詳細を得るには、

SELECT *
  FROM game JOIN goal ON (id=matchid)

FROM節は``goal``テーブルと``game``テーブルのデータを統合する。 ON で gameの行と goalの行を、goalテーブルのidをgameの matchidと一致させて対応させる。 (もっと明確に言うとすれば、こう言う。

ON (game.id=goal.matchid)

得点が有るたびに、``goal``テーブルから`` player``を表示して、``stadium`` 名は`` game`` ゲーブルから表示する。

ドイツの全ゴールについて`` player, teamid ,stadium, mdate ``を表示するように修正する。

SELECT player, teamid ,stadium, mdate
  FROM game JOIN goal ON (id=matchid)
  WHERE teamid = "GER"

``ON``で合わせる基準となるカラムを定めて、``JOIN``でtableを合わせて、目的のものを引き出す。

#### 4
上の問題と同様に JOIN を利用して、

Marioという名前の選手のゴールについて、``team1, team2 , player ``を表示する。``player LIKE 'Mario%'``

SELECT team1, team2 , player
  FROM game JOIN goal ON (id=matchid)
  WHERE player LIKE 'Mario%'

#### 5
``eteam ``には各参加国のコーチが記載されている。 JOIN で`` goal`` を ``eteam ``に結合する。`` goal JOIN eteam on teamid=id``

最初の１０分間でゴールしたという条件で、`` player, teamid, coach, gtime ``を表示。``gtime<=10``

SELECT player, teamid, coach, gtime
  FROM goal JOIN eteam on teamid=id
  WHERE gtime<=10

#### 6
``game`` と ``eteam`` を結合するには、

``game JOIN eteam ON (team1=eteam.id)``

または、

``game JOIN eteam ON (team2=eteam.id)``

``id ``は`` game``と`` eteam ``で同じ名前なので、単に``id``と書く代わりに``eteam.id``と書かねばならないことに注意する。

``team1``のコーチ``coach``が ``'Fernando Santos'`` となる試合日``mdate``とチーム名``teamname``を表示。

SELECT mdate,teamname
  FROM game JOIN eteam ON (game.team1=eteam.id)
  WHERE coach='Fernando Santos'

カラム名が被っている場合は、どちらのカラムか明示する。

#### 7
``'National Stadium, Warsaw'`` スタジアムで開催された試合でゴールした選手を表示する。

SELECT player FROM game JOIN goal ON (id=matchid)
 WHERE stadium = 'National Stadium, Warsaw'

#### 8
例として、ドイツ―ギリシャ戦の全試合を表示するクエリーが入力してある。
代わりに、ドイツと対戦して、ゴールした選手の名前を全て表示する。

SELECT DISTINCT(player)
  FROM game JOIN goal ON matchid = id 
  WHERE (team1='GER' OR team2='GER')
      AND (teamid <> "GER")

ドイツの選手は除くらしい。

#### 9
チーム名``teamname``とゴール数の合計を表示する。

SELECT teamname, COUNT(matchid)
 FROM goal JOIN eteam ON teamid=id
 GROUP BY teamname

ゴール数は``goal``のidの数とする。

#### 10
スタジアム``stadium``の名前と、そのスタジアムでのゴール数を各スタジアムごとに表示する。

SELECT stadium, COUNT(matchid)
 FROM goal JOIN game ON matchid=id
 GROUP BY stadium

#### 11
'ポーランド（``POL``）が参戦している全試合の ``matchid`` と 日程 とその試合のゴール数 を表示する。

SELECT id,mdate,COUNT(matchid)
 FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
 GROUP BY id

#### 12
ドイツ``'GER'``が得点した試合の`` matchid ``と 日程 と ドイツの得点 を表示する。

SELECT id,mdate,COUNT(matchid)
 FROM game JOIN goal ON matchid = id 
 WHERE teamid = 'GER'
 GROUP BY id

#### 13
下に示す様に、各試合ごとに各チームの得点を表示する。 この問題は、ここまでまだ未解説のSQL構文``CASE WHEN``を使用する。

select
 mdate,
 team1,
 sum(case when teamid=team1 then 1 else 0 end) score1,
 team2,
 sum(case when teamid=team2 then 1 else 0 end) score2
from
 game
join
 goal
  on id=matchid
group by
 mdate,team1,team2
order by
 mdate, matchid

``CASE WHEN``で場合分け、``SUM``でスコアをカウント、``join``で``table``合わせて、取り出し。

## 今後
SQLは来週で終わり、PHPに入りたい。