【問題1】部門データ作成

-- 1回のINSERT文で、2件のデータを挿入しています
INSERT 
INTO DEPARTMENT 
VALUES ( 
    '1100'
    , '2024-04-01'
    , NULL
    , '営業1課'
    , 2
    , '1000'
    , '1'
    , 'TANAKA'
    , '2024-04-01 00:00:00'
) 
, ( 
    '2200'
    , '2024-04-10'
    , NULL
    , '製造2課'
    , 2
    , '2000'
    , '1'
    , 'YAMADA'
    , '2024-04-10 00:00:00'
);

【問題2】文字列の切り出し

SELECT
    EMP_CODE AS CODE

    -- ||で項目を連結しています
    , EMP_LAST_NAME || EMP_FIRST_NAME AS NAME
    , EMP_ENG_NAME AS ENG_NAME

    -- IFNULLで、値がNULLの場合は「未入力」と表示させます
    , IFNULL( 

        -- REPLACEで、社員英字名の半角スペースを空文字に置換ます
        -- SUBSTRで、先頭から10文字を切り出します
        SUBSTR(REPLACE (EMP_ENG_NAME, ' ', ''), 1, 10)
        , '未入力'
    ) AS CUTOUT_STR 
FROM
    EMP 
ORDER BY
    CUTOUT_STR DESC
    , CODE DESC;

【問題3】データ操作履歴

SELECT

    -- 商品データが存在する場合は、商品データの商品コードを表示し、存在しない場合は履歴データより表示する
    COALESCE(IT_M.ITEM_CODE, IT_H.ITEM_CODE) AS CODE
    , COALESCE(IT_M.ITEM_NAME, IT_H.ITEM_NAME) AS NAME
    , CASE 

        -- 履歴データに存在しない場合
        WHEN IT_H.USER_UPDATE_DATETIME IS NULL 
            THEN 'ADDED' 

        -- 商品データに存在しない場合
        WHEN IT_M.USER_UPDATE_DATETIME IS NULL 
            THEN 'DELETED' 

        -- 両方に存在して、担当者更新日時が違う場合
        WHEN IT_M.USER_UPDATE_DATETIME <> IT_H.USER_UPDATE_DATETIME 
            THEN 'UPDATED' 
        END AS COMP_RSLT
FROM
    ITEM AS IT_M 

    -- 完全外部結合で、商品と商品履歴の全データを取得します
    FULL OUTER JOIN ITEM_HISTORY AS IT_H 
        ON IT_M.ITEM_CODE = IT_H.ITEM_CODE 
WHERE

    -- 担当者更新日が違うデータを対象とします
    -- IS NOTを使用してNULL値も比較可能とします
    IT_M.USER_UPDATE_DATETIME IS NOT IT_H.USER_UPDATE_DATETIME 
ORDER BY
    CODE DESC;

【問題4】曜日別売上分析

WITH SALES_SUM AS ( 

   -- 売上データを集計する
   SELECT

        -- STRFTIMEで、売上日の曜日番号を取得する
        STRFTIME('%w', SALES_DATE) AS WEEK_NO

        -- 曜日番号より、曜日の文字列より該当する曜日1文字を取得する 
        , SUBSTR('日月火水木金土', STRFTIME('%w', SALES_DATE) + 1, 1) AS WEEK_NAME

        -- 曜日毎のデータ件数を集計し、4週間の4で割る事により1日の平均件数を求める
        , ROUND(CAST(COUNT(SALES_NO) AS REAL) / 4) AS AVG_CNT

        -- 曜日毎の売上金額合計を集計し、4週間の4で割る事により1日の平均売上金額を求める
        , CAST(ROUND(CAST(SUM(SALES_AMT) AS REAL) / 4) AS INT) AS AVG_SA_AMT 
    FROM
        SALES 
    WHERE

        -- 4週間分の売上データを対象とする
        SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28' 

       -- 売上返品データは対象外かつ、黒伝データを対象とする
        AND SALES_TYPE != 2 
        AND UPDATED_NO IS NULL 
    GROUP BY
        WEEK_NAME
) 
, WEEK_TBL AS ( 

    -- 再帰クエリで1週間分のデータを作成する（日付は何日でも構わない）
    SELECT
        '2024-03-01' AS CLDATE
        , STRFTIME('%w', '2024-03-01') AS WEEK_NO
        , SUBSTR('日月火水木金土', STRFTIME('%w', '2024-03-01') + 1, 1) AS WEEK_NAME 
    UNION ALL 
    SELECT

        -- 翌日のデータを取得
        DATE (CLDATE, '+1 days')
        , STRFTIME('%w', DATE (CLDATE, '+1 days'))
        , SUBSTR( 
            '日月火水木金土'
            , STRFTIME('%w', DATE (CLDATE, '+1 days')) + 1
            , 1
        ) 
    FROM

       -- WITH句のテーブル名を指定して、上記のSELECT文で作成した項目を使用可能とする
        WEEK_TBL 
    WHERE

        -- 7日分のデータを対象にする
        CLDATE < '2024-03-07'
) 
SELECT
    WEEK_TBL.WEEK_NAME AS WEEK
    , IFNULL(AVG_CNT, 0) AS AVG_CNT

    -- 桁数によりカンマを付加する場所を判定して、金額を3桁毎に分割してカンマを追加する
    , CASE 
        WHEN LENGTH(AVG_SA_AMT) > 6 
            THEN SUBSTR(AVG_SA_AMT, 1, LENGTH(AVG_SA_AMT) - 6) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 5, 3)
         || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 2, 3) 
        WHEN LENGTH(AVG_SA_AMT) > 3 
            THEN SUBSTR(AVG_SA_AMT, 1, LENGTH(AVG_SA_AMT) - 3) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 2, 3)
        ELSE IFNULL(AVG_SA_AMT, '0') 
        END || '円' AS AVG_AMT 
FROM
    WEEK_TBL 

    -- 売上データに存在しない曜日も表示するため、外部結合を使用する
    LEFT OUTER JOIN SALES_SUM 
        ON SALES_SUM.WEEK_NO = WEEK_TBL.WEEK_NO 
ORDER BY
    WEEK_TBL.WEEK_NO ASC;