# 非エンジニアのための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
```
Sign in with Wallet
Connect another wallet