【問題1】組合せ一覧

SELECT
    ITEM_CODE AS ITEM
    , ITEM_NAME AS I_NAME
    , CL_NAME AS C_NAME
    , SIZE_NAME AS S_NAME 
FROM
    ITEM 
    -- 交差結合で全てのテーブルを結合
    CROSS JOIN COLOR_TBL 
    CROSS JOIN SIZE_TBL 
WHERE
    -- 在庫管理する商品のみ対象
    STOCK_MANAGEMENT_TYPE = 1 
ORDER BY
    ITEM_CODE ASC
    , CL_CODE ASC
    , SIZE_CODE ASC;

【問題2】同姓同名抽出

SELECT
    EMP_CODE AS CODE
    , EMP_LAST_NAME AS SURNAME
    , EMP_FIRST_NAME AS NAME 
FROM
    EMP 
WHERE
    VALID_FLG = '1' 
   -- 社員姓、社員名の半角スペースを空文字に置き換えて比較 
    AND REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') IN ( 
        -- 副問合せで同姓同名が2件以上存在するデータを抽出
        SELECT
            REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') AS EMP_NAME 
        FROM
            EMP 
        WHERE
            VALID_FLG = '1' 
        GROUP BY
            EMP_NAME 
        HAVING
            COUNT(*) >= 2
    ) 
ORDER BY
    REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') ASC
    , EMP_CODE ASC;

【問題3】偏差値の算出

WITH SUB AS ( 
    -- １．点数の平均値を算出
    SELECT
        AVG(POINT) AS AVERAGE 
    FROM
        TEST_RESULTS 
    WHERE
        TEST_ID = '100'
) 
, SUB2 AS ( 
    SELECT
        -- ４．標準偏差（分散の平方根）を求める
        SQRT( 
            -- ３．二乗の平均（分散）を求める
            AVG( 
                -- ２．個々の点数と平均値の差の二乗を求める
                (T.POINT - SUB.AVERAGE) * (T.POINT - SUB.AVERAGE)
            )
        ) AS STANDARD_DEVIATION 
    FROM
        TEST_RESULTS AS T 
        -- 求めた平均値を交差結合し計算に使用する
        CROSS JOIN SUB 
    WHERE
        TEST_ID = '100'
) 
SELECT
    USER_ID AS USER
    , POINT AS PT
    , CASE 
        WHEN STANDARD_DEVIATION <> 0 
            THEN ROUND( 
            -- ５．個々の点数と平均値の差に10を掛ける
            -- ６．上記の値を標準偏差で割る
            (((POINT - AVERAGE) * 10) / STANDARD_DEVIATION)
            , 1
        -- ７．標準偏差で割った値に50を足す
        ) + 50 
        -- 標準偏差が0の場合は一律で偏差値は50となる
        ELSE 50 
        END AS DEV_VAL 
FROM
    TEST_RESULTS 
    CROSS JOIN SUB 
    CROSS JOIN SUB2 
WHERE
    TEST_ID = '100' 
ORDER BY
    DEV_VAL DESC
    , USER_ID ASC;

【問題4】MRRの集計

WITH RSLT AS ( 
    -- 最も古い年月のMRRを０とする
    SELECT
        (SELECT MIN(MONTHLY) FROM MRR_DATA) AS YM
        , 0 AS MRR 
    UNION ALL 
    SELECT
        -- 月を1カ月カウントアップする
        DATE (YM, '+1 month')
        -- 問題文の計算式をもとにMRRを計算する
        , MRR + ( 
            ( 
                SELECT
                    NEW_MRR + EXPANSION_MRR - DOWNGRADE_MRR - CHURN_MRR 
                FROM
                    MRR_DATA 
                WHERE
                    MONTHLY = YM
            )
        ) 
    FROM
        RSLT 
    WHERE
       -- 最も新しい年月の翌月までデータを作成する
        YM < DATE ((SELECT MAX(MONTHLY) FROM MRR_DATA), '1 month')
) 
-- 仮想テーブルに作成したデータを年月の昇順で表示する
SELECT
    * 
FROM
    RSLT
ORDER BY
    YM ASC;