【問題1】文字数チェック

SELECT
    EMP_CODE AS CODE
    , EMP_ENG_NAME AS NAME
    , LENGTH(EMP_ENG_NAME) AS WORD_CNT 
FROM
    EMP 
WHERE
    LENGTH(EMP_ENG_NAME) >= 15 
ORDER BY
    WORD_CNT DESC
    , EMP_CODE ASC;

【問題2】受注金額TOP5

SELECT
    ODD.ITEM_CODE AS CODE
    , IT.ITEM_NAME AS NAME

    -- 販売単価と受注数量を掛けた値をSUM関数で集計します
    , SUM(ODD.UNITPRICE * ODD.ORDER_QTY) AS TOTAL_AMT 
FROM
   -- メインテーブルを受注テーブルにしています
    ORDERS AS OD 

    -- 商品コード、販売単価、受注数量を取得するために、受注明細テーブルを内部結合しています
    INNER JOIN ORDERS_DTL AS ODD 
        ON OD.ORDER_NO = ODD.ORDER_NO 

    -- 商品名を取得するために、商品テーブルを内部結合しています
    INNER JOIN ITEM AS IT 
        ON ODD.ITEM_CODE = IT.ITEM_CODE 
WHERE

    -- BETWEEN句を使用して受注日が範囲内のデータを抽出します
    OD.ORDER_DATE BETWEEN '2023-05-14' AND '2023-05-20' 
GROUP BY 

    -- 商品コード、商品名でグルーピングします
    ODD.ITEM_CODE
    , IT.ITEM_NAME
ORDER BY
    TOTAL_AMT DESC
    , ODD.ITEM_CODE DESC 

-- LIMIT句で５件までの表示制御をしています
LIMIT
    5;

【問題3】商品整理

DELETE 
FROM
    ITEM 
WHERE

    -- 在庫管理する商品が対象
    STOCK_MANAGEMENT_TYPE = 1 
    AND ( 

        -- 商品コードが在庫テーブルに存在しないこと確認
        NOT EXISTS ( 
            SELECT
                ITEM_CODE 
            FROM
                STOCK 
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE
        ) 

        -- 商品コードが在庫テーブルに存在しているが、削除条件を満たしていることを確認
        OR EXISTS ( 
            SELECT
                ITEM_CODE 
            FROM
                STOCK 
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE 

            -- 商品コードで集計した結果を判定するためグルーピングをおこなう
            GROUP BY
                ITEM_CODE 
            HAVING

               -- 最終出荷日の最大値が６カ月以上前かチェック
               -- 入荷実績があるがまだ出荷実績がない場合は、最終出荷日はNULLなので削除の対象外となる
               --  （最終出荷日がNULLの場合は対象外とするため、ダミーで'2999-12-31'をセット)
                MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 months') 

                -- 実在庫数の合計がゼロかチェック
                AND SUM(ACTUAL_AMT) = 0
        )
    );

【問題4】ABC分析

WITH TOTAL_SALES AS ( 

    -- 対象期間の売上データの売上金額の総合計を取得する
    SELECT
        SUM(SALES_AMT) AS TOTAL_AMT 
    FROM
        SALES 
    WHERE
        SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
) 
, SUM_ITEM AS ( 

    -- 商品毎に対象期間の売上金額合計を取得する
    SELECT
        SAD.ITEM_CODE
        , SUM(SAD.UNITPRICE * SAD.SALES_QTY) AS AMT 
    FROM
        SALES AS SA 

        -- 販売単価、売上数量を取得するために売上データ明細を内部結合
        INNER JOIN SALES_DTL AS SAD 
            ON SA.SALES_NO = SAD.SALES_NO 
    WHERE
        SA.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30' 
    GROUP BY
        SAD.ITEM_CODE
) 
, CUMUL_SALES AS ( 

    -- 上記の商品毎の売上金額合計をもとに、売上金額の降順に累積金額を取得
    SELECT
        ITEM_CODE
        , AMT

        -- SUM関数とOVER句で累積金額を取得
        -- 売上合計が同じ場合は、同じ商品の売上金額を合計した値が累積金額になる
        -- B,Cの売上金額が100で同じなので、B,Cの売上金額を加算した200を前のレコードの350に加算して、
        -- B,Cそれぞれの累積金額は同額の550になる
        --+--------+---------+---------+
        --| コード | 売上合計 | 累積金額 |
        --+--------+---------+---------+
        --|    A   |     350 |     350 |
        --+--------+---------+---------+
        --|    B   |     100 |     550 |
        --+--------+---------+---------+
        --|    C   |     100 |     550 |
        --+--------+---------+---------+

        , SUM(AMT) OVER (ORDER BY (AMT) DESC) AS CUMUL_AMT 
    FROM
        SUM_ITEM
) 
SELECT
    CS.ITEM_CODE AS CODE
    , ITEM_NAME AS NAME
    , CS.AMT AS SAL_AMT
    , CS.CUMUL_AMT AS CML_AMT

    -- 小数点を計算するので、CAST関数でREAL型に変換して、結果をROUND関数で四捨五入する
    , ROUND((CAST(CS.AMT AS REAL) / TS.TOTAL_AMT * 100), 1) || '%' AS SAL_COMP
    , ROUND( 
        (CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100)
        , 1
    ) || '%' AS TTL_COMP

    -- CASE句で計算結果によりランク分けをおこなう
    , CASE 
        WHEN ROUND( 
            (CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100)
            , 1
        ) <= 40 
            THEN 'A' 
        WHEN ROUND( 
            (CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100)
            , 1
        ) <= 80 
            THEN 'B' 
        ELSE 'C' 
        END AS RANK 
FROM
    CUMUL_SALES AS CS 

    -- 各レコードで使用するため、1レコードのみの総合計金額をCROSS結合
    CROSS JOIN TOTAL_SALES AS TS 

    -- 商品名を取得するため、商品テーブルを内部結合
    INNER JOIN ITEM 
        ON ITEM.ITEM_CODE = CS.ITEM_CODE 
ORDER BY
    CS.AMT DESC
    ,CS.ITEM_CODE DESC;