■ありがたい、問題集

・問題集リンク
https://tech.pjin.jp/blog/2016/12/05/sql%E7%B7%B4%E7%BF%92%E5%95%8F%E9%A1%8C-%E4%B8%80%E8%A6%A7%E3%81%BE%E3%81%A8%E3%82%81/
■使用テーブル

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;