MySQL8版MySQL10本ノック

mysql-10-knock-in-mysql8.md

MySQL8版 MySQL10本ノック

概要

MySQL8でMySQL10本ノックを解いてみる | Qiita

1問目

SELECT * FROM city WHERE CountryCode = 'JPN';

2問目

SELECT
  SUM(POPULATION) AS 合計, 
  AVG(POPULATION) AS 平均, 
  MIN(POPULATION) AS 最小値, 
  MAX(POPULATION) AS 最大値
FROM 
  city
WHERE
  CountryCode = 'JPN'
GROUP BY 
  CountryCode
ORDER BY
  CountryCode
DESC
;

3問目

SELECT DISTINCT
  ID,
  NAME,
  CountryCode,
  AVG(Population) AS AvgPopulation
FROM
  city c
GROUP BY
  ID,
  NAME,
  CountryCode
ORDER BY 
  AvgPopulation 
DESC
LIMIT 10
;

4問目

SELECT
  country.Name as country_name, 
  country.Continent as country_continent,
  city.Name as city_name
FROM
  country
INNER JOIN
  city
ON
  country.Code = city.CountryCode
LIMIT 
  100
;

5問目

WITH t1 as (
  SELECT 
    CountryCode,
    AVG(Population) AS AvgPopulation
  FROM
    city
  GROUP BY
    CountryCode
), 
t2 as (
  SELECT
    Code,
    Name,
    Continent
  FROM
    country
)
SELECT
  t2.Name,
  t2.Continent,
  t1.AvgPopulation
FROM
  t1
INNER JOIN
  t2
ON
  t1.CountryCode = t2.Code
ORDER BY
  t1.AvgPopulation
DESC
;

6問目

country.GNPOld列がNULLだった場合について指定がなかったので、COALESCEを普通に使ってる

WITH 
  t1 as (
    SELECT 
      CountryCode,
      AVG(Population) AS AvgPopulation
    FROM
      city
    GROUP BY
      CountryCode
  ),
  t2 as (
    SELECT
      Code,
      Name,
      Continent,
      CASE
        WHEN GNP > COALESCE(GNPOld, 0) THEN GNP
        ELSE COALESCE(GNPOld, 0)
      END
      AS GNP_big
    FROM
      country
  )
SELECT
  t2.Name,
  t2.Continent,
  t1.AvgPopulation,
  t2.GNP_big
FROM
  t1
INNER JOIN
  t2
ON
  t1.CountryCode = t2.Code
ORDER BY
  t1.AvgPopulation
DESC
;

7問目

これで許してほしい

WITH 
  t1 as (
    SELECT 
      CountryCode,
      AVG(Population) AS AvgPopulation
    FROM
      city
    GROUP BY
      CountryCode
  ),
  t2 as (
    SELECT
      Code,
      Name,
      Continent,
      CASE
        WHEN GNP > COALESCE(GNPOld, 0) THEN GNP
        ELSE COALESCE(GNPOld, 0)
      END
      AS GNP_big
    FROM
      country
  ),
  t3 as (
    SELECT
      t2.Continent as continent_name,
      AVG(t2.GNP_big) as avg_gnp_big,
      MAX(t1.AvgPopulation) as max_avg_population
    FROM
      t1
    INNER JOIN
      t2
    ON
      t1.CountryCode = t2.Code
    GROUP BY
      t2.Continent
  )
SELECT
  *
FROM
  t3
WHERE 
  t3.avg_gnp_big >= 100000
AND
  t3.max_avg_population
;

8問目

T.B.D

9問目

問題文が曖昧なので...
10問目を参考にして、CountryCodeごとにPercentage列の値の高い順にランキングをつけている。

SELECT
  CountryCode,
  Language,
  isOfficial,
  Percentage,
  RANK() OVER (
    PARTITION BY
      CountryCode
    ORDER BY
      Percentage
    DESC
  )
FROM
  countrylanguage
;

10問目

country_with_idテーブルを使わずに普通にcountryテーブルで結合しちゃった

WITH t1 as (
  SELECT
    CountryCode,
    Language,
    isOfficial,
    Percentage,
    RANK ()
      OVER (
        PARTITION BY
          CountryCode
        ORDER BY
          Percentage
        DESC
      ) as PercentageRank
  FROM
    countrylanguage
),
t2 as (
  SELECT
    CountryCode,
    Language,
    isOfficial,
    Percentage,
    PercentageRank
  FROM
    t1
  WHERE
    PercentageRank = 1
),
t3 as (
  SELECT
    CountryCode,
    MAX(Population) MaxPopulation
  FROM
    city
  GROUP BY
    CountryCode
),
t4 as (
  SELECT
    *
  FROM
    country
)
SELECT
  t4.Name,
  t4.Continent,
  t2.Language,
  t2.Percentage,
  t3.MaxPopulation
FROM
  t4
INNER JOIN
  t2
ON
  t2.CountryCode = t4.Code
INNER JOIN
  t3
ON
  t3.CountryCode = t4,.Code
ORDER BY
  t2.Percentage
DESC,
  t3.MaxPopulation
DESC
;