『SQLパズル 第2版』サポートページ

• 書籍の本文に掲載されているコードは、版元の翔泳社のサイトよりダウンロードできます。まずこちらからコードを取得しましょう。
• 以下に掲載するコードは、Oracle でも動作するように、テーブルの相関名を付けるときのキーワード「AS」を省略しています。
• OLAP 関数を使うコードは、現在のところ、Oracle、DB2、SQLServer（2005バージョン以上） でのみ動作します。PostgreSQL、MySQL では動きません。

CREATE TABLE FiscalYearTable1
(fiscal_year INTEGER NOT NULL PRIMARY KEY,
 start_date DATE NOT NULL,
    CONSTRAINT valid_start_date
    CHECK((EXTRACT (YEAR FROM start_date) = fiscal_year - 1)
               AND (EXTRACT (MONTH FROM start_date) = 10)
               AND (EXTRACT (DAY FROM start_date) = 01)),
 end_date DATE NOT NULL,
    CONSTRAINT valid_end_date
    CHECK((EXTRACT (YEAR FROM end_date) = fiscal_year)
               AND (EXTRACT (MONTH FROM end_date) = 09)
               AND (EXTRACT (DAY FROM end_date) = 30)),
    CONSTRAINT valid_interval
    CHECK ((end_date - start_date) = INTERVAL '365' DAY));

--正常データ
INSERT INTO FiscalYearTable1 VALUES(1995, '1994-10-01', '1995-09-30');
INSERT INTO FiscalYearTable1 VALUES(1997, '1996-10-01', '1997-09-30');
INSERT INTO FiscalYearTable1 VALUES(1998, '1997-10-01', '1998-09-30');

--エラーデータ
INSERT INTO FiscalYearTable1 VALUES(1996, '1995-10-01', '1996-08-30');  -- 終了日が8月
INSERT INTO FiscalYearTable1 VALUES(1999, '1998-10-02', '1999-09-30');  -- 開始日が2日

• INTERVAL 定数を使う問題は、「2．欠勤」、「37．移動平均」、「45．ペパロニピザ」などを参照。
• PostgreSQL の日付データの書式については、「PostgreSQL 7.2.3 ユーザガイド」を参照。標準 SQL への準拠を意識する同 DB としては例外的に独特。
• MySQL の日付データの書式については、「MySQL 4.1 リファレンスマニュアル」を参照。

CREATE TABLE Personnel
 (emp_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE ExcuseList
 ( reason_code CHAR (40) NOT NULL PRIMARY KEY);

CREATE TABLE Absenteeism (
  emp_id INTEGER NOT NULL , 
  absent_date DATE NOT NULL, 
  reason_code CHAR (40) NOT NULL , 
  severity_points INTEGER NOT NULL CHECK (severity_points BETWEEN 0 AND 4), 
  PRIMARY KEY (emp_id, absent_date)); 

INSERT INTO Personnel VALUES(1);
INSERT INTO Personnel VALUES(2);

INSERT INTO Absenteeism VALUES(1, '2007-05-01', 'ずる', 4);
INSERT INTO Absenteeism VALUES(1, '2007-05-02', '病気', 2);   --0になる
INSERT INTO Absenteeism VALUES(1, '2007-05-03', '病気', 2);   --0になる
INSERT INTO Absenteeism VALUES(1, '2007-05-05', 'ケガ', 1);
INSERT INTO Absenteeism VALUES(1, '2007-05-06', '病気', 3);   --0になる
INSERT INTO Absenteeism VALUES(2, '2007-05-01', 'ずる', 4);
INSERT INTO Absenteeism VALUES(2, '2007-05-03', '病気', 2);
INSERT INTO Absenteeism VALUES(2, '2007-05-05', 'サボリ', 2);
INSERT INTO Absenteeism VALUES(2, '2007-05-06', 'サボリ', 2); --0になる

--連続する休みを病欠扱いにする
emp_id absent_d reason_code       severity_points
------ -------- ----------------- ----------------
     1 07-05-01 ずる                            4
     1 07-05-02 long term illness           0
     1 07-05-03 long term illness           0
     1 07-05-05 病気                            3
     1 07-05-06 long term illness           0
     2 07-05-03 病気                            2
     2 07-05-05 サボリ                          2
     2 07-05-06 long term illness           0

CREATE TABLE Procs(
 proc_id INTEGER,
 anest_name VARCHAR(64),
 start_time TIMESTAMP,
 end_time   TIMESTAMP );

INSERT INTO Procs VALUES( 10, 'Baker', '01-07-01 08:00', '01-07-01 11:00');
INSERT INTO Procs VALUES( 20, 'Baker', '01-07-01 09:00', '01-07-01 13:00');
INSERT INTO Procs VALUES( 30, 'Dow'  , '01-07-01 09:00', '01-07-01 15:30'); 
INSERT INTO Procs VALUES( 40, 'Dow'  , '01-07-01 08:00', '01-07-01 13:30'); 
INSERT INTO Procs VALUES( 50, 'Dow'  , '01-07-01 10:00', '01-07-01 11:30'); 
INSERT INTO Procs VALUES( 60, 'Dow'  , '01-07-01 12:30', '01-07-01 13:30'); 
INSERT INTO Procs VALUES( 70, 'Dow'  , '01-07-01 13:30', '01-07-01 14:30'); 
INSERT INTO Procs VALUES( 80, 'Dow'  , '01-07-01 18:00', '01-07-01 19:00'); 

--答え１用
CREATE TABLE Badges
(badge_nbr INTEGER NOT NULL PRIMARY KEY,
 emp_id INTEGER NOT NULL,
 issued_date DATE NOT NULL,
 badge_status CHAR(1) NOT NULL
    CHECK (badge_status IN ('A', 'I')));

INSERT INTO Badges VALUES(100, 1, '2007-01-01', 'I');
INSERT INTO Badges VALUES(200, 1, '2007-02-01', 'I');  --社員1番の最新バッジ
INSERT INTO Badges VALUES(300, 2, '2007-03-01', 'I');  --社員2番の最新バッジ
INSERT INTO Badges VALUES(400, 3, '2007-03-01', 'I');  
INSERT INTO Badges VALUES(500, 3, '2007-04-01', 'I');  
INSERT INTO Badges VALUES(600, 3, '2007-05-01', 'I');  --社員3番の最新バッジ


--答え２用
CREATE TABLE Badges
(badge_nbr INTEGER NOT NULL PRIMARY KEY, 
 emp_id INTEGER NOT NULL, 
 issued_date DATE NOT NULL, 
 badge_seq INTEGER NOT NULL CHECK (badge_seq > 0), 
    UNIQUE (emp_id, badge_seq));

INSERT INTO Badges VALUES(100, 1, '2007-01-01', 1);
INSERT INTO Badges VALUES(200, 1, '2007-02-01', 2);  --社員1番の最新バッジ
INSERT INTO Badges VALUES(10,  2, '2007-03-01', 1);  --社員2番の最新バッジ
INSERT INTO Badges VALUES(2000,3, '2007-03-01', 1);  
INSERT INTO Badges VALUES(3,   3, '2007-04-01', 2);  
INSERT INTO Badges VALUES(50,  3, '2007-05-01', 3);  --社員3番の最新バッジ

-- アクティブなバッジを割り振る

badge_nbr emp_id issued_d b
--------- ------ -------- -
      100      1 07-01-01 I
      200      1 07-02-01 A
      300      2 07-03-01 A
      400      3 07-03-01 I
      500      3 07-04-01 I
      600      3 07-05-01 A

--最大の連番を持つのがアクティブなバッジ
SELECT emp_id, badge_nbr
  FROM Badges  B1
 WHERE badge_seq = (SELECT MAX(badge_seq)
                      FROM Badges B2
                     WHERE B1.emp_id = B2.emp_id);

emp_id badge_nbr
------ ---------
     1       200
     2        10
     3        50

--DB2でのみ可能
UPDATE Badges
   SET badge_seq = ROW_NUMBER()
                   OVER (PARTITION BY emp_id ORDER BY badge_seq);

• 連番を割り振るサブクエリの動作については、「自己結合の使い方」（特に「ランキングを求める」）
• 同じ構造の再帰集合を使うのが、「35．在庫調整」

--答え２用
--PostgreSQL
CREATE TABLE Foobar
(all_alpha VARCHAR(6) NOT NULL 
    CHECK (all_alpha SIMILAR TO '[a-zA-Z]+') );

--Oracle
CREATE TABLE Foobar
(all_alpha VARCHAR(6) NOT NULL 
    CHECK (REGEXP_LIKE(all_alpha, '^[a-zA-Z]+$') ));

--正常データ
INSERT INTO Foobar VALUES('');    --Oracleでは正常だが、PostgreSQLではエラーと見なされる
INSERT INTO Foobar VALUES('a');
INSERT INTO Foobar VALUES('A');
INSERT INTO Foobar VALUES('aA');
INSERT INTO Foobar VALUES('abcdef');
INSERT INTO Foobar VALUES('ABCDEF');
INSERT INTO Foobar VALUES('AbCdEf');

--エラーデータ
INSERT INTO Foobar VALUES(NULL);
INSERT INTO Foobar VALUES('a1aaaa');
INSERT INTO Foobar VALUES('ceeg0d');
INSERT INTO Foobar VALUES('Aあ');
INSERT INTO Foobar VALUES('123456');
INSERT INTO Foobar VALUES('s23');
INSERT INTO Foobar VALUES('1a3456');
INSERT INTO Foobar VALUES('N23456');

CREATE TABLE Hotel
(room_nbr INTEGER NOT NULL,
 arrival_date DATE NOT NULL,
 departure_date DATE NOT NULL,
 guest_name CHAR(30) NOT NULL,
    PRIMARY KEY (room_nbr, arrival_date),
    CHECK (departure_date >= arrival_date));

-- OK
INSERT INTO HotelStays VALUES (1, '2008-01-01', '2008-01-03', 'Coe');
-- OK
INSERT INTO HotelStays VALUES (1, '2008-01-03', '2008-01-05', 'Doe');
-- 失敗！ Coe氏とかぶる
INSERT INTO HotelStays VALUES (1, '2008-01-02', '2008-01-05', 'Roe');

スティーブの考えた隣接リストモデルのデータ構造(chain=1のケース)

• 入れ子集合モデルについては、「SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル」
• 同じくポインタ・チェインの構造を扱う問題として、32．税金の計算
• SQL という言語がアドレスからプログラミングを解放することを目的に登場した歴史的背景については、「アドレス、この巨大な怪物」をどうぞ。

--答え１用
CREATE TABLE PrinterControl
(user_id_start CHAR(10) NOT NULL,
 user_id_finish CHAR(10) NOT NULL,
 printer_name CHAR(4) NOT NULL,
 printer_description CHAR(40) NOT NULL,
    PRIMARY KEY (user_id_start, user_id_finish));

INSERT INTO PrinterControl VALUES( 'chacha', 'chacha', 'LPT1', '一階のプリンタ' );
INSERT INTO PrinterControl VALUES( 'lee'   , 'lee'   , 'LPT2', '二階のプリンタ' ); 
INSERT INTO PrinterControl VALUES( 'thomas', 'thomas', 'LPT3', '三階のプリンタ' ); 
INSERT INTO PrinterControl VALUES( 'a',   'mzzzzzzz' , 'LPT4', '共有プリンタ #1' ); 
INSERT INTO PrinterControl VALUES( 'n',   'zzzzzzzz' , 'LPT5', '共有プリンタ #2' ); 

--答え２、３用
CREATE TABLE PrinterControl
(user_id CHAR(10), -- NULLは空きプリンタを意味する
 printer_name CHAR(4) NOT NULL PRIMARY KEY,
 printer_description CHAR(40) NOT NULL);

INSERT INTO PrinterControl VALUES( 'chacha',  'LPT1',  '一階のプリンタ');
INSERT INTO PrinterControl VALUES( 'lee'   ,  'LPT2',  '二階のプリンタ'); 
INSERT INTO PrinterControl VALUES( 'thomas',  'LPT3',  '三階のプリンタ' );
INSERT INTO PrinterControl VALUES( NULL    ,  'LPT4',  '共有プリンタ' );
INSERT INTO PrinterControl VALUES( NULL    ,  'LPT5',  '共有プリンタ' );

--答え４用
CREATE TABLE PrinterControl
(user_id CHAR(10), -- NULLは空きプリンタを意味する
 printer_name CHAR(4) NOT NULL PRIMARY KEY,
 assignable_flag CHAR(1) DEFAULT 'Y' NOT NULL
    CHECK (assignable_flag IN ('Y', 'N')),
 printer_description CHAR(40) NOT NULL);

INSERT INTO PrinterControl VALUES( 'chacha',  'LPT1',  'N', '一階のプリンタ');
INSERT INTO PrinterControl VALUES( 'lee'   ,  'LPT2',  'N', '二階のプリンタ'); 
INSERT INTO PrinterControl VALUES( 'thomas',  'LPT3',  'N', '三階のプリンタ' );
INSERT INTO PrinterControl VALUES( NULL    ,  'LPT4',  'Y', '共有プリンタ #1' );
INSERT INTO PrinterControl VALUES( NULL    ,  'LPT5',  'Y', '共有プリンタ #2' );

• NULL と空集合の扱いに関する SQL の欠点については、「存在と無 ―― 空集合とNULL」を参照

CREATE TABLE Restaurant( seat INTEGER );

INSERT INTO Restaurant VALUES(1);
INSERT INTO Restaurant VALUES(3);
INSERT INTO Restaurant VALUES(4);
INSERT INTO Restaurant VALUES(7);

--答え３の番兵用
INSERT INTO Restaurant VALUES(0);
INSERT INTO Restaurant VALUES(1001);

--答え４の結果：
start finish
----- ------
    2      2
    5      6

--答え５の結果：
seat rn available_seat_cnt
---- -- ------------------
   3  2                  1
   4  3                  1
   7  4                  3

• 同じく欠番探索のテーマについては、「57．欠番探しバージョン1」および「58．欠番探しバージョン2」を参照。

--○1番：1年間はたらかなった後、5年間フルにﾉ働いた
INSERT INTO Pensions VALUES('1', 2006, 12, 10);
INSERT INTO Pensions VALUES('1', 2005, 12, 10);
INSERT INTO Pensions VALUES('1', 2004, 12, 10);
INSERT INTO Pensions VALUES('1', 2003, 12, 10);
INSERT INTO Pensions VALUES('1', 2002, 12, 10);
INSERT INTO Pensions VALUES('1', 2001,  0,  0);

--○2番：10ヶ月 * 6年間
INSERT INTO Pensions VALUES('2', 2006, 10, 1);
INSERT INTO Pensions VALUES('2', 2005, 10, 1);
INSERT INTO Pensions VALUES('2', 2004, 10, 1);
INSERT INTO Pensions VALUES('2', 2003, 10, 1);
INSERT INTO Pensions VALUES('2', 2002, 10, 1);
INSERT INTO Pensions VALUES('2', 2001, 10, 1);

--×3番：10ヶ月 * 3年間, 1年休み, 10ヶ月 *  3
INSERT INTO Pensions VALUES('3', 2006, 10, 1);
INSERT INTO Pensions VALUES('3', 2005, 10, 1);
INSERT INTO Pensions VALUES('3', 2004, 10, 1);
INSERT INTO Pensions VALUES('3', 2003,  0, 0);
INSERT INTO Pensions VALUES('3', 2002, 10, 1);
INSERT INTO Pensions VALUES('3', 2001, 10, 1);
INSERT INTO Pensions VALUES('3', 2000, 10, 1);


--×4番：10ヶ月 * 1年間, 1年休み, 10ヶ月 *  5 + 9ヶ月
INSERT INTO Pensions VALUES('4', 2007,  9, 1);
INSERT INTO Pensions VALUES('4', 2006, 10, 1);
INSERT INTO Pensions VALUES('4', 2005, 10, 1);
INSERT INTO Pensions VALUES('4', 2004, 10, 1);
INSERT INTO Pensions VALUES('4', 2003, 10, 1);
INSERT INTO Pensions VALUES('4', 2002, 10, 1);
INSERT INTO Pensions VALUES('4', 2001,  0, 0);
INSERT INTO Pensions VALUES('4', 2000, 10, 1);

--○5番：10ヶ月 * 1年間, 1年休み, 10ヶ月 *  6
INSERT INTO Pensions VALUES('5', 2007, 10, 1);
INSERT INTO Pensions VALUES('5', 2006, 10, 1);
INSERT INTO Pensions VALUES('5', 2005, 10, 1);
INSERT INTO Pensions VALUES('5', 2004, 10, 1);
INSERT INTO Pensions VALUES('5', 2003, 10, 1);
INSERT INTO Pensions VALUES('5', 2002, 10, 1);
INSERT INTO Pensions VALUES('5', 2001,  0, 0);
INSERT INTO Pensions VALUES('5', 2000, 10, 1);

--×6番：12ヶ月 * 4年間, 1年休み
INSERT INTO Pensions VALUES('6', 2004, 0, 1);
INSERT INTO Pensions VALUES('6', 2003, 12, 1);
INSERT INTO Pensions VALUES('6', 2002, 12, 1);
INSERT INTO Pensions VALUES('6', 2001, 12, 0);
INSERT INTO Pensions VALUES('6', 2000, 12, 1);

--答え３用のビュー
CREATE VIEW PensionsView (sin, start_year, end_year, sumofearnings, sumofmonth_cnt) 
AS SELECT  PV0.sin,
  PV0.pen_year AS start_year,
  PV2.pen_year AS end_year,
  SUM(PV1.earnings),
  SUM(PV1.month_cnt)
  FROM Pensions PV0, Pensions PV1, Pensions PV2
 WHERE PV0.month_cnt > 0
   AND PV1.month_cnt > 0
   AND PV2.month_cnt > 0
   AND PV0.sin = PV1.sin
   AND PV0.sin = PV2.sin
   AND PV0.pen_year BETWEEN PV2.pen_year-59 AND (PV2.pen_year - 4)
   AND PV1.pen_year BETWEEN PV0.pen_year AND PV2.pen_year
GROUP BY PV0.sin, PV0.pen_year, PV2.pen_year
HAVING SUM(PV1.month_cnt) >= 60
   AND (PV2.pen_year - PV0.pen_year) = (COUNT(*) - 1);

CREATE TABLE Projects
(workorder_id CHAR(5) NOT NULL,
 step_nbr INTEGER NOT NULL
    CHECK (step_nbr BETWEEN 0 AND 1000),
 step_status CHAR(1) NOT NULL
    CHECK (step_status IN ('C', 'W')), -- Cは完了、Wは待機
    PRIMARY KEY (workorder_id, step_nbr));

INSERT INTO Projects VALUES('AA100', 0, 'C' );
INSERT INTO Projects VALUES('AA100', 1, 'W' );
INSERT INTO Projects VALUES('AA100', 2, 'W' );
INSERT INTO Projects VALUES('AA200', 0, 'W' );
INSERT INTO Projects VALUES('AA200', 1, 'W' );
INSERT INTO Projects VALUES('AA300', 0, 'C' );
INSERT INTO Projects VALUES('AA300', 1, 'C' );

workorder_id
------------
       AA100

--別解：述語論理の応用
SELECT DISTINCT workorder_id
  FROM Projects P1
 WHERE NOT EXISTS
       (SELECT step_status
          FROM Projects P2
         WHERE P1.workorder_id = P2. workorder_id     --プロジェクトごとに条件を調べる
           AND step_status <> CASE WHEN step_nbr = 0   --全称文を二重否定で表現する
                                      THEN 'C'
                                  ELSE 'W' END);

workorder_id
------------
       AA100

• 同じく HAVING で全称量化を扱う問題は、「20．テスト結果」を参照。

CREATE TABLE Claims(
   claim_id INTEGER, 
   patient_name VARCHAR(64),
   PRIMARY KEY(claim_id));

CREATE TABLE Defendants(
   claim_id INTEGER, 
   defendant_name VARCHAR(64),
   PRIMARY KEY(claim_id, defendant_name));

CREATE TABLE LegalEvents(
   claim_id INTEGER, 
   defendant_name VARCHAR(64),
   claim_status CHAR(2),
   change_date DATE,
   PRIMARY KEY(claim_id, defendant_name, claim_status));

CREATE TABLE ClaimStatusCodes(
   claim_status CHAR(2) PRIMARY KEY,
   claim_status_desc VARCHAR(64),
   claim_seq INTEGER);

INSERT INTO Claims VALUES( 10, 'Smith');
INSERT INTO Claims VALUES( 20, 'Jones');
INSERT INTO Claims VALUES( 30, 'Brown');

INSERT INTO Defendants VALUES (10, 'Johnson');
INSERT INTO Defendants VALUES (10, 'Meyer');
INSERT INTO Defendants VALUES (10, 'Dow');
INSERT INTO Defendants VALUES (20, 'Baker');
INSERT INTO Defendants VALUES (20, 'Meyer');
INSERT INTO Defendants VALUES (30, 'Johnson');

INSERT INTO LegalEvents VALUES(10,  'Johnson',  'AP', '1994-01-01' );
INSERT INTO LegalEvents VALUES(10,  'Johnson',  'OR', '1994-02-01' );
INSERT INTO LegalEvents VALUES(10,  'Johnson',  'SF', '1994-03-01' );
INSERT INTO LegalEvents VALUES(10,  'Johnson',  'CL', '1994-04-01' );
INSERT INTO LegalEvents VALUES(10,  'Meyer'  ,  'AP', '1994-01-01' );
INSERT INTO LegalEvents VALUES(10,  'Meyer'  ,  'OR', '1994-02-01' );
INSERT INTO LegalEvents VALUES(10,  'Meyer'  ,  'SF', '1994-03-01' );
INSERT INTO LegalEvents VALUES(10,  'Dow'    ,  'AP', '1994-01-01' );
INSERT INTO LegalEvents VALUES(10,  'Dow'    ,  'OR', '1994-02-01' );
INSERT INTO LegalEvents VALUES(20,  'Meyer'  ,  'AP', '1994-01-01' );
INSERT INTO LegalEvents VALUES(20,  'Meyer'  ,  'OR', '1994-02-01' );
INSERT INTO LegalEvents VALUES(20,  'Baker'  ,  'AP', '1994-01-01' );
INSERT INTO LegalEvents VALUES(30,  'Johnson',  'AP', '1994-01-01' );

INSERT INTO ClaimStatusCodes VALUES('AP', 'Awaiting review panel' ,  1);
INSERT INTO ClaimStatusCodes VALUES('OR', 'Panel opinion rendered',  2);
INSERT INTO ClaimStatusCodes VALUES('SF', 'Suit filed'            ,  3);
INSERT INTO ClaimStatusCodes VALUES('CL', 'closed'                ,  4);

CREATE TABLE Register
(course_nbr INTEGER NOT NULL,
 student_name CHAR(10) NOT NULL,
 teacher_name CHAR(10) NOT NULL);

INSERT INTO Register VALUES(10, '生徒１', '先生１');

INSERT INTO Register VALUES(20, '生徒１', '先生１');
INSERT INTO Register VALUES(20, '生徒１', '先生２');

INSERT INTO Register VALUES(30, '生徒１', '先生１');
INSERT INTO Register VALUES(30, '生徒１', '先生２');
INSERT INTO Register VALUES(30, '生徒１', '先生３');

--答え２の結果
course_nbr student  teacher_1 teacher_2
---------- -------- --------- ---------
       10     生徒１    先生１
        20     生徒１    先生１        先生２
        30     生徒１    先生１      --More--

course_nbr student teacher
---------- ------- -------
        10  生徒１  先生１
         20  生徒１  先生１
                     先生２
         30  生徒１  先生１
                     先生２
               	   先生３

CREATE TABLE Personnel
(emp_id INTEGER PRIMARY KEY,
 first_name CHAR(20) NOT NULL,
 last_name CHAR(20) NOT NULL);

CREATE TABLE Phones
(emp_id INTEGER NOT NULL,
 phone_type CHAR(3) NOT NULL
    CHECK (phone_type IN ('hom', 'fax')),
 phone_nbr CHAR(12) NOT NULL,
    PRIMARY KEY (emp_id, phone_type),
    FOREIGN KEY (emp_id) REFERENCES Personnel(emp_id));

INSERT INTO Personnel VALUES(1, '山田', '太郎');
INSERT INTO Personnel VALUES(2, '上野', '二郎');
INSERT INTO Personnel VALUES(3, '高田', '三郎');
INSERT INTO Personnel VALUES(4, '松岡', '四郎');

INSERT INTO Phones VALUES(1, 'hom', 1111);
INSERT INTO Phones VALUES(1, 'fax', 2222);
INSERT INTO Phones VALUES(2, 'hom', 3333);
INSERT INTO Phones VALUES(3, 'fax', 4444);

結果：
last_name first_name home_phone fax_phone
--------- ---------- ---------- ---------
太郎      山田           1111       2222
二郎      上野           3333
三郎      高田                        4444
四郎      松岡

CREATE TABLE Salaries
(emp_name CHAR(10) NOT NULL,
 sal_date DATE NOT NULL,
 sal_amt DECIMAL (8,2) NOT NULL,
    PRIMARY KEY (emp_name, sal_date));

INSERT INTO Salaries VALUES ('トム', '1996-06-20', 500.00); 
INSERT INTO Salaries VALUES ('トム', '1996-08-20', 700.00); 
INSERT INTO Salaries VALUES ('トム', '1996-10-20', 800.00); 
INSERT INTO Salaries VALUES ('トム', '1996-12-20', 900.00); 
INSERT INTO Salaries VALUES ('ディック', '1996-06-20', 500.00); 
INSERT INTO Salaries VALUES ('ハリー','1996-07-20',500.00); 
INSERT INTO Salaries VALUES ('ハリー', '1996-09-20', 700.00); 

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
 emp_name CHAR(20) NOT NULL,
 mech_type CHAR(10) NOT NULL
    CHECK (mech_type IN ('Primary', 'Assistant')),
    UNIQUE (emp_id, mech_type));

CREATE TABLE Jobs
(job_id INTEGER NOT NULL PRIMARY KEY REFERENCES Teams (job_id),
 start_date DATE NOT NULL);

CREATE TABLE Teams
(job_id INTEGER NOT NULL REFERENCES Jobs(job_id),
 primary_mech INTEGER NOT NULL,
 primary_type CHAR(10) DEFAULT 'Primary' NOT NULL
    CHECK (primary_type = 'Primary'),
 assist_mech INTEGER NOT NULL ,
 assist_type CHAR(10) DEFAULT 'Assistant' NOT NULL
    CHECK (assist_type = 'Assistant') ,
    CONSTRAINT fk_primary FOREIGN KEY (primary_mech, primary_type)
        REFERENCES Personnel(emp_id, mech_type),
    CONSTRAINT fk_assist FOREIGN KEY (assist_mech, assist_type)
        REFERENCES Personnel(emp_id, mech_type),
    CONSTRAINT at_least_one_mechanic
        CHECK(COALESCE (primary_mech, assist_mech) IS NOT NULL)) ;

INSERT INTO Personnel VALUES(1, '赤木',  'Primary');
INSERT INTO Personnel VALUES(2, '伊藤',  'Assistant');
INSERT INTO Personnel VALUES(3, '宇佐美','Primary');

INSERT INTO Jobs VALUES(1, '2007-01-01');
INSERT INTO Jobs VALUES(2, '2007-02-01');
INSERT INTO Jobs VALUES(3, '2007-03-01');
INSERT INTO Jobs VALUES(4, '2007-04-01');

INSERT INTO Teams VALUES(1, 1, 'Primary', 2, 'Assistant');
INSERT INTO Teams VALUES(2, 3, 'Primary', 2, 'Assistant');

CREATE TABLE CandidateSkills
(candidate_id INTEGER NOT NULL,
 skill_code CHAR(15) NOT NULL,
    PRIMARY KEY (candidate_id, skill_code));

CREATE TABLE JobOrders
(job_id INTEGER NOT NULL,
 skill_group INTEGER NOT NULL,
 skill_code CHAR(15) NOT NULL,
    PRIMARY KEY (job_id, skill_group, skill_code));

INSERT INTO CandidateSkills VALUES(100, '会計');
INSERT INTO CandidateSkills VALUES(100, '在庫管理');
INSERT INTO CandidateSkills VALUES(100, '製造'); 
INSERT INTO CandidateSkills VALUES(200, '会計');
INSERT INTO CandidateSkills VALUES(200, '在庫管理');
INSERT INTO CandidateSkills VALUES(300, '製造');
INSERT INTO CandidateSkills VALUES(400, '在庫管理');
INSERT INTO CandidateSkills VALUES(400, '製造'); 
INSERT INTO CandidateSkills VALUES(500, '会計'); 
INSERT INTO CandidateSkills VALUES(500, '製造');

INSERT INTO JobOrders VALUES(1, 1, '在庫管理');
INSERT INTO JobOrders VALUES(1, 1, '製造'); 
INSERT INTO JobOrders VALUES(1, 2, '会計');
INSERT INTO JobOrders VALUES(2, 1, '在庫管理');
INSERT INTO JobOrders VALUES(2, 1, '製造'); 
INSERT INTO JobOrders VALUES(2, 2, '会計'); 
INSERT INTO JobOrders VALUES(2, 2, '製造'); 
INSERT INTO JobOrders VALUES(3, 1, '製造');
INSERT INTO JobOrders VALUES(4, 1, '在庫管理');
INSERT INTO JobOrders VALUES(4, 1, '製造'); 
INSERT INTO JobOrders VALUES(4, 1, '会計'); 

--答え１
SELECT DISTINCT C1.candidate_id, 'job_id #2' -- 仕事IDコードの定数
  FROM CandidateSkills C1, -- スキル1つにつき1テーブル
       CandidateSkills C2, 
       CandidateSkills C3,
       CandidateSkills C4
WHERE C1.candidate_id = C2.candidate_id
  AND C1.candidate_id = C3.candidate_id
  AND C1.candidate_id = C4.candidate_id
  AND -- 以下で紹介依頼を表す式を作る
     (   (    C1.skill_code = '在庫管理'
          AND C2.skill_code = '製造')
      OR (    C3.skill_code = '会計'
          AND C4.skill_code = '製造'));

candidate_id job_id#2
------------ ---------
         100 job_id #2
         400 job_id #2
         500 job_id #2

--答え２
SELECT DISTINCT J1.job_id, C1.candidate_id
  FROM JobOrders J1 INNER JOIN CandidateSkills C1
    ON J1.skill_code = C1.skill_code
 GROUP BY candidate_id, skill_group, job_id
HAVING COUNT(*) >= (SELECT COUNT(*)
                      FROM JobOrders J2
                     WHERE J1.skill_group = J2.skill_group
                       AND J1.job_id = J2.job_id);

CREATE TABLE Consumers
(conname VARCHAR(64),
 address VARCHAR(64),
 con_id  INTEGER,
 fam     INTEGER);

INSERT INTO Consumers VALUES('ボブ',        'A', 1, NULL );
INSERT INTO Consumers VALUES('ジョー',      'B', 3, NULL );
INSERT INTO Consumers VALUES('マーク' ,     'C', 5, NULL );
INSERT INTO Consumers VALUES('メアリー',    'A', 2,    1 );
INSERT INTO Consumers VALUES('ヴィッキー',  'B', 4,    3 );
INSERT INTO Consumers VALUES('ウェイン',    'D', 6, NULL );

conname   addres  con_id  fam
--------- ------- ------  ----
マーク        C         5
メアリー      A         2      1
ヴィッキー    B         4      3
ウェイン      D         6

CREATE TABLE SalesData
(district_nbr INTEGER NOT NULL,
 sales_person CHAR(10) NOT NULL,
 sales_id INTEGER NOT NULL,
 sales_amt DECIMAL(5,2) NOT NULL);

INSERT INTO SalesData VALUES(1, 'カーリー'     ,   5,  3.00  );
INSERT INTO SalesData VALUES(1, 'ハーポ'       ,  11,  4.00  );
INSERT INTO SalesData VALUES(1, 'ラリー'       ,   1, 50.00  );
INSERT INTO SalesData VALUES(1, 'ラリー'       ,   2, 50.00  );
INSERT INTO SalesData VALUES(1, 'ラリー'       ,   3, 50.00  );
INSERT INTO SalesData VALUES(1, 'モー'         ,   4,  5.00  );
INSERT INTO SalesData VALUES(2, 'ディック'     ,   8,  5.00  );
INSERT INTO SalesData VALUES(2, 'フレッド'     ,   7,  5.00  );
INSERT INTO SalesData VALUES(2, 'ハリー'       ,   6,  5.00  );
INSERT INTO SalesData VALUES(2, 'トム'         ,   7,  5.00  );
INSERT INTO SalesData VALUES(3, 'アーヴィン' ,  10,  5.00  );
INSERT INTO SalesData VALUES(3, 'メルヴィン'   ,   9,  7.00  );
INSERT INTO SalesData VALUES(4, 'ジェニー'     ,  15, 20.00  );
INSERT INTO SalesData VALUES(4, 'ジェシー'     ,  16, 10.00  );
INSERT INTO SalesData VALUES(4, 'メアリー'     ,  12, 50.00  );
INSERT INTO SalesData VALUES(4, 'オプラ'       ,  14, 30.00  );
INSERT INTO SalesData VALUES(4, 'サリー'       ,  13, 40.00  );

CREATE TABLE TestResults
(test_name CHAR(20) NOT NULL,
 test_step INTEGER NOT NULL,
 comp_date DATE, -- NULLは未完了を意味する
    PRIMARY KEY (test_name, test_step));

INSERT INTO TestResults VALUES('読解', 1, '2006-03-10');
INSERT INTO TestResults VALUES('読解', 2, '2006-03-12');
INSERT INTO TestResults VALUES('数学', 1, NULL);
INSERT INTO TestResults VALUES('数学', 2, '2006-03-12');
INSERT INTO TestResults VALUES('化学', 1, '2006-03-08');
INSERT INTO TestResults VALUES('化学', 2, '2006-03-12');
INSERT INTO TestResults VALUES('化学', 3, '2006-03-15');

--答え２：CASE式版
SELECT test_name
  FROM TestResults
 GROUP BY test_name
HAVING COUNT(*) = SUM(CASE WHEN comp_date IS NOT NULL 
                           THEN 1 
                           ELSE 0 END);

• 「HAVING句の力」の「NULLを含まない集合を探す」を参照。
• 同じく HAVING で全称量化を扱う問題は、「11．作業依頼」を参照。

CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
 plane CHAR(15) NOT NULL,
    PRIMARY KEY (pilot, plane));

CREATE TABLE Hangar
(plane CHAR(15) PRIMARY KEY);

INSERT INTO PilotSkills VALUES ('Celko', 'Piper Cub');
INSERT INTO PilotSkills VALUES ('Higgins', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Higgins', 'F-14 Fighter');
INSERT INTO PilotSkills VALUES ('Higgins', 'Piper Cub');
INSERT INTO PilotSkills VALUES ('Jones', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Jones', 'F-14 Bomber');
INSERT INTO PilotSkills VALUES ('Smith', 'B-1 Bomber');
INSERT INTO PilotSkills VALUES ('Smith', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Smith', 'F-14 Fighter');
INSERT INTO PilotSkills VALUES ('Wilson', 'B-1 Bomber');
INSERT INTO PilotSkills VALUES ('Wilson', 'B-52 Bomber');
INSERT INTO PilotSkills VALUES ('Wilson', 'F-14 Fighter');
INSERT INTO PilotSkills VALUES ('Wilson', 'F-17 Fighter');

INSERT INTO Hangar VALUES ('B-1 Bomber');
INSERT INTO Hangar VALUES ('B-52 Bomber');
INSERT INTO Hangar VALUES ('F-14 Fighter');

--関係除算：減算に還元して解く
SELECT DISTINCT pilot
  FROM PilotSkills PS1
 WHERE NOT EXISTS
        (SELECT plane
           FROM Hangar
         EXCEPT
         SELECT plane
           FROM PilotSkills PS2
          WHERE PS1.pilot = PS2.pilot);

pilot
-------
Smith
Wilson

• 差集合演算によって関係除算を行う方法の解説は、「SQLで集合演算」
• 関係除算の詳しい解説は、「HAVING句の力」
• 本書のほかの問題での関係除算の応用については、「17．人材紹介会社」、「31．すべての製品を購入した顧客」、「39．保険損失」、「50．3分の2」
• 関係除算を多次元に拡張するという面白いパズルが、「64．ボックス」
• 本文で言及されているシェリル・ラーセンの記事は、「Powerful SQL: Beyond the Basics」。しかし1996年のものなので、今となっては数字は古いかも。

CREATE TABLE Tenants
 (tenant_id INTEGER,
  unit_nbr  INTEGER,
  vacated_date DATE,
  PRIMARY KEY (tenant_id, unit_nbr));

CREATE TABLE Units
 (complex_id INTEGER,
  unit_nbr   INTEGER,
  PRIMARY KEY (complex_id, unit_nbr));

CREATE TABLE RentPayments
 (tenant_id   INTEGER,
  unit_nbr   INTEGER,
  payment_date   DATE,
  PRIMARY KEY (tenant_id, unit_nbr));

--サンプルデータ
INSERT INTO Tenants VALUES(1, 1, NULL);
INSERT INTO Tenants VALUES(1, 2, NULL);
INSERT INTO Tenants VALUES(1, 3, '2007-01-01');

INSERT INTO Units VALUES(32, 1);
INSERT INTO Units VALUES(32, 2);
INSERT INTO Units VALUES(32, 3);

/* ユニット１は家賃を払っている。２は払っていない */
INSERT INTO RentPayments VALUES(1, 1, '2007-03-01');

CREATE TABLE Titles
(product_id INTEGER NOT NULL PRIMARY KEY,
 magazine_sku INTEGER NOT NULL,
 issn INTEGER NOT NULL,
 issn_year INTEGER NOT NULL);

CREATE TABLE Newsstands
 (stand_nbr INTEGER NOT NULL PRIMARY KEY,
  stand_name CHAR(20) NOT NULL);

CREATE TABLE Sales
(product_id   INTEGER NOT NULL REFERENCES Titles(product_id),
 stand_nbr    INTEGER NOT NULL REFERENCES Newsstands(stand_nbr),
 net_sold_qty INTEGER NOT NULL,
 PRIMARY KEY(product_id, stand_nbr));

INSERT INTO Titles VALUES(1, 12345, 1, 2006);
INSERT INTO Titles VALUES(2, 2667,  1, 2006);
INSERT INTO Titles VALUES(3, 48632, 1, 2006);
INSERT INTO Titles VALUES(4, 1107,  1, 2006);
INSERT INTO Titles VALUES(5, 12345, 2, 2006);
INSERT INTO Titles VALUES(6, 2667,  2, 2006);
INSERT INTO Titles VALUES(7, 48632, 2, 2006);
INSERT INTO Titles VALUES(8, 1107,  2, 2006);

INSERT INTO Newsstands VALUES(1, 'Newsstands1');
INSERT INTO Newsstands VALUES(2, 'Newsstands2');
INSERT INTO Newsstands VALUES(3, 'Newsstands3');
INSERT INTO Newsstands VALUES(4, 'Newsstands4');

INSERT INTO Sales VALUES(1, 1, 1);
INSERT INTO Sales VALUES(2, 1, 4);
INSERT INTO Sales VALUES(3, 1, 1);
INSERT INTO Sales VALUES(4, 1, 1);
INSERT INTO Sales VALUES(5, 1, 1);
INSERT INTO Sales VALUES(6, 1, 2);
INSERT INTO Sales VALUES(7, 1, 1);
INSERT INTO Sales VALUES(3, 2, 1);
INSERT INTO Sales VALUES(4, 2, 5);
INSERT INTO Sales VALUES(8, 2, 6);
INSERT INTO Sales VALUES(1, 3, 1);
INSERT INTO Sales VALUES(2, 3, 3);
INSERT INTO Sales VALUES(3, 3, 3);
INSERT INTO Sales VALUES(4, 3, 1);
INSERT INTO Sales VALUES(5, 3, 1);
INSERT INTO Sales VALUES(6, 3, 3);
INSERT INTO Sales VALUES(7, 3, 3);
INSERT INTO Sales VALUES(1, 4, 1);
INSERT INTO Sales VALUES(2, 4, 1);
INSERT INTO Sales VALUES(3, 4, 4);
INSERT INTO Sales VALUES(4, 4, 1);
INSERT INTO Sales VALUES(5, 4, 1);
INSERT INTO Sales VALUES(6, 4, 1);
INSERT INTO Sales VALUES(7, 4, 2);

CREATE TABLE MyTable
(keycol INTEGER NOT NULL,
 f1 INTEGER NOT NULL,
 f2 INTEGER NOT NULL,
 f3 INTEGER NOT NULL,
 f4 INTEGER NOT NULL,
 f5 INTEGER NOT NULL,
 f6 INTEGER NOT NULL,
 f7 INTEGER NOT NULL,
 f8 INTEGER NOT NULL,
 f9 INTEGER NOT NULL,
 f10 INTEGER NOT NULL);

--選択対象
INSERT INTO MyTable VALUES(333, 0, -1, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO MyTable VALUES(444, 0, 0, 0, 0, 0, 0, 0, 0, 0, 9);
INSERT INTO MyTable VALUES(999, 0, 0, 0, 0, 6, 0, 0, 0, 0, 0);

--選択対象外
INSERT INTO MyTable VALUES(555, 1, -1, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO MyTable VALUES(666, 1, 2, 3, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO MyTable VALUES(777, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

SELECT * 
  FROM MyTable 
 WHERE (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10) IN 
          ((f1, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
           (0, f2, 0, 0, 0, 0, 0, 0, 0, 0), 
           (0, 0, f3, 0, 0, 0, 0, 0, 0, 0), 
           (0, 0, 0, f4, 0, 0, 0, 0, 0, 0), 
           (0, 0, 0, 0, f5, 0, 0, 0, 0, 0), 
           (0, 0, 0, 0, 0, f6, 0, 0, 0, 0), 
           (0, 0, 0, 0, 0, 0, f7, 0, 0, 0), 
           (0, 0, 0, 0, 0, 0, 0, f8, 0, 0), 
           (0, 0, 0, 0, 0, 0, 0, 0, f9, 0), 
           (0, 0, 0, 0, 0, 0, 0, 0, 0, f10)) 
   AND (f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9 + f10) <> 0; 

--答え＃４
SELECT * 
  FROM MyTable 
 WHERE 0 IN ((f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9 + f10), 
             (f1 + f3 + f4 + f5 + f6 + f7 + f8 + f9 + f10), 
             (f1 + f2 + f4 + f5 + f6 + f7 + f8 + f9 + f10), 
             (f1 + f2 + f3 + f5 + f6 + f7 + f8 + f9 + f10), 
             (f1 + f2 + f3 + f4 + f6 + f7 + f8 + f9 + f10), 
             (f1 + f2 + f3 + f4 + f5 + f7 + f8 + f9 + f10), 
             (f1 + f2 + f3 + f4 + f5 + f6 + f8 + f9 + f10), 
             (f1 + f2 + f3 + f4 + f5 + f6 + f7 + f9 + f10), 
             (f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f10), 
             (f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9))
   AND (f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9 + f10) <> 0;

keycol f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
------ -- -- -- -- -- -- -- -- -- ---
333     0 -1  0  0  0  0  0  0  0  0
444     0  0  0  0  0  0  0  0  0  9
999     0  0  0  0  6  0  0  0  0  0

• なんちゃって配列なテーブルに対処する問題は、「39．保険損失」、「73．データのクリーニング」も参照。後者では COALESCE が大活躍です。
• COALESCE の見事な応用例としては「53．テーブルを列ごとに折りたたむ」も見逃せない
• ある意味、配列を扱うのが、「66．数独パズル」。

CREATE TABLE ServicesSchedule
(shop_id CHAR(3) NOT NULL,
 order_nbr CHAR(10) NOT NULL,
 sch_seq INTEGER NOT NULL CHECK (sch_seq IN (1,2,3)),
 service_type CHAR(2) NOT NULL,
 sch_date DATE,
    PRIMARY KEY (shop_id, order_nbr, sch_seq));

INSERT INTO ServicesSchedule VALUES('002',   '4155526710',   1,   '01', '1994-07-16' );
INSERT INTO ServicesSchedule VALUES('002',   '4155526710',   2,   '01', '1994-07-30' ); 
INSERT INTO ServicesSchedule VALUES('002',   '4155526710',   3,   '01', '1994-10-01' );
INSERT INTO ServicesSchedule VALUES('002',   '4155526711',   1,   '01', '1994-07-16' ); 
INSERT INTO ServicesSchedule VALUES('002',   '4155526711',   2,   '01', '1994-07-30' ); 
INSERT INTO ServicesSchedule VALUES('002',   '4155526711',   3,   '01',  NULL ); 

結果：
order_nbr  processed completed confirmed
---------- --------  --------  --------
4155526710 94-07-16  94-07-30  94-10-01

CREATE TABLE DataFlowDiagrams
(diagram_name CHAR(10) NOT NULL,
 bubble_name CHAR(10) NOT NULL,
 flow_name CHAR(10) NOT NULL,
    PRIMARY KEY (diagram_name, bubble_name, flow_name));

INSERT INTO DataFlowDiagrams VALUES('Proc1',  'input' ,   'guesses');
INSERT INTO DataFlowDiagrams VALUES('Proc1',  'input' ,   'opinions'); 
INSERT INTO DataFlowDiagrams VALUES('Proc1',  'crunch',   'facts');
INSERT INTO DataFlowDiagrams VALUES('Proc1',  'crunch',   'guesses');
INSERT INTO DataFlowDiagrams VALUES('Proc1',  'crunch',   'opinions' );
INSERT INTO DataFlowDiagrams VALUES('Proc1',  'output',   'facts');
INSERT INTO DataFlowDiagrams VALUES('Proc1',  'output',   'guesses');
INSERT INTO DataFlowDiagrams VALUES('Proc2',  'reckon',   'guesses');
INSERT INTO DataFlowDiagrams VALUES('Proc2',  'reckon',   'opinions' );

結果：
diagram bubble flow
------- ------ --------
Proc1   input  facts
Proc1   output opinions
Proc2   reckon facts

• 同じく「存在しないデータ」を求める問題として、42．魚のサンプリング調査も参照。

CREATE TABLE SupParts
(sno INTEGER NOT NULL,
 pno INTEGER NOT NULL,
    PRIMARY KEY (sno, pno));

--1と3、2と4が一致する
INSERT INTO SupParts VALUES(1, 01);
INSERT INTO SupParts VALUES(1, 02);
INSERT INTO SupParts VALUES(1, 03);

INSERT INTO SupParts VALUES(2, 01);
INSERT INTO SupParts VALUES(2, 03);

INSERT INTO SupParts VALUES(3, 01);
INSERT INTO SupParts VALUES(3, 02);
INSERT INTO SupParts VALUES(3, 03);

INSERT INTO SupParts VALUES(4, 01);
INSERT INTO SupParts VALUES(4, 03);

INSERT INTO SupParts VALUES(5, 05);

INSERT INTO SupParts VALUES(6, 01);
INSERT INTO SupParts VALUES(6, 02);


--答え８用
CREATE TABLE Tbl_A
 (key  INTEGER PRIMARY KEY,
  col1 INTEGER , 
  col2 INTEGER, 
  col3 INTEGER);

CREATE TABLE Tbl_B
 (key  INTEGER PRIMARY KEY,
  col1 INTEGER, 
  col2 INTEGER, 
  col3 INTEGER);

INSERT INTO Tbl_A VALUES(1, 2, 3, 4);
INSERT INTO Tbl_A VALUES(2, 0, 0, NULL);
INSERT INTO Tbl_A VALUES(3, 1, 1, 1);

INSERT INTO Tbl_B VALUES(1, 2, 3, 4);
INSERT INTO Tbl_B VALUES(2, 0, 0, 0);
INSERT INTO Tbl_B VALUES(3, 1, 1, 1);

sno sno
--- ---
  1   3
  2   4

• 自己非等値結合による組み合わせの作り方は、「自己結合の使い方」を参照。
• 同じ問題を「SQLで集合演算」でも取り上げています。

CREATE TABLE Sine
(x REAL NOT NULL,
 sin REAL NOT NULL);

INSERT INTO Sine VALUES (0.00, 0.0000);
INSERT INTO Sine VALUES (0.75, 0.6816); 
INSERT INTO Sine VALUES (0.76, 0.6889);
INSERT INTO Sine VALUES (1.00, 1.0000);

• テーブル駆動型の解法は、68．バスを待ちながらでもう一度登場します。

CREATE TABLE Payroll
(check_nbr INTEGER NOT NULL PRIMARY KEY,
 check_amt DECIMAL(8,2) NOT NULL);

INSERT INTO Payroll VALUES(1, 100);
INSERT INTO Payroll VALUES(2, 100);
INSERT INTO Payroll VALUES(3, 200);
INSERT INTO Payroll VALUES(4, 150);
INSERT INTO Payroll VALUES(5, 300);
INSERT INTO Payroll VALUES(6, 150);
INSERT INTO Payroll VALUES(7, 300);
INSERT INTO Payroll VALUES(8, 300);
INSERT INTO Payroll VALUES(9, 100);

結果：
check_amt check_cnt
--------- ---------
      100         3
      300         3

• 最頻値の求め方についての詳しい解説は、「HAVING句の力」

CREATE TABLE Sales
(customer_name CHAR(5) NOT NULL,
sale_date DATE NOT NULL,
PRIMARY KEY (customer_name, sale_date));

INSERT INTO Sales VALUES('Fred', '1994-06-01');
INSERT INTO Sales VALUES('Mary', '1994-06-01');
INSERT INTO Sales VALUES('Bill', '1994-06-01');
INSERT INTO Sales VALUES('Fred', '1994-06-02');
INSERT INTO Sales VALUES('Bill', '1994-06-02');
INSERT INTO Sales VALUES('Bill', '1994-06-03');
INSERT INTO Sales VALUES('Bill', '1994-06-04');
INSERT INTO Sales VALUES('Bill', '1994-06-05');
INSERT INTO Sales VALUES('Bill', '1994-06-06');
INSERT INTO Sales VALUES('Bill', '1994-06-07');
INSERT INTO Sales VALUES('Fred', '1994-06-07');
INSERT INTO Sales VALUES('Mary', '1994-06-08');

-- Bill について見た場合の下界集合
S0 (1994-06-01): Ø
S1 (1994-06-02): { 1994-06-01 }
S2 (1994-06-03): { 1994-06-01, 1994-06-02 }
S3 (1994-06-04): { 1994-06-01, 1994-06-02, 1994-06-03 }
      ・
       ・

-- OLAP 関数で直近の日付を求める
SELECT customer_name, sale_date,
       LAG(sale_date, 1) OVER(PARTITION BY customer_name 
                              ORDER BY sale_date) AS latest_date
FROM Sales ;

• 「自己結合の使い方」（特に「ランキングを求める」）
• 35．在庫調整

CREATE TABLE Customers
(customer_id INTEGER NOT NULL PRIMARY KEY,
 acct_balance DECIMAL (12, 2) NOT NULL);

CREATE TABLE Orders
(customer_id INTEGER NOT NULL,
 order_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE OrderDetails
(order_id INTEGER NOT NULL,
 item_id INTEGER NOT NULL,
 item_qty INTEGER NOT NULL,
 PRIMARY KEY(order_id, item_id));

CREATE TABLE Products
(item_id INTEGER NOT NULL PRIMARY KEY,
 item_qty_on_hand INTEGER NOT NULL);

--顧客１と２は全部の製品を買っている。顧客ｂRと４は一部の製品だけ買っている。顧客５と６は一つも注文していない。
DELETE FROM Customers;
INSERT INTO Customers VALUES(1, 10);
INSERT INTO Customers VALUES(2, 20);
INSERT INTO Customers VALUES(3, 30);
INSERT INTO Customers VALUES(4, 40);
INSERT INTO Customers VALUES(5, 50);
INSERT INTO Customers VALUES(6, 60);

--注文テーブル
DELETE FROM Orders;
INSERT INTO Orders VALUES(1, 1);
INSERT INTO Orders VALUES(1, 2);
INSERT INTO Orders VALUES(2, 3);
INSERT INTO Orders VALUES(3, 4);
INSERT INTO Orders VALUES(3, 5);
INSERT INTO Orders VALUES(3, 6);
INSERT INTO Orders VALUES(4, 7);

--注文明細テーブル
DELETE FROM OrderDetails;
INSERT INTO OrderDetails VALUES(1, 1, 1);

INSERT INTO OrderDetails VALUES(2, 1, 1);
INSERT INTO OrderDetails VALUES(2, 2, 1);
INSERT INTO OrderDetails VALUES(2, 3, 1);

INSERT INTO OrderDetails VALUES(3, 1, 1);
INSERT INTO OrderDetails VALUES(3, 2, 1);
INSERT INTO OrderDetails VALUES(3, 3, 1);

INSERT INTO OrderDetails VALUES(4, 1, 1);
INSERT INTO OrderDetails VALUES(5, 1, 1);
INSERT INTO OrderDetails VALUES(6, 1, 1);
INSERT INTO OrderDetails VALUES(7, 1, 1);

--製品テーブル
DELETE FROM Products;
INSERT INTO Products VALUES(1, 1);
INSERT INTO Products VALUES(2, 1);
INSERT INTO Products VALUES(3, 1);

SELECT customer_id
  FROM Customers C1
 WHERE EXISTS
       (SELECT *
          FROM Products P1
         WHERE P1.item_id
                  NOT IN (SELECT D1.item_id
                            FROM Orders O1, OrderDetails D1
                           WHERE O1.customer_id = C1.customer_id
                             AND O1.order_id = D1.order_id));

customer_id
-----------
          3
          4
          5
          6

SELECT customer_id
  FROM Customers C1
 WHERE (SELECT COUNT(DISTINCT item_id) -- 販売中の全製品
          FROM Products) -- 対
    <> (SELECT COUNT(DISTINCT item_id) -- 顧客が買った製品
          FROM Orders, OrderDetails
         WHERE Orders.customer_id = C1.customer_id
           AND Orders.order_id = OrderDetails.order_id);

customer_id
-----------
          3
          4
          5
          6

/* 答え２-１：HAVINGを使ってより関係除算らしく */
SELECT C.customer_id
  FROM Customers C 
       LEFT OUTER JOIN
          (Orders O INNER JOIN OrderDetails OD
               ON O.order_id = OD.order_id)
         ON O.customer_id = C.customer_id
GROUP BY C.customer_id
HAVING COUNT(DISTINCT item_id)         -- 顧客が買った製品
    <> (SELECT COUNT(DISTINCT item_id) -- 販売中の全製品
          FROM Products);

customer_id
-----------
          3
          4
          5
          6

• 関係除算の詳しい解説は、「HAVING句の力」

CREATE TABLE TaxAuthorities
(tax_authority CHAR(10) NOT NULL,
 tax_area CHAR(10) NOT NULL,
 PRIMARY KEY (tax_authority, tax_area));

CREATE TABLE TaxRates
(tax_authority CHAR(10) NOT NULL,
 effect_date DATE NOT NULL,
 tax_rate DECIMAL (8,2) NOT NULL,
 PRIMARY KEY (tax_authority, effect_date));

--税務当局テーブル
INSERT INTO TaxAuthorities VALUES('city1',   'city1');
INSERT INTO TaxAuthorities VALUES('city2',   'city2');
INSERT INTO TaxAuthorities VALUES('city3',   'city3');
INSERT INTO TaxAuthorities VALUES('county1', 'city1');
INSERT INTO TaxAuthorities VALUES('county1', 'city2');
INSERT INTO TaxAuthorities VALUES('county2', 'city3');
INSERT INTO TaxAuthorities VALUES('state1',  'city1');
INSERT INTO TaxAuthorities VALUES('state1',  'city2');
INSERT INTO TaxAuthorities VALUES('state1',  'city3');

--税率テーブル
INSERT INTO TaxRates VALUES('city1',  '1993-01-01', 1.0);
INSERT INTO TaxRates VALUES('city1',  '1994-01-01', 1.5);
INSERT INTO TaxRates VALUES('city2',  '1993-09-01', 1.5);
INSERT INTO TaxRates VALUES('city2',  '1994-01-01', 2.0);
INSERT INTO TaxRates VALUES('city2',  '1995-01-01', 2.0);
INSERT INTO TaxRates VALUES('city3',  '1993-01-01', 1.7);
INSERT INTO TaxRates VALUES('city3',  '1993-07-01', 1.9);
INSERT INTO TaxRates VALUES('county1', '1993-01-01' , 2.3);
INSERT INTO TaxRates VALUES('county1', '1994-10-01' , 2.5);
INSERT INTO TaxRates VALUES('county1', '1995-01-01' , 2.7);
INSERT INTO TaxRates VALUES('county2', '1993-01-01' , 2.4);
INSERT INTO TaxRates VALUES('county2', '1994-01-01' , 2.7);
INSERT INTO TaxRates VALUES('county2', '1995-01-01' , 2.8);
INSERT INTO TaxRates VALUES('state1' , '1993-01-01' , 0.5);
INSERT INTO TaxRates VALUES('state1' , '1994-01-01' , 0.8);
INSERT INTO TaxRates VALUES('state1' , '1994-07-01' , 0.9);
INSERT INTO TaxRates VALUES('state1' , '1994-10-01' , 1.1);

税務当局をノードと見なした木構造

木構造を入れ子集合で表す

CREATE TABLE TaxRates
(tax_authority CHAR(10) NOT NULL,
 lft INTEGER NOT NULL CHECK (lft > 0),
 rgt INTEGER NOT NULL,
     CHECK (lft < rgt),
 start_date DATE NOT NULL,
 end_date DATE, -- NULLならば現在の税率を意味する
 tax_rate DECIMAL(8,2) NOT NULL,
 PRIMARY KEY (tax_authority, start_date));

INSERT INTO TaxRates VALUES('state1',  1, 12, '1993-01-01', '1993-12-31', 0.5);
INSERT INTO TaxRates VALUES('state1',  1, 12, '1994-01-01', '1994-06-30', 0.8);
INSERT INTO TaxRates VALUES('state1',  1, 12, '1994-07-01', '1994-09-30', 0.9);
INSERT INTO TaxRates VALUES('state1',  1, 12, '1994-10-01', NULL, 1.1);
INSERT INTO TaxRates VALUES('state1',  1, 12, '1993-01-01', '1993-12-31', 0.5);
INSERT INTO TaxRates VALUES('county1', 2,  7, '1993-01-01', '1994-09-30', 2.3);
INSERT INTO TaxRates VALUES('county1', 2,  7, '1994-10-01', '1994-12-31', 2.5);
INSERT INTO TaxRates VALUES('county1', 2,  7, '1995-01-01', NULL, 2.7);
INSERT INTO TaxRates VALUES('county2', 8, 11, '1993-01-01', '1993-12-31', 2.4);
INSERT INTO TaxRates VALUES('county2', 8, 11, '1994-01-01', '1994-12-31', 2.7);
INSERT INTO TaxRates VALUES('county2', 8, 11, '1995-01-01', NULL, 2.8);
INSERT INTO TaxRates VALUES('city1',   3,  4, '1993-01-01', '1993-12-31', 1.0);
INSERT INTO TaxRates VALUES('city1',   3,  4, '1994-01-01', NULL, 1.5);
INSERT INTO TaxRates VALUES('city2',   5,  6, '1993-01-01', '1993-12-31', 1.5);
INSERT INTO TaxRates VALUES('city2',   5,  6, '1994-01-01', '1994-12-31', 2.0);
INSERT INTO TaxRates VALUES('city2',   5,  6, '1995-01-01', NULL, 2.0);
INSERT INTO TaxRates VALUES('city3',   9, 10, '1993-01-01', '1993-06-30', 1.7);
INSERT INTO TaxRates VALUES('city3',   9, 10, '1993-07-01', NULL, 1.9);

SELECT SUM(DISTINCT T2.tax_rate) AS total_taxes
FROM TaxRates T1, TaxRates T2
WHERE T1.tax_authority = 'city2'
  AND '1994-11-01' BETWEEN T2.start_date
                       AND COALESCE (T2.end_date, CURRENT_DATE)
  AND T1.lft BETWEEN T2.lft AND T2.rgt;

total_taxes
-----------
        5.6

• 「SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル」

CREATE TABLE Machines
(machine_name CHAR(20) NOT NULL PRIMARY KEY,
 purchase_date DATE NOT NULL,
 initial_cost DECIMAL (10,2) NOT NULL,
 lifespan INTEGER NOT NULL);

CREATE TABLE ManufactHrsCosts
(machine_name CHAR(20) NOT NULL
    REFERENCES Machines(machine_name),
 manu_date DATE NOT NULL,
 batch_nbr INTEGER NOT NULL,
 manu_hrs DECIMAL(4,2) NOT NULL,
 manu_cost DECIMAL (6,2) NOT NULL,
    PRIMARY KEY (machine_name, manu_date, batch_nbr));

--機械テーブル
INSERT INTO Machines VALUES('Frammis', '1995-07-24', 10000, 1000);

--作業時間コストテーブル
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-24',   101,  2.5, 123.00);
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-25',   102,  2.5, 120.00);
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-25',   103,  2.0, 100.00);
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-26',   104,  2.5, 118.00);
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-27',   105,  2.5, 116.00);
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-27',   106,  2.5, 113.00);
INSERT INTO ManufactHrsCosts VALUES('Frammis',  '1995-07-28',   107,  2.5, 110.00);

CREATE TABLE Consultants
(emp_id INTEGER NOT NULL,
 emp_name CHAR(10) NOT NULL);

CREATE TABLE Billings
(emp_id INTEGER NOT NULL,
 bill_date DATE NOT NULL,
 bill_rate DECIMAL (5,2));

CREATE TABLE HoursWorked
(job_id INTEGER NOT NULL,
 emp_id INTEGER NOT NULL,
 work_date DATE NOT NULL,
 bill_hrs DECIMAL(5, 2));

INSERT INTO Consultants VALUES (1, 'Larry');
INSERT INTO Consultants VALUES (2, 'Moe');
INSERT INTO Consultants VALUES (3, 'Curly');

INSERT INTO Billings VALUES (1, '1990-01-01', 25.00);
INSERT INTO Billings VALUES (2, '1989-01-01', 15.00);
INSERT INTO Billings VALUES (3, '1989-01-01', 20.00);
INSERT INTO Billings VALUES (1, '1991-01-01', 30.00);

INSERT INTO HoursWorked VALUES (4, 1, '1990-07-01', 3);
INSERT INTO HoursWorked VALUES (4, 1, '1990-08-01', 5);
INSERT INTO HoursWorked VALUES (4, 2, '1990-07-01', 2);
INSERT INTO HoursWorked VALUES (4, 1, '1991-07-01', 4);

CREATE TABLE Roles(
 person VARCHAR2(32), 
 role   VARCHAR2(32), 
    PRIMARY KEY (person, role));

INSERT INTO Roles VALUES('Smith', 'O');
INSERT INTO Roles VALUES('Smith', 'D');
INSERT INTO Roles VALUES('Jones', 'O');
INSERT INTO Roles VALUES('White', 'D');
INSERT INTO Roles VALUES('Brown', 'X');

--PostgreSQL版
SELECT person,
       SUBSTRING ('DOB', CAST(SUM (POSITION (role IN 'DO')) AS INTEGER), 1)
  FROM Roles
 WHERE role IN ('D','O')
 GROUP BY person;

CREATE TABLE InventoryAdjustments
(req_date DATE NOT NULL,
 req_qty INTEGER NOT NULL
    CHECK (req_qty <> 0),
    PRIMARY KEY (req_date, req_qty));

INSERT INTO InventoryAdjustments VALUES('1994-07-01', 100 );
INSERT INTO InventoryAdjustments VALUES('1994-07-02', 120 );
INSERT INTO InventoryAdjustments VALUES('1994-07-03', -150);
INSERT INTO InventoryAdjustments VALUES('1994-07-04', 50  );
INSERT INTO InventoryAdjustments VALUES('1994-07-05', -35 );

• 「自己結合の使い方」（特に「ランキングを求める」）
• ノイマンのアイデアについての理論的・歴史的背景を知りたい方は「SQLと再帰集合」

CREATE TABLE Samples
(sample_time TIMESTAMP NOT NULL PRIMARY KEY,
 moving_avg REAL DEFAULT 0 NOT NULL ,
 load REAL DEFAULT 0 NOT NULL);

INSERT INTO Samples VALUES('2007-01-01 01:00', 0, 0);
INSERT INTO Samples VALUES('2007-01-01 01:15', 0, 1);
INSERT INTO Samples VALUES('2007-01-01 01:30', 0, 2);
INSERT INTO Samples VALUES('2007-01-01 01:45', 0, 3);
INSERT INTO Samples VALUES('2007-01-01 02:00', 0, 4);
INSERT INTO Samples VALUES('2007-01-01 02:15', 0, 5);
INSERT INTO Samples VALUES('2007-01-01 02:30', 0, 6);

SELECT S1.sample_time, AVG(S2.load) AS avg_prev_hour_load
  FROM Samples S1, Samples S2
 WHERE S2.sample_time BETWEEN (S1.sample_time - INTERVAL '1' HOUR)
                          AND S1.sample_time
 GROUP BY S1.sample_time;

sample_time       avg_prev_hour_load
----------------- ------------------
20-07-01 01:00:00	        0
20-07-01 01:15:00	        0.5
20-07-01 01:30:00	        1
20-07-01 01:45:00	        1.5
20-07-01 02:00:00	        2
20-07-01 02:15:00	        3
20-07-01 02:30:00	        4

• 集合が少しづつずれていくイメージについては、「相関サブクエリで行と行を比較する」の「移動累計と移動平均」を参照。

CREATE TABLE Journal
(acct_nbr INTEGER NOT NULL,
 trx_date DATE NOT NULL,
 trx_amt DECIMAL (10, 2) NOT NULL,
 duration INTEGER NOT NULL);

INSERT INTO Journal VALUES(1, '2007-01-01', 10, 0);
INSERT INTO Journal VALUES(1, '2007-01-02', 20, 0);
INSERT INTO Journal VALUES(1, '2007-01-04', 30, 0);
INSERT INTO Journal VALUES(2, '2007-01-07', 40, 0);
INSERT INTO Journal VALUES(2, '2007-01-11', 50, 0);
INSERT INTO Journal VALUES(3, '2007-01-20', 60, 0);
INSERT INTO Journal VALUES(4, '2007-01-01', 70, 0);
INSERT INTO Journal VALUES(4, '2007-01-02', 80, 0);

更新結果：
acct_nbr trx_date trx_amt duration
-------- -------- ------- --------
       1 07-01-01      10       -1
       1 07-01-02      20       -2
       1 07-01-04      30        0
       2 07-01-07      40       -4
       2 07-01-11      50        0
       3 07-01-20      60        0
       4 07-01-01      70       -1
       4 07-01-02      80        0

テーブル設計の鉄則：列持ちのメリットはほとんどない。速攻で行持ちへ変換せよ。

CREATE TABLE Losses
(cust_nbr INTEGER NOT NULL PRIMARY KEY,
 a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER,
 f INTEGER, g INTEGER, h INTEGER, i INTEGER, j INTEGER,
 k INTEGER, l INTEGER, m INTEGER, n INTEGER, o INTEGER);

CREATE TABLE Policy_Criteria
(criteria_id INTEGER NOT NULL,
 criteria CHAR(1) NOT NULL,
 crit_val INTEGER NOT NULL,
    PRIMARY KEY (criteria_id, criteria, crit_val));

CREATE TABLE LossDoneRight
(cust_nbr INTEGER NOT NULL,
 criteria CHAR(1) NOT NULL,
 crit_val INTEGER NOT NULL);

INSERT INTO Losses 
VALUES ( 99, 5, 10, 15, NULL, NULL, NULL, NULL, NULL, 
         NULL, NULL,NULL, NULL, NULL, NULL, NULL);

INSERT INTO Policy_Criteria VALUES (1, 'A', 5);
INSERT INTO Policy_Criteria VALUES (1, 'A', 9);
INSERT INTO Policy_Criteria VALUES (1, 'A', 14);
INSERT INTO Policy_Criteria VALUES (1, 'B', 4);
INSERT INTO Policy_Criteria VALUES (1, 'B', 10);
INSERT INTO Policy_Criteria VALUES (1, 'B', 20);
INSERT INTO Policy_Criteria VALUES (2, 'B', 10);
INSERT INTO Policy_Criteria VALUES (2, 'B', 19);
INSERT INTO Policy_Criteria VALUES (3, 'A', 5);
INSERT INTO Policy_Criteria VALUES (3, 'B', 10);
INSERT INTO Policy_Criteria VALUES (3, 'B', 30);
INSERT INTO Policy_Criteria VALUES (3, 'C', 3);
INSERT INTO Policy_Criteria VALUES (3, 'C', 15);
INSERT INTO Policy_Criteria VALUES (4, 'A', 5);
INSERT INTO Policy_Criteria VALUES (4, 'B', 21);
INSERT INTO Policy_Criteria VALUES (4, 'B', 22);

cust_nbr criteria crit_val
-------- -------- --------
      99        A        5
      99        B       10
      99        C       15

CREATE TABLE Elements
(i INTEGER NOT NULL PRIMARY KEY);

INSERT INTO Elements VALUES (1);
INSERT INTO Elements VALUES (2);
INSERT INTO Elements VALUES (3);
INSERT INTO Elements VALUES (4);
INSERT INTO Elements VALUES (5);
INSERT INTO Elements VALUES (6);
INSERT INTO Elements VALUES (7);

• 本文で言及されているロバート・シジウィックの論文は、「Permutation Generation Methods」
を参照。

CREATE TABLE Items
(item_nbr INTEGER,
 item_descr CHAR(10));

CREATE TABLE Actuals
(item_nbr   INTEGER, 
 actual_amt DECIMAL(5,2),
 check_nbr  CHAR(4));

CREATE TABLE Estimates
(item_nbr      INTEGER, 
 estimated_amt DECIMAL(5,2));

INSERT INTO Items VALUES(10, 'Item 10');
INSERT INTO Items VALUES(20, 'Item 20');
INSERT INTO Items VALUES(30, 'Item 30');
INSERT INTO Items VALUES(40, 'Item 40');
INSERT INTO Items VALUES(50, 'item 50');

INSERT INTO Actuals VALUES(10, 300.00, '1111');
INSERT INTO Actuals VALUES(20, 325.00, '2222');
INSERT INTO Actuals VALUES(20, 100.00, '3333');
INSERT INTO Actuals VALUES(30, 525.00, '1111');

INSERT INTO Estimates VALUES(10, 300.00);
INSERT INTO Estimates VALUES(10, 50.00);
INSERT INTO Estimates VALUES(20, 325.00);
INSERT INTO Estimates VALUES(20, 110.00);
INSERT INTO Estimates VALUES(40, 25.00);

CREATE TABLE Samples
(sample_id INTEGER NOT NULL,
 fish_name CHAR(20) NOT NULL,
 found_tally INTEGER NOT NULL,
    PRIMARY KEY (sample_id, fish_name));

CREATE TABLE SampleGroups
(group_id INTEGER NOT NULL,
 group_descr CHAR(20) NOT NULL,
 sample_id INTEGER NOT NULL,
    PRIMARY KEY (group_id, sample_id));

INSERT INTO Samples VALUES (1, 'minnow', 18);
INSERT INTO Samples VALUES (1, 'pike', 7);
INSERT INTO Samples VALUES (2, 'pike', 4);
INSERT INTO Samples VALUES (2, 'carp', 3);
INSERT INTO Samples VALUES (3, 'carp', 9);

INSERT INTO SampleGroups VALUES (1, 'muddy water', 1);
INSERT INTO SampleGroups VALUES (1, 'muddy water', 2);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 1);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 3);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 2);

INSERT INTO SampleGroups VALUES (2, 'fresh water', 4);

CREATE TABLE Categories
(credit_cat CHAR(1) NOT NULL,
 rqd_credits INTEGER NOT NULL);

CREATE TABLE CreditsEarned --主キーなし
(student_name CHAR(10) NOT NULL,
 credit_cat CHAR(1) NOT NULL,
 credits INTEGER NOT NULL);

INSERT INTO Categories VALUES ('A', 10);
INSERT INTO Categories VALUES ('B', 3);
INSERT INTO Categories VALUES ('C', 5);

INSERT INTO CreditsEarned
VALUES ('Joe', 'A', 3), ('Joe', 'A', 2), ('Joe', 'A', 3),
       ('Joe', 'A', 3), ('Joe', 'B', 3), ('Joe', 'C', 3),
       ('Joe', 'C', 2), ('Joe', 'C', 3),
       ('Bob', 'A', 2), ('Bob', 'C', 2), ('Bob', 'A', 12),
       ('Bob', 'C', 4),
       ('John', 'A', 1), ('John', 'B', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'A', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'A', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'A', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'B', 1),
       ('Mary', 'B', 1), ('Mary', 'B', 1), ('Mary', 'B', 1),
       ('Mary', 'B', 1), ('Mary', 'B', 1), ('Mary', 'B', 1),
       ('Mary', 'C', 1), ('Mary', 'C', 1), ('Mary', 'C', 1),
       ('Mary', 'C', 1), ('Mary', 'C', 1), ('Mary', 'C', 1),
       ('Mary', 'C', 1), ('Mary', 'C', 1);

CREATE TABLE SalesSlips
(item_a INTEGER NOT NULL,
 item_b INTEGER NOT NULL,
    PRIMARY KEY(item_a, item_b),
 pair_tally INTEGER NOT NULL);

INSERT INTO SalesSlips VALUES(12345, 12345, 12);
INSERT INTO SalesSlips VALUES(12345, 67890,  9);
INSERT INTO SalesSlips VALUES(67890, 12345,  5);

• 非等値結合で順列と組み合わせを作る原理については、「自己結合の使い方」を参照。

--重複行があるというので、主キーなし
CREATE TABLE FriendsofPepperoni
(cust_id INTEGER,
 bill_date DATE,
 pizza_amt DECIMAL(5,2));

--答え３用マスタテーブル
CREATE TABLE ReportRanges
(day_count CHAR(32), 
 start_cnt INTEGER, 
 end_cnt   INTEGER);

INSERT INTO FriendsofPepperoni VALUES(1, '2007-05-07', 10);
INSERT INTO FriendsofPepperoni VALUES(1, '2007-04-01', 20);
INSERT INTO FriendsofPepperoni VALUES(1, '2007-03-01', 30);
INSERT INTO FriendsofPepperoni VALUES(1, '2007-01-01', 40);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-05-06', 10);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-04-01', 20);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-03-01', 30);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-01-01', 40);

--答え３用
INSERT INTO ReportRanges VALUES('under Thirty days', 00, 30);
INSERT INTO ReportRanges VALUES('Sixty days', 31, 60);
INSERT INTO ReportRanges VALUES('Ninty days', 61, 90);

SELECT cust_id,
       SUM(CASE WHEN bill_date BETWEEN CURRENT_DATE - INTERVAL '30' DAY
                                   AND CURRENT_DATE
                THEN pizza_amt ELSE 0.00 END) AS age1,
       SUM(CASE WHEN bill_date BETWEEN CURRENT_DATE - INTERVAL '60' DAY
                                   AND CURRENT_DATE - INTERVAL '31' DAY
                THEN pizza_amt ELSE 0.00 END) AS age2,
       SUM(CASE WHEN bill_date BETWEEN CURRENT_DATE - INTERVAL '90' DAY
                                   AND CURRENT_DATE - INTERVAL '61' DAY
                THEN pizza_amt ELSE 0.00 END) AS age3,
       SUM(CASE WHEN bill_date < CURRENT_DATE - INTERVAL '91' DAY
                THEN pizza_amt ELSE 0.00 END) AS age4
  FROM FriendsofPepperoni
 GROUP BY cust_id
 ORDER BY cust_id;

cust_id  age1  age2  age3  age4
------- ----- ----- ----- -----
      1 10.00  0.00 20.00 70.00
      2 10.00  0.00 20.00 70.00

CREATE TABLE Promotions
(promo_name CHAR(25) NOT NULL PRIMARY KEY,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
    CHECK (start_date <= end_date));

CREATE TABLE Sales
(ticket_nbr INTEGER NOT NULL PRIMARY KEY,
 clerk_name CHAR (15) NOT NULL,
 sale_date DATE NOT NULL,
 sale_amt DECIMAL (8,2) NOT NULL);

INSERT INTO Promotions VALUES('Feast of St. Fred'    ,'1995-02-01'  ,'1995-02-07');
INSERT INTO Promotions VALUES('National Pickle Pageant'  ,'1995-11-01'  ,'1995-11-07');
INSERT INTO Promotions VALUES('Christmas Week'    ,'1995-12-18'  ,'1995-12-25');

INSERT INTO Sales VALUES(1, 'スミス', '1995-02-01', 10);
INSERT INTO Sales VALUES(2, 'ジョン', '1995-02-02', 20);
INSERT INTO Sales VALUES(3, 'ジョン', '1995-02-03', 30);
INSERT INTO Sales VALUES(4, 'ジョン', '1995-02-04', 20);
INSERT INTO Sales VALUES(5, 'メアリ', '1995-02-05', 70);
INSERT INTO Sales VALUES(6, 'ケン',   '1995-11-03', 10);
INSERT INTO Sales VALUES(7, 'テリー', '1995-11-05', 20);
INSERT INTO Sales VALUES(8, 'テリー', '1995-11-05', 20);

clerk_name promo_name              sales_tot
---------- ----------------------- -----------
ジョン     Feast of St. Fred          70.00
メアリ     Feast of St. Fred          70.00
テリー     National Pickle Pageant    40.00

CREATE TABLE Inventory
(goods CHAR(10) NOT NULL PRIMARY KEY,
 pieces INTEGER NOT NULL CHECK (pieces >= 0));

INSERT INTO Inventory VALUES('Alpha'  , 4);
INSERT INTO Inventory VALUES('Beta'   , 5);
INSERT INTO Inventory VALUES('Delta'  ,16);
INSERT INTO Inventory VALUES('Gamma'  ,50);
INSERT INTO Inventory VALUES('Epsilon', 1);

• 連番テーブルを利用する問題は、「57．欠番探しバージョン1」、「60．バーコード」、「65．製品の対象年齢の範囲」などを参照。

CREATE TABLE Production
(production_center INTEGER NOT NULL,
 wk_date DATE NOT NULL,
 batch_nbr INTEGER NOT NULL,
 widget_cnt INTEGER NOT NULL,
    PRIMARY KEY (production_center, wk_date, batch_nbr));

INSERT INTO Production VALUES(1, '2007-05-02', 1, 1);
INSERT INTO Production VALUES(1, '2007-05-02', 2, 2);
INSERT INTO Production VALUES(1, '2007-05-02', 3, 3);
INSERT INTO Production VALUES(1, '2007-05-02', 4, 4);
INSERT INTO Production VALUES(1, '2007-05-02', 5, 5);
INSERT INTO Production VALUES(1, '2007-05-02', 6, 6);
INSERT INTO Production VALUES(1, '2007-05-02', 7, 7);

INSERT INTO Production VALUES(1, '2007-10-02', 1, 1);
INSERT INTO Production VALUES(1, '2007-10-02', 2, 2);
INSERT INTO Production VALUES(1, '2007-10-02', 3, 3);

 wk_date   widget_cnt third
---------- ---------- -----
2007-05-02          1    1
2007-05-02          2    1
2007-05-02          3    2
2007-05-02          4    2
2007-05-02          5    3
2007-05-02          6    3
2007-05-02          7    3
2007-10-02          1    1
2007-10-02          2    2
2007-10-02          3    3

SELECT production_center, wk_date, third, AVG(widget_cnt)
  FROM (SELECT production_center, wk_date, 
               NTILE(3) OVER (PARTITION BY production_center, wk_date 
                              ORDER BY batch_nbr ASC) third, 
               widget_cnt 
          FROM production)
 GROUP BY production_center, wk_date, third;

CREATE TABLE AnthologyContributors
(isbn CHAR(10) NOT NULL,
 contributor CHAR(20) NOT NULL,
 category INTEGER NOT NULL,
    PRIMARY KEY (isbn, contributor));

INSERT INTO AnthologyContributors VALUES(1, 'スミス', 1);
INSERT INTO AnthologyContributors VALUES(2, 'スミス', 2);
INSERT INTO AnthologyContributors VALUES(3, 'スミス', 3);
INSERT INTO AnthologyContributors VALUES(4, 'ジョン', 1);
INSERT INTO AnthologyContributors VALUES(5, 'ジョン', 2);
INSERT INTO AnthologyContributors VALUES(6, 'メアリ', 1);
INSERT INTO AnthologyContributors VALUES(7, 'メアリ', 1);
INSERT INTO AnthologyContributors VALUES(8, 'ガトー', 2);
INSERT INTO AnthologyContributors VALUES(9, 'ガトー', 3);
INSERT INTO AnthologyContributors VALUES(10,'シモン', 1);

SELECT contributor, 1, 2, 3
  FROM AnthologyContributors AS A1
 WHERE A1.category IN (1, 2, 3)
 GROUP BY contributor
HAVING (SELECT SUM(DISTINCT
                   CASE WHEN category = 1
                        THEN 1
                        WHEN category = 2
                        THEN 2
                        WHEN category = 3
                        THEN -3 ELSE NULL END)) = 3;

contributor cat_1 cat_2 cat_3
----------- ----- ----- -----
ジョン          1     2     3

INSERT INTO Budgeted VALUES(1, 9100, 100.00);
INSERT INTO Budgeted VALUES(2, 9100,  15.00);
INSERT INTO Budgeted VALUES(3, 9100,   6.00);
INSERT INTO Budgeted VALUES(4, 9200,   8.00);
INSERT INTO Budgeted VALUES(5, 9200,  11.00);

INSERT INTO Actual VALUES(1,  1,  10.00);
INSERT INTO Actual VALUES(2,  1,  20.00);
INSERT INTO Actual VALUES(3,  1,  15.00);
INSERT INTO Actual VALUES(4,  2,  32.00);
INSERT INTO Actual VALUES(5,  4,  8.00);
INSERT INTO Actual VALUES(6,  5,  3.00);
INSERT INTO Actual VALUES(7,  5,  4.00);

--構文エラー！？
SELECT category, SUM(B1.est_cost) AS estimated,
       (SELECT SUM(T1.act_cost)
          FROM Actual AS T1
         WHERE T1.task BETWEEN MIN(B1.task)
                           AND MAX(B1.task)) AS spent
  FROM Budgeted AS B1
 GROUP BY category;

category estimated spent
-------- --------- -----
    9200     19.00 15.00
    9100    121.00 77.00

• 「SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル」の「2-7．パスを列挙する（列持ちバージョン）」でも、同じトリックを利用しています。

CREATE TABLE Personnel
(emp_name CHAR(10) NOT NULL,
 dept_id  CHAR(10) );

INSERT INTO Personnel VALUES('Daren', 'Acct');
INSERT INTO Personnel VALUES('Joe'  , 'Acct');
INSERT INTO Personnel VALUES('Lisa' , 'DP');
INSERT INTO Personnel VALUES('Helen', 'DP');
INSERT INTO Personnel VALUES('Fonda', 'DP');

SELECT AVG(COUNT(*)) AS avg_cnt
  FROM Personnel
 GROUP BY dept_id;

avg_cnt
-------
    2.5

CREATE TABLE Foobar
(lvl INTEGER NOT NULL PRIMARY KEY,
 color VARCHAR(10),
 length INTEGER,
 width INTEGER,
 hgt INTEGER);

INSERT INTO Foobar VALUES (1, 'RED', 8, 10, 12);
INSERT INTO Foobar VALUES (2, NULL, NULL, NULL, 20);
INSERT INTO Foobar VALUES (3, NULL, 9, 82, 25);
INSERT INTO Foobar VALUES (4, 'BLUE', NULL, 67, NULL);
INSERT INTO Foobar VALUES (5, 'GRAY', NULL, NULL, NULL);

• COALESCE はうまく使うととっても便利。「73．データのクリーニング」を参照。

CREATE TABLE Customers
(custnbr INTEGER,
 last_name  CHAR(10),
 first_name CHAR(10),
 street_address INTEGER,
 city_name  CHAR(10),
 state_code INTEGER,
 phone_nbr  INTEGER);

INSERT INTO Customers VALUES(1, 'Smith',  'Mike', 1, 'New York', 1, 1);
INSERT INTO Customers VALUES(2, 'Darwin', 'Ken',  1, 'New York', 0, 5);
INSERT INTO Customers VALUES(3, 'Haxley', 'Kate', 2, 'Chicago',  1, 10);
INSERT INTO Customers VALUES(4, 'Darwin', 'John', 9, 'Los',      0, 5);
INSERT INTO Customers VALUES(5, 'Haxley', 'Mick', 2, 'Keswick',  0, 10);

SELECT C0.custnbr, C1.custnbr
  FROM Customers AS C0, Customers AS C1
 WHERE C0.last_name = C1.last_name
   AND C0.custnbr < C1.custnbr
   AND (CASE WHEN C0.first_name = C1.first_name
             THEN 1 ELSE 0 END)
     + (CASE WHEN C0.street_address = C1.street_address
             THEN 1 ELSE 0 END)
     + (CASE WHEN C0.city_name = C1.city_name
             THEN 1 ELSE 0 END)
     + (CASE WHEN C0.state_code = C1.state_code
             THEN 1 ELSE 0 END)
     + (CASE WHEN C0.phone_nbr = C1.phone_nbr
             THEN 1 ELSE 0 END) >= 2;

custnbr custnbr
------- -------
      2       4
      3       5

CREATE TABLE RacingResults
(track_id CHAR(3) NOT NULL,
 race_date DATE NOT NULL,
 race_nbr INTEGER NOT NULL,
 win_name CHAR(30) NOT NULL,
 place_name CHAR(30) NOT NULL,
 show_name CHAR(30) NOT NULL,
    PRIMARY KEY (track_id, race_date, race_nbr));

CREATE TABLE HorseNames
(horse CHAR(30) NOT NULL PRIMARY KEY);

INSERT INTO RacingResults VALUES(1, '2007-05-01', 1, 'A', 'B', 'C');
INSERT INTO RacingResults VALUES(1, '2007-05-01', 2, 'E', 'F', 'P');
INSERT INTO RacingResults VALUES(1, '2007-05-02', 1, 'B', 'C', 'A');
INSERT INTO RacingResults VALUES(2, '2007-05-02', 1, 'O', 'P', 'Q');
INSERT INTO RacingResults VALUES(2, '2007-05-02', 2, 'A', 'P', 'Q');

INSERT INTO HorseNames VALUES('A');
INSERT INTO HorseNames VALUES('B');
INSERT INTO HorseNames VALUES('C');
INSERT INTO HorseNames VALUES('D');
INSERT INTO HorseNames VALUES('E');
INSERT INTO HorseNames VALUES('F');
INSERT INTO HorseNames VALUES('O');
INSERT INTO HorseNames VALUES('P');
INSERT INTO HorseNames VALUES('Q');

SELECT H1.horse, COUNT(R1.track_id)
  FROM HorseNames AS H1 LEFT OUTER JOIN RacingResults AS R1
  ON H1.horse IN (R1.win_name, R1.place_name, R1.show_name)
 GROUP BY H1.horse;

horse count
----- -----
A         3
B         2
C         2
D         0  --入賞0回の D も現れる
E         1
F         1
O         1
P         3
Q         2

CREATE TABLE Hotel
(floor_nbr INTEGER NOT NULL,
 room_nbr INTEGER);

INSERT INTO Hotel VALUES(1, NULL);
INSERT INTO Hotel VALUES(1, NULL);
INSERT INTO Hotel VALUES(1, NULL);
INSERT INTO Hotel VALUES(2, NULL);
INSERT INTO Hotel VALUES(2, NULL);
INSERT INTO Hotel VALUES(3, NULL);

1. OLAP 関数を SET 句で使える。
2. OVER句に ORDER BY の指定が必要ない。

--Oracle
INSERT INTO Hotel VALUES(1, COALESCE(MAX(room_nbr), 0) +1);
INSERT INTO Hotel VALUES(1, COALESCE(MAX(room_nbr), 0) +1);
INSERT INTO Hotel VALUES(1, COALESCE(MAX(room_nbr), 0) +1);
INSERT INTO Hotel VALUES(2, COALESCE(MAX(room_nbr), 0) +1);
INSERT INTO Hotel VALUES(2, COALESCE(MAX(room_nbr), 0) +1);
INSERT INTO Hotel VALUES(3, COALESCE(MAX(room_nbr), 0) +1);

--PostgreSQL
INSERT INTO Hotel VALUES(1, (SELECT COALESCE(MAX(room_nbr), 0) +1 FROM hotel));
INSERT INTO Hotel VALUES(1, (SELECT COALESCE(MAX(room_nbr), 0) +1 FROM hotel));
INSERT INTO Hotel VALUES(1, (SELECT COALESCE(MAX(room_nbr), 0) +1 FROM hotel));
INSERT INTO Hotel VALUES(2, (SELECT COALESCE(MAX(room_nbr), 0) +1 FROM hotel));
INSERT INTO Hotel VALUES(2, (SELECT COALESCE(MAX(room_nbr), 0) +1 FROM hotel));
INSERT INTO Hotel VALUES(3, (SELECT COALESCE(MAX(room_nbr), 0) +1 FROM hotel));

floor_nbr room_nbr
--------- --------
        1        1
        1        2
        1        3
        2        4
        2        5
        3        6

UPDATE Hotel
   SET room_nbr
         =   (floor_nbr * 100)
           + (SELECT COUNT(*)
                FROM Hotel H1
               WHERE Hotel.floor_nbr = H1.floor_nbr
                 AND Hotel.room_nbr >= H1.room_nbr);

floor_nbr room_nbr
--------- --------
        1      101
        1      102
        1      103
        2      201
        2      202
        3      301

CREATE TABLE Numbers (seq INTEGER NOT NULL PRIMARY KEY);

INSERT INTO Numbers VALUES (2);
INSERT INTO Numbers VALUES (3);
INSERT INTO Numbers VALUES (5);
INSERT INTO Numbers VALUES (7);
INSERT INTO Numbers VALUES (8);
INSERT INTO Numbers VALUES (14);
INSERT INTO Numbers VALUES (20);

1. 欠番の昇順か降順にソートする。
2. ループさせて、1行ずつ次の行と連番を比較する。

• 「帰ってきたHAVING句」

CREATE TABLE Tickets
(buyer_name CHAR(5) NOT NULL,
 ticket_nbr INTEGER DEFAULT 1 NOT NULL
    CHECK (ticket_nbr > 0),
    PRIMARY KEY (buyer_name, ticket_nbr));

INSERT INTO Tickets VALUES ('a', 2);
INSERT INTO Tickets VALUES ('a', 3);
INSERT INTO Tickets VALUES ('a', 4);
INSERT INTO Tickets VALUES ('b', 4);
INSERT INTO Tickets VALUES ('c', 1);
INSERT INTO Tickets VALUES ('c', 2);
INSERT INTO Tickets VALUES ('c', 3);
INSERT INTO Tickets VALUES ('c', 4);
INSERT INTO Tickets VALUES ('c', 5);
INSERT INTO Tickets VALUES ('d', 1);
INSERT INTO Tickets VALUES ('d', 6);
INSERT INTO Tickets VALUES ('d', 7);
INSERT INTO Tickets VALUES ('d', 9);
INSERT INTO Tickets VALUES ('e', 10);

--答え３
SELECT DISTINCT T2.buyer_name, T2.ticket_nbr
  FROM (SELECT T1.buyer_name, S1.seq AS ticket_nbr
          FROM (SELECT buyer_name, MAX(ticket_nbr)
                  FROM Tickets
               GROUP BY buyer_name) AS T1(buyer_name, max_nbr),
               Sequence AS S1
         WHERE S1.seq <= max_nbr) AS T2
       LEFT OUTER JOIN Tickets AS T3
         ON T2.buyer_name = T3.buyer_name
        AND T2.ticket_nbr = T3.ticket_nbr
 WHERE T3.ticket_nbr IS NULL;

buyer_name ticket_nbr
---------- ---------
a                  1
b                  1
b                  2
b                  3
d                  2
d                  3
d                  4
d                  5
d                  8
e                  1
e                  2
e                  3
e                  4
e                  5
e                  6
e                  7
e                  8
e                  9

CREATE TABLE Timesheets
(task_id CHAR(10) NOT NULL PRIMARY KEY,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
    CHECK(start_date <= end_date));

INSERT INTO Timesheets VALUES (1, '1997-01-01', '1997-01-03');
INSERT INTO Timesheets VALUES (2, '1997-01-02', '1997-01-04');
INSERT INTO Timesheets VALUES (3, '1997-01-04', '1997-01-05');
INSERT INTO Timesheets VALUES (4, '1997-01-06', '1997-01-09');
INSERT INTO Timesheets VALUES (5, '1997-01-09', '1997-01-09');
INSERT INTO Timesheets VALUES (6, '1997-01-09', '1997-01-09');
INSERT INTO Timesheets VALUES (7, '1997-01-12', '1997-01-15');
INSERT INTO Timesheets VALUES (8, '1997-01-13', '1997-01-14');
INSERT INTO Timesheets VALUES (9, '1997-01-14', '1997-01-14');
INSERT INTO Timesheets VALUES (10, '1997-01-17', '1997-01-17');

• 「開始日と終了日をまとめる」

--答え２：PL/SQL版
CREATE OR REPLACE FUNCTION Barcode_CheckSum(barcode IN CHAR) RETURN INTEGER
AS
  check_sum INTEGER := 0;
BEGIN
    IF REGEXP_LIKE(barcode, '[^0-9]') THEN 
       RETURN -1;
    ELSIF LENGTH(barcode) > 12 THEN
       RETURN -2;
    ELSE 
        SELECT ABS(MOD(SUM(CAST(SUBSTR(barcode, seq, 1) AS INTEGER)
                  * CASE MOD(seq, 2) 
                         WHEN 0 THEN 1 
                         ELSE -1 END), 10)) INTO check_sum
          FROM Sequence
         WHERE seq <= 12;
        
        RETURN check_sum;
    END IF;
END;
/

SELECT Barcode_CheckSum('283723281122') AS check_sum
  FROM dual;

check_sum
---------
        7

SELECT Barcode_CheckSum('2837232r1122') AS check_sum
  FROM dual;

check_sum
---------
       -1

SELECT Barcode_CheckSum('2837232811229') AS check_sum
  FROM dual;

check_sum
---------
       -2

--答え３：PL/SQL版
CREATE OR REPLACE FUNCTION Barcode_CheckSum(barcode IN CHAR) RETURN INTEGER
AS
  check_sum INTEGER := 0;
BEGIN
  SELECT ABS(MOD(SUM(CAST(SUBSTR(barcode, seq, 1) AS INTEGER)
                 * CASE MOD(seq, 2) 
                   WHEN 0 THEN 1 
                   ELSE -1 END), 10)) INTO check_sum
    FROM Sequence
   WHERE seq <= 12
     AND NOT REGEXP_LIKE(barcode, '[^0-9]');

  RETURN check_sum;
END;
/

SELECT Barcode_CheckSum('283723281122') AS check_sum
  FROM dual;

check_sum
---------
        7

SELECT Barcode_CheckSum('2837232r1122') AS check_sum
  FROM dual;

check_sum
---------
     NULL

SELECT Barcode_CheckSum('2837232811229') AS check_sum
  FROM dual;

check_sum
---------
     NULL

CREATE TABLE SortMeFast
(unsorted_string CHAR(7) NOT NULL PRIMARY KEY);

INSERT INTO SortMeFast VALUES('CABBDBC');

--PostgreSQL
CREATE VIEW SortedStrings (sortedstring)
AS SELECT REPEAT('A', (LENGTH(unsorted_string) -
            LENGTH(REPLACE(unsorted_string, 'A', '')))) ||
          REPEAT('B', (LENGTH(unsorted_string) -
            LENGTH(REPLACE(unsorted_string, 'B', '')))) ||
          REPEAT('C', (LENGTH(unsorted_string) -
            LENGTH(REPLACE(unsorted_string, 'C', '')))) ||
          REPEAT('D', (LENGTH(unsorted_string) -
            LENGTH(REPLACE(unsorted_string, 'D', ''))))
     FROM SortMeFast;

--MySQL
CREATE VIEW SortedStrings (sortedstring)
AS SELECT CONCAT(REPEAT('A', (LENGTH(unsorted_string) -
                   LENGTH(REPLACE(unsorted_string, 'A', '')))),
                 CONCAT(REPEAT('B', (LENGTH(unsorted_string) -
                          LENGTH(REPLACE(unsorted_string, 'B', '')))),
                        CONCAT(REPEAT('C', (LENGTH(unsorted_string) -
                                 LENGTH(REPLACE(unsorted_string, 'C', '')))),
                               REPEAT('D', (LENGTH(unsorted_string) -
                                 LENGTH(REPLACE(unsorted_string, 'D', '')))))))
     FROM SortMeFast;

sortedstring
------------
ABBBCCD

• 本文で言及されている：R.C. ボースとR.J. ネルソンの論文は、「A Sorting Problem」を参照。セルコがそれを SQL に移植した記事は、「Scrubbing Data with Non-1NF Tables」。C.T. ファイクの論文は「A permutation generation method」。

CREATE TABLE Names
(name VARCHAR(15) NOT NULL PRIMARY KEY);

INSERT INTO Names VALUES ('Al');
INSERT INTO Names VALUES ('Ben');
INSERT INTO Names VALUES ('Charlie');
INSERT INTO Names VALUES ('David');
INSERT INTO Names VALUES ('Ed');
INSERT INTO Names VALUES ('Frank');
INSERT INTO Names VALUES ('Greg');
INSERT INTO Names VALUES ('Howard');
INSERT INTO Names VALUES ('Ida');
INSERT INTO Names VALUES ('Joe');
INSERT INTO Names VALUES ('Ken');
INSERT INTO Names VALUES ('Larry');
INSERT INTO Names VALUES ('Mike');
INSERT INTO Names VALUES ('Neal');

CREATE TABLE T
(num INTEGER NOT NULL PRIMARY KEY,
 data CHAR(1) NOT NULL);

INSERT INTO T VALUES (1, 'a');
INSERT INTO T VALUES (2, 'a');
INSERT INTO T VALUES (3, 'b');
INSERT INTO T VALUES (6, 'b');
INSERT INTO T VALUES (8, 'a');

SELECT MIN(T1.num) AS low,
       MAX(T1.num) AS high,
       T1.data
  FROM T T1
       LEFT OUTER JOIN T T2
         ON T1.num < T2.num  --論理的には不要な条件
        AND T2.num
              = (SELECT MIN(num)
                   FROM T
                  WHERE num > T1.num
                    AND data <> T1.data)
 GROUP BY T1.data, T2.num;

low high data
--- ---- ----
  1    2    a
  3    6    b
  8    8    a

CREATE TABLE Boxes
(box_id CHAR(1) NOT NULL,
 dim CHAR(1) NOT NULL,
    PRIMARY KEY (box_id, dim),
 low INTEGER NOT NULL,
 high INTEGER NOT NULL);

INSERT INTO Boxes VALUES('A', 'x',0,2);
INSERT INTO Boxes VALUES('A', 'y',0,2);
INSERT INTO Boxes VALUES('A', 'z',0,2);
INSERT INTO Boxes VALUES('B', 'x',1,3);
INSERT INTO Boxes VALUES('B', 'y',1,3);
INSERT INTO Boxes VALUES('B', 'z',1,3);
INSERT INTO Boxes VALUES('C', 'x',10,12);
INSERT INTO Boxes VALUES('C', 'y',0,4);
INSERT INTO Boxes VALUES('C', 'z',0,100);

• 関係除算については「21．飛行機と飛行士」を参照。

CREATE TABLE PriceByAge
(product_id CHAR(10) NOT NULL,
 low_age INTEGER NOT NULL,
 high_age INTEGER NOT NULL,
 CHECK (low_age < high_age),
 product_price DECIMAL (12,4) NOT NULL,
 PRIMARY KEY (product_id, low_age));

--Product1は×、Product2は○
INSERT INTO PriceByAge VALUES ('Product1', 5, 15, 20.00);
INSERT INTO PriceByAge VALUES ('Product1', 16, 60, 18.00);
INSERT INTO PriceByAge VALUES ('Product1', 65, 150, 17.00);
INSERT INTO PriceByAge VALUES ('Product2', 1, 5, 20.00);
INSERT INTO PriceByAge VALUES ('Product2', 6, 70, 25.00);
INSERT INTO PriceByAge VALUES ('Product2', 71, 150, 40.00);

CREATE TABLE SudokuGrid
(i INTEGER NOT NULL
    CHECK (i BETWEEN 1 AND 9),
 j INTEGER NOT NULL
    CHECK (j BETWEEN 1 AND 9),
 val INTEGER NOT NULL
    CHECK (val BETWEEN 1 AND 9),
 region_nbr INTEGER NOT NULL,
    PRIMARY KEY (i, j, val));

CREATE TABLE Digits
 (digit INTEGER PRIMARY KEY); 

INSERT INTO Digits VALUES (1);
INSERT INTO Digits VALUES (2);
INSERT INTO Digits VALUES (3);
INSERT INTO Digits VALUES (4);
INSERT INTO Digits VALUES (5);
INSERT INTO Digits VALUES (6);
INSERT INTO Digits VALUES (7);
INSERT INTO Digits VALUES (8);
INSERT INTO Digits VALUES (9);

• より発展的な解法を扱う方法は、CodeZine の記事「SQLによる数独の解法」、「SQLによる数独の高速解法」、「動的SQLによる数独の超高速解法」を参照。

CREATE TABLE Schedule
(route_nbr INTEGER NOT NULL,
 depart_time TIMESTAMP NOT NULL,
 arrive_time TIMESTAMP NOT NULL,
    CHECK (depart_time < arrive_time),
    PRIMARY KEY (route_nbr, depart_time));

INSERT INTO Schedule VALUES (3, '2006-02-09 10:00', '2006-02-09 14:00');
INSERT INTO Schedule VALUES (4, '2006-02-09 16:00', '2006-02-09 17:00');
INSERT INTO Schedule VALUES (5, '2006-02-09 18:00', '2006-02-09 19:00');
INSERT INTO Schedule VALUES (6, '2006-02-09 20:00', '2006-02-09 21:00');
INSERT INTO Schedule VALUES (7, '2006-02-09 11:00', '2006-02-09 13:00');
INSERT INTO Schedule VALUES (8, '2006-02-09 15:00', '2006-02-09 16:00');
INSERT INTO Schedule VALUES (9, '2006-02-09 18:00', '2006-02-09 20:00');

CREATE TABLE Schedule
(route_nbr INTEGER NOT NULL,
 wait_time TIMESTAMP NOT NULL,
 depart_time TIMESTAMP NOT NULL,
 arrive_time TIMESTAMP NOT NULL,
    CHECK (depart_time < arrive_time),
    PRIMARY KEY (route_nbr, depart_time));

INSERT INTO Schedule VALUES (3, '2006-02-09 00:00', '2006-02-09 10:00', '2006-02-09 14:00');
INSERT INTO Schedule VALUES (7, '2006-02-09 10:00', '2006-02-09 11:00', '2006-02-09 13:00');
INSERT INTO Schedule VALUES (8, '2006-02-09 11:00', '2006-02-09 15:00', '2006-02-09 16:00');
INSERT INTO Schedule VALUES (4, '2006-02-09 15:00', '2006-02-09 16:00', '2006-02-09 17:00');
INSERT INTO Schedule VALUES (5, '2006-02-09 16:00', '2006-02-09 18:00', '2006-02-09 19:00');
INSERT INTO Schedule VALUES (9, '2006-02-09 16:00', '2006-02-09 18:00', '2006-02-09 20:00');
INSERT INTO Schedule VALUES (6, '2006-02-09 18:00', '2006-02-09 20:00', '2006-02-09 21:00');

• テーブル駆動型の解法については、28．正弦関数を作るも参照。

CREATE TABLE WidgetInventory
(receipt_nbr INTEGER NOT NULL PRIMARY KEY,
 purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
 qty_on_hand INTEGER NOT NULL
    CHECK (qty_on_hand >= 0),
 unit_price DECIMAL (12,4) NOT NULL);

INSERT INTO WidgetInventory VALUES(1, '2005-08-01', 15, 10.00);
INSERT INTO WidgetInventory VALUES(2, '2005-08-02', 25, 12.00);
INSERT INTO WidgetInventory VALUES(3, '2005-08-03', 40, 13.00);
INSERT INTO WidgetInventory VALUES(4, '2005-08-04', 35, 12.00);
INSERT INTO WidgetInventory VALUES(5, '2005-08-05', 45, 10.00);

CREATE TABLE StockHistory
(ticker_sym CHAR(5) NOT NULL,
 sale_date DATE DEFAULT CURRENT_DATE NOT NULL,
 closing_price DECIMAL (10,4) NOT NULL,
 trend INTEGER DEFAULT 0 NOT NULL
    CHECK(trend IN(-1, 0, 1)),
    PRIMARY KEY (ticker_sym, sale_date));

--4月1日(trend=0)
INSERT INTO StockHistory (ticker_sym, sale_date, closing_price, trend)
VALUES ('XXX', '2007-04-01', 100, 
        COALESCE(SIGN(100
             - (SELECT H1.closing_price
                  FROM StockHistory  H1
                 WHERE H1.ticker_sym = StockHistory.ticker_sym
                   AND H1.sale_date
                         = (SELECT MAX(sale_date)
                              FROM StockHistory  H2
                             WHERE H2.ticker_sym = 'XXX'
                               AND H2.sale_date  < '2007-04-01'))), 0));


--4月3日(trend=1)
INSERT INTO StockHistory (ticker_sym, sale_date, closing_price, trend)
VALUES ('XXX', '2007-04-03', 200, 
        COALESCE(SIGN(200
             - (SELECT H1.closing_price
                  FROM StockHistory  H1
                 WHERE H1.ticker_sym = StockHistory.ticker_sym
                   AND H1.sale_date
                         = (SELECT MAX(sale_date)
                              FROM StockHistory  H2
                             WHERE H2.ticker_sym = 'XXX'
                               AND H2.sale_date  < '2007-04-03'))), 0));

--4月2日(trend=-1)
INSERT INTO StockHistory (ticker_sym, sale_date, closing_price, trend)
VALUES ('XXX', '2007-04-02',300, 
        COALESCE(SIGN(300
             - (SELECT H1.closing_price
                  FROM StockHistory  H1
                 WHERE H1.ticker_sym = StockHistory.ticker_sym
                   AND H1.sale_date
                         = (SELECT MAX(sale_date)
                              FROM StockHistory  H2
                             WHERE H2.ticker_sym = 'XXX'
                               AND H2.sale_date  < '2007-04-02'))), 0));

--3日の trend が再計算されない。
ticke sale_dat closing_price trend
----- -------- ------------- -----
XXX   07-04-01           100     0
XXX   07-04-03           200     1
XXX   07-04-02            50     1

INSERT INTO StockHistory VALUES(1, '2007-04-01', 100);
INSERT INTO StockHistory VALUES(1, '2007-04-02', 200);
INSERT INTO StockHistory VALUES(1, '2007-04-03', 199);
INSERT INTO StockHistory VALUES(1, '2007-04-04', 199);
INSERT INTO StockHistory VALUES(2, '2006-10-10', 10);
INSERT INTO StockHistory VALUES(2, '2007-04-14', 20);
INSERT INTO StockHistory VALUES(2, '2007-04-20',  5);

--答え３：修正版
SELECT ticker_sym, sale_date, closing_price, 
       SIGN(closing_price - 
                (SELECT closing_price
                   FROM StockHistory H2
                  WHERE H1.ticker_sym = H2.ticker_sym
                    AND H2.sale_date = (SELECT MAX(H3.sale_date)
                                          FROM StockHistory H3
                                         WHERE H2.ticker_sym = H3.ticker_sym
                                           AND H3.sale_date < H1.sale_date))) AS trend
  FROM StockHistory H1;

ticker_sym  sale_date   closing_price  trend
---------- ----------- --------------- ------
1           2007-04-01       100.0000 
1           2007-04-02       200.0000      1
1           2007-04-03       199.0000     -1
1           2007-04-04       199.0000      0
2           2006-10-10        10.0000 
2           2007-04-14        20.0000      1
2           2007-04-20         5.0000     -1

CREATE TABLE Foobar
(empl_id CHAR(6) NOT NULL, pin_nbr CHAR(5) NOT NULL,
 empl_rcd INTEGER NOT NULL, calc_rslt_val INTEGER NOT NULL,
 calc_adj_val INTEGER NOT NULL, unit_rslt_val INTEGER NOT NULL,
 unit_adj_val INTEGER NOT NULL, PRIMARY KEY (empl_id, pin_nbr));

INSERT INTO Foobar VALUES('xxxxxx', '52636', 0, 10, 20, 30, 40);
INSERT INTO Foobar VALUES('xxxxxx', '52751', 0,  5,  6,  7,  8);
INSERT INTO Foobar VALUES('xxxxxx', '52768', 0, 20, 40, 60, 80);

--答え２
SELECT DISTINCT
         SUM(F1.calc_rslt_val + F1.calc_adj_val) AS calc_1,
         SUM(F1.unit_rslt_val + F1.unit_adj_val) AS unit_1,
         SUM(f2.calc_rslt_val + f2.calc_adj_val) AS calc_2,
         SUM(f2.unit_rslt_val + f2.unit_adj_val) AS unit_2,
         SUM(f3.calc_rslt_val + f3.calc_adj_val) AS calc_3,
         SUM(f3.unit_rslt_val + f3.unit_adj_val) AS unit_3
  FROM Foobar F1, Foobar f2, Foobar f3
 WHERE F1.empl_id = 'xxxxxx'
   AND f2.empl_id = 'xxxxxx'
   AND f3.empl_id = 'xxxxxx'
   AND F1.empl_rcd = 0
   AND F1.pin_nbr = '52636'
   AND f2.pin_nbr = '52751'
   AND f3.pin_nbr = '52768';

calc_1 unit_1 calc_2 unit_2 calc_3 unit_3
------ ------ ------ ------ ------ ------
    30     70     11     15     60    140

--答え３
SELECT F1.pin_nbr,
       SUM(F1.calc_rslt_val + F1.calc_adj_val) AS calc_val,
       SUM(F1.unit_rslt_val + F1.unit_adj_val) AS unit_val
  FROM Foobar F1
 WHERE F1.empl_id = 'xxxxxx'
   AND F1.empl_rcd = 0
   AND F1.pin_nbr IN ('52636', '52751', '52768')
 GROUP BY F1.pin_nbr;

pin_nbr calc_val unit_val
-----   -------- --------
52636         30       70
52751         11       15
52768         60      140

CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY,
 first_name VARCHAR(15) NOT NULL,
 last_name VARCHAR(20) NOT NULL,
 phone_nbr CHAR(15) NOT NULL,
 phone_nbr_2 CHAR(15),
 client_street VARCHAR(35) NOT NULL,
 client_city_name VARCHAR(20) NOT NULL);

CREATE TABLE Personnel
(emp_id CHAR(9) NOT NULL PRIMARY KEY,
 first_name VARCHAR(15) NOT NULL,
 last_name VARCHAR(20) NOT NULL,
 home_phome_nbr CHAR(15) NOT NULL,
 cell_phone_nbr CHAR(15) NOT NULL,
 street_addr VARCHAR(35) NOT NULL,
 city_name VARCHAR(20) NOT NULL,
 zip_code CHAR(5) NOT NULL);

CREATE TABLE ScheduledCalls
(client_id INTEGER NOT NULL
    REFERENCES Clients (client_id),
 scheduled_start_time TIMESTAMP NOT NULL,
 scheduled_end_time TIMESTAMP NOT NULL,
    CHECK (scheduled_start_time < scheduled_end_time),
 emp_id CHAR(9) DEFAULT '{xxxxxxx}' NOT NULL
    REFERENCES Personnel (emp_id),
    PRIMARY KEY (client_id, emp_id, scheduled_start_time));

CREATE TABLE PersonnelSchedule
(emp_id CHAR(9) NOT NULL
    REFERENCES Personnel(emp_id),
 avail_start_time TIMESTAMP NOT NULL,
 avail_end_time TIMESTAMP NOT NULL,
    CHECK (avail_start_time < avail_end_time),
    PRIMARY KEY (emp_id, avail_start_time));

--顧客テーブル
INSERT INTO Clients VALUES (1, '太郎', '田中', '03-5478-1243', '090-442-8765', '世田谷', '東京');
INSERT INTO Clients VALUES (2, '次郎', '鈴木', '03-9999-3487', '090-532-7543', '入谷', '東京');
INSERT INTO Clients VALUES (3, '三郎', '鎌田', '03-8765-3432', '090-223-1111', '中野', '東京');

--社員テーブル
INSERT INTO Personnel VALUES (1, 'aaa', 'bbb', '0', '0', 'hoge', 'hage', 'huga');
INSERT INTO Personnel VALUES (2, 'aaa', 'bbb', '0', '0', 'hoge', 'hage', 'huga');
INSERT INTO Personnel VALUES (3, 'aaa', 'bbb', '0', '0', 'hoge', 'hage', 'huga');

--予約テーブル
INSERT INTO ScheduledCalls VALUES(1, '2007-01-01:12:00', '2007-01-01:17:00', 1);
INSERT INTO ScheduledCalls VALUES(1, '2007-01-01:18:00', '2007-01-01:21:00', 2);
INSERT INTO ScheduledCalls VALUES(2, '2007-01-02:09:00', '2007-01-02:15:00', 3);
INSERT INTO ScheduledCalls VALUES(3, '2007-01-02:18:00', '2007-01-02:21:00', 2);

--対応可能な社員テーブル
INSERT INTO PersonnelSchedule VALUES (1, '2007-01-01:09:00', '2007-01-01:15:00');
INSERT INTO PersonnelSchedule VALUES (2, '2007-01-02:07:00', '2007-01-02:08:00');
INSERT INTO PersonnelSchedule VALUES (2, '2007-01-02:17:00', '2007-01-02:21:00');
INSERT INTO PersonnelSchedule VALUES (3, '2007-01-02:17:00', '2007-01-02:22:00');

--答え２
SELECT P.emp_id,
       S.client_id,
       S.scheduled_start_time,
       S.scheduled_end_time
  FROM ScheduledCalls S,
       PersonnelSchedule P
 WHERE S.emp_id = P.emp_id
   AND P.emp_id <> '{xxxxxxx}'
   AND S.scheduled_start_time BETWEEN P.avail_start_time AND P.avail_end_time
   AND S.scheduled_end_time   BETWEEN P.avail_start_time AND P.avail_end_time;

emp_id	client_id  scheduled_start_time	 scheduled_end_time
------  ---------  --------------------  -------------------
2	3	   20-07-01 02:18:00	 20-07-01 02:21:00

• 時間データの扱い方については、68．バスを待ちながら、テーブル設計の練習がしたい方は16．主任とアシスタントを参照。

CREATE TABLE Staging
 (col1  INTEGER,
  col2  INTEGER,
  col3  INTEGER,
  col4  INTEGER,
  col5  INTEGER,
  col6  INTEGER,
  col7  INTEGER,
  col8  INTEGER,
  col9  INTEGER,
  col10 INTEGER);

--オールNULL
INSERT INTO Staging VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

--オール1
INSERT INTO Staging VALUES(1, 1, 1, 1, 1, 1, 1, 1, 1, 1);

--少なくとも一つは9
INSERT INTO Staging VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9);
INSERT INTO Staging VALUES(NULL, NULL, NULL, NULL, NULL, NULL, 0, 1, 9, 9);

--答え３（SELECTに変更）
SELECT *
  FROM Staging
 WHERE COALESCE(col1 ,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9 ,col10) IS NULL;

col1  col2  col3  col4  col5  col6  col7  col8  col9  col10
----  ----  ----  ----  ----  ----  ----  -----  ----  ----
NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL

--オール1の行を求める
SELECT *
  FROM Staging
 WHERE 1 = ALL (col1 ,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9 ,col10);

col1  col2  col3  col4  col5  col6  col7  col8  col9  col10
----  ----  ----  ----  ----  ----  ----  -----  ----  ----
   1     1     1     1     1     1     1     1     1     1

CREATE TABLE Accounts
 (acct_nbr INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE Foo
 (acct_nbr INTEGER NOT NULL
    REFERENCES Accounts(acct_nbr),
  foo_qty INTEGER NOT NULL);

CREATE TABLE Bar
(acct_nbr INTEGER NOT NULL
    REFERENCES Accounts(acct_nbr),
 bar_qty INTEGER NOT NULL);

INSERT INTO Accounts VALUES (1);
INSERT INTO Accounts VALUES (2);
INSERT INTO Accounts VALUES (3);
INSERT INTO Accounts VALUES (4);

INSERT INTO Foo VALUES (1, 10);
INSERT INTO Foo VALUES (2, 20);
INSERT INTO Foo VALUES (2, 40);
INSERT INTO Foo VALUES (3, 80);

INSERT INTO Bar VALUES (2, 160);
INSERT INTO Bar VALUES (3, 320);
INSERT INTO Bar VALUES (3, 640);
INSERT INTO Bar VALUES (3, 1);

SELECT A.acct_nbr,
       COALESCE(SUM(F.foo_qty), 0) AS foo_qty_tot,
       COALESCE(MAX(B.bar_qty), 0) AS bar_qty_tot
  FROM Accounts A
       LEFT OUTER JOIN Foo F
         ON A.acct_nbr = F.acct_nbr
            LEFT OUTER JOIN
            (SELECT acct_nbr, SUM(bar_qty) AS bar_qty
               FROM Bar
              GROUP BY acct_nbr) B
              ON A.acct_nbr = B.acct_nbr
 GROUP BY A.acct_nbr;

acct_nbr foo_qty_tot bar_qty_tot
-------- ----------- -----------
       1          10           0
       2          60         160
       3          80         961
       4           0           0

CREATE TABLE PubMap
(pub_id CHAR(5) NOT NULL PRIMARY KEY,
x INTEGER NOT NULL,
y INTEGER NOT NULL);

--始点(0, 0)
INSERT INTO PubMap VALUES(1, 0,  0);

--半径１：○
INSERT INTO PubMap VALUES(2, 0,  1);
INSERT INTO PubMap VALUES(3, 1,  0);

--半径√2：×
INSERT INTO PubMap VALUES(4, 1,  1);
--半径√5：×
INSERT INTO PubMap VALUES(5, -2, 0);

円近傍モデルで居酒屋検索