プログラミング

4. SELECT within SELECT/SQL ZOO の答えと解説

sql_zoo_4 select within select

SQLZooの答えと解説です。

この章では 主にSELECT句について学習していきます

今までの解説はこちら

5. SUM and COUNT/SQL Zooの答えと解説
6. JOIN /SQL ZOOの答えと解説
7. More JOIN operations /SQL ZOOの答えと解説
8. Using NUll/SQL Zooの答えと解説

1. ロシア(Russia)よりも人口(population)が多い国の名前を表示する

Q. List each country name where the population is larger than that of ‘Russia’. world(name, continent, area, population, gdp)

SELECT name FROM world
WHERE population >(
 SELECT population FROM world
 WHERE name = 'Russia'
);

2. 国民一人当たりの国内総生産がイギリス ‘United Kingdom’ よりも大きなヨーロッパの国を表示する。

Q. Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.

 

サブクエリ内で’United Kingdom’の 国民総生産(gdp/population)を計算させる。

SELECT name FROM world
WHERE continent='Europe'
AND gdp/population>(
 SELECT gdp/population
 FROM world 
 WHERE name= 'United Kingdom'
)

3. ‘Argentina’または’Australia’を含む大陸にある国の、国名と大陸を表示する。国名順に表示する。

Q. List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

SELECT name,continent 
FROM world
WHERE continent  = 'South America' or continent= 'Oceania'
ORDER BY name

4. W人口がカナダ Canadaよりも多く、ポーランドPolandよりも少ない国の、国名と人口を表示する

Which country has a population that is more than Canada but less than Poland? Show the name and the population.

一度、カナダとポーランドの人口を出しておくとわかりやすい

select name,population from world
where name = 'Canada' or name= 'Poland'
# name	population
# Canada	38007166
# Poland	38379000
# これをWHERE句の中に入れていく
SELECT name,population FROM world
WHERE population>(
 SELECT population FROM world 
 WHERE name = 'Canada'
)AND population<(
 SELECT population FROM world 
 WHERE name = 'Poland'
)

5. ヨーロッパの各国について 国名と人口を表示する。ドイツ人口の何%かで表示する

Q. Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

ドイツは(人口 80000000人)とヨーロッパで最大の人口の国である。 オーストリア(人口8500000人)はドイツの人口の11%である。

ALLで全ての要素を適用させる

キーワード `ALL` で大小記号 >= > < <= をリストの全ての要素に対して適用できる。

例えば、人口が最大の国を見つけるクエリーは:

SELECT name FROM world 
WHERE population >= ALL(
 SELECT population FROM world 
 WHERE population>0
)

サブクエリー中の条件式 population>0 は
一部の国の人口が null である場合に必要な処理である。

答え

CONCAT句で%を結合させ追加。
小数点以下はROUNDで丸め込み

SELECT name,concat(
 ROUND(
 (population/(SELECT population FROM world 
  WHERE name = 'Germany'))*100,0
 )
,'%')
from world
where continent ='Europe'

6. ヨーロッパのどの国のGDPよりも大きなGDPを持つ国の国名だけを表示する。(GDPがNULL の国も有る)

Q. Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

外側の SELECT 文の値を、内側の SELECT で参照できる。 内と外のテーブルを、各テーブルに名前を付けて区別する。

# Europe全体のgdpのTop
SELECT name,gdp 
FROM world
wHERE gdp >= ALL(
 SELECT gdp FROM world WHERE gdp>0
)
AND continent ='Europe'
# 上を使用して
SELECT name FROM world
WHERE gdp >(
 SELECT gdp From world
 WHERE gdp >= ALL(
  SELECT gdp FROM world
  WHERE gdp>0 
  AND continent ='Europe'
 )
and continent ='Europe'
)

7.各大陸のもっとも大きな国(面積で)の大陸、国名、面積を表示する。

Q. Find the largest country (by area) in each continent, show the continent, the name and the area:

自己相関サブクエリーについて

外側のクエリの値をサブクエリ内で使用できる。

SQLがややこしくなり、処理速度も遅いが 基本形を抑えるとだいぶ理解が進む。
今回の同じ大陸内での比較や 同じ所属部署間での比較などで使用。

# 大陸が同じ各国の人口と比べてより大きいか等しいような人口を持つ国をworldから選ぶ
SELECT continent,name,population FROM world x
  WHERE population >= ALL
    (SELECT population FROM world y
        WHERE y.continent=x.continent
          AND population>0
    )

https://qiita.com/aki3061/items/736abd6ea883ba647586

答え

Q. 各大陸のもっとも大きな国(面積で)の大陸、国名、面積を表示する。

Q. Find the largest country (by area) in each continent, show the continent, the name and the area:

ALL以下では大陸が同じ面積の大陸全てを指定しており その面積をWHERE area >= ALLで比較しているため、 最も大きな面積をもつ国が大陸ごとに出力される

SELECT 
continent, name, area 
FROM world x
WHERE area >= ALL(SELECT area FROM world y
 WHERE y.continent=x.continent
 AND population>0)

8. 各大陸の中でアルファベット順で先頭になる国の大陸と国名を表示する。

Q. List each continent and the name of the country that comes first alphabetically.

集計関数とGROUP BY

GROUP BY を使用する時は、 欲しいデータが「単一のデータ」だけではなく、 「複数のデータを集計した計算結果」も含まれている時、集計関数を使用して、制限する必要がある。

集計関数の例

数を返すCOUNT()、最大値と最小値を返す MAX(), MIN()、合計を返す SUM() 平均を返す AVG() など

注意点として、 Groupbyした結果1つのカラムに複数の物が入る場合は、 集計関数で選択させる必要がある。(COUNTやMINを使用)

https://qiita.com/Go-Noji/items/5baeb790ade4b57126ff  

答え

SELECT continent,min(name)
FROM world
Group By continent

9. 【難問】大陸に属する各国の人口が25000000以下である大陸を見つけ、それらの大陸に属する国の名前と大陸と人口を表示。

Q. Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

SELECT continent,min(name)
FROM world
Group By continent

10. 同じ大陸にある他の全ての国よりも3倍は大きな人口の国の、国名と大陸を表示する。

Q. Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

SELECT name, continent 
From world x
Where population > ALL(
 SELECT 3*population
 FROM world y
 WHERE y.continent=x.continent
 and B.name <> A.name
)

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です