# 2020/07/06 upper layer progress
## SQL勉強会
今回は、SQL zoo(https://sqlzoo.net/) にて[JOIN]のquizから、
[MORE JOIN operations]のquizまで取り掛かった。
以下問題と答え
- [JOIN quiz](#JOIN-quiz)
- [1. プレーヤーの”ディミトリスサルピンギディス”が得点したスタジアムを見つけるための使用する結合条件を選択せよ。](#1-プレーヤーの”ディミトリスサルピンギディス”が得点したスタジアムを見つけるための使用する結合条件を選択せよ。)
- [2. SQLステートメントでテーブルのゴールとチームを結合する。SELECT行で使用できる列名のリストを列挙せよ。](#2-SQLステートメントでテーブルのゴールとチームを結合する。SELECT行で使用できる列名のリストを列挙せよ。)
- [3. プレーヤーとチーム、ギリシャ（GRE）のゴール数を示すコードを選べ。](#3-プレーヤーとチーム、ギリシャ（GRE）のゴール数を示すコードを選べ。)
- [4. 次のコードの実行結果を選べ](#4-次のコードの実行結果を選べ)
- [5. ワルシャワのナショナルスタジアムでポーランド（POL）と対戦した選手とそのチームを示すコードを選べ。](#5-ワルシャワのナショナルスタジアムでポーランド（POL）と対戦した選手とそのチームを示すコードを選べ。)
- [6. Stadion Miejski（ヴロツワフ）でプレーしたが、イタリア（ITA）でプレーしたことのないプレーヤーについて、プレーヤー、チーム、および得点を示すコードを選べ。](#6-Stadion Miejski（ヴロツワフ）でプレーしたが、イタリア（ITA）でプレーしたことのないプレーヤーについて、プレーヤー、チーム、および得点を示すコードを選べ。)
- [7. 次のコードの実行結果を選べ](#7-次のコードの実行結果を選べ。)
### JOIN quiz
**game**
| id | mdate | stadium | team1 | team2 |
|-------------|--------------|:-----------------:|-------|-------|
| 1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
| 1002 | 8 June 2012 | Stadion Miejski (Wroclaw)| RUS | CZE |
| 1003 | 12 June 2012| Stadion Miejski (Wroclaw)| GRE | CZE |
| 1004 | 12 June 2012| National Stadium, Warsaw | POL | RUS |
|... | | | | |
**goal**
| id |teamid| player | gtime |
|-------|------|:-------------------:|-------|
| 1001 | POL | Robert Lewandowski | 17 |
| 1002 | GRE | Dimitris Salpingidis| 51 |
| 1003 | RUS | Alan Dzagoev | 15 |
| 1004 | RUS | Roman Pavlyuchenko | 82 |
|... | | | |
**eteam**
| id | teamname | coach |
|------|---------------|:----------------:|
| POL | Poland | Franciszek Smuda |
| RUS | Russia | Dick Advocaat |
| CZE | Czech Republic| Michal Bilek |
| GRE | Greece | Fernando Santos |
|... | | |
#### 1. プレーヤーの”ディミトリスサルピンギディス”が得点したスタジアムを見つけるための使用する結合条件を選択せよ。
```sql
game JOIN goal ON (id=matchid)
```
idとmatchidを同一としてgameとgoalを結合
#### 2. SQLステートメントでテーブルのゴールとチームを結合する。SELECT行で使用できる列名のリストを列挙せよ。
```sql
matchid 、 teamid 、 player 、 gtime 、 id 、 teamname 、 coach
```
goalとeteamのテーブルにある列名全てをあげれば良い。
#### 3. プレーヤーとチーム、ギリシャ（GRE）のゴール数を示すコードを選べ。
```sql
SELECT player, teamid, COUNT(*)
FROM game JOIN goal ON matchid = id
WHERE (team1 = "GRE" OR team2 = "GRE")
AND teamid != 'GRE'
GROUP BY player, teamid
```
プレイヤーとチームの列を持つテーブルをJOINで作り、そのテーブルから条件を指定してCOUNT関数を使いゴール数を数える。
#### 4. 次のコードの実行結果を選べ:
```sql
SELECT DISTINCT teamid 、 mdate
FROM goal JOIN game on （matchid = id ）
WHERE mdate = '9 June 2012'
```
| DEN | 9 June 2012 |
|------|---------------|
| GER | 9 June 2012 |
#### 5. ワルシャワのナショナルスタジアムでポーランド（POL）と対戦した選手とそのチームを示すコードを選べ。
```sql
SELECT DISTINCT player, teamid
FROM game JOIN goal ON matchid = id
WHERE stadium = 'National Stadium, Warsaw'
AND (team1 = 'POL' OR team2 = 'POL')
AND teamid != 'POL'
```
[3](#3-プレーヤーとチーム、ギリシャ（GRE）のゴール数を示すコードを選べ。)と同じく条件を指定してテーブルを結合しSELECT
#### 6. Stadion Miejski（ヴロツワフ）でプレーしたが、イタリア（ITA）でプレーしたことのないプレーヤーについて、プレーヤー、チーム、および得点を示すコードを選べ。
```sql
SELECT DISTINCT player, teamid, gtime
FROM game JOIN goal ON matchid = id
WHERE stadium = 'Stadion Miejski (Wroclaw)'
AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))
```
[3](#3-プレーヤーとチーム、ギリシャ（GRE）のゴール数を示すコードを選べ。)と同じく条件を指定してテーブルを結合しSELECT
#### 7. 次のコードの実行結果を選べ。
```sql
SELECT teamname , COUNT （* ）
FROM eteamが JOIN goal ON teamid = id
GROUP BY teamname
HAVING COUNT （* ） < 3
```
| Netherlands | 2 |
| ----------------- | --- |
| Poland | 2 |
| Republic of Ireland | 1 |
| Ukraine | 2 |
teamnameとCOUNT関数による数字があり、カウント数が３未満のものを選ぶ。
### MORE JOIN operations
#### 1 1962 年の映画のリストを表示（ id と title を表示）
```sql
select id, title
from movie
where yr=1962;
```
#### 2 Citizen Kane'の年を表示
```sql
select yr
from movie
where title = 'Citizen Kane';
```
#### 3 スタートレック（'Star Trek'）というシリーズ映画のリストを表示（ id title yr ）。 年の順に掲載。
```sql
select id, title, yr
from movie
where title like '%Star Trek%'
order by yr;
```
order byで年の順に表示
#### 4 女優'Glenn Close'のidを表示
```sql
select id
from actor
where name='Glenn Close' ;
```
#### 5 映画'Casablanca'のidを表示
```sql
select id
from movie
where title= 'Casablanca' ;
```
#### 6 映画'Casablanca'の出演リスト(name)を出力する。
```sql
SELECT name
FROM casting, actor
WHERE movieid=(SELECT id
FROM movie
WHERE title='Casablanca')
AND actorid=actor.id
```
#### 7 映画「エイリアン」'Alien'の出演者リストを表示。
```sql
SELECT name
FROM movie, casting, actor
WHERE title='Alien'
AND movieid=movie.id
AND actorid=actor.id
```
#### 8 'Harrison Ford' ハリソン＝フォードが出演した映画のリストを表示する。
```sql
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
```
#### 9 'Harrison Ford'ハリソン＝フォードが出演した映画で、彼が主演していない(ord <> 1） のリストを表示。
```sql
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
AND ord<>1
```
ord は、映画の出演リスト順、1 が主演を意味する。
#### 10 1962年の全映画を、そのタイトルと主演と併記してリスト表示。
```sql
SELECT title, name
FROM movie, casting, actor
WHERE yr=1962
AND movieid=movie.id
AND actorid=actor.id
AND ord=1
```
Sign in with Wallet
Connect another wallet