応用情報技術者試験「データベース」に出てきたSQLを眺めてみる

ONの後ろが空欄

ONの後ろの空欄は、「○○.●● = △△.●●」の形の条件文が入ります。
例1) 平成29年春期

SELECT 申請書.申請書ID,
       申請書.タイトル,
       申請書.申請日
       ユーザ.ユーザ名,
       部署マスタ.部署名
FROM 申請書
    INNER JOIN 承認申請 ON 申請書.申請書ID = 承認申請.申請書ID
    INNER JOIN ユーザ ON 申請書.申請者ユーザID = ユーザ.ユーザID
    INNER JOIN 部署マスタ ON ユーザ.部署ID = 部署マスタ.部署ID
WHERE (承認申請.承認申請状態 NOT IN ('可決', '否決'))
      AND
      ((申請書.申請者ユーザID = :ユーザID)
       OR
       (申請書.申請書ID
        IN (SELECT DISTINCT 申請書ID
            FROM 承認者情報
                --この行のON以降が空欄
                INNER JOIN 承認申請 ON 承認者情報.承認申請ID = 承認申請.承認申請ID
            WHERE 承認者情報.承認者ユーザID = ：ユーザID )
      ))

SELECT 申請書.申請書ID, 申請書.タイトル, t1.項目値 AS 金額, t2.項目値 AS 支払日
                                         --下の2行のON以降が共に空欄
    FROM 申請書 INNER JOIN 申請書項目 t1 ON 申請書.申請書ID = t1.申請書ID
                INNER JOIN 申請書項目 T2 ON 申請書.申請書ID = t2.申請書ID
    WHERE
    ((申請書.書式ID = '購買' AND t1.項目キー = 'amount' AND t2.項目キー = 'pay_data') OR
     (申請書.書式ID = '契約' AND t1.項目キー = 'pay_initial' AND t2.項目キー = 'start_date'))
    AND (承認申請.申請書ID = 申請書.申請書ID AND 承認申請.承認申請状態 = '可決')

例2) 平成23年秋期

SELECT 明細行番号, 日付, 費用種別.費用種別名, 金額
FROM 申請明細 INNER JOIN 費用種別
    ON 申請明細.費用種別コード = 費用種別.費用種別コード    --この行のON以降が空欄
WHERE 申請明細.申請書番号 = :申請書番号 AND 費用種別.証憑フラグ = 'Y'
ORDER BY 明細行番号

GROUP BYの後ろが空欄

GROUP BYの後ろの空欄には、SELECTの後ろに続く項目名が入ります。
例1）平成29年秋期

SELECT 品名コード, 品目名, 産地コード, 産地名,
SUM(t2.単価 * t1.パレット数) AS 合計返品金額, SUM(t1.パレット数) AS 合計返品数量
    FROM 返品 t1
        INNER JOIN 販売明細 t2 USING (販売番号, 販売明細番号)
        INNER JOIN 仕入明細 USING(仕入番号，仕入明細番号)
        INNER JOIN 品目 USING (品目コード)
        INNER JOIN 産地 USING (産地コード)
    WHERE 返品日 = CURRENT_DATE
    GROUP BY 品目コード，品目名，産地コード，産地名    --この行のGROUP BY以降が空欄
    ORDER BY 品目コード ASC, 産地コード ASC

COALESCEを含むSELECT文

COALESCE()関数が出てきたら、検索結果にNULLが含まれる場合があることを意味します。
この時、多くの場合外挿（OUTER JOIN）が行われていています。
例1) 平成28年春期

--このSELECT文にはCOALESCE()がある
SELECT ST.確認年月日, ST.店舗ID, ST.商品ID, COALESCE(SS.日間販売数量, 0),
    ST.日間在庫数量
FROM
    (SELECT SC.確認年月日, SC.店舗ID, SC.商品ID,
        AVG(SC.在庫数量) AS 日間在庫数量
     FROM 在庫 SC
     GROUP BY SC.確認年月日, SC.店舗ID, SC.商品ID) ST
     LEFT OUTER JOIN    --ここにLEFT OUTER JOINがいる
    (SELECT SL.販売年月日, SL.店舗ID, SD.商品ID,
        SUM(SD.販売数量) AS 日間販売数量
     FROM 販売 SL
        INNER JOIN 販売詳細 SD ON SL.販売ID = SD.販売ID
     GROUP BY SL.販売年月日, SL.店舗ID, SD.商品ID) SS
      ON ST.確認年月日 = SS.販売年月日
         AND ST.店舗ID = SS.店舗ID
         AND ST.商品ID = SS.商品ID

SELECT SF.売上年月, SF.店舗ID, IT.商品分別ID,
    AVG(SF.日間販売数量) AS 平均販売数量, AVG(SF.日間在庫数量) AS 平均在庫数量
FROM
    (SELECT TO_YYYYMM(SA.売上年月日) AS 売上年月, SA.店舗ID, SA.商品ID,
        SA.日間販売数量, SA.日間在庫数量
     FROM 売上ファクト SA) SF
    INNER JOIN 商品 IT ON SF.商品ID = IT.商品ID
GROUP BY SF.売上年月, SF.店舗ID, IT.商品分類ID
ORDER BY SF.売上年月 DESC，SF.店舗ID ASC，平均在庫数量 DESC

例2) 平成25年春期

INSERT INTO 発言キーワード重み (キーワードID, 重み)
--このSELECT文にはCOALESCE()がある
SELECT キーワード.キーワードID, COALESCE(OMOMI.CNT,0)
FROM キーワード
    LEFT OUTER JOIN    --ここにLEFT OUTER JOINがいる（ここは空欄であった）
    (SELECT 発言キーワード.キーワードID, COUNT(*) AS CNT
     FROM 発言キーワード
        INNER JOIN 発言 ON 発言.発言ID = 発言キーワード.発言ID
     WHERE 発言.社員番号 IS NULL
     GROUP BY 発言キーワード.キーワードID) OMOMI
    ON キーワード.キーワードID = OMOMI.キーワードID


SELECT 発言.発言ID, SUM(発言キーワード重み.重み) AS WEIGHT
FROM 発言
    INNER JOIN 発言キーワード ON 発言.発言ID = 発言キーワード.発言ID
    INNER JOIN 発言キーワード重み
        ON 発言キーワード.キーワードID = 発言キーワード重み.キーワードID
WHERE 発言.否定的フラグ = '1'
GROUP BY 発言.発言ID
ORDER BY WEIGHT DESC

平成28年秋期

SELECT t1.会員番号, t1.氏名, t6.商品分類番号,
    t6.商品分類名, SUM(t4.商品単価 * t4.個数) AS 購入金額合計
FROM 会員 t1
    INNER JOIN (SELECT t2.購入番号, t2.会員番号
        FROM 購入 t2 WHERE t2.購入日時 > :一年前) t3 ON t1.会員番号 = t3.会員番号
    INNER JOIN 購入明細 t4 ON t3.購入番号 = t4.購入番号
    INNER JOIN 商品 t5 ON t4.商品番号 = t5.商品番号
    INNER JOIN 商品分類 t6 ON t5.商品分類番号 = t6.商品分類番号
GROUP BY t1.会員番号, t1.氏名, t6.商品分類番号, t6.商品分類名

DECLARE cur CURSOR FOR
    SELECT t2.会員番号, t2.購入番号, t2.購入金額
    FROM 購入 t2
    WHERE t2.購入ステータス = '完了'
    AND t2.購入日時 <= :判定対象期限
    AND t2.判定処理状態 <> '判定処理済み'
    ORDER BY t2.会員番号，t2.購入日時 ;
UPDATE 会員 t1 SET t1.会員種別 = '一般会員';
SET current_kaiin_no = 0;
SET goukei = 0;
OPEN cur;
fetch_loop: LOOP
    FETCH cur INTO kaiin_no, kounyu_no, kounyu_kingaku;
    IF kaiin_no <> current_kaiin_no THEN
        SET current_kaiin_no = kaiin_no;
        SET update_flag = 0;
        SET goukei = 0;
    END IF;
    IF update_flag = 0 THEN
        SET goukei = goukei + kounyu_kingaku;
        UPDATE 購入番号 = kounyu_no;
            WHERE t2.購入番号 = kounyu_no;
        IF goukei >= 50000 THEN
            UPDATE 会員 t1 SET t1.会員種別 = '特別会員' WHERE t1.会員番号 = kaiin_no;
            SET update_flag = 1;
        END IF;
    ELSE
        UPDATE 購入 t2 SET t2.判定処理状態 = '繰越し' WHERE t2.購入番号 = kunyu_no;
    END IF;
END LOOP fetch_loop;
CLOSE cur;

平成27年秋期

WITH RECURSIVE 関連部署(部署ID, 部署名, 上位部署ID) AS (
    SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
        FROM 部署 WHERE 部署.上位部署ID IS NULL
            AND :年月日 BETWEEN 部署.適用開始年月日 AND 部署.適用終了年月日
UNION ALL
    SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
        FROM 部署, 関連部署 WHERE 部署.上位部署ID = 関連部署.部署ID
            AND :年月日 BETWEEN 部署.適用開始年月日 AND 部署.適用終了年月日
)
SELECT 部署ID, 部署名, 上位部署ID FROM 関連部署

平成27年春期

SELECT AC.*
FROM アクセスログ AC
WHERE EXISTS
    (SELECT * FROM 非営業日 NS
     WHERE AC.操作年月日 = NS.非営業年月日)

SELECT AC.*
FROM アクセスログ AC
    INNER JOIN 利用者 US ON AC.利用者ID = US.利用者ID
    INNER JOIN サーバ SV ON AC.サーバID = SV.サーバID
WHERE AC.操作結果 = 'F'
    AND US.部署ID <> SV.部署ID

平成26年春期

SELECT COUNT(*) AS 判定結果
FROM Webユーザ
WHERE ユーザID = :ユーザID
    AND パスワードハッシュ値 = HASH(:パスワード)

SELECT A.船便番号, A.船便名, C.座席クラス番号, C.座席クラス名,
    COUNT(*) AS 空席数
FROM 船便 A
    INNER JOIN 座席クラス C ON B.座席クラス番号 = C.座席クラス番号
WHERE A.出発日 = :出発日
  AND A.出発地 = :出発地
  AND A.到着地 = :到着地
  AND B.空席状況 = '0'
GROUP BY A.船便番号，A.船便名，C.座席クラス番号，C.座席クラス名

平成25年秋期

SELECT タイトルマスタ.タイトル, 会員マスタ.会員ID, 会員マスタ.氏名,
    会員マスタ.連絡先, 貸出予約.予約日時
FROM (タイトルマスタ
    INNER JOIN 貸出予約 ON タイトルマスタ.タイトルID = 貸出予約.タイトルID)
    INNER JOIN 会員マスタ ON 貸出予約.会員ID = 会員マスタ.会員ID
WHERE タイトルマスタ.タイトルID = :予約タイトルID
    AND 貸出予約.予約日時 = (SELECT MIN(貸出予約.予約日時) FROM 貸出予約
                             WHERE 貸出予約.タイトルID = :予約タイトル
                               AND 貸出予約.予約ステータス = '予約中')

平成24年春期

CREATE VIEW 統合検索
    (書籍名, 著者名, 出版社名, ISBN番号, 図書館名, 貸出可能冊数) AS
SELECT 書籍A.書籍名, 書籍A.著者名, 書籍A.出版社名, 書籍A.ISBN番号,
    'A市図書館', COUNT(書籍A.書籍番号) FROM 蔵書A, 書籍A
WHERE 蔵書A.書籍番号 = 書籍A.書籍番号
    AND 蔵書A.蔵書番号 NOT IN (SELECT 貸出記録A.蔵書番号 FROM 貸出記録A
                               WHERE 貸出記録A.返却日 IS NULL)
GROUP BY 書籍名, 著者名, 出版社名, 書籍A.ISBN番号
UNION
SELECT 書籍B.書籍名, 書籍B.著者名, 書籍B.出版社名, 書籍B.ISBN番号,
    'B市図書館', COUNT(書籍B.ISBN番号) FROM 蔵書B, 書籍B
WHERE 蔵書B.ISBN番号 = 書籍B.ISBN番号
    AND 蔵書B.蔵書番号 NOT IN (SELECT 貸出記録B.蔵書番号 FROM 貸出記録B
                               WHERE 貸出記録B.返却日 IS NULL)
GROUP BY 書籍名, 著者名, 出版社名, 書籍B.ISBN番号


CREATE VIEW 統合貸出予約
    (蔵書番号, 書籍名, 著者名, 出版社名, ISBN番号, 図書館名, 貸出状況) AS
SELECT 蔵書A.蔵書番号, 書籍A.書籍名, 書籍A.著者名, 書籍A.出版社名,
    書籍A.ISBN番号, 'A市図書館', 蔵書A.貸出状況) FROM 蔵書A, 書籍A
WHERE 蔵書A.書籍番号 = 書籍A.書籍番号
    AND 蔵書A.貸出状況 = '貸出可'
UNION
SELECT 蔵書B.蔵書番号, 書籍B.書籍名, 書籍B.著者名, 書籍B.出版社名,
    書籍B.ISBN番号, 'B市図書館', 蔵書B.貸出状況) FROM 蔵書B, 書籍B
WHERE 蔵書B.ISBN番号 = 書籍B.ISBN番号
    AND 蔵書B.貸出状況 = '貸出可'

平成23年秋期

SELECT 明細行番号, 日付, 費用種別.費用種別名, 金額
FROM 申請明細 INNER JOIN 費用種別
    ON 申請明細.費用種別コード = 費用種別.費用種別コード
WHERE 申請明細.申請書番号 = :申請書番号 AND 費用種別.証憑フラグ = 'Y'
ORDER BY 明細行番号


SELECT 申請書.組織コード, 組織.組織名,
    SUM (申請明細.金額) AS 組織交通費合計
FROM 申請書, 申請明細, 組織
WHERE 申請明細.日付 BETWEEN :指定月開始日 AND :指定月終了日
    AND 申請書.申請書番号 = 申請明細.申請書番号
    AND 申請書.組織コード = 組織.組織コード
GROUP BY 申請書.組織コード, 組織.組織名
ORDER BY 申請書.組織コード

平成23年特別

CREATE TABLE 新刊 (書籍番号 INTEGER, ジャンル INTEGER,
    PRIMARY KEY(書籍番号),
    FOREIGN KEY(書籍番号)
    REFERENCES 書籍(書籍番号))

DELETE FROM 新刊;
INSERT INTO 新刊 (書籍番号, ジャンル)
    SELECT 書籍番号, ジャンル FROM 書籍 WHERE 発行年月日 + 60 > :今日;


CREATE TABLE 購入傾向
    (顧客番号 INTEGER, ジャンル INTEGER, 購入冊数 INTEGER,
     PRIMARY KEY(顧客番号, ジャンル),
     FOREIGN KEY(顧客番号)
     REFERENCES 顧客(顧客番号))

DELETE FROM 購入傾向;
INSERT INTO 購入傾向 (顧客番号, ジャンル, 購入冊数)
    SELECT 注文.顧客番号, 書籍.ジャンル, SUM(注文明細.数量)
    FROM 注文, 注文明細, 書籍
    WHERE 注文.注文年月日＋180＞:今日
    AND 注文.注文番号 = 注文明細.注文番号
    AND 注文明細.書籍番号 = 書籍.書籍番号
    GROUP BY 注文.顧客番号, 書籍.ジャンル;


SELECT 書籍.書籍番号, 書籍.書籍名, 書籍.書籍説明 FROM 書籍, 新刊, 購入傾向
    WHERE 書籍.書籍番号 = 新刊.書籍番号
    AND 購入傾向.顧客番号 = :顧客番号
    AND 書籍.ジャンル = 購入傾向.ジャンル
    AND 購入傾向.購入冊数 * 10 > :購入総冊数


UPDATE 購入傾向 SET 購入冊数 =
    (SELECT 購入傾向.購入冊数 + 注文明細.数量 FROM 注文明細, 書籍
     WHERE 注文明細.注文番号 = :注文番号 AND 注文明細.注文明細番号 = :注文明細番号
     AND 注文明細.書籍番号 = 書籍.書籍番号 AND 書籍.ジャンル = 購入傾向.ジャンル)
WHERE 購入傾向.顧客番号 = :顧客番号
AND 購入傾向.ジャンル IN
    (SELECT 書籍.ジャンル FROM 注文明細, 書籍
     WHERE 注文明細.注文番号 = :注文番号 AND 注文明細.注文明細番号 = :注文明細番号
     AND 注文明細.書籍番号 = 書籍.書籍番号)

平成22年秋期

CREATE TABLE 請求
(請求書番号 CHAR(5),
顧客番号 CHAR(5), 請求日 CHAR(8), 計上年月 CHAR(6), 請求額 NUMERIC(10),
買上額 NUMERIC(10), 消込額 NUMERIC(10),
PRIMARY KEY (請求書番号),
FOREIGN KEY(顧客番号) REFERENCES 顧客(顧客番号))


SELECT 顧客番号, SUM(入金額-消込額)
    FROM 入金
    WHERE 入金日　<= :今月度末日
      AND 入金額 > 消込額
    GROUP BY 顧客番号

平成22年春期

SELECT t.店舗番号, t.店舗名, SUM(m.受注金額) AS 金額
FROM (店舗 t INNER JOIN (SELECT j.受注店舗番号, j.受注番号 FROM 受注 j
           WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p
           ON t.店舗番号 = p.受注店舗番号)
          INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号
GROUP BY t.店舗番号, t.店舗名
ORDER BY t.店舗番号


SELECT m.出荷店舗番号, SUM(m.受注金額) AS Web売上分
FROM 受注明細 m, 受注 j
WHERE (j.受注店舗番号 = 'A09999' ) AND ( j.受注番号 = m.受注番号 ) AND
      (j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日)
GROUP BY m.出荷店舗番号
ORDER BY m.出荷店舗番号


SELECT t.店舗番号, t.店舗名, SUM(m.受注金額) AS 金額
FROM 店舗 t LEFT OUTER JOIN
    (SELECT j.受注店舗番号, j.受注番号 FROM 受注 j
        WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p
            INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号
    ON t.店舗番号 = p.受注店舗番号
GROUP BY t.店舗番号, t.店舗名
ORDER BY t.店舗番号

平成21年春期

SELECT TempTbl.商品番号, SUM(TempTbl.小計)
FROM (SELECT 注文明細.商品番号, 注文明細.数量 AS 小計
      FROM 注文明細
          INNER JOIN 商品 ON 注文明細.商品番号 = 商品.商品番号
      WHERE 注文明細.注文番号 = :注文番号
          AND 商品.セットフラグ = '0'
      UNION ALL
      SELECT セット商品.子商品番号 AS 商品番号,
               セット商品.数量 * 注文明細.数量 AS 小計
      FROM 注文明細
          INNER JOIN 商品 ON 注文明細.商品番号 = 商品.商品番号
          INNER JOIN セット商品 ON 商品.商品番号 = セット商品.セット商品番号
      WHERE 注文明細.注文番号 = :注文番号
          AND 商品.セットフラグ = '1') TempTbl
GROUP BY TempTbl.商品番号

SELECT DISTINCT 注文明細.注文明細番号, 注文明細.商品番号, 商品.商品名,
    注文明細.数量, 注文明細.販売単価, 注文明細.数量 * 注文明細.販売単価,
    CASE WHEN 新商品モデル.新商品番号 IS NOT NULL THEN '新モデルあり'
    ELSE '' END
FROM 注文明細
    LEFT OUTER JOIN 新商品モデル
        ON 注文明細.商品番号 = 新商品モデル.旧商品番号
    INNER JOIN 商品 ON 注文明細.商品番号 = 商品.商品番号
WHERE 注文明細.注文番号 = :注文番号
ORDER BY 注文明細.注文明細番号