# 2020/07/08 upper layer progress
## SQL勉強会
今回は、SQL zoo(https://sqlzoo.net/) にて[Using Null]のから、[Self join]まで。
### Using Null
#### 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
#### 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番目のバスの会社を表示。
わかりませんでした。
Sign in with Wallet
Connect another wallet