---
# System prepended metadata

title: 2020/07/06 upper layer progress

---

# 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

![](https://i.imgur.com/BhjFXXU.png)




#### 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
``` 

