実践! 「TOPSIC SQL CONTEST」の練習問題にチャレンジしよう

はじめに

最終回となる今回は、これまでの「総まとめ」という意味で実践に突入します。本連載の最後に紹介していた「TOPSIC SQL CONTEST(TSC)」の難易度1から4まで全4問の練習問題にチャレンジしていきます。訓練場は、いつもの「SQLite」ではなく、実践場のTSCを使用します。皆さんも、ぜひTSCにログインして一緒にチャレンジしてください。

それでは、最終回も気合を振り絞って、実践に挑んで行きましょう!

都道府県の人口

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

【問題】

【使用テーブル】

【解答例】

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

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

【解答記入】

解答を記入後、「コードテスト」ボタンを押下して結果を確認します。

【コードテスト結果】

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

世帯入院率

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

【問題】

【使用テーブル】

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型(不動小数点数)に変換して計算しています。項目を利用して計算する場合は、項目のデータ型に注意して計算を行ってください
③都道府県名を取得するため、都道府県テーブルを内部結合しています
④表示順は、降順と昇順が混在しているので注意してください

【解答記入】

【コードテスト結果】