---
# System prepended metadata

title: 2020/6/29 高レイヤー勉強会

---

# 2020/6/29 高レイヤー勉強会 
## SQL勉強会
[SQL zoo]
https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
***SELECT_within_SELECT_Tutorial***の7～１０。
***Nested SELECT Quiz***
https://sqlzoo.net/wiki/SUM_and_COUNT の***SUM and COUNT***の１～８。

### h3 SELECT_within_SELECT_Tutorialの解答
>7
>SELECT continent, name, population FROM world x
  WHERE population >= ALL
    (SELECT population FROM world y
        WHERE y.continent=x.continent
          AND population>0)

ALL　(SELECT　・・・)でリストから特定の要素のみを比較したりできる。

>8
>select
 continent, min(name) 
from
 world
group by
 continent;
 
GROUP BY グループ化する要素名
で指定した要素でグループ化を行える。
表示は、指定したグループの中で集計した結果が表示される。
 
>9
>SELECT
 name,continent,population
FROM
 world A
WHERE
 25000000>=ALL(SELECT population
               FROM world B 
               WHERE B.continent=A.continent);

>10
>select
 name, continent 
from
 world A
where
 population>all(select 3*population
               from world B
               where B.continent= A.continent
                and B.name <> A.name);
                
### h3　SUM and COUNT　解答
>1
>select
 sum(population)
from
 world;
sum(カラム名)

でカラムの数値を合計した値を表示
 
>2
>select
 distinct(continent)
from
 world;
 
distinct(カラム名)
で重複無しでカラムを表示

>3
>select
 sum(gdp)
from
 world
where
 continent = 'Africa';
 
>4
>select
 sum(gdp)
from
 world
where
 continent = 'Africa';
 
>5
>select
 sum(population)
from
 world
where
 name in ('Estonia', 'Latvia', 'Lithuania');
 
>6
>select
 continent, count(name)
from
 world
group by
 continent;
 
count(カラム名)
で
 
>7
>select
 continent, count(name)
from
 world
where
 population >= 10000000
group by
 continent;
 
>8
>select
 continent
from
 world
group by
 continent
having
 sum(population) >= 100000000;
 
### h3今回学習したSQLの語法
#### h4ALL

#### h4 GROUP BY

#### h4ALL
#### h4ALL
#### h4ALL
#### h4ALL
#### h4ALL
#### h4ALL

