---
# System prepended metadata

title: 非エンジニアのためのSQL 演習問題

---

# 非エンジニアのためのSQL 演習問題

## これは

『非エンジニアのためのSQL』
https://hackmd.io/@Ikazuchi/HkVpqeowr
の演習問題集です。

## もくじ

[TOC]

## hackerrankについて

この文書では、演習問題として
https://www.hackerrank.com/
の問題を用いる。
そのため、事前にアカウント登録を行っておくとスムーズである。
Sign Upにgoogleアカウントを指定すると即座にアカウント登録できるが、言語を選ばされいきなり普通の問題に入ってしまうのでそのあたりでページを閉じ、下記演習問題のアドレスにアクセスするとよい。

本資料は基本的にMySQLに準拠するため、処理系はMySQLを選択すること。

## 演習問題

### select句とfrom句

#### Select All

https://www.hackerrank.com/challenges/select-all-sql/problem

**CITY**テーブルの全ての行、全てのカラムを出力するクエリを書け。

### where句

#### Select By ID

https://www.hackerrank.com/challenges/select-by-id/problem

`id`が1661のレコードの全てのカラムを出力せよ。

#### Japanese Cities Attributes

https://www.hackerrank.com/challenges/japanese-cities-attributes/problem

日本の都市の全てのカラムを出力せよ。ただし、日本の国コードは`JPN`である。

#### Weather Observation Station 3

https://www.hackerrank.com/challenges/weather-observation-station-3/problem

IDが偶数の都市の名前の一覧を出力せよ。ただし、出力は重複しないようにすること。

#### Revising the Select Query II

https://www.hackerrank.com/challenges/revising-the-select-query-2/problem

人口が12万を超えるようなアメリカの都市名の一覧を出力せよ。ただし、アメリカの国コードは`USA`である。

### 集計関数

#### Revising Aggregations - The Sum Function

https://www.hackerrank.com/challenges/revising-aggregations-sum/problem

地区がカリフォルニアの都市の人口の合計を出力せよ。

#### Revising Aggregations - The Count Function

https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

人口が10万を超える都市の数を求めよ。

#### Revising Aggregations - Averages

https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

地区がカリフォルニアの都市の人口の平均を出力せよ。

#### Population Density Difference

https://www.hackerrank.com/challenges/population-density-difference/problem

人口が最大の都市の人口と、最小の都市の人口の差を求めよ。

#### Weather Observation Station 4

https://www.hackerrank.com/challenges/weather-observation-station-4/problem

**STATION**テーブルに含まれる都市の数をNとし、同じく互いに異なる都市の数をN'としたときに、N-N'を求めよ。

### group by句 + having句

HackerRankに`group by`を用いた練習問題がないので、シンプルな例を出す。

#### #1

以下のテーブルが与えられたとき、ユーザごとのスコアの合計を求めるクエリを書け。

**scores**
|カラム名|データの例|
|-|-|
|user_id|12345|
|score|12345|
|date|2018-01-01|


#### #2

**scores**テーブルが与えられたとき、日付ごとのスコアの合計が30より大きいような日付とそのスアの合計を求めるクエリを書け。

#### #3

**scores**テーブルが与えられたとき、日付ごとのスコアの合計が30より大きいような日付を求めるクエリを書け。

### テーブルの結合

#### African Cities

https://www.hackerrank.com/challenges/african-cities/problem

**CITY**テーブルと**COUNTRY**テーブルが与えられる。
アフリカ大陸の国の都市の一覧を出力せよ。

#### Asian Population

https://www.hackerrank.com/challenges/asian-population/problem

アジア大陸のすべての都市の人口の和を出力せよ。

#### Average Population of Each Continent

https://www.hackerrank.com/challenges/average-population-of-each-continent/problem

大陸名と、大陸ごとの都市の人口の平均（小数点以下切り捨て）を求めよ。
ただし、`x`の小数点切り捨ては`truncate(x, 0)`と書く。

#### Top Competitors

https://www.hackerrank.com/challenges/full-score/problem

Juliaはコーディングテストの開催を終え、その順位表を作ろうとしており、あなたの助けが必要である。
複数の問題で満点を取ったハッカーの`hacker_id`と名前を求めるクエリを書け。
ただし、出力は満点を取った問題が多い参加者から順番に並べ、同じ場合は`hacker_id`が若い順に並べよ。

##### 入力

**Hackers**
`hacker_id`はハッカーのid、`name`はハッカーの名前である。

**Difficulty**
`difficult_level`は問題の難易度で、`score`はその難易度の問題の満点である。

**Challenges**
`challenge_id`は問題のid、`hacker_id`は問題作成者のhacker_id、`difficulty_level`は難易度である。

**Submissions**
`submission_id`は提出のid、`hacker_id`は提出者のhacker_id、`challenge_id`は提出した問題のid、`score`はその提出で得られた得点である。

#### New Companies

https://www.hackerrank.com/challenges/the-company/problem

アンバーの経営する大企業は、ちょうど何社かの新しい会社を買収したところである。
各企業は、次のような階層構造になっている。

```
創業者→リードマネージャ→シニアマネージャ→マネージャ→労働者
```

以下の入力を用いて、各会社のcompany_codeと創業者の名前、リードマネージャの数、シニアマネージャの数、マネージャの数、労働者の数を求めるクエリを書け。ただし、出力はcompany_codeの昇順とする。

##### 注意事項

- 各テーブルには、重複したレコードが含まれる可能性がある。
- company_codeは文字列であるから、`C_1`、`C_2`、`C_10`が与えられた場合その昇順とは、`C_1`、`C_10`、`C_2`の順である。

##### 入力

**Company**
`company_code`は会社ごとに振られているコード、`founder`は創業者の名前である。

**Lead_Manager**
`lead_manager_code`はリードマネージャのコード、`company_code`はそのリードマネージャが勤めている会社のコードである。

**Senior_Manager**
`senior_manager_code`はシニアマネージャのコード、`lead_manager_code`はそのシニアマネージャの上司のリードマネージャのコード、`company_code`はそのシニアマネージャが勤めている会社のコードである。

**Manager**
`manager_code`はマネージャのコード、`senior_manager_code`はそのマネージャの上司のシニアマネージャのコード、`lead_manager_code`はそのマネージャの上司のリードマネージャのコード、`company_code`はそのマネージャが勤めている会社のコードである。

**Employee**
`employee_code`はその労働者のコード、`manager_code`はその労働者の上司のマネージャのコード、`senior_manager_code`はその労働者の上司のシニアマネージャのコード、`lead_manager_code`はその労働者の上司のリードマネージャのコード、`company_code`はその労働者が勤めている会社のコードである。

### サブクエリ

#### Contest Leaderboard

https://www.hackerrank.com/challenges/contest-leaderboard/problem

前回のコンテストで素晴らしい仕事をしたあなたは、Juliaに次の仕事をしてほしいと言われた。
ハッカーの合計スコアは、それぞれの問題に対する最高得点の和である。
hacker_id、ハッカーの名前、合計スコアを合計スコアの降順で求めるクエリを書け。
ただし、複数のハッカーが同じ合計スコアを持つ場合、hacker_idの昇順で並べよ。
また、合計スコアが0のハッカーは取り除け。

##### 入力

**Hackers**
`hacker_id`はハッカーのid、`name`はハッカーの名前である。

**Submissions**
`submission_id`は提出のid、`hacker_id`は提出者のhacker_id、`challenge_id`は提出した問題のid、`score`はその提出で得られた得点である。

## 回答例

### select句とfrom句

#### Select All

```sql=
select
  *
from
  city
```

### where句

#### Select By ID

```sql=
select
  *
from
  city
where
  id = 1661
```

#### Japanese Cities Attributes

```sql=
select
  *
from
  city
where
  countrycode = 'JPN'
```

#### Weather Observation Station 3

```sql=
select distinct
  city
from
  station
where
  id % 2 = 0
```

#### Revising the Select Query II

```sql=
select
  name
from
  city
where
  population > 120000
  and countrycode = 'USA'
```

### 集計関数

#### Revising Aggregations - The Sum Function

```sql=
select
  sum(population)
from
  city
where
  district = 'California'
```

#### Revising Aggregations - The Count Function

```sql=
select
  count(*)
from
  city
where
  population > 100000
```

#### Revising Aggregations - Averages

```sql=
select
  avg(population)
from
  city
where
  district = 'California'
```

#### Population Density Difference

```sql=
select
  max(population) - min(population)
from
  city
```

#### Weather Observation Station 4

```sql=
select
  count(city) - count(distinct city)
from
  station
```

### group by句 + having句

#### #1
```sql=
select
  user_id
  , sum(score) as s
from
  scores
group by
  user_id
```

#### #2

```sql=
select
  date
  , sum(score) as s
from
  scores
group by
  date
having
  s > 30
```

#### #3

```sql=
select
  date
from
  scores
group by
  date
having
  sum(score) > 30
```

### テーブルの結合

#### African Cities

```sql=
select
  city.name
from
  city
  inner join country
    on city.countrycode = country.code
where
  country.continent = 'Africa'
```


#### Asian Population

```sql=
select
  sum(ci.population)
from
  city as ci
  inner join country as co
    on ci.countrycode = co.code
where
  co.continent = 'Asia'
```

#### Average Population of Each Continent

```sql=
select
  co.continent
  , truncate(avg(ci.population), 0)
from
  city as ci
  inner join country as co
    on ci.countrycode = co.code
group by
  co.continent
```

#### Top Competitors

```sql=
select
  h.hacker_id
  , h.name
from
  hackers as h
  inner join submissions as s
    using(hacker_id)
  inner join challenges as c
    using(challenge_id)
  inner join difficulty as d
    using(difficulty_level)
where
  d.score = s.score
group by
  h.hacker_id
  , h.name
having
  count(distinct s.challenge_id) > 1
order by
  count(distinct s.challenge_id) desc
  , h.hacker_id
```

#### New Companies

```sql=
select
  c.company_code
  , c.founder
  , count(distinct l.lead_manager_code)
  , count(distinct s.senior_manager_code)
  , count(distinct m.manager_code)
  , count(distinct e.employee_code)
from
  company as c
  left join lead_manager as l
    on c.company_code = l.company_code
  left join senior_manager as s
    on c.company_code = s.company_code
  left join manager as m
    on c.company_code = m.company_code
  left join employee as e
    on c.company_code = e.company_code
group by
  c.company_code
  , c.founder
order by
  c.company_code
```

### サブクエリ

#### Contest Leaderboard

```sql=
select
  hacker_id
  , name
  , sum(max_score) as sum_score
from
  (
    select
      h.hacker_id
      , h.name
      , s.challenge_id
      , max(score) as max_score
    from
      hackers as h
      inner join submissions as s
        on h.hacker_id = s.hacker_id
    group by
      h.hacker_id
      , h.name
      , s.challenge_id
  ) as a
group by
  hacker_id
  , name
having
  sum_score != 0
order by
  sum_score desc
  , hacker_id
```