「TOPSIC SQL CONTEST」の練習コンテストにチャレンジ!

 2023.12.19  株式会社システムインテグレータ

 今回の初心者訓練所#11は、以前のブログで「おすすめの学習コンテンツ」として紹介しておりました「TOPSIC SQL CONTEST(TSC)」の難易度1から4の全4問の練習用コンテスト問題にチャレンジしていきます。皆さんも、是非、「TSC」にログインして一緒にチャレンジしましょう。(会員登録がまだの方も、簡単に登録できます!)

教養としてのプログラミング入門BOOK

問題1「都道府県の人口」

難易度1の問題です。シンプルな一覧出力の問題になります。

【問題】

sql11-01

【使用テーブル】

sql11-02

【解答例】

SELECT
    DISTRICT_NAME AS 都道府県名  -- ①
    , TOTAL_AMT AS 総人口
FROM POPULATION
WHERE LVL = 2  -- ②
ORDER BY TOTAL_AMT DESC;  -- ③

【解説】(解答例のコメント番号に対応した説明になります)
①    表示項目は、AS句を使用して別名にしています。
②    階層= 2のデータが対象なので、WHERE句に条件を記載しています。
③    総人口の降順に表示なので、ORDER BYにはDESCを指定しています。

【解答記入】

sql11-03

【コードテスト結果】
sql11-04

実際は、コードテスト結果に問題ないことを確認後に「提出」ボタンを押下することで採点されます。

問題2「世帯入院率」

難易度2ということで、テーブル結合を利用して2つのテーブルを結合し、小数点の計算を行う問題となっています。

【問題】

sql11-05

【使用テーブル】

sql11-06

ER図を見てみると、入院状況テーブルと都道府県テーブルが実線で繋がれているため、2つのテーブルは依存型リレーションであることを確認できます。入院状況テーブルの都道府県コードが(FK)外部キーとなっているので、都道府県テーブルが親で入院状況テーブルが子という親子関係となります。子テーブルの都道府県コードが親テーブルの都道府県コードの存在に依存していることがわかります。

【解答例】

SELECT
    HP.PF_CODE AS 都道府県コード
    , PF.PF_NAME AS 都道府県名
    , ROUND(  -- ①
        CAST(HP.INP_YES AS REAL) /  -- ②
        CAST( (HP.INP_YES + HP.INP_NO + HP.UNIDENTIFIED) AS REAL )
        * 100
        , 1  -- ①
    ) AS 入院率
FROM
    HOSPITALIZATION AS HP
    INNER JOIN PREFECTURE AS PF  -- ③
        ON PF.PF_CODE = HP.PF_CODE 
ORDER BY  -- ④
    入院率 DESC
    , HP.PF_CODE ASC;

【解説】
①    入院率は、小数点第2位を四捨五入なのでROUND関数のパラメータに1を指定しています。
②    小数点の計算を行うため、INTEGER型(符号付整数)の計算項目をREAL型(不動小数点数)に変換して計算しています。項目を利用して計算する場合は、項目のデータ型に注意して計算を行ってください。
③    都道府県名を取得するため、都道府県テーブルを内部結合しています。
④    表示順は、降順と昇順が混在しているので注意してください。

【解答記入】

sql11-07

【コードテスト結果】

sql11-08

問題3「人口増加率分析」

難易度3となるこの問題は、先程と同様にテーブル結合を利用しますが、同じテーブルを結合(自己結合)して、調査年毎に総人口を取得しています。

【問題】
sql11-09
【使用テーブル】
sql11-10

【解答例】

SELECT
    PO.PF_CODE AS 都道府県コード
    , PF.PF_NAME AS 都道府県名
    , PO.TOTAL_AMT AS 総人口2015年
    , PO2020.TOTAL_AMT AS 総人口2020年
    , ROUND(  -- ①
            CAST(PO2020.TOTAL_AMT AS REAL)  -- ②
   / CAST(PO.TOTAL_AMT AS REAL) * 100
    )  AS 人口増加率 
FROM
    POPU_TRANSITION AS PO  -- ③
    INNER JOIN PREFECTURE AS PF  -- ④
        ON PF.PF_CODE = PO.PF_CODE 
    INNER JOIN POPU_TRANSITION AS PO2020  -- ⑤
        ON PO2020.PF_CODE = PO.PF_CODE
        AND PO2020.SURVEY_YEAR = 2020  -- ⑤
WHERE
    PO.SURVEY_YEAR = 2015  -- ③
    AND  PO2020.TOTAL_AMT >= PO.TOTAL_AMT  -- ⑥
ORDER BY  -- ⑦
    人口増加率 DESC
    , 都道府県コード ASC;
 

【解説】
①    人口増加率の小数点以下は四捨五入なので、ROUND関数のパラメータは指定していません。
②   先程と同様に、小数点の計算を行うため、INTEGER型(符号付整数)の計算項目をREAL型(不動小数点数)に変換して計算しています。
③    メインテーブルで調査年が2015年のデータを取得しています。
④    都道府県名を取得するため、内部結合で都道府県テーブルを結合しています。
⑤    もう一つの内部結合で、メインテーブルと同じ人口推移テーブルを結合して、調査年が2020年のデータを取得しています。どちらのテーブルの項目か判断できるように、AS句を使用して違う名称にしています。
⑥     人口が増加した都道府県を抽出するために、WHERE句でメインテーブルから取得した2015年の総人口と内部結合で取得した2020年の総人口を比較しています。
⑦    表示順は、降順と昇順が混ざっているので注意してください。

【解答記入】
sql11-11

【コードテスト結果】

sql11-12

問題4「年齢別睡眠時間分析」

それでは最後の問題、難易度4にチャレンジしてみましょう。問題に登場してくる睡眠時間詳細データのように縦持ちのデータを、表形式にする場合によく利用される方法なので、是非、覚えてください。

【問題】
sql11-13
【使用テーブル】
sql11-14

【解答例】 

SELECT 
    AGE_GRP.AGE_NAME AS 年齢階層,
    SUM(  -- ④
    CASE WHEN TIME_CODE = 120  -- ①
       THEN TARGET_POP
       ELSE 0 END
    ) AS "5時間未満",  -- ②
    SUM(CASE WHEN TIME_CODE = 130 THEN TARGET_POP ELSE 0 END)
     AS "5時間以上6時間未満",
    SUM(CASE WHEN TIME_CODE = 140 THEN TARGET_POP ELSE 0 END)
     AS "6時間以上7時間未満",
    SUM(CASE WHEN TIME_CODE = 150 THEN TARGET_POP ELSE 0 END)
     AS "7時間以上8時間未満",
    SUM(CASE WHEN TIME_CODE = 160 THEN TARGET_POP ELSE 0 END)
     AS "8時間以上9時間未満",
    SUM(CASE WHEN TIME_CODE = 170 THEN TARGET_POP ELSE 0 END)
     AS "9時間以上",
    SUM(CASE WHEN TIME_CODE = 180 THEN TARGET_POP ELSE 0 END)
     AS 不詳
FROM SLEEP_TIME_DTL
INNER JOIN AGE_GRP  -- ③
  ON AGE_GRP.AGE_CODE = SLEEP_TIME_DTL.AGE_CODE
GROUP BY AGE_GRP.AGE_CODE  -- ④
ORDER BY AGE_GRP.AGE_CODE;

【解説】
①   CASE句を用いて時間コードの値で、対象人数を対応するカラムに振分けます。時間コードが条件外の場合はゼロをセットしています。
②    AS句で変更している表示項目名の先頭が数字の場合は、ダブルクォーテーションで項目名を囲む必要がありますので注意しましょう。
③    年齢階層名を取得するために、年齢階層テーブルを内部結合しています。
④    年齢コードでグルーピングを行い、振分けた値をサマリして表示します。

【解答記入】
sql11-15
【コードテスト結果】

sql11-16

上記の解説ではちょっと分りにくいと思いますので、下記のSELECT文でグルーピングする前の状態を見てみましょう。(SUM関数とGROUP BY句が無い状態です)

 【グルーピング前】
sql11-17

【サンプルデータ】
sql11-18

【グルーピング前実行結果】
sql11-19

睡眠時間詳細データの1レコード毎の対象人数が、年齢階層と睡眠時間に振り分けられていることが確認できます。この結果を年齢階層で集計することにより、最終的に表示フォーマット例のように表形式で表示することができます。それほど難しい関数等を使用していないので、実装方法さえ覚えてしまえば色々と活用できると思います。

解説は以上となります。

おすすめの学習コンテンツ

本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください!

SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
 SQLiteとは?他のデータベースとの違いも解説

SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
 「SQLの教科書」ビックデータ時代の必須科目

SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
 TOPSIC-SQL無料トライアルテスト

SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
    過去に開催されたコンテストの問題にもチャレンジできます。
 TOPSIC SQL CONTEST

SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
 SQL-BOOT CAMP #1 
 ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう!」

あとがき

初心者訓練所の第11弾となる今回は、「TSC」の練習コンテスト問題に挑戦してみました。 皆さんはどうでしたか?4問目はちょっと難しかったかも知れません。でも、これで同じタイプの問題にも対応できるようになったと思います。「TSC」は、会員になると過去のコンテスト問題にも挑戦できます。さらにスキルを磨くチャンスですので、ぜひ、トライしてみてください。


(株)システムインテグレータ インキュベーション部 元鬼軍曹 久保 司

ビッグデータ時代の必須科目 SQLの教科書(初級編)

RELATED POST関連記事


RECENT POST「SQL‐BOOTCAMP」の最新記事


SQL‐BOOTCAMP

SQLのサブクエリを覚えよう!(FROM句、WHERE句での使い方)

SQL‐BOOTCAMP

SQLのサブクエリを覚えよう!(SELECT、UPDATE、INSERTでの使い方)

SQL‐BOOTCAMP

SQLの内部結合(INNER JOIN)を使ってみよう!

SQL‐BOOTCAMP

SQLのテーブル結合(JOIN, UNION, NATURAL)を覚えよう!基本構文と使用例を解説

「TOPSIC SQL CONTEST」の練習コンテストにチャレンジ!

TOPSIC TOPへ

新規CTA

TOPSIC-SQL4コマ漫画

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事