---
# System prepended metadata

title: 2020/07/08 upper layer progress

---

# 2020/07/08 upper layer progress


## SQL勉強会
今回は、SQL zoo(https://sqlzoo.net/) にて[Using Null]のから、[Self join]まで。

### Using Null

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





#### 1　部門にNULLがある教師をリストします。

```sql
select
 name
from
 teacher
where
 dept is null;
```
nullを使うときは＝ではなくisを使う。


#### 2　INNER JOINは、学部のない教師と教師のない学部を見落としていることに注意。
```sql
select
 teacher.name Teacher, dept.name Dept
from
 teacher 
inner join
 dept
 on (teacher.dept=dept.id);
```
inner join はそれぞれのテーブルの指定したカラムの値が一致するものだけを結合。

#### 3　別のJOINを使用して、すべての教師がリストされるようにする。

```sql
select　t.name Teacher, d.name Dept
from　teacher 
left join　dept on t.dept=d.id
```
left joinは左のテーブルにもう1つのテーブルを結合する。


#### 4　別のJOINを使用して、すべての部門がリストされるようにする。
```sql
SELECT teacher.name, dept.name
FROM teacher 
RIGHT JOIN dept ON (teacher.dept=dept.id)

```

3と同様に、右のテーブルにもう1つのテーブルを結合。

#### 5 COALESCEを使用して携帯電話番号を印刷する。番号が指定されていない場合は、「07986 444 2266」を使用。 教師の名前と携帯番号を表示するか、「07986 444 2266」を表示。
```sql
SELECT name, COALESCE(mobile,'07986 444 2266')
FROM teacher

  ```
  COALESCE関数でmobileのNullに07986-444-2266をいれる。
 
  
#### 6　COALESCE関数とLEFT JOINを使用して、教師名と部門名を印刷。部門がない場合は、文字列「None」を使用。

```sql
SELECT teacher.name, COALESCE(dept.name,'None')
FROM teacher 
LEFT JOIN dept　ON teacher.dept=dept.id
``` 
  
#### 7　COUNTを使用して、教師の数と携帯電話の数を表示します。

```sql
SELECT COUNT(teacher.name), COUNT(mobile)
FROM teacher
``` 
  
#### 8　COUNTおよびGROUP BY dept.nameを使用して、各部門とスタッフの数を表示。RIGHT JOINを使用して、エンジニアリング部門がリストされていることを確認。

```sql
SELECT dept.name, COUNT(teacher.name)
FROM teacher RIGHT JOIN dept
ON teacher.dept=dept.id
GROUP BY dept.name
``` 
GROUP BYでカラム名の同じデータをグループとしてまとめる。

#### 9　CASEを使用して各教師の名前を表示し、教師が部門 1または2 の場合は「Sci」、それ以外の場合は「Art」を表示。


```sql
SELECT name, CASE WHEN dept IN (1,2) 
THEN 'Sci'
ELSE 'Art' END
FROM teacher
``` 
```sql
  CASE WHEN条件1 THEN値1 
       WHEN条件2 THEN値2  
       ELSE def_value 
```
CASEを使用することで様々な条件下で様々な値を返せる。

#### 10　CASEを使用して、各教師の名前を表示。教師が部門1または2の場合は「Sci」が続き、教師の部門が3の場合は「Art」、それ以外の場合は「None」を表示。

```sql
SELECT name, CASE WHEN dept IN (1,2) 
THEN 'Sci'
WHEN dept = 3 
THEN 'Art'
ELSE 'None' END
FROM teacher
``` 

### Self join

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






#### 1 バス停の数は？

```sql
SELECT COUNT(*) 
FROM stops
```
”＊”をつかうと全てのレコード数を取得できる。


#### 2　'Craiglockhart'のidは？
```sql
SELECT id 
FROM stops 
WHERE name='Craiglockhart'
```


#### 3　LRTサービスの4番目の停車地とidは？

```sql
SELECT id, name FROM stops, route
  WHERE id=stop
    AND company='LRT'
    AND num='4'
```



#### 4　ロンドンロード(149)とグレイググロックハート(53)を通るルートで停車地が２つのルートを通る物ものを出力。
```sql
SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2

```

HAVINGはWHEREと同様に抽出条件を設定できる。
[HAVINGとWHEREの違い](https://www.sejuku.net/blog/73003)

#### 5 CraiglockhartからLondon Roadまでのサービスを表示します。
```sql
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop = 53 AND b.stop=149


  ```

 
  
#### 6　示されたクエリを変更して「Craiglockhart」と「London Road」の間のサービスが表示されるようにする。

```sql
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart'
  AND stopb.name='London Road'

``` 
  
#### 7　115と137の停留所を結ぶすべてのサービスのリストをだす。（「ヘイマーケット」と「リース」）

```sql
SELECT DISTINCT R1.company, R1.num
  FROM route R1, route R2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=115 AND R2.stop=137

``` 
  DISTINCTはGROUP BYと同様に重複排除ができるが、表示したい列のみ表示できる。
  
#### 8　停車駅「クレイグロックハート」と「トールクロス」 を結ぶサービスのリストをだす。

```sql
SELECT R1.company, R1.num
  FROM route R1, route R2, stops S1, stops S2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=S1.id AND R2.stop=S2.id
    AND S1.name='Craiglockhart'
    AND S2.name='Tollcross'
``` 


#### 9　LRT会社が提供する「クレイグロックハート」自体を含む1つのバスに乗ることで、「クレイグロックハート」から到達できる停車地の明確なリストを示す。関連会社とバス番号を含む。


```sql
SELECT DISTINCT S2.name, R2.company, R2.num
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Craiglockhart'
  AND S1.id=R1.stop
  AND R1.company=R2.company AND R1.num=R2.num
  AND R2.stop=S2.id
```

#### 10　2台のバスがいくことのできるCraiglockhartにLOCHENDを含むルート検索。バス番号、最初のバスの会社、乗り換えの停車地の名前、バス番号と2番目のバスの会社を表示。
わかりませんでした。

