【問題1】同一文字が複数ある場合の検索

SELECT
    EMP_CODE AS CODE
    , EMP_ENG_NAME AS ENG_NAME 
FROM
    EMP 
WHERE
    EMP_ENG_NAME LIKE '%s%s%' 
ORDER BY
    EMP_CODE ASC;

【問題2】無効データ削除

DELETE 
FROM
    EMP 
WHERE
    VALID_FLG <> '1' 
    AND EMP_CODE IN (SELECT EMP_CODE FROM EMP_INVALID);

【問題3】中央値の算出(例1)

SELECT
    ROUND(AVG(TOTAL_VALUE)) AS SA_MEDIAN 
FROM
    ( 
        SELECT
            CV1.TOTAL_VALUE 
        FROM
            CONVENIENCE AS CV1 
            INNER JOIN CONVENIENCE AS CV2 
                ON CV2.SURVEY_YEAR = 2019 
                AND CV2.KIND_CODE = '100' 
        WHERE
            CV1.SURVEY_YEAR = 2019 
            AND CV1.KIND_CODE = '100' 
        GROUP BY
            CV1.TOTAL_VALUE 
        HAVING
            -- 自分より大きな値を持つ行数と、小さな値を持つ行数の両方の条件を満たすとHAVING句の条件が成り立つ
            CASE MOD(COUNT(*), 2) 
                -- データ件数が偶数の場合                
                -- 「>= COUNT(*) / 2」で件数を判断し大きな値と小さな値の共通部分を対象とする
                WHEN 0 THEN SUM( 
                    CASE 
                        WHEN CV2.TOTAL_VALUE >= CV1.TOTAL_VALUE 
                            THEN 1 
                        ELSE 0 
                        END
                ) >= COUNT(*) / 2 
                AND SUM( 
                    CASE 
                        WHEN CV2.TOTAL_VALUE <= CV1.TOTAL_VALUE 
                            THEN 1 
                        ELSE 0 
                        END
                ) >= COUNT(*) / 2 
                -- データ件数が奇数の場合
                -- 「> COUNT(*) / 2 」で件数を判断して１件のみ対象とする
                ELSE SUM( 
                    CASE 
                        WHEN CV2.TOTAL_VALUE >= CV1.TOTAL_VALUE 
                            THEN 1 
                        ELSE 0 
                        END
                ) > COUNT(*) / 2 
                AND SUM( 
                    CASE 
                        WHEN CV2.TOTAL_VALUE <= CV1.TOTAL_VALUE 
                            THEN 1 
                        ELSE 0 
                        END
                ) > COUNT(*) / 2 
                END
    );

【問題3】中央値の算出(例2)

SELECT ROUND(AVG(TOTAL_VALUE)) AS SA_MEDIAN
FROM (
    SELECT
        TOTAL_VALUE
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019
        AND KIND_CODE = 100
    ORDER BY
        TOTAL_VALUE
    -- LIMITとOFFSETを利用して、データ件数が偶数の場合は対象データを2件、奇数の場合は1件とする
    LIMIT 2 - (SELECT COUNT(*) % 2 FROM CONVENIENCE WHERE SURVEY_YEAR = 2019 AND KIND_CODE = 100)
    OFFSET (SELECT (COUNT(*)-1) / 2 FROM CONVENIENCE WHERE SURVEY_YEAR = 2019 AND KIND_CODE = 100)
);

【問題4】登録人数の日別集計

WITH DATE_TBL AS ( 
    -- 2022年08月のカレンダー仮想テーブルを定義する
    SELECT
        '2022-08-01' AS CLDATE
        , STRFTIME('%w', '2022-08-01') AS WKNO
        -- 曜日の番号を取得し該当する曜日を取得する
        , SUBSTR('日月火水木金土', STRFTIME('%w', '2022-08-01') + 1, 1) AS WEEK 
    UNION ALL 
    SELECT
        DATE (CLDATE, '+1 days')
        , STRFTIME('%w', DATE (CLDATE, '+1 days'))
        , SUBSTR( 
            '日月火水木金土'
            , STRFTIME('%w', DATE (CLDATE, '+1 days')) + 1
            , 1
        ) 
    FROM
        DATE_TBL 
    WHERE
        CLDATE < '2022-08-31'
) 
SELECT
    CLDATE AS REGIST_DATE
    , WEEK AS WK
    , COUNT(USER_CODE) AS TOTAL 
FROM
    DATE_TBL 
    LEFT OUTER JOIN USERS 
        ON DATE (USERS.CONFIRMED_AT) = DATE_TBL.CLDATE 
        AND VALID_FLG = '1' 
GROUP BY
    CLDATE 
ORDER BY
    CLDATE ASC;