■ありがたい、問題集
■使用テーブル
select * from countries;
select * from players;
select * from pairings;
select * from goals;
■解答 一覧
-------------- Q 08 - 02 （サブクエリ,副問い合わせ）
--- 問題：各ポジションごとの総得点を表示してください。
--- players, goals
select tt.ポジション, tt.ゴール数
from (select p.position as ポジション ,count(g.id) as ゴール数 from goals g join players p
on g.player_id = p.id
group by p."position") tt
order by tt.ゴール数 desc;
-------------- Q 08
--- 問題：各ポジションごとの総得点を表示してください。
--- players, goals
select p."position" as ポジション , count(g.id) as ゴール数 from players p
join goals g on g.player_id = p.id
group by p."position"
order by ゴール数 desc;
---------------------------- END
-------------- Q 06
--- 問題：すべての選手を対象として選手ごとの得点ランキングを表示してください。（SELECT句で副問合せを使うこと）
--- players , goals
select p.name, p.position, p.club,
(select count(id) from goals g where g.player_id = p.id) as 得点数
from players p
order by 得点数 desc;
---------------------------- END
-------------- Q 05 - 02 副問い合わせ(サブクエリ) 書き方
--- 問題：キックオフ日時と対戦国の国名をキックオフ日時の早いものから順に表示してください。
--pairings countries
select p.kickoff,
(select name from countries c where c.id = p.my_country_id) as 国名01,
(select name from countries c2 where c2.id = p.enemy_country_id) as 国名02
from pairings p
order by p.kickoff asc;
-------------- Q 05
--- 問題：キックオフ日時と対戦国の国名をキックオフ日時の早いものから順に表示してください。
--pairings countries
select p.kickoff, c.name as 国名01, c2.name as 国名02 from pairings p
join countries c on c.id = p.my_country_id
join countries c2 on c2.id = p.enemy_country_id
order by kickoff asc;
---------------------------- END
-------------- Q 04
--- 各国の平均身長を高い方から順に表示してください。ただし、FROM句はplayersテーブルとして、テーブル結合を使わず副問合せを用いてください。
select (
select c.name from countries c where c.id = p.country_id),
AVG(p.height) as 身長
from players p
group by p.country_id
order by 身長 desc;
-------------- Q 03
--- 各国の平均身長を高い方から順に表示してください。ただし、FROM句はcountriesテーブルとしてください。
select c.name as 国名,AVG(p.height) as 平均身長
from countries c
join players p on p.country_id = c.id
group by 国名
order by 平均身長 desc;
-------------- Q 02
--- 全ゴールキーパーの平均身長、平均体重を表示してください
select AVG(p.height) as 平均身長, AVG(p.weight) as 平均体重
from players p
group by p.position
having p.position = 'GK';
-------------- Q 01
--- 各グループの中でFIFAランクが最も高い国と低い国のランキング番号を表示してください。
select c.group_name , min(c.ranking), max(c.ranking)
from countries c
group by c.group_name
order by c.group_name;
-------------- Q 24
--- 問題：身長の高い選手6位～20位を抽出し、以下の項目を表示してください。
SELECT name, height, weight
FROM players
ORDER BY height DESC
LIMIT 15 OFFSET 4;
-------------- Q 23
--- 問題：身長の高い選手ベスト5を抽出し、以下の項目を表示してください。
select p.name, p.height,p.weight
from players p
order by p.height desc
limit 5;
-------------- Q 19
select extract(year from age(DATE '2014-06-13',players.birth)) as age,
count(players.id)
from players
group by age
order by age;
-- ********* 問21
------- 年齢ごとの選手数を表
-------5歳毎に合算して表示
/*
【ロジック 解説】
1: TIMESTAMPDIFF関数で年齢を算出→例1）19、例2）16
2: 上記の答えを5で割る→例1）3.8、例2）3.2
3: 小数点以下を切り捨てる→例1）3、例2）3
4: 5倍して年齢（5歳毎になっている）に戻す→例1）15、例2）15
*/
SELECT
ABS(FLOOR(EXTRACT(YEAR FROM AGE(BIRTH, '2014-06-13')) / 5) * 5) AS AGE_GROUP,
COUNT(ID) AS PLAYER_COUNT
FROM
PLAYERS
GROUP BY
AGE_GROUP
ORDER BY
AGE_GROUP;
----------------- ********* END 問21
-- ********* 問20
------- 年齢ごとの選手数を表
-------10歳毎に合算して表示
SELECT
ABS(FLOOR(EXTRACT(YEAR FROM AGE(BIRTH, '2014-06-13')) / 10) * 10) AS AGE_GROUP,
COUNT(ID) AS PLAYER_COUNT
FROM
PLAYERS
GROUP BY
AGE_GROUP
ORDER BY
AGE_GROUP;
-- ********* 問19
------- 年齢ごとの選手数を表示
------- 年齢はワールドカップ開催当時である2014-06-13を使って算出
SELECT
EXTRACT(YEAR FROM AGE(DATE '2014-06-13', P.BIRTH)) AS 年齢,
COUNT(P.ID) AS 人数
FROM
PLAYERS P
GROUP BY
年齢
ORDER BY
年齢 ASC;
------------ END
-- ********* 問18
------- ブラジル（my_country_id = 1）対クロアチア（enemy_country_id = 4）戦のキックオフ時間（現地時間）を表示
SELECT
P.KICKOFF,
P.KICKOFF - INTERVAL '12 hours' AS 現地時間
FROM
PAIRINGS P
WHERE
P.MY_COUNTRY_ID = 1
AND P.ENEMY_COUNTRY_ID = 4;
------------ END
---------------------- Q47 全ての選手のポジションの1文字目（GKであればG、FWであればF）を出力してください。
select id,country_id,uniform_num ,substring("position", 1, 1) as "ポジション 1文字目" from players;
---------------------- Q48 出場国の国名が長いものから順に出力してください。
select name, length(name) as LEN from countries order by LEN desc;
---------------------- Q64 【副問い合わせ】 全てのゴール時間と得点を上げたプレイヤー名を表示してください。
----------- オウンゴールは表示しないでください。ただし、結合は使わずに副問合せを用いてください。
select id, goal_time,
(select p.name from players p where p.id = g.player_id) as name
from goals g
where g.player_id is not null;
---------------------- Q65 【副問い合わせ】 全てのゴール時間と得点を上げたプレイヤー名を表示してください。
------------ オウンゴールは表示しないでください。ただし、副問合せは使わずに、結合を用いてください。
select id, goal_time, name
from goals g
left outer join
----------------------
CREATE TABLE ppp_users (
id SERIAL NOT NULL,
name VARCHAR(200),
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (id)
);
--------------------------- サッカー Q1 ---------------------------
--- 各グループの中でFIFAランクが最も高い国と低い国のランキング番号を表示してください。
--- グループ, ランキング最上位, ラインキング最下位
select * from countries;
select * from players;
select * from pairings;
select * from goals;
--- Q1 答え
select group_name, MAX(ranking) as ランキング最上位, MIN(ranking) as ランキング最下位 from countries
GROUP by group_name;
--------------------------- サッカー Q2 position ---------------------------
--- 全ゴールキーパーの平均身長、平均体重を表示してください
--- 平均身長, 平均体重
select AVG(height) as 平均身長, AVG(weight) as 平均体重
from players
where position = 'GK';
select AVG(height) as 平均身長, AVG(weight) as 平均体重
from players
where position like 'GK%';
--------------------------- サッカー Q3 position ---------------------------
---------- 各国の平均身長を高い方から順に表示してください。ただし、FROM句はcountriesテーブルとしてください。
--- 国名, 平均身長
select countries.name as 国名,AVG(players.height) as 平均身長
from players left outer join countries on players.country_id = countries.id
group by countries.name
order by 平均身長 DESC;
--------------------------- サッカー Q4 position 副問い合わせ サブクエリー ---------------------------
--- 各国の平均身長を高い方から順に表示してください。ただし、FROM句はplayersテーブルとして、
--- テーブル結合を使わず副問合せを用いてください。
select (select c.name from countries c where p.country_id = c.id) as 国名,
AVG(p.height) as 平均身長
from players p
GROUP by p.country_id
order by AVG(p.height) desc;
--------------------------- Q5----------------------------------------------------------
---サッカー Q4 キックオフ日時と対戦国の国名をキックオフ日時の
--早いものから順に表示してください。 ---------------------------
select p.kickoff as キックオフ日時, c.name as 国名1,
c2.name as 国名2
from pairings p
left outer join countries c on c.id = p.my_country_id
left outer join countries c2 on c2.id = p.enemy_country_id
order by キックオフ日時 asc;
------- ========== Q6 ポジション 毎に ゴール数を表示する。
select p.position as ポジション, count(g.pairing_id) as ゴール数 from players p
left outer join goals g on g.player_id = p.id
group by p.position
order by ゴール数 desc;
------- ========== Q7 すべての選手を対象として選手ごとの得点ランキングを表示してください。（テーブル結合を使うこと）
--- ★★★ group by の時は、 出したいカラムを全て記載 （Mysql 以外）
select p.name, p.position, p.club, count(g.player_id) as ゴール数
from players p
left outer join goals g on g.player_id = p.id
group by p.name, p.position, p.club
order by ゴール数 desc;
select * from countries;
select * from players;
select * from pairings;
select * from goals;
------- ========== Q8
select p.position as ポジション, count(g.id) as ゴール数
from players p
left outer join goals g on g.player_id = p.id
group by p.position
order by ゴール数 desc;
------ ========== Q9 2014-06-13 時点での年齢を表示
select birth, date_part('year', age('2014-06-13', birth)) as age, name, position
from players
order by age desc;
----- ========== Q 10 player_id の null を表示 IS null
select count(id) as オウンゴール from goals where player_id IS null
group by player_id;
------- ========== Q11 2014-6-13から2014-6-27までに行われていました。
select countries.group_name, count(goals.id)
from goals
left outer join pairings on pairings.id = goals.pairing_id
left outer join countries on countries.id = pairings.my_country_id
where pairings.kickoff between '2014-06-13 0:00:00' and '2014-06-27 23:59:59'
group by countries.group_name
order by countries.group_name asc;
------ ========== Q14 グループCの各対戦毎にゴール数を表示してください。
------------------ スペシャル Q1
--- 別別のテーブルにある 国ごとの 平均身長 を 降順に 並べる
--
SELECT countries.name AS 国名, AVG(players.height) AS 平均身長 FROM countries
LEFT OUTER JOIN players on countries.id = players.country_id
GROUP BY countries.name
ORDER BY 平均身長 DESC;