�w�B�l�Ɋw�� SQL�O��w�쏑�x�T�|�[�g�y�[�W

• �ĉj�Ђ̃T�C�g�ɂ́A�{���̓��e�Ɋւ���Q & A���f�ڂ���Ă��܂��B��������Q�Ƃ̂قǁB

/* �����̃R�[�h�̌n��V�����̌n�ɕϊ����ďW�v */
CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
 population INTEGER NOT NULL);

INSERT INTO PopTbl VALUES('����', 100);
INSERT INTO PopTbl VALUES('����', 200);
INSERT INTO PopTbl VALUES('���Q', 150);
INSERT INTO PopTbl VALUES('���m', 200);
INSERT INTO PopTbl VALUES('����', 300);
INSERT INTO PopTbl VALUES('����', 100);
INSERT INTO PopTbl VALUES('����', 200);
INSERT INTO PopTbl VALUES('����', 400);
INSERT INTO PopTbl VALUES('�Q�n', 50);


/* �قȂ�����̏W�v��1�‚�SQL�ōs�� */
CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
 sex CHAR(1) NOT NULL,
 population INTEGER NOT NULL,
    PRIMARY KEY(pref_name, sex));

INSERT INTO PopTbl2 VALUES('����', '1',	60 );
INSERT INTO PopTbl2 VALUES('����', '2',	40 );
INSERT INTO PopTbl2 VALUES('����', '1',	100);
INSERT INTO PopTbl2 VALUES('����', '2',	100);
INSERT INTO PopTbl2 VALUES('���Q', '1',	100);
INSERT INTO PopTbl2 VALUES('���Q', '2',	50 );
INSERT INTO PopTbl2 VALUES('���m', '1',	100);
INSERT INTO PopTbl2 VALUES('���m', '2',	100);
INSERT INTO PopTbl2 VALUES('����', '1',	100);
INSERT INTO PopTbl2 VALUES('����', '2',	200);
INSERT INTO PopTbl2 VALUES('����', '1',	20 );
INSERT INTO PopTbl2 VALUES('����', '2',	80 );
INSERT INTO PopTbl2 VALUES('����', '1',	125);
INSERT INTO PopTbl2 VALUES('����', '2',	125);
INSERT INTO PopTbl2 VALUES('����', '1',	250);
INSERT INTO PopTbl2 VALUES('����', '2',	150);


/* CHECK����ŕ����̗�̏����֌W���`���� */
CREATE TABLE TestSal
(sex CHAR(1) ,
 salary INTEGER,
    CONSTRAINT check_salary CHECK
             ( CASE WHEN sex = '2'
                    THEN CASE WHEN salary <= 200000
                              THEN 1 ELSE 0 END
                    ELSE 1 END = 1 ));

INSERT INTO TestSal VALUES(1, 200000);
INSERT INTO TestSal VALUES(1, 300000);
INSERT INTO TestSal VALUES(1, NULL);
INSERT INTO TestSal VALUES(2, 200000);
INSERT INTO TestSal VALUES(2, 300000);  --error
INSERT INTO TestSal VALUES(2, NULL);
INSERT INTO TestSal VALUES(1, 300000);


/* �����𕪊򂳂���UPDATE */
CREATE TABLE SomeTable
(p_key CHAR(1) PRIMARY KEY,
 col_1 INTEGER NOT NULL, 
 col_2 CHAR(2) NOT NULL);

INSERT INTO SomeTable VALUES('a', 1, '��');
INSERT INTO SomeTable VALUES('b', 2, '��');
INSERT INTO SomeTable VALUES('c', 3, '��');


/* �e�[�u�����m�̃}�b�`���O */
CREATE TABLE CourseMaster
(course_id   INTEGER PRIMARY KEY,
 course_name VARCHAR(32) NOT NULL);

INSERT INTO CourseMaster VALUES(1, '�o������');
INSERT INTO CourseMaster VALUES(2, '�����m��');
INSERT INTO CourseMaster VALUES(3, '��L����');
INSERT INTO CourseMaster VALUES(4, '�ŗ��m');

CREATE TABLE OpenCourses
(month       INTEGER ,
 course_id   INTEGER ,
    PRIMARY KEY(month, course_id));

INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);


/* CASE���̒��ŏW��֐����g�� */
CREATE TABLE StudentClub
(std_id  INTEGER,
 club_id INTEGER,
 club_name VARCHAR(32),
 main_club_flg CHAR(1),
 PRIMARY KEY (std_id, club_id));

INSERT INTO StudentClub VALUES(100, 1, '�싅',        'Y');
INSERT INTO StudentClub VALUES(100, 2, '���t�y',      'N');
INSERT INTO StudentClub VALUES(200, 2, '���t�y',      'N');
INSERT INTO StudentClub VALUES(200, 3, '�o�h�~���g��','Y');
INSERT INTO StudentClub VALUES(200, 4, '�T�b�J�[',    'N');
INSERT INTO StudentClub VALUES(300, 4, '�T�b�J�[',    'N');
INSERT INTO StudentClub VALUES(400, 5, '���j',        'N');
INSERT INTO StudentClub VALUES(500, 6, '�͌�',        'N');

/* ���R�[�h��n���R�[�h�ɍĕ��ނ���(p.11) */
SELECT CASE pref_name
             WHEN '����' THEN '�l��'
             WHEN '����' THEN '�l��'
             WHEN '���Q' THEN '�l��'
             WHEN '���m' THEN '�l��'
             WHEN '����' THEN '��B'
             WHEN '����' THEN '��B'
             WHEN '����' THEN '��B'
             ELSE '���̑�' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY CASE pref_name
             WHEN '����' THEN '�l��'
             WHEN '����' THEN '�l��'
             WHEN '���Q' THEN '�l��'
             WHEN '���m' THEN '�l��'
             WHEN '����' THEN '��B'
             WHEN '����' THEN '��B'
             WHEN '����' THEN '��B'
             ELSE '���̑�' END;

/* �l���K�����Ƃɓs���{���𕪗ނ���(p.11) */
SELECT CASE WHEN population < 100 THEN '01'
            WHEN population >= 100 AND population < 200 THEN '02'
            WHEN population >= 200 AND population < 300 THEN '03'
            WHEN population >= 300 THEN '04'
            ELSE NULL END AS pop_class,
       COUNT(*) AS cnt
  FROM PopTbl
 GROUP BY CASE WHEN population < 100 THEN '01'
               WHEN population >= 100 AND population < 200 THEN '02'
               WHEN population >= 200 AND population < 300 THEN '03'
               WHEN population >= 300 THEN '04'
               ELSE NULL END;

/* �n���P�ʂɃR�[�h���ĕ��ނ���@���̂Q�FCASE ������ӏ��ɂ܂Ƃ߂�(p.12) */
SELECT CASE pref_name
              WHEN '����' THEN '�l��'
              WHEN '����' THEN '�l��'
              WHEN '���Q' THEN '�l��'
              WHEN '���m' THEN '�l��'
              WHEN '����' THEN '��B'
              WHEN '����' THEN '��B'
              WHEN '����' THEN '��B'
              ELSE '���̑�' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY district;

/* �قȂ�����̏W�v��1 �‚�SQL �ōs�Ȃ�(p.14) */
SELECT pref_name,
       /* �j���̐l�� */
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
       /* �����̐l�� */
       SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
  FROM PopTbl2
 GROUP BY pref_name;


/* CHECK ����ŕ����̗�̏����֌W���`���� */
/* �����@(p.15) */
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
       THEN CASE WHEN salary <= 200000
                 THEN 1 ELSE 0 END
       ELSE 1 END = 1 )

/* �_����(p.16) */
CONSTRAINT check_salary CHECK
( sex = '2' AND salary <= 200000 )

/* �����𕪊򂳂���UPDATE(p.17) */
UPDATE Personnel
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salary END;

/* CASE ���Ŏ�L�[�����ւ���(p.19) */
UPDATE SomeTable
   SET p_key = CASE WHEN p_key = 'a'
                    THEN 'b'
                    WHEN p_key = 'b'
                    THEN 'a'
                    ELSE p_key END
 WHERE p_key IN ('a', 'b');

/* �e�[�u���̃}�b�`���O�FIN �q��̗��p(p.21) */
SELECT CM.course_name,
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200706) THEN '��'
            ELSE '�~' END AS "200706",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200707) THEN '��'
            ELSE '�~' END AS "200707",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200708) THEN '��'
            ELSE '�~' END  AS "200708"
  FROM CourseMaster CM;

/* �e�[�u���̃}�b�`���O�FEXISTS �q��̗��p(p.21) */
SELECT CM.course_name,
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200706
                        AND CM.course_id = OC.course_id) THEN '��'
            ELSE '�~' END AS "200706",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200707
                        AND CM.course_id = OC.course_id) THEN '��'
            ELSE '�~' END AS "200707",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200708
                        AND CM.course_id = OC.course_id) THEN '��'
            ELSE '�~' END  AS "200708"
  FROM CourseMaster CM;

/* CASE ���̒��ŏW��֐����g��(p.23) */
SELECT std_id,
       CASE WHEN COUNT(*) = 1 /* ��‚̃N���u�ɐ�O����w���̏ꍇ */
            THEN MAX(club_id)
            ELSE MAX(CASE WHEN main_club_flg = 'Y'
                          THEN club_id
                          ELSE NULL END)
        END AS main_club
  FROM StudentClub
 GROUP BY std_id;

• �d�v�Ȃ̂����ǂ��܂�L����������Ă��Ȃ����Ƃ�I��I�ɏE���B�d�v�ł��L����������Ă���A���邢�͎��グ����@��̑����g�s�b�N�͈���Ȃ��i�Ⴆ�΃p�t�H�[�}���X�E�`���[�j���O�j�B�u���������Ȃ���΂���10�N�N�������܂��v�Ƃ����悤�Șb���ɏo���B
• �P�Ȃ� Tips �̗񋓂ɏI���̂ł͂Ȃ��A���ɐ��ސ��w�Ƙ_���iSQL �̏ꍇ�͏W���_�Əq��_���j�ɂ܂œ͂��悤�ȁu�[���v����������B���������āA�����ˑ��̋@�\�͋ɗ͎g��Ȃ��i�Ƃ����‚A�ŏ�����s�|�C���^�̘b���Ă邶��Ȃ��́j�B
• 10�l��10�l���u���ɗ������v�ƌ�������́A10�l��2, 3�l�Ɂu�ց[�ASQL���Ėʔ���[�ȁv�Ɗ����Ă��炦�΂�����B

CREATE TABLE Products
(name VARCHAR(16) PRIMARY KEY,
 price INTEGER NOT NULL);

--�d������E����E�g�ݍ��킹
INSERT INTO Products VALUES('���',	100);
INSERT INTO Products VALUES('�݂���',	50);
INSERT INTO Products VALUES('�o�i�i',	80);

--�����I�ɕs��v�ȃL�[�̌���
CREATE TABLE Addresses
(name VARCHAR(32),
 family_id INTEGER,
 address VARCHAR(32),
 PRIMARY KEY(name, family_id));

INSERT INTO Addresses VALUES('�O�c �`��', '100', '�����s�`��Ճm��3-2-29');
INSERT INTO Addresses VALUES('�O�c �R��', '100', '�����s�`��Ճm��3-2-92');
INSERT INTO Addresses VALUES('���� ��',   '200', '�����s�V�h�搼�V�h2-8-1');
INSERT INTO Addresses VALUES('���� ��',   '200', '�����s�V�h�搼�V�h2-8-1');
INSERT INTO Addresses VALUES('�z�[���Y',  '300', '�x�[�J�[�X221B');
INSERT INTO Addresses VALUES('���g�\��',  '400', '�x�[�J�[�X221B');

--�����L���O
DELETE FROM Products;
INSERT INTO Products VALUES('���',	50);
INSERT INTO Products VALUES('�݂���',	100);
INSERT INTO Products VALUES('�Ԃǂ�',	50);
INSERT INTO Products VALUES('�X�C�J',	80);
INSERT INTO Products VALUES('������',	30);
INSERT INTO Products VALUES('�o�i�i',	50);

--��W��e�X�g�p
DELETE FROM Products;
INSERT INTO Products VALUES('�݂���',	100);
INSERT INTO Products VALUES('�Ԃǂ�',	50);
INSERT INTO Products VALUES('�X�C�J',	80);
INSERT INTO Products VALUES('������',	30);

/* �d������𓾂�SQL(p.29) */
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1, Products P2;

/* ����𓾂�SQL(p.30) */
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1, Products P2
 WHERE P1.name <> P2.name;

/* �g�ݍ��킹�𓾂�SQL(p.31) */
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;

/* �d���s���폜����SQL ����1�F�ɒl�֐��̗��p(p.33) */
DELETE FROM Products P1
 WHERE rowid < ( SELECT MAX(P2.rowid)
                   FROM Products P2
                  WHERE P1.name = P2. name
                    AND P1.price = P2.price ) ;

/* �d���s���폜����SQL ����2�F�񓙒l�����̗��p(p.34) */
DELETE FROM Products P1
 WHERE EXISTS ( SELECT *
                  FROM Products P2
                 WHERE P1.name = P2.name
                   AND P1.price = P2.price
                   AND P1.rowid < P2.rowid );

/* �����Ƒ������ǁA�Z�����Ⴄ���R�[�h����������(p.35) */
SELECT DISTINCT A1.name, A1.address
  FROM Addresses A1, Addresses A2
 WHERE A1.family_id = A2.family_id
   AND A1.address <> A2.address ;

/* �����l�i�����ǁA���i�����Ⴄ���R�[�h���������� (p.36) */
SELECT DISTINCT P1.name, P1.price
  FROM Products P1, Products P2
 WHERE P1.price = P2.price
   AND P1.name <> P2.name;

/* �����L���O�Z�o�FOLAP �֐��̗��p(p.37) */
SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS rank_1,
       DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
  FROM Products;

/* �����L���O 1 �ʂ���n�܂�B�����ʂ���������͕s�A��(p.37) */
SELECT P1.name,
       P1.price,
      (SELECT COUNT(P2.price)
         FROM Products P2
        WHERE P2.price > P1.price) + 1 AS rank_1
 FROM Products P1;

/* �����L���O�����߂�F���Ȍ����̗��p(p.40) */
SELECT P1.name,
       MAX(P1.price) AS price,
       COUNT(P2.name) +1 AS rank_1
  FROM Products P1 LEFT OUTER JOIN Products P2
    ON P1.price < P2.price
 GROUP BY P1.name;

/* �����L���O�����߂�F���������ɕς���ƁE�E�E(p.41) */
SELECT P1.name,
       MAX(P1.price) AS price,
       COUNT(P2.name) +1 AS rank_1
  FROM Products P1 INNER JOIN Products P2
    ON P1.price < P2.price
 GROUP BY P1.name;

/* ���H�ҁF�P. ��r�q���NULL�@���̂P�F�r�������������Ȃ� */
CREATE TABLE Students
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER );

INSERT INTO Students VALUES('�u���E��', 22);
INSERT INTO Students VALUES('�����[',   19);
INSERT INTO Students VALUES('�W����',   NULL);
INSERT INTO Students VALUES('�{�M�[', 21);


/* ���H�ҁF�R. NOT IN ��NOT EXISTS �͓��l�ł͂Ȃ� */
CREATE TABLE Class_A
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

CREATE TABLE Class_B
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

INSERT INTO Class_A VALUES('�u���E��', 22, '����');
INSERT INTO Class_A VALUES('�����[',   19, '���');
INSERT INTO Class_A VALUES('�{�M�[',   21, '��t');

INSERT INTO Class_B VALUES('�֓�',  22,   '����');
INSERT INTO Class_B VALUES('�c�K',  23,   '����');
INSERT INTO Class_B VALUES('�R�c',  NULL, '����');
INSERT INTO Class_B VALUES('�a��',  18,   '��t');
INSERT INTO Class_B VALUES('���c',  20,   '��t');
INSERT INTO Class_B VALUES('�ΐ�',  19,   '�_�ސ�');


/* ���H�ҁF4�D����q���NULL */
DELETE FROM Class_A;
INSERT INTO Class_A VALUES('�u���E��', 22, '����');
INSERT INTO Class_A VALUES('�����[',   19, '���');
INSERT INTO Class_A VALUES('�{�M�[',   21, '��t');

DELETE FROM Class_B;
INSERT INTO Class_B VALUES('�֓�', 22, '����');
INSERT INTO Class_B VALUES('�c�K', 23, '����');
INSERT INTO Class_B VALUES('�R�c', 20, '����');
INSERT INTO Class_B VALUES('�a��', 18, '��t');
INSERT INTO Class_B VALUES('���c', 20, '��t');
INSERT INTO Class_B VALUES('�ΐ�', 19, '�_�ސ�');


/* ���H�ҁF5�D����q��Ƌɒl�֐��͓��l�ł͂Ȃ� */
DELETE FROM Class_B;
INSERT INTO Class_B VALUES('�a��', 18, '��t');
INSERT INTO Class_B VALUES('���c', 20, '��t');
INSERT INTO Class_B VALUES('�ΐ�', 19, '�_�ސ�');

/* �N�20 �΂��A20 �΂łȂ����k��I������(p.52) */
SELECT *
  FROM Students
 WHERE age = 20
    OR age <> 20;

/* ��3 �̏�����lj��F�u�N�20 �΂��A20 �΂łȂ����A�܂��͔N�������Ȃ��v(p.53) */
SELECT *
  FROM Students
 WHERE age = 20
    OR age <> 20
    OR age IS NULL;

/* B �N���X�̓����ݏZ�̐��k�ƔN���v���Ȃ�A �N���X�̐��k��I������SQL�H (p.55) */
SELECT *
  FROM Class_A
 WHERE age NOT IN ( SELECT age
                      FROM Class_B
                     WHERE city = '����' );

/* ������SQL�F�����[�ƃ{�M�[���I�������(p.57) */
SELECT *
  FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
                      FROM Class_B B
                     WHERE A.age = B.age
                       AND B.city = '����' );

/* B �N���X�̓����ݏZ�̒N�����ႢA �N���X�̐��k��I������(p.59) */
SELECT *
  FROM Class_A
 WHERE age < ALL ( SELECT age
                     FROM Class_B
                    WHERE city = '����' );

/* �����ݏZ�̐��k�̕��ϔN����ႢA �N���X�̐��k��I������SQL�H (p.61) */
SELECT *
  FROM Class_A
 WHERE age < ( SELECT AVG(age)
                 FROM Class_B
                WHERE city = '����' );

/* �f�[�^�̎�������T�� */
CREATE TABLE SeqTbl
(seq  INTEGER PRIMARY KEY,
 name VARCHAR(16) NOT NULL);

INSERT INTO SeqTbl VALUES(1,	'�f�B�b�N');
INSERT INTO SeqTbl VALUES(2,	'�A��');
INSERT INTO SeqTbl VALUES(3,	'���C��');
INSERT INTO SeqTbl VALUES(5,	'�J�[');
INSERT INTO SeqTbl VALUES(6,	'�}���[');
INSERT INTO SeqTbl VALUES(8,	'�x��');


/* HAVING ��ŃT�u�N�G���F�ŕp�l�����߂�(���W�A���������T���v�����g�p) */
CREATE TABLE Graduates
(name   VARCHAR(16) PRIMARY KEY,
 income INTEGER NOT NULL);

INSERT INTO Graduates VALUES('�T���v�\��', 400000);
INSERT INTO Graduates VALUES('�}�C�N',     30000);
INSERT INTO Graduates VALUES('�z���C�g',   20000);
INSERT INTO Graduates VALUES('�A�[�m���h', 20000);
INSERT INTO Graduates VALUES('�X�~�X',     20000);
INSERT INTO Graduates VALUES('�������X',   15000);
INSERT INTO Graduates VALUES('�n�h�\��',   15000);
INSERT INTO Graduates VALUES('�P���g',     10000);
INSERT INTO Graduates VALUES('�x�b�J�[',   10000);
INSERT INTO Graduates VALUES('�X�R�b�g',   10000);


/* NULL ���܂܂Ȃ��W����T�� */
CREATE TABLE Students
(student_id   INTEGER PRIMARY KEY,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE);

INSERT INTO Students VALUES(100,  '���w��',   '2005-10-10');
INSERT INTO Students VALUES(101,  '���w��',   '2005-09-22');
INSERT INTO Students VALUES(102,  '���w��',   NULL);
INSERT INTO Students VALUES(103,  '���w��',   '2005-09-10');
INSERT INTO Students VALUES(200,  '���w��',   '2005-09-22');
INSERT INTO Students VALUES(201,  '�H�w��',   NULL);
INSERT INTO Students VALUES(202,  '�o�ϊw��', '2005-09-25');


/* �֌W���Z�Ńo�X�P�b�g��� */
CREATE TABLE Items
(item VARCHAR(16) PRIMARY KEY);
 
CREATE TABLE ShopItems
(shop VARCHAR(16),
 item VARCHAR(16),
    PRIMARY KEY(shop, item));

INSERT INTO Items VALUES('�r�[��');
INSERT INTO Items VALUES('���I���c');
INSERT INTO Items VALUES('���]��');

INSERT INTO ShopItems VALUES('���',  '�r�[��');
INSERT INTO ShopItems VALUES('���',  '���I���c');
INSERT INTO ShopItems VALUES('���',  '���]��');
INSERT INTO ShopItems VALUES('���',  '�J�[�e��');
INSERT INTO ShopItems VALUES('����',  '�r�[��');
INSERT INTO ShopItems VALUES('����',  '���I���c');
INSERT INTO ShopItems VALUES('����',  '���]��');
INSERT INTO ShopItems VALUES('���',  '�e���r');
INSERT INTO ShopItems VALUES('���',  '���I���c');
INSERT INTO ShopItems VALUES('���',  '���]��');

/* ���ʂ��Ԃ�Ύ���������(p.65) */
SELECT '����������' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

/* �������̍ŏ��l��T��(p.67) */
SELECT MIN(seq + 1) AS gap
  FROM SeqTbl
 WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

/* �ŕp�l�����߂�SQL�@����1�FALL�q��̗��p(p.68) */
  SELECT income
    FROM Graduates
GROUP BY income
  HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                             FROM Graduates
                         GROUP BY income);

/* �ŕp�l�����߂�SQL�@����2�F�ɒl�֐��̗��p(p.69) */
SELECT income
  FROM Graduates
 GROUP BY income
HAVING COUNT(*) >=  ( SELECT MAX(cnt)
                        FROM ( SELECT COUNT(*) AS cnt
                                 FROM Graduates
                             GROUP BY income) TMP) ;

/* ���W�A�������߂�SQL�F���Ȕ񓙒l������HAVING��Ŏg��(p.70) */
SELECT AVG(DISTINCT income)
  FROM (SELECT T1.income
          FROM Graduates T1, Graduates T2
      GROUP BY T1.income
               /* S1�̏��� */
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) 
                   >= COUNT(*) / 2
               /* S2�̏��� */
           AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) 
                   >= COUNT(*) / 2 ) TMP;

/* ��o����NULL���܂܂Ȃ��w����I������@���̂P�FCOUNT �֐��̗��p(p.73) */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

/* ��o����NULL���܂܂Ȃ��w����I������@���̂Q�FCASE ���̗��p(p.73) */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
                           THEN 1
                           ELSE 0 END);

/* �r�[���Ǝ��I���c�Ǝ��]�Ԃ����ׂĒu���Ă���X�܂���������F�Ԉ����SQL(p.74) */
SELECT DISTINCT shop
  FROM ShopItems
 WHERE item IN (SELECT item FROM Items);

/* �r�[���Ǝ��I���c�Ǝ��]�Ԃ����ׂĒu���Ă���X�܂���������F������ SQL(p.75) */
SELECT SI.shop
  FROM ShopItems SI, Items I
 WHERE SI.item = I.item
 GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);

/* COUNT(I.item) �͂��͂�3 �Ƃ͌���Ȃ�(p.75) */
SELECT SI.shop, COUNT(SI.item), COUNT(I.item)
  FROM ShopItems SI, Items I
 WHERE SI.item = I.item
 GROUP BY SI.shop;

/* �����Ȋ֌W���Z�F�O��������COUNT�֐��̗��p(p.76) */
  SELECT SI.shop
    FROM ShopItems AS SI LEFT OUTER JOIN Items AS I
      ON SI.item=I.item
GROUP BY SI.shop
  HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items)   /* ����1 */
     AND COUNT(I.item)  = (SELECT COUNT(item) FROM Items);  /* ����2 */

/* �O�������ōs��ϊ��@����1�i�s����j�F�N���X�\����� */
CREATE TABLE Courses
(name   VARCHAR(32), 
 course VARCHAR(32), 
 PRIMARY KEY(name, course));

INSERT INTO Courses VALUES('�Ԉ�', 'SQL����');
INSERT INTO Courses VALUES('�Ԉ�', 'UNIX��b');
INSERT INTO Courses VALUES('���', 'SQL����');
INSERT INTO Courses VALUES('�H��', 'SQL����');
INSERT INTO Courses VALUES('�H��', 'Java����');
INSERT INTO Courses VALUES('�g�c', 'UNIX��b');
INSERT INTO Courses VALUES('�n��', 'SQL����');

/* �O�������ōs��ϊ��@����2�i�񁨍s�j�F�J��Ԃ����ڂ�1 ��ɂ܂Ƃ߂� */
CREATE TABLE Personnel
 (employee   varchar(32), 
  child_1    varchar(32), 
  child_2    varchar(32), 
  child_3    varchar(32), 
  PRIMARY KEY(employee));

INSERT INTO Personnel VALUES('�Ԉ�', '��Y', '��Y', '�O�Y');
INSERT INTO Personnel VALUES('�H��', '�t�q', '�Ďq', NULL);
INSERT INTO Personnel VALUES('���', '�Ďq', NULL,   NULL);
INSERT INTO Personnel VALUES('�g�c', NULL,   NULL,   NULL);

/* �N���X�\�œ���q�̕\������� */
CREATE TABLE TblSex
(sex_cd   char(1), 
 sex varchar(5), 
 PRIMARY KEY(sex_cd));

CREATE TABLE TblAge 
(age_class char(1), 
 age_range varchar(30), 
 PRIMARY KEY(age_class));

CREATE TABLE TblPop 
(pref_name  varchar(30), 
 age_class  char(1), 
 sex_cd     char(1), 
 population integer, 
 PRIMARY KEY(pref_name, age_class,sex_cd));

INSERT INTO TblSex (sex_cd, sex ) VALUES('m',	'�j');
INSERT INTO TblSex (sex_cd, sex ) VALUES('f',	'��');

INSERT INTO TblAge (age_class, age_range ) VALUES('1',	'21�`30��');
INSERT INTO TblAge (age_class, age_range ) VALUES('2',	'31�`40��');
INSERT INTO TblAge (age_class, age_range ) VALUES('3',	'41�`50��');

INSERT INTO TblPop VALUES('�H�c', '1', 'm', 400 );
INSERT INTO TblPop VALUES('�H�c', '3', 'm', 1000 );
INSERT INTO TblPop VALUES('�H�c', '1', 'f', 800 );
INSERT INTO TblPop VALUES('�H�c', '3', 'f', 1000 );
INSERT INTO TblPop VALUES('�X', '1', 'm', 700 );
INSERT INTO TblPop VALUES('�X', '1', 'f', 500 );
INSERT INTO TblPop VALUES('�X', '3', 'f', 800 );
INSERT INTO TblPop VALUES('����', '1', 'm', 900 );
INSERT INTO TblPop VALUES('����', '1', 'f', 1500 );
INSERT INTO TblPop VALUES('����', '3', 'f', 1200 );
INSERT INTO TblPop VALUES('��t', '1', 'm', 900 );
INSERT INTO TblPop VALUES('��t', '1', 'f', 1000 );
INSERT INTO TblPop VALUES('��t', '3', 'f', 900 );

/* �|���Z�Ƃ��Ă̌��� */
CREATE TABLE Items
 (item_no INTEGER PRIMARY KEY,
  item    VARCHAR(32) NOT NULL);

INSERT INTO Items VALUES(10, 'FD');
INSERT INTO Items VALUES(20, 'CD-R');
INSERT INTO Items VALUES(30, 'MO');
INSERT INTO Items VALUES(40, 'DVD');

CREATE TABLE SalesHistory
 (sale_date DATE NOT NULL,
  item_no   INTEGER NOT NULL,
  quantity  INTEGER NOT NULL,
  PRIMARY KEY(sale_date, item_no));

INSERT INTO SalesHistory VALUES('2007-10-01',  10,  4);
INSERT INTO SalesHistory VALUES('2007-10-01',  20, 10);
INSERT INTO SalesHistory VALUES('2007-10-01',  30,  3);
INSERT INTO SalesHistory VALUES('2007-10-03',  10, 32);
INSERT INTO SalesHistory VALUES('2007-10-03',  30, 12);
INSERT INTO SalesHistory VALUES('2007-10-04',  20, 22);
INSERT INTO SalesHistory VALUES('2007-10-04',  30,  7);

/* ���S�O������ */
CREATE TABLE Class_A
(id char(1), 
 name varchar(30), 
 PRIMARY KEY(id));

CREATE TABLE Class_B
(id   char(1), 
 name varchar(30), 
 PRIMARY KEY(id));

INSERT INTO Class_A (id, name) VALUES('1', '�c��');
INSERT INTO Class_A (id, name) VALUES('2', '���');
INSERT INTO Class_A (id, name) VALUES('3', '�ɏW�@');

INSERT INTO Class_B (id, name) VALUES('1', '�c��');
INSERT INTO Class_B (id, name) VALUES('2', '���');
INSERT INTO Class_B (id, name) VALUES('4', '������');

/* �N���X�\�����߂鐅���W�J�@����1�F�O�������̗��p(p.83) */
SELECT C0.name,
       CASE WHEN C1.name IS NOT NULL THEN '��' ELSE NULL END AS "SQL����",
       CASE WHEN C2.name IS NOT NULL THEN '��' ELSE NULL END AS "UNIX��b",
       CASE WHEN C3.name IS NOT NULL THEN '��' ELSE NULL END AS "Java����"
  FROM  (SELECT DISTINCT name FROM  Courses) C0
    LEFT OUTER JOIN
    (SELECT name FROM Courses WHERE course = 'SQL����' ) C1
    ON  C0.name = C1.name
      LEFT OUTER JOIN
        (SELECT name FROM Courses WHERE course = 'UNIX��b' ) C2
        ON  C0.name = C2.name
          LEFT OUTER JOIN
            (SELECT name FROM Courses WHERE course = 'Java����' ) C3
            ON  C0.name = C3.name;

/* �����W�J�@����2�F�X�J���E�T�u�N�G���̗��p(p.84) */
SELECT  C0.name,
  (SELECT '��'
     FROM Courses C1
    WHERE course = 'SQL����'
      AND C1.name = C0.name) AS "SQL����",
  (SELECT '��'
     FROM Courses C2
    WHERE course = 'UNIX��b'
      AND C2.name = C0.name) AS "UNIX��b",
  (SELECT '��'
     FROM Courses C3
    WHERE course = 'Java����'
      AND C3.name = C0.name) AS "Java����"
  FROM (SELECT DISTINCT name FROM Courses) C0;

/* �����W�J�@����3�FCASE�������q�ɂ���(p.85) */
SELECT  name,
        CASE WHEN SUM(CASE WHEN course = 'SQL����' THEN 1 ELSE NULL END) >= 1
             THEN '��' ELSE NULL END AS "SQL����",
        CASE WHEN SUM(CASE WHEN course = 'UNIX��b' THEN 1 ELSE NULL END) >= 1
             THEN '��' ELSE NULL END AS "UNIX��b",
        CASE WHEN SUM(CASE WHEN course = 'Java����' THEN 1 ELSE NULL END) >= 1
             THEN '��' ELSE NULL END AS "Java����"
  FROM Courses
 GROUP BY name;

/* �񂩂�s�ւ̕ϊ��FUNION ALL�̗��p(p.86) */
SELECT employee, child_1 AS child FROM Personnel
UNION ALL
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel;

/* �Ј��̎q�����X�g�𓾂�SQL�i�q���̂��Ȃ��Ј����o�͂���j(p.86) */
SELECT EMP.employee, CHILDREN.child
FROM   Personnel EMP
  LEFT OUTER JOIN
   (SELECT child_1 AS child FROM Personnel
    UNION
    SELECT child_2 AS child FROM Personnel
    UNION
    SELECT child_3 AS child FROM Personnel) CHILDREN
  ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

/* �q���}�X�^�̃r���[(p.87) */
CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
   UNION
   SELECT child_2 FROM Personnel
   UNION
   SELECT child_3 FROM Personnel;

/* �Ј��̎q�����X�g�𓾂�SQL�i�q���̂��Ȃ��Ј����o�͂���j(p.87) */
SELECT EMP.employee, CHILDREN.child
  FROM Personnel EMP
       LEFT OUTER JOIN Children
    ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

/* �O�������œ���q�̕\�������F�Ԉ����SQL(p.89) */
SELECT MASTER1.age_class AS age_class,
       MASTER2.sex_cd AS sex_cd,
       DATA.pop_tohoku AS pop_tohoku,
       DATA.pop_kanto AS pop_kanto
  FROM (SELECT age_class, sex_cd,
               SUM(CASE WHEN pref_name IN ('�X', '�H�c')
                        THEN population ELSE NULL END) AS pop_tohoku,
               SUM(CASE WHEN pref_name IN ('����', '��t')
                        THEN population ELSE NULL END) AS pop_kanto
          FROM TblPop
         GROUP BY age_class, sex_cd) DATA
        RIGHT OUTER JOIN TblAge MASTER1 /* �O������1�F�N��K���}�X�^�ƌ��� */
           ON MASTER1.age_class = DATA.age_class
              RIGHT OUTER JOIN TblSex MASTER2 /* �O������2�F���ʃ}�X�^�ƌ��� */
                 ON MASTER2.sex_cd = DATA.sex_cd;

/* �ŏ��̊O�������Ŏ~�߂��ꍇ�F�N��K���u2�v�����ʂɌ����(p.90) */
SELECT MASTER1.age_class AS age_class,
       DATA.sex_cd AS sex_cd,
       DATA.pop_tohoku AS pop_tohoku,
       DATA.pop_kanto AS pop_kanto
  FROM (SELECT age_class, sex_cd,
               SUM(CASE WHEN pref_name IN ('�X', '�H�c')
                        THEN population ELSE NULL END) AS pop_tohoku,
               SUM(CASE WHEN pref_name IN ('����', '��t')
                        THEN population ELSE NULL END) AS pop_kanto
          FROM TblPop
         GROUP BY age_class, sex_cd) DATA
        RIGHT OUTER JOIN TblAge MASTER1
           ON MASTER1.age_class = DATA.age_class;

/* �O�������œ���q�̕\�������F������SQL(p.91) */
SELECT
  MASTER.age_class AS age_class,
  MASTER.sex_cd    AS sex_cd,
  DATA.pop_tohoku  AS pop_tohoku,
  DATA.pop_kanto   AS pop_kanto
FROM
  (SELECT
     age_class,
     sex_cd,
     SUM(CASE WHEN pref_name IN ('�X', '�H�c')
              THEN population ELSE NULL END) AS pop_tohoku,
     SUM(CASE WHEN pref_name IN ('����', '��t')
              THEN population ELSE NULL END) AS pop_kanto
   FROM TblPop
   GROUP BY age_class, sex_cd) DATA
     RIGHT OUTER JOIN
       (SELECT age_class, sex_cd
          FROM TblAge 
                CROSS JOIN
               TblSex ) MASTER
     ON  MASTER.age_class = DATA.age_class
    AND  MASTER.sex_cd    = DATA.sex_cd;

/* �����@���̂P�F�����̑O�ɏW�񂷂邱�ƂŁA��Έ�̊֌W�����(p.93) */
SELECT I.item_no, SH.total_qty
  FROM Items I LEFT OUTER JOIN
       (SELECT item_no, SUM(quantity) AS total_qty
          FROM SalesHistory
         GROUP BY item_no) SH
    ON I.item_no = SH.item_no;

/* �����@���̂Q�F�W��̑O�Ɉ�Α��̌������s��(p.94) */
SELECT I.item_no, SUM(SH.quantity) AS total_qty
  FROM Items I LEFT OUTER JOIN SalesHistory SH
    ON I.item_no = SH.item_no /* ��Α��̌��� */
 GROUP BY I.item_no;

/* ���S�O�������͏����u���S�v�ɕۑ�����(p.95) */
SELECT COALESCE(A.id, B.id) AS id,
       A.name AS A_name,
       B.name AS B_name
FROM Class_A  A  FULL OUTER JOIN Class_B  B
  ON A.id = B.id;

/* ���S�O���������g���Ȃ��‹��ł̑�֕��@(p.96) */
SELECT A.id AS id, A.name, B.name
  FROM Class_A  A   LEFT OUTER JOIN Class_B  B
    ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
  FROM Class_A  A  RIGHT OUTER JOIN Class_B  B
    ON A.id = B.id;

/* �O�������ō��W�������߂�FA-B(p.97) */
SELECT A.id AS id,  A.name AS A_name
  FROM Class_A  A LEFT OUTER JOIN Class_B B
    ON A.id = B.id
 WHERE B.name IS NULL;

/* �O�������ō��W�������߂�FB-A(p.98) */
SELECT B.id AS id, B.name AS B_name
  FROM Class_A  A  RIGHT OUTER JOIN Class_B B
    ON A.id = B.id
 WHERE A.name IS NULL;

/* ���S�O�������Ŕr���I�a�W�������߂�(p.98) */
SELECT COALESCE(A.id, B.id) AS id,
       COALESCE(A.name , B.name ) AS name
  FROM Class_A  A  FULL OUTER JOIN Class_B  B
    ON A.id = B.id
 WHERE A.name IS NULL 
    OR B.name IS NULL;

/* �O�������Ŋ֌W���Z�F���W���̉��p(p.99) */
SELECT DISTINCT shop
  FROM ShopItems SI1
WHERE NOT EXISTS
      (SELECT I.item 
         FROM Items I LEFT OUTER JOIN ShopItems SI2
           ON SI1.shop = SI2.shop
          AND I.item   = SI2.item 
        WHERE SI2.item IS NULL) ;

--�����E��ށE����ێ�
CREATE TABLE Sales
(year INTEGER NOT NULL , 
 sale INTEGER NOT NULL ,
 PRIMARY KEY (year));

INSERT INTO Sales VALUES (1990, 50);
INSERT INTO Sales VALUES (1991, 51);
INSERT INTO Sales VALUES (1992, 52);
INSERT INTO Sales VALUES (1993, 52);
INSERT INTO Sales VALUES (1994, 50);
INSERT INTO Sales VALUES (1995, 50);
INSERT INTO Sales VALUES (1996, 49);
INSERT INTO Sales VALUES (1997, 55);

--���n��Ɏ�����������ꍇ�F���߂Ɣ�r
CREATE TABLE Sales2
(year INTEGER NOT NULL , 
 sale INTEGER NOT NULL , 
 PRIMARY KEY (year));

INSERT INTO Sales2 VALUES (1990, 50);
INSERT INTO Sales2 VALUES (1992, 50);
INSERT INTO Sales2 VALUES (1993, 52);
INSERT INTO Sales2 VALUES (1994, 55);
INSERT INTO Sales2 VALUES (1997, 55);

--�ړ��݌v�ƈړ�����
CREATE TABLE Accounts
(prc_date DATE NOT NULL , 
 prc_amt  INTEGER NOT NULL , 
 PRIMARY KEY (prc_date)) ;

INSERT INTO Accounts VALUES ('2006-10-26',  12000 );
INSERT INTO Accounts VALUES ('2006-10-28',   2500 );
INSERT INTO Accounts VALUES ('2006-10-31', -15000 );
INSERT INTO Accounts VALUES ('2006-11-03',  34000 );
INSERT INTO Accounts VALUES ('2006-11-04',  -5000 );
INSERT INTO Accounts VALUES ('2006-11-06',   7200 );
INSERT INTO Accounts VALUES ('2006-11-11',  11000 );

--�I�[�o�[���b�v������Ԃ𒲂ׂ�
CREATE TABLE Reservations
(reserver    VARCHAR(30) PRIMARY KEY,
 start_date  DATE  NOT NULL,
 end_date    DATE  NOT NULL);

INSERT INTO Reservations VALUES('�ؑ�', '2006-10-26', '2006-10-27');
INSERT INTO Reservations VALUES('�r��', '2006-10-28', '2006-10-31');
INSERT INTO Reservations VALUES('�x',   '2006-10-31', '2006-11-01');
INSERT INTO Reservations VALUES('�R�{', '2006-11-03', '2006-11-04');
INSERT INTO Reservations VALUES('���c', '2006-11-03', '2006-11-05');
INSERT INTO Reservations VALUES('���J', '2006-11-06', '2006-11-06');

--�R�{���̓��h����4���̏ꍇ
DELETE FROM Reservations WHERE reserver = '�R�{';
INSERT INTO Reservations VALUES('�R�{', '2006-11-04', '2006-11-04');

/* �O�N�ƔN���������N�x�����߂�@����1�F���փT�u�N�G���̗��p(p.106) */
SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year;

/* �O�N�ƔN���������N�x�����߂�@����2�F���Ȍ����̗��p(p.106) */
SELECT S1.year, S1.sale
  FROM Sales S1, 
       Sales S2
 WHERE S2.sale = S1.sale
   AND S2.year = S1.year - 1
 ORDER BY year;

/* �����A��ށA����ێ�����x�ɋ��߂�@����1�F���փT�u�N�G���̗��p(p.107) */
SELECT S1.year, S1.sale,
       CASE WHEN sale =
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '��' /* ���΂� */
            WHEN sale >
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '��' /* ���� */
            WHEN sale <
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '��' /* ��� */
       ELSE '-' END AS var
  FROM Sales S1
 ORDER BY year;

/* �����A��ށA����ێ�����x�ɋ��߂�@����2�F���Ȍ����̗��p(p.108)
   ���{���̓�–ڂ̕���́A�u���v�ł͂Ȃ��u���v�̊ԈႢ�ł��B */
SELECT S1.year, S1.sale,
       CASE SIGN(sale -
                    (SELECT sale
                       FROM Sales S2
                      WHERE S2.year = S1.year - 1) )
       WHEN 0 THEN  '��' 
       WHEN 1 THEN  '��' 
       WHEN -1 THEN '��' 
       ELSE '-' END AS var
  FROM Sales S1
 ORDER BY year;

/* ���߂̔N�x�Ɠ����N���̔N�x��I������(p.109) */
SELECT year, sale
  FROM Sales2 S1
 WHERE sale =
   (SELECT sale
      FROM Sales2 S2
     WHERE S2.year =
       (SELECT MAX(year)            /* ����2�F����1�𖞂����N�x�̒��ōő� */
          FROM Sales2 S3
         WHERE S1.year > S3.year))  /* ����1�F�������ߋ��ł��� */
 ORDER BY year;

/* ���߂̔N�x�Ɠ����N���̔N�x��I������F���Ȍ����ƕ��p(p.109) */
SELECT S2.year AS pre_year,
       S1.year AS now_year
  FROM Sales2 S1, Sales2 S2
 WHERE S1.sale = S2.sale
   AND S2.year = (SELECT MAX(year)
                    FROM Sales2 S3
                   WHERE S1.year > S3.year)
 ORDER BY now_year;

/* �ݐύ������߂�@����1�F�J�n���_�͌��ʂɊ܂܂�Ȃ�(p.109) */
SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale  AS diff
 FROM Sales2 S1, Sales2 S2
 WHERE S2.year = (SELECT MAX(year)
                    FROM Sales2 S3
                   WHERE S1.year > S3.year)
 ORDER BY now_year;

/* �ݐύ������߂�@����2�F���ȊO�������̗��p�B�J�n���_�����ʂɊ܂܂��(p.110) */
SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale AS diff
 FROM Sales2 S1 LEFT OUTER JOIN Sales2 S2
   ON S2.year = (SELECT MAX(year)
                   FROM Sales2 S3
                  WHERE S1.year > S3.year)
 ORDER BY now_year;

/* �݌v�����߂�FOLAP �֐��̗��p(p.111) */
SELECT prc_date, prc_amt,
       SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
  FROM Accounts;

/* �݌v�����߂�F�m�C�}���^�ċA�W���̗��p(p.111) */
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
  FROM Accounts A1
 ORDER BY prc_date;

/* �ړ��݌v�����߂�@���̂P�FOLAP �֐��̗��p(p.113) */
SELECT prc_date, prc_amt,
       SUM(prc_amt) OVER (ORDER BY prc_date
                           ROWS 2 PRECEDING) AS onhand_amt
  FROM Accounts;

/* �ړ��݌v�����߂�@����2�F3�s�ɖ����Ȃ����Ԃ��o�͂���(p.113) */
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date
          AND (SELECT COUNT(*)
                 FROM Accounts A3
                WHERE A3.prc_date 
                  BETWEEN A2.prc_date AND A1.prc_date  ) <= 3 ) AS mvg_sum
  FROM Accounts A1
 ORDER BY prc_date;

/* �ړ��݌v�����߂�@����3�F3�s�ɖ����Ȃ����Ԃ͖�������(p.114) */
SELECT prc_date, A1.prc_amt,
 (SELECT SUM(prc_amt)
    FROM Accounts A2
   WHERE A1.prc_date >= A2.prc_date
     AND (SELECT COUNT(*)
            FROM Accounts A3
           WHERE A3.prc_date 
             BETWEEN A2.prc_date AND A1.prc_date  ) <= 3
   HAVING  COUNT(*) =3) AS mvg_sum  /* 3�s�����͔�\�� */
  FROM  Accounts A1
 ORDER BY prc_date;

/* �I�[�o�[���b�v������Ԃ����߂�(p.117) */
SELECT reserver, start_date, end_date
  FROM Reservations R1
 WHERE EXISTS
       (SELECT *
          FROM Reservations R2
         WHERE R1.reserver <> R2.reserver  /* �����ȊO�̋q�Ɣ�r���� */
           AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date    /* ����(1)�F�J�n�������̊��ԓ��ɂ��� */
              OR R1.end_date  BETWEEN R2.start_date AND R2.end_date));  /* ����(2)�F�I���������̊��ԓ��ɂ��� */


/* ���p�ŁF��������S�Ɋ܂ފ��Ԃ��o�͂���(p.118) */
SELECT reserver, start_date, end_date
 FROM Reservations R1
WHERE EXISTS
       (SELECT *
          FROM Reservations R2
         WHERE R1.reserver <> R2.reserver
           AND (  (     R1.start_date BETWEEN R2.start_date AND R2.end_date
                     OR R1.end_date   BETWEEN R2.start_date AND R2.end_date)
                OR (    R2.start_date BETWEEN R1.start_date AND R1.end_date
                    AND R2.end_date   BETWEEN R1.start_date AND R1.end_date)));

/* �e�[�u�����m�̃R���y�A�@�W���̑������`�F�b�N */
CREATE TABLE Tbl_A
 (keycol  CHAR(1) PRIMARY KEY,
  col_1   INTEGER , 
  col_2   INTEGER, 
  col_3   INTEGER);

CREATE TABLE Tbl_B
 (keycol  CHAR(1) PRIMARY KEY,
  col_1   INTEGER, 
  col_2   INTEGER, 
  col_3   INTEGER);

/* �������e�[�u�����m�̃P�[�X */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', 2, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', 5, 1, 6);

DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', 2, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', 5, 1, 6);


/* �uB�v�̍s�����Ⴗ��P�[�X */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', 2, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', 5, 1, 6);

DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', 2, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 8);
INSERT INTO Tbl_B VALUES('C', 5, 1, 6);


/* NULL���܂ރP�[�X�i�������j */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', NULL, NULL, NULL);

DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', NULL, NULL, NULL);


/* NULL���܂ރP�[�X�i�uC�v�̍s���قȂ�j */
DELETE FROM Tbl_A;
INSERT INTO Tbl_A VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_A VALUES('B', 0, 7, 9);
INSERT INTO Tbl_A VALUES('C', NULL, NULL, NULL);

DELETE FROM Tbl_B;
INSERT INTO Tbl_B VALUES('A', NULL, 3, 4);
INSERT INTO Tbl_B VALUES('B', 0, 7, 9);
INSERT INTO Tbl_B VALUES('C', 0, NULL, NULL);


/* 3. ���W���Ŋ֌W���Z��\������ */
CREATE TABLE Skills 
(skill VARCHAR(32),
 PRIMARY KEY(skill));

CREATE TABLE EmpSkills 
(emp   VARCHAR(32), 
 skill VARCHAR(32),
 PRIMARY KEY(emp, skill));

INSERT INTO Skills VALUES('Oracle');
INSERT INTO Skills VALUES('UNIX');
INSERT INTO Skills VALUES('Java');

INSERT INTO EmpSkills VALUES('���c', 'Oracle');
INSERT INTO EmpSkills VALUES('���c', 'UNIX');
INSERT INTO EmpSkills VALUES('���c', 'Java');
INSERT INTO EmpSkills VALUES('���c', 'C#');
INSERT INTO EmpSkills VALUES('�_��', 'Oracle');
INSERT INTO EmpSkills VALUES('�_��', 'UNIX');
INSERT INTO EmpSkills VALUES('�_��', 'Java');
INSERT INTO EmpSkills VALUES('����', 'UNIX');
INSERT INTO EmpSkills VALUES('����', 'Oracle');
INSERT INTO EmpSkills VALUES('����', 'PHP');
INSERT INTO EmpSkills VALUES('����', 'Perl');
INSERT INTO EmpSkills VALUES('����', 'C++');
INSERT INTO EmpSkills VALUES('��c��', 'Perl');
INSERT INTO EmpSkills VALUES('�n��', 'Oracle');

/* 4. �����������W�������‚��� */
CREATE TABLE SupParts
(sup  CHAR(32) NOT NULL,
 part CHAR(32) NOT NULL,
 PRIMARY KEY(sup, part));

INSERT INTO SupParts VALUES('A',  '�{���g');
INSERT INTO SupParts VALUES('A',  '�i�b�g');
INSERT INTO SupParts VALUES('A',  '�p�C�v');
INSERT INTO SupParts VALUES('B',  '�{���g');
INSERT INTO SupParts VALUES('B',  '�p�C�v');
INSERT INTO SupParts VALUES('C',  '�{���g');
INSERT INTO SupParts VALUES('C',  '�i�b�g');
INSERT INTO SupParts VALUES('C',  '�p�C�v');
INSERT INTO SupParts VALUES('D',  '�{���g');
INSERT INTO SupParts VALUES('D',  '�p�C�v');
INSERT INTO SupParts VALUES('E',  '�q���[�Y');
INSERT INTO SupParts VALUES('E',  '�i�b�g');
INSERT INTO SupParts VALUES('E',  '�p�C�v');
INSERT INTO SupParts VALUES('F',  '�q���[�Y');

/* 5. �d���s���폜���鍂���ȃN�G��
PostgreSQL�ł́uwith oids�v��CREATE TABLE���̍Ō�ɒlj����邱�� */
CREATE TABLE Products
(name  CHAR(16),
 price INTEGER);

INSERT INTO Products VALUES('���',  50);
INSERT INTO Products VALUES('�݂���', 100);
INSERT INTO Products VALUES('�݂���', 100);
INSERT INTO Products VALUES('�݂���', 100);
INSERT INTO Products VALUES('�o�i�i',  80);

/* �e�[�u�����m�̃R���y�A�F��{��(p.125) */
SELECT COUNT(*) AS row_cnt
  FROM ( SELECT * 
           FROM   tbl_A 
         UNION
         SELECT * 
           FROM   tbl_B ) TMP;

/* �e�[�u�����m�̃R���y�A�F���p�ҁiOracle�ł͒ʂ�Ȃ��j(p.128) */
SELECT DISTINCT CASE WHEN COUNT(*) = 0 
                     THEN '������'
                     ELSE '�قȂ�' END AS result
  FROM ((SELECT * FROM  tbl_A
         UNION
         SELECT * FROM  tbl_B) 
         EXCEPT
        (SELECT * FROM  tbl_A
         INTERSECT 
         SELECT * FROM  tbl_B)) TMP;

/* �e�[�u���ɑ΂���diff�F�r���I�a�W�������߂�(p.128) */
(SELECT * FROM  tbl_A
   EXCEPT
 SELECT * FROM  tbl_B)
 UNION ALL
(SELECT * FROM  tbl_B
   EXCEPT
 SELECT * FROM  tbl_A);

/* ���W���Ŋ֌W���Z�i��]�����������Z�j(p.130) */
SELECT DISTINCT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         EXCEPT
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp);

/* �����������W�������‚���(p.134) */
SELECT SP1.sup, SP2.sup
  FROM SupParts SP1, SupParts SP2 
 WHERE SP1.sup < SP2.sup              /* �Ǝ҂̑g�ݍ��킹����� */
   AND SP1.part = SP2.part            /* �����P�D������ނ̕��i������ */
GROUP BY SP1.sup, SP2.sup 
HAVING COUNT(*) = (SELECT COUNT(*)    /* �����Q�D�����̕��i������ */
                     FROM SupParts SP3 
                    WHERE SP3.sup = SP1.sup)
   AND COUNT(*) = (SELECT COUNT(*) 
                     FROM SupParts SP4 
                    WHERE SP4.sup = SP2.sup);

/* �d���s���폜���鍂���ȃN�G���P�F��W����EXCEPT�ŋ��߂�(p.136) */
DELETE FROM Products
 WHERE rowid IN ( SELECT rowid
                    FROM Products 
                  EXCEPT
                  SELECT MAX(rowid)
                    FROM Products 
                   GROUP BY name, price);

/* �d���s���폜���鍂���ȃN�G���Q�F��W����NOT IN �ŋ��߂�(p.137) */
DELETE FROM Products 
 WHERE rowid NOT IN ( SELECT MAX(rowid)
                        FROM Products 
                       GROUP BY name, price);

/* �e�[�u���ɑ��݁u���Ȃ��v�f�[�^��T�� */
CREATE TABLE Meetings
(meeting CHAR(32) NOT NULL,
 person  CHAR(32) NOT NULL,
 PRIMARY KEY (meeting, person));

INSERT INTO Meetings VALUES('��P��', '�ɓ�');
INSERT INTO Meetings VALUES('��P��', '����');
INSERT INTO Meetings VALUES('��P��', '�Ⓦ');
INSERT INTO Meetings VALUES('��Q��', '�ɓ�');
INSERT INTO Meetings VALUES('��Q��', '�{�c');
INSERT INTO Meetings VALUES('��R��', '�Ⓦ');
INSERT INTO Meetings VALUES('��R��', '����');
INSERT INTO Meetings VALUES('��R��', '�{�c');

/* �S�̗ʉ��@���̂P�F�m��̓�d�ے�̕ϊ��Ɋ���悤 */
CREATE TABLE TestScores
(student_id INTEGER,
 subject    VARCHAR(32) ,
 score      INTEGER,
  PRIMARY KEY(student_id, subject));

INSERT INTO TestScores VALUES(100, '�Z��',100);
INSERT INTO TestScores VALUES(100, '����',80);
INSERT INTO TestScores VALUES(100, '����',80);
INSERT INTO TestScores VALUES(200, '�Z��',80);
INSERT INTO TestScores VALUES(200, '����',95);
INSERT INTO TestScores VALUES(300, '�Z��',40);
INSERT INTO TestScores VALUES(300, '����',90);
INSERT INTO TestScores VALUES(300, '�Љ�',55);
INSERT INTO TestScores VALUES(400, '�Z��',80);

/* �S�̗ʉ��@���̂Q�F�W��VS �q��\�\�����̂͂ǂ������H */
CREATE TABLE Projects
(project_id VARCHAR(32),
 step_nbr   INTEGER ,
 status     VARCHAR(32),
  PRIMARY KEY(project_id, step_nbr));

INSERT INTO Projects VALUES('AA100', 0, '����');
INSERT INTO Projects VALUES('AA100', 1, '�ҋ@');
INSERT INTO Projects VALUES('AA100', 2, '�ҋ@');
INSERT INTO Projects VALUES('B200',  0, '�ҋ@');
INSERT INTO Projects VALUES('B200',  1, '�ҋ@');
INSERT INTO Projects VALUES('CS300', 0, '����');
INSERT INTO Projects VALUES('CS300', 1, '����');
INSERT INTO Projects VALUES('CS300', 2, '�ҋ@');
INSERT INTO Projects VALUES('CS300', 3, '�ҋ@');
INSERT INTO Projects VALUES('DY400', 0, '����');
INSERT INTO Projects VALUES('DY400', 1, '����');
INSERT INTO Projects VALUES('DY400', 2, '����');

/* ��ɑ΂���ʉ��F�I�[���P�̍s��T�� */
CREATE TABLE ArrayTbl
 (keycol CHAR(1) PRIMARY KEY,
  col1  INTEGER,
  col2  INTEGER,
  col3  INTEGER,
  col4  INTEGER,
  col5  INTEGER,
  col6  INTEGER,
  col7  INTEGER,
  col8  INTEGER,
  col9  INTEGER,
  col10 INTEGER);

--�I�[��NULL
INSERT INTO ArrayTbl VALUES('A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO ArrayTbl VALUES('B', 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
--�I�[��1
INSERT INTO ArrayTbl VALUES('C', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
--���Ȃ��Ƃ���‚�9
INSERT INTO ArrayTbl VALUES('D', NULL, NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO ArrayTbl VALUES('E', NULL, 3, NULL, 1, 9, NULL, NULL, 9, NULL, NULL);

/* ���Ȏ҂��������߂�N�G���@���̂P�F���ݗʉ��̉��p(p.146) */
SELECT DISTINCT M1.meeting, M2.person
  FROM Meetings M1 CROSS JOIN Meetings M2
 WHERE NOT EXISTS
        (SELECT *
           FROM Meetings M3
          WHERE M1.meeting = M3.meeting
            AND M2.person = M3.person);

/* ���Ȏ҂��������߂�N�G���@���̂Q�F���W�����Z�̗��p(p.146) */
SELECT M1.meeting, M2.person
  FROM Meetings M1, Meetings M2
EXCEPT
SELECT meeting, person
  FROM Meetings;

/* �S�̗ʉ����̂P�F�m��̓�d�ے�̕ϊ��Ɋ���悤(p.147) */
SELECT DISTINCT student_id
  FROM TestScores TS1
 WHERE NOT EXISTS  /* �ȉ��̏����𖞂����s�����݂��Ȃ� */
        (SELECT *
           FROM TestScores TS2
          WHERE TS2.student_id = TS1.student_id
            AND TS2.score < 50);   /* 50 �_�����̋��� */

/* �S�̗ʉ����̂P�F�m��̓�d�ے�̕ϊ��Ɋ���悤(p.149) */
SELECT DISTINCT student_id
  FROM TestScores TS1
 WHERE subject IN ('�Z��', '����')
   AND NOT EXISTS
        (SELECT *
           FROM TestScores TS2
          WHERE TS2.student_id = TS1.student_id
            AND 1 = CASE WHEN subject = '�Z��' AND score < 80 THEN 1
                         WHEN subject = '����' AND score < 50 THEN 1
                         ELSE 0 END);

/* �S�̗ʉ����̂P�F�m��̓�d�ے�̕ϊ��Ɋ���悤(p.149) */
SELECT student_id
  FROM TestScores TS1
 WHERE subject IN ('�Z��', '����')
   AND NOT EXISTS
        (SELECT *
           FROM TestScores TS2
          WHERE TS2.student_id = TS1.student_id
            AND 1 = CASE WHEN subject = '�Z��' AND score < 80 THEN 1
                         WHEN subject = '����' AND score < 50 THEN 1
                         ELSE 0 END)
 GROUP BY student_id
HAVING COUNT(*) = 2; /* �K��2 ���ȑ����Ă��邱�� */

/* �H��1 �Ԃ܂Ŋ����̃v���W�F�N�g��I���F�W���w���I�ȉ�(p.150) */
SELECT project_id
  FROM Projects
 GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= 1 AND status = '����' THEN 1
                           WHEN step_nbr > 1 AND status = '�ҋ@' THEN 1
                           ELSE 0 END);

/* �H��1 �Ԃ܂Ŋ����̃v���W�F�N�g��I���F�q��_���I�ȉ�(p.151) */
SELECT *
  FROM Projects P1
 WHERE NOT EXISTS
        (SELECT status
           FROM Projects P2
          WHERE P1.project_id = P2. project_id  /* �v���W�F�N�g���Ƃɏ����𒲂ׂ� */
            AND status <> CASE WHEN step_nbr <= 1 /* �S�̕����d�ے�ŕ\������ */
                               THEN '����'
                               ELSE '�ҋ@' END);

/* ������ւ̑S�̗ʉ��F�|�̂Ȃ�����(p.153) */
SELECT *
  FROM ArrayTbl
 WHERE col1 = 1
   AND col2 = 1
   AND col3 = 1
   AND col4 = 1
   AND col5 = 1
   AND col6 = 1
   AND col7 = 1
   AND col8 = 1
   AND col9 = 1
   AND col10 = 1;

/* ������ւ̑S�̗ʉ��F�|�̂��铚��(p.153) */
SELECT *
  FROM ArrayTbl
 WHERE 1 = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

/* ������ւ̑��ݗʉ��F���̂P(p.154) */
SELECT *
  FROM ArrayTbl
 WHERE 9 = ANY (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

/* ������ւ̑��ݗʉ��F���̂Q(p.154) */
SELECT *
  FROM ArrayTbl
 WHERE 9 IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

/* �I�[��NULL �̍s��T���F�Ԉ��������(p.154) */
SELECT *
  FROM ArrayTbl
 WHERE NULL = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

/* �I�[��NULL �̍s��T���F����������(p.154) */
SELECT *
  FROM ArrayTbl
 WHERE COALESCE(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) IS NULL;

--�A�Ԃ���낤
CREATE TABLE Digits
 (digit INTEGER PRIMARY KEY); 

INSERT INTO Digits VALUES (0);
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);

--���Ԃ�S�����߂�
CREATE TABLE SeqTbl
 (seq INTEGER PRIMARY KEY); 

INSERT INTO SeqTbl VALUES (1);
INSERT INTO SeqTbl VALUES (2);
INSERT INTO SeqTbl VALUES (4);
INSERT INTO SeqTbl VALUES (5);
INSERT INTO SeqTbl VALUES (6);
INSERT INTO SeqTbl VALUES (7);
INSERT INTO SeqTbl VALUES (8);
INSERT INTO SeqTbl VALUES (11);
INSERT INTO SeqTbl VALUES (12);

--3�l�Ȃ�ł����ǁA����܂����H
CREATE TABLE Seats
(seat   INTEGER NOT NULL  PRIMARY KEY,
 status CHAR(2) NOT NULL
 CHECK (status IN ('��', '��')) ); 

INSERT INTO Seats VALUES (1,  '��');
INSERT INTO Seats VALUES (2,  '��');
INSERT INTO Seats VALUES (3,  '��');
INSERT INTO Seats VALUES (4,  '��');
INSERT INTO Seats VALUES (5,  '��');
INSERT INTO Seats VALUES (6,  '��');
INSERT INTO Seats VALUES (7,  '��');
INSERT INTO Seats VALUES (8,  '��');
INSERT INTO Seats VALUES (9,  '��');
INSERT INTO Seats VALUES (10, '��');
INSERT INTO Seats VALUES (11, '��');
INSERT INTO Seats VALUES (12, '��');
INSERT INTO Seats VALUES (13, '��');
INSERT INTO Seats VALUES (14, '��');
INSERT INTO Seats VALUES (15, '��');

--�܂�Ԃ����l��
CREATE TABLE Seats2
 ( seat   INTEGER NOT NULL  PRIMARY KEY,
   row_id CHAR(1) NOT NULL,
   status CHAR(2) NOT NULL
     CHECK (status IN ('��', '��')) ); 

INSERT INTO Seats2 VALUES (1, 'A', '��');
INSERT INTO Seats2 VALUES (2, 'A', '��');
INSERT INTO Seats2 VALUES (3, 'A', '��');
INSERT INTO Seats2 VALUES (4, 'A', '��');
INSERT INTO Seats2 VALUES (5, 'A', '��');
INSERT INTO Seats2 VALUES (6, 'B', '��');
INSERT INTO Seats2 VALUES (7, 'B', '��');
INSERT INTO Seats2 VALUES (8, 'B', '��');
INSERT INTO Seats2 VALUES (9, 'B', '��');
INSERT INTO Seats2 VALUES (10,'B', '��');
INSERT INTO Seats2 VALUES (11,'C', '��');
INSERT INTO Seats2 VALUES (12,'C', '��');
INSERT INTO Seats2 VALUES (13,'C', '��');
INSERT INTO Seats2 VALUES (14,'C', '��');
INSERT INTO Seats2 VALUES (15,'C', '��');

--�ő剽�l�܂ō���܂����H
CREATE TABLE Seats3
 ( seat   INTEGER NOT NULL  PRIMARY KEY,
   status CHAR(2) NOT NULL
     CHECK (status IN ('��', '��')) ); 

INSERT INTO Seats3 VALUES (1,  '��');
INSERT INTO Seats3 VALUES (2,  '��');
INSERT INTO Seats3 VALUES (3,  '��');
INSERT INTO Seats3 VALUES (4,  '��');
INSERT INTO Seats3 VALUES (5,  '��');
INSERT INTO Seats3 VALUES (6,  '��');
INSERT INTO Seats3 VALUES (7,  '��');
INSERT INTO Seats3 VALUES (8,  '��');
INSERT INTO Seats3 VALUES (9,  '��');
INSERT INTO Seats3 VALUES (10, '��');

--�P�������ƒP������
CREATE TABLE MyStock
 (deal_date  DATE PRIMARY KEY,
  price      INTEGER ); 

INSERT INTO MyStock VALUES ('2007-01-06', 1000);
INSERT INTO MyStock VALUES ('2007-01-08', 1050);
INSERT INTO MyStock VALUES ('2007-01-09', 1050);
INSERT INTO MyStock VALUES ('2007-01-12', 900);
INSERT INTO MyStock VALUES ('2007-01-13', 880);
INSERT INTO MyStock VALUES ('2007-01-14', 870);
INSERT INTO MyStock VALUES ('2007-01-16', 920);
INSERT INTO MyStock VALUES ('2007-01-17', 1000);

/* �A�Ԃ����߂�@����1�F0�`99 (p.161)*/
SELECT D1.digit + (D2.digit * 10)  AS seq
  FROM Digits D1, Digits D2
ORDER BY seq;

/* �A�Ԃ����߂�@����2�F1�`542�����߂� (p.161) */
SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100) AS seq
  FROM Digits D1, Digits D2, Digits D3
 WHERE D1.digit + (D2.digit * 10) + (D3.digit * 100) BETWEEN 1 AND 542
ORDER BY seq;

/* �V�[�P���X�E�r���[�����i0�`999�܂ł��J�o�[�j(p.162) */
CREATE VIEW Sequence (seq)
AS SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
     FROM Digits D1, Digits D2, Digits D3;

/* �V�[�P���X�E�r���[����1�`100�܂Ŏ擾 (p.162) */
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 100
ORDER BY seq;

/* ���Ԃ�S�����߂�FEXCEPT�o�[�W���� (p.163) */
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
EXCEPT
SELECT seq FROM SeqTbl;

/* ���Ԃ�S�����߂�FNOT IN�o�[�W���� (p.163) */
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
   AND seq NOT IN (SELECT seq FROM SeqTbl);

/* �A�Ԃ͈̔͂𓮓I�Ɍ��肷��N�G�� (p.163) */
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
               AND (SELECT MAX(seq) FROM SeqTbl)
EXCEPT
SELECT seq FROM SeqTbl;

/* �l�����̋�Ȃ�T���@����1�F�s�̐܂�Ԃ����l�����Ȃ� (p.165) */
SELECT S1.seat   AS start_seat, '�`' , S2.seat AS end_seat
  FROM Seats S1, Seats S2
 WHERE S2.seat = S1.seat + (:head_cnt -1)  /* �n�_�ƏI�_�����߂� */
   AND NOT EXISTS
          (SELECT *
             FROM Seats S3
            WHERE S3.seat BETWEEN S1.seat AND S2.seat
              AND S3.status <> '��' )
ORDER BY start_seat;

/* �l�����̋�Ȃ�T���@����2�F�s�̐܂�Ԃ����l������ (p.167) */
SELECT S1.seat   AS start_seat, '�`' , S2.seat AS end_seat
  FROM Seats2 S1, Seats2 S2
 WHERE S2.seat = S1.seat + (:head_cnt -1)  --�n�_�ƏI�_�����߂�
   AND NOT EXISTS
          (SELECT *
             FROM Seats2 S3
            WHERE S3.seat BETWEEN S1.seat AND S2.seat
              AND (    S3.status <> '��'
                    OR S3.row_id <> S1.row_id))
ORDER BY start_seat;

/* ��1�i�K�F���ׂẴV�[�P���X��ێ�����r���[����� (p.169) */
CREATE VIEW Sequences (start_seat, end_seat, seat_cnt) AS
SELECT S1.seat  AS start_seat,
       S2.seat  AS end_seat,
       S2.seat - S1.seat + 1 AS seat_cnt
  FROM Seats3 S1, Seats3 S2
 WHERE S1.seat <= S2.seat  /* �X�e�b�v1�F�n�_�ƏI�_�̑g�ݍ��킹����� */
   AND NOT EXISTS   /* �X�e�b�v2�F�V�[�P���X���̂��ׂĂ̍s���������ׂ��������L�q���� */
       (SELECT *
          FROM Seats3 S3
         WHERE (     S3.seat BETWEEN S1.seat AND S2.seat 
                 AND S3.status <> '��')                         /* ����1�̔ے� */
            OR  (S3.seat = S2.seat + 1 AND S3.status = '��' )    /* ����2�̔ے� */
            OR  (S3.seat = S1.seat - 1 AND S3.status = '��' ));  /* ����3�̔ے� */

/* ��2�i�K�F�ő�̃V�[�P���X�����߂�(p.170) */
SELECT start_seat, '�`', end_seat, seat_cnt
  FROM Sequences
 WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);

/* �P������������Ԃ����߂�N�G���F�����W�����o�͂���(p.172) */
SELECT S1.deal_date   AS start_date,
       S2.deal_date   AS end_date
  FROM MyStock S1, MyStock S2
 WHERE S1.deal_date < S2.deal_date  /* �X�e�b�v1�F�n�_�ƏI�_�̑g�ݍ��킹����� */
   AND  NOT EXISTS                  /* �X�e�b�v2�F���ԓ��̂��ׂĂ̍s���������ׂ��������L�q���� */
           ( SELECT *
               FROM MyStock S3, MyStock S4
              WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
                AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
                AND S3.deal_date < S4.deal_date
                AND S3.price >= S4.price)
ORDER BY start_date, end_date;

--�����W�������O���āA�ő�͈͂̊��Ԃ���������
SELECT MIN(start_date) AS start_date,          /* �n�_���ő���܂��ɐL�΂� */
       end_date
  FROM  (SELECT S1.deal_date AS start_date,
                MAX(S2.deal_date) AS end_date  /* �I�_���ő��������ɐL�΂� */
           FROM MyStock S1, MyStock S2
          WHERE S1.deal_date < S2.deal_date
            AND NOT EXISTS
             (SELECT *
                FROM MyStock S3, MyStock S4
               WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
                 AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
                 AND S3.deal_date < S4.deal_date
                 AND S3.price >= S4.price)
         GROUP BY S1.deal_date) TMP
GROUP BY end_date
ORDER BY start_date;

/* �A�Ԃ���낤�i2�i������10�i���֕ϊ��j*/
CREATE TABLE Digits
 (digit INTEGER PRIMARY KEY); 

INSERT INTO Digits VALUES (0);
INSERT INTO Digits VALUES (1);

/* 0�`31�܂Ŏ擾 */
SELECT D1.digit + D2.digit * 2 + D3.digit * 4 + D4.digit * 8 + D5.digit * 16 AS seq
  FROM Digits D1, Digits D2, Digits D3, Digits D4, Digits D5
 ORDER BY seq;

--�e���A�����_�āI
CREATE TABLE Teams
(member  CHAR(12) NOT NULL PRIMARY KEY,
 team_id INTEGER  NOT NULL,
 status  CHAR(8)  NOT NULL);

INSERT INTO Teams VALUES('�W���[',   1, '�ҋ@');
INSERT INTO Teams VALUES('�P��',     1, '�o����');
INSERT INTO Teams VALUES('�~�b�N',   1, '�ҋ@');
INSERT INTO Teams VALUES('�J����',   2, '�o����');
INSERT INTO Teams VALUES('�L�[�X',   2, '�x��');
INSERT INTO Teams VALUES('�W����',   3, '�ҋ@');
INSERT INTO Teams VALUES('�n�[�g',   3, '�ҋ@');
INSERT INTO Teams VALUES('�f�B�b�N', 3, '�ҋ@');
INSERT INTO Teams VALUES('�x�X',     4, '�ҋ@');
INSERT INTO Teams VALUES('�A����',   5, '�o����');
INSERT INTO Teams VALUES('���o�[�g', 5, '�x��');
INSERT INTO Teams VALUES('�P�[�K��', 5, '�ҋ@');

--��ӏW���Ƒ��d�W��
CREATE TABLE Materials
(center         CHAR(12) NOT NULL,
 receive_date   DATE     NOT NULL,
 material       CHAR(12) NOT NULL,
 PRIMARY KEY(center, receive_date));

INSERT INTO Materials VALUES('����'	,'2007-4-01',	'��');
INSERT INTO Materials VALUES('����'	,'2007-4-12',	'����');
INSERT INTO Materials VALUES('����'	,'2007-5-17',	'�A���~�j�E��');
INSERT INTO Materials VALUES('����'	,'2007-5-20',	'����');
INSERT INTO Materials VALUES('���'	,'2007-4-20',	'��');
INSERT INTO Materials VALUES('���'	,'2007-4-22',	'�j�b�P��');
INSERT INTO Materials VALUES('���'	,'2007-4-29',	'��');
INSERT INTO Materials VALUES('����',	'2007-3-15',	'�`�^��');
INSERT INTO Materials VALUES('����',	'2007-4-01',	'�Y�f�|');
INSERT INTO Materials VALUES('����',	'2007-4-24',	'�Y�f�|');
INSERT INTO Materials VALUES('����',	'2007-5-02',	'�}�O�l�V�E��');
INSERT INTO Materials VALUES('����',	'2007-5-10',	'�`�^��');
INSERT INTO Materials VALUES('����'	,'2007-5-10',	'����');
INSERT INTO Materials VALUES('����'	,'2007-5-28',	'��');

--���Ԃ�T���F���W��
CREATE TABLE SeqTbl
( seq INTEGER NOT NULL PRIMARY KEY);

--�������Ȃ��F�J�n�l��1
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(1);
INSERT INTO SeqTbl VALUES(2);
INSERT INTO SeqTbl VALUES(3);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);

--����������F�J�n�l��1
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(1);
INSERT INTO SeqTbl VALUES(2);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);
INSERT INTO SeqTbl VALUES(8);

--�������Ȃ��F�J�n�l��1�ł͂Ȃ�
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(3);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);
INSERT INTO SeqTbl VALUES(6);
INSERT INTO SeqTbl VALUES(7);

--����������F�J�n�l��1�ł͂Ȃ�
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(3);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(7);
INSERT INTO SeqTbl VALUES(8);
INSERT INTO SeqTbl VALUES(10);

--�W���ɂ��ߍׂ��ȏ�����ݒ肷��
CREATE TABLE TestResults
(student CHAR(12) NOT NULL PRIMARY KEY,
 class   CHAR(1)  NOT NULL,
 sex     CHAR(1)  NOT NULL,
 score   INTEGER  NOT NULL);

INSERT INTO TestResults VALUES('001', 'A', '�j', 100);
INSERT INTO TestResults VALUES('002', 'A', '��', 100);
INSERT INTO TestResults VALUES('003', 'A', '��',  49);
INSERT INTO TestResults VALUES('004', 'A', '�j',  30);
INSERT INTO TestResults VALUES('005', 'B', '��', 100);
INSERT INTO TestResults VALUES('006', 'B', '�j',  92);
INSERT INTO TestResults VALUES('007', 'B', '�j',  80);
INSERT INTO TestResults VALUES('008', 'B', '�j',  80);
INSERT INTO TestResults VALUES('009', 'B', '��',  10);
INSERT INTO TestResults VALUES('010', 'C', '�j',  92);
INSERT INTO TestResults VALUES('011', 'C', '�j',  80);
INSERT INTO TestResults VALUES('012', 'C', '��',  21);
INSERT INTO TestResults VALUES('013', 'D', '��', 100);
INSERT INTO TestResults VALUES('014', 'D', '��',   0);
INSERT INTO TestResults VALUES('015', 'D', '��',   0);

/* �S�̕����q��ŕ\������(p.177) */
SELECT team_id, member
  FROM Teams T1
 WHERE NOT EXISTS
        (SELECT *
           FROM Teams T2
          WHERE T1.team_id = T2.team_id
            AND status <> '�ҋ@' );

/* �S�̕����W���ŕ\������F����1(p.178) */
SELECT team_id
  FROM Teams
 GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '�ҋ@'
                           THEN 1
                           ELSE 0 END);

/* �S�̕����W���ŕ\������F����2(p.180) */
SELECT team_id
  FROM Teams
 GROUP BY team_id
HAVING MAX(status) = '�ҋ@'
   AND MIN(status) = '�ҋ@';

/* �����X�^���o�C���ǂ������`�[�����ƂɈꗗ�\��(p.180) */
SELECT team_id,
       CASE WHEN MAX(status) = '�ҋ@' AND MIN(status) = '�ҋ@'
            THEN '�����X�^���o�C'
            ELSE '�����I �����o�[������܂���' END AS status
  FROM Teams
 GROUP BY team_id;

/* ���ނ̂��Ԃ��Ă��鋒�_��I������(p.182) */
SELECT center
  FROM Materials
 GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);

/* �_�u��̗L�����ꗗ�\��(p.182) */
SELECT center,
       CASE WHEN COUNT(material) <> COUNT(DISTINCT material) 
            THEN '�_�u��L��'
            ELSE '�_�u�薳��' END AS status
  FROM Materials
 GROUP BY center;

/* �_�u��̂���W���FEXISTS �̗��p(p.183) */
SELECT center, material
  FROM Materials M1
 WHERE EXISTS
       (SELECT *
          FROM Materials M2
         WHERE M1.center = M2.center
           AND M1.receive_date <> M2.receive_date
           AND M1.material = M2.material);

/* ���ʂ��Ԃ�Ύ���������F����̘A�����̂ݒ��ׂ�(p.185) */
SELECT '����������' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1;

/* ���Ԃ������Ă��Ȃ��Ă���s�Ԃ�(p.185) */
SELECT CASE WHEN COUNT(*) = 0
               THEN '�e�[�u������ł�'
            WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1
               THEN '����������'
            ELSE '�A��' END AS gap
  FROM SeqTbl;

/* �������̍ŏ��l��T���F�e�[�u����1���Ȃ��ꍇ�́A1��Ԃ�(p.185) */
SELECT CASE WHEN MIN(seq) > 1          /* ������1�łȂ��ꍇ��1��Ԃ� */
            THEN 1
            ELSE (SELECT MIN(seq +1)  /* ������1�̏ꍇ���ŏ��̌��Ԃ�Ԃ� */
                    FROM SeqTbl S1
                   WHERE NOT EXISTS
                        (SELECT * 
                           FROM SeqTbl S2 
                          WHERE S2.seq = S1.seq + 1))
             END AS min_gap
  FROM SeqTbl;

/* �N���X��75%�ȏオ80�_�ȏ���Ƃ����N���X(p.187) */
SELECT class
  FROM TestResults 
GROUP BY class
HAVING COUNT(*) * 0.75 
         <= SUM(CASE WHEN score >= 80 
                     THEN 1
                     ELSE 0 END) ;

/* 50�_�ȏ����������k�̂����A�j�q�̐������q�̐���葽���N���X(p.187) */
SELECT class
  FROM TestResults 
GROUP BY class
HAVING SUM(CASE WHEN score >= 50 AND sex = '�j'
                THEN 1
                ELSE 0 END)
       > SUM(CASE WHEN score >= 50 AND sex = '��'
                  THEN 1
                  ELSE 0 END) ;

/* �j�q�Ə��q�̕��ϓ_���r����N�G�� ����1�F��W���ɑ΂���AVG ��0�ŕԂ�(p.188) */
SELECT class
  FROM TestResults
 GROUP BY class
HAVING AVG(CASE WHEN sex = '�j'
                THEN score
                ELSE 0 END)
     < AVG(CASE WHEN sex = '��'
                THEN score
                ELSE 0 END) ;

/* �j�q�Ə��q�̕��ϓ_���r����N�G�� ����2�F��W���ɑ΂��镽�ς�NULL�ŕԂ�(p.189) */
SELECT class
  FROM TestResults
 GROUP BY class
HAVING AVG(CASE WHEN sex = '�j'
                THEN score
                ELSE NULL END)
     < AVG(CASE WHEN sex = '��'
                THEN score
                ELSE NULL END);

• Codd, E.F. �uA Relational Model of Data for Large Shared Data Banks�v

• Backus, J.W. �uCan Programming Be Liberated From the von Neumann Style?�v

CREATE TABLE Teams
(member VARCHAR(32) PRIMARY KEY,
 team   CHAR(1)     NOT NULL,
 age    INTEGER     NOT NULL);

INSERT INTO Teams VALUES('���',	'A',	28);
INSERT INTO Teams VALUES('�팩',	'A',	19);
INSERT INTO Teams VALUES('�V��',	'A',	23);
INSERT INTO Teams VALUES('�R�c',	'B',	40);
INSERT INTO Teams VALUES('�v�{',	'B',	29);
INSERT INTO Teams VALUES('���c',	'C',	30);
INSERT INTO Teams VALUES('��X�{',      'D',	28);
INSERT INTO Teams VALUES('�S��',	'D',	28);
INSERT INTO Teams VALUES('����',	'D',	24);
INSERT INTO Teams VALUES('�V��',	'D',	22);

CREATE TABLE Natural
(num INTEGER  NOT NULL PRIMARY KEY);

INSERT INTO Natural VALUES(0);
INSERT INTO Natural VALUES(1);
INSERT INTO Natural VALUES(2);
INSERT INTO Natural VALUES(3);
INSERT INTO Natural VALUES(4);
INSERT INTO Natural VALUES(5);
INSERT INTO Natural VALUES(6);
INSERT INTO Natural VALUES(7);
INSERT INTO Natural VALUES(8);
INSERT INTO Natural VALUES(9);
INSERT INTO Natural VALUES(10);

/* PARTITION BY �̃C���[�W���‚���(p.251) */
SELECT member, team, age ,
       RANK() OVER(PARTITION BY team ORDER BY age DESC) rn,
       DENSE_RANK() OVER(PARTITION BY team ORDER BY age DESC) dense_rn,
       ROW_NUMBER() OVER(PARTITION BY team ORDER BY age DESC) row_num
  FROM Teams
 ORDER BY team, rn;

/* 1 ����10 �܂ł��A3 ��@�Ƃ����]�ނɕ���(p.251) */
SELECT MOD(num, 3) AS modulo,
       num
  FROM Natural
 ORDER BY modulo, num;

/* �`�[���P�ʂɏW�񂷂�N�G��(p.280) */
SELECT team, AVG(age)
  FROM Teams
 GROUP BY team;

/* �`�[���P�ʂɏW�񂷂�N�G���H (p.280) */
SELECT team, AVG(age), age
  FROM Teams
 GROUP BY team;

/* �G���[(p.282) */
SELECT team, AVG(age), member
  FROM Teams
 GROUP BY team;

/* ������(p.282) */
SELECT team, MAX(age),
       (SELECT MAX(member)
          FROM Teams T2
         WHERE T2.team = T1.team
           AND T2.age = MAX(T1.age)) AS oldest
  FROM Teams T1
 GROUP BY team;

/* ���K���1-1�F������̍ő�l�i1-3�Ƃ����p�j */
CREATE TABLE Greatests
(key CHAR(1) PRIMARY KEY,
 x   INTEGER NOT NULL,
 y   INTEGER NOT NULL,
 z   INTEGER NOT NULL);

INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);

/* x �� y �̍ő�l(p.286) */
SELECT key,
       CASE WHEN x < y THEN y
            ELSE x END AS greatest
  FROM Greatests;

/* x �� y �� z �̍ő�l(p.286) */
SELECT key,
       CASE WHEN CASE WHEN x < y THEN y ELSE x END < z
            THEN z
            ELSE CASE WHEN x < y THEN y ELSE x END
        END AS greatest
  FROM Greatests;

/* �s�����ɕϊ�����MAX �֐�(p.287) */
SELECT key, MAX(col) AS greatest
  FROM (SELECT key, x AS col FROM Greatests
        UNION ALL
        SELECT key, y AS col FROM Greatests
        UNION ALL
        SELECT key, z AS col FROM Greatests) TMP
 GROUP BY key;

/* Oracle��MySQL�̂�(p.287) */
SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
  FROM Greatests;

/* ���K���Q�F���v�ƍČf��\���ɏo�͂���s��ϊ�(p.287) */
SELECT sex,
       SUM(population) AS total,
       SUM(CASE WHEN pref_name = '����' THEN population ELSE 0 END) AS col_1,
       SUM(CASE WHEN pref_name = '����' THEN population ELSE 0 END) AS col_2,
       SUM(CASE WHEN pref_name = '���Q' THEN population ELSE 0 END) AS col_3,
       SUM(CASE WHEN pref_name = '���m' THEN population ELSE 0 END) AS col_4,
       SUM(CASE WHEN pref_name IN ('����', '����', '���Q', '���m')
                THEN population ELSE 0 END) AS saikei
  FROM PopTbl2
 GROUP BY sex;

/* ���K���R�FORDER BY �Ń\�[�g������(p.288) */
SELECT *
  FROM Greatests
 ORDER BY CASE key
            WHEN 'B' THEN 1
            WHEN 'A' THEN 2
            WHEN 'D' THEN 3
            WHEN 'C' THEN 4
            ELSE NULL END;

/* �\�[�g������ʂɊ܂߂�(p.288) */
SELECT key,
       CASE key
         WHEN 'B' THEN 1
         WHEN 'A' THEN 2
         WHEN 'D' THEN 3
         WHEN 'C' THEN 4
         ELSE NULL END AS sort_col
  FROM Greatests
 ORDER BY sort_col;

/* ���K���2-2�F�n�悲�Ƃ̃����L���O */
CREATE TABLE DistrictProducts
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 PRIMARY KEY(district, name));

INSERT INTO DistrictProducts VALUES('���k', '�݂���',	100);
INSERT INTO DistrictProducts VALUES('���k', '���',	50);
INSERT INTO DistrictProducts VALUES('���k', '�Ԃǂ�',	50);
INSERT INTO DistrictProducts VALUES('���k', '������',	30);
INSERT INTO DistrictProducts VALUES('�֓�', '������',	100);
INSERT INTO DistrictProducts VALUES('�֓�', '�p�C��',	100);
INSERT INTO DistrictProducts VALUES('�֓�', '���',	100);
INSERT INTO DistrictProducts VALUES('�֓�', '�Ԃǂ�',	70);
INSERT INTO DistrictProducts VALUES('�֐�', '������',	70);
INSERT INTO DistrictProducts VALUES('�֐�', '�X�C�J',	30);
INSERT INTO DistrictProducts VALUES('�֐�', '���',	20);

/* ���K���2-3�F�����L���O�̍X�V */
CREATE TABLE DistrictProducts2
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 ranking   INTEGER,
 PRIMARY KEY(district, name));

INSERT INTO DistrictProducts2 VALUES('���k', '�݂���',	100, NULL);
INSERT INTO DistrictProducts2 VALUES('���k', '���',	50 , NULL);
INSERT INTO DistrictProducts2 VALUES('���k', '�Ԃǂ�',	50 , NULL);
INSERT INTO DistrictProducts2 VALUES('���k', '������',	30 , NULL);
INSERT INTO DistrictProducts2 VALUES('�֓�', '������',	100, NULL);
INSERT INTO DistrictProducts2 VALUES('�֓�', '�p�C��',	100, NULL);
INSERT INTO DistrictProducts2 VALUES('�֓�', '���',	100, NULL);
INSERT INTO DistrictProducts2 VALUES('�֓�', '�Ԃǂ�',	70 , NULL);
INSERT INTO DistrictProducts2 VALUES('�֐�', '������',	70 , NULL);
INSERT INTO DistrictProducts2 VALUES('�֐�', '�X�C�J',	30 , NULL);
INSERT INTO DistrictProducts2 VALUES('�֐�', '���',	20 , NULL);

/* ���K���P�F�d���g�ݍ��킹(p.289) */
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1, Products P2
 WHERE P1.name >= P2.name;

/* ���K���Q�F�n�悲�Ƃ̃����L���O(p.290) */
SELECT district, name,
          RANK() OVER(PARTITION BY district 
                      ORDER BY price DESC) AS rank_1
  FROM DistrictProducts;

/* ���K���Q�F���փT�u�N�G��(p.290) */
SELECT P1.district, P1.name,
       P1.price,
       (SELECT COUNT(P2.price)
          FROM DistrictProducts P2
         WHERE P1.district = P2.district    /* �����n����Ŕ�r���� */
           AND P2.price > P1.price) + 1 AS rank_1
  FROM DistrictProducts P1;

/* ���K���Q�F���Ȍ���(p.290) */
SELECT P1.district, P1.name,
       MAX(P1.price) AS price, 
       COUNT(P2.name) +1 AS rank_1
  FROM DistrictProducts P1 LEFT OUTER JOIN DistrictProducts P2
    ON  P1.district = P2.district
   AND P1.price < P2.price
 GROUP BY P1.district, P1.name;

/* ���K���R�F�����L���O�̍X�V(p.291) */
UPDATE DistrictProducts2 P1
   SET ranking = (SELECT COUNT(P2.price) + 1
                    FROM DistrictProducts2 P2
                   WHERE P1.district = P2.district
                     AND P2.price > P1.price);

/* ���K���R�FDB2�̂�(p.291) */
UPDATE DistrictProducts2
   SET ranking = RANK() OVER(PARTITION BY district
                             ORDER BY price DESC);

/* ���K���R�FOracle�ASQL Server�APostgreSQL (p.291) */
UPDATE DistrictProducts2
   SET ranking =
         (SELECT P1.ranking
            FROM (SELECT district , name ,
                         RANK() OVER(PARTITION BY district
                                     ORDER BY price DESC) AS ranking
                    FROM DistrictProducts2) P1
                   WHERE P1.district = DistrictProducts2.district
                     AND P1.name = DistrictProducts2.name);

/* ���K���P�F��Ɍ��ʂ���s�Ԃ����ԃ`�F�b�N(p.292) */
SELECT ' ����������' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq)
UNION ALL
SELECT ' �������Ȃ�' AS gap
  FROM SeqTbl
HAVING COUNT(*) = MAX(seq);

/* ���K���P�F��Ɍ��ʂ���s�Ԃ����ԃ`�F�b�N(p.292) */
SELECT CASE WHEN COUNT(*) <> MAX(seq)
            THEN '����������'
            ELSE '�������Ȃ�' END AS gap
  FROM SeqTbl;

/* ���K���Q�F�����֐��̗��K(p.293) 
   �S����9 �����ɒ�o�ς݂̊w����I������@���̂P�FBETWEEN �q��̗��p */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005-09-01' AND '2005-09-30'
                           THEN 1 ELSE 0 END);

/* ���K���Q�F�����֐��̗��K(p.293) 
   �S����9 �����ɒ�o�ς݂̊w����I������@���̂Q�FEXTRACT �֐��̗��p */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN EXTRACT (YEAR FROM sbmt_date) = 2005
                            AND EXTRACT (MONTH FROM sbmt_date) = 09
                           THEN 1 ELSE 0 END);

/* ���K���R�F�o�X�P�b�g��͂̈�ʉ�(p.294) */
SELECT SI.shop,
       COUNT(SI.item) AS my_item_cnt,
       (SELECT COUNT(item) FROM Items) - COUNT(SI.item) AS diff_cnt
  FROM ShopItems SI, Items I
 WHERE SI.item = I.item
 GROUP BY SI.shop;

/* ���K���P�F�������悩�A�W�񂪐悩�H (p.294) 
   �C�����C���E�r���[����폜�����C���� */
SELECT MASTER.age_class AS age_class,
       MASTER.sex_cd AS sex_cd,
       SUM(CASE WHEN pref_name IN ('�X', '�H�c')
                THEN population ELSE NULL END) AS pop_tohoku,
       SUM(CASE WHEN pref_name IN ('����', '��t')
                THEN population ELSE NULL END) AS pop_kanto
  FROM (SELECT age_class, sex_cd
          FROM TblAge CROSS JOIN TblSex) MASTER
        LEFT OUTER JOIN TblPop DATA      /* DATA ��TblPop ���̂��̂ł���̂��~�\�B */
    ON MASTER.age_class = DATA.age_class
   AND MASTER.sex_cd = DATA.sex_cd
 GROUP BY MASTER.age_class, MASTER.sex_cd;

/* ���K���Q�F�q���̐��ɂ��p�S(p.295) */
SELECT EMP.employee, COUNT(*) AS child_cnt /* COUNT(*) �͎g���Ă̓_���I */
  FROM Personnel EMP
       LEFT OUTER JOIN Children
    ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
 GROUP BY EMP.employee;

/* ���K���Q�F�q���̐��ɂ��p�S(p.295) */
SELECT EMP.employee, COUNT(CHILDREN.child) AS child_cnt
  FROM Personnel EMP
       LEFT OUTER JOIN Children
    ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
 GROUP BY EMP.employee;

/* ���K���R�F���S�O��������MERGE ��(p.296) */
MERGE INTO Class_A A
    USING (SELECT *
             FROM Class_B ) B
      ON (A.id = B.id)
    WHEN MATCHED THEN
        UPDATE SET A.name = B.name
    WHEN NOT MATCHED THEN
        INSERT (id, name) VALUES (B.id, B.name);

/* ���K���P�F�s�Ԕ�r�̊ȗ���(p.297) */
SELECT S1.year, S1.sale,
       CASE SIGN(sale -
              (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1) )
            WHEN 0  THEN '��'  /* ���΂� */
            WHEN 1  THEN '��'  /* ����   */
            WHEN -1 THEN '��'  /* ���   */
            ELSE '-' END AS var
  FROM Sales S1
 ORDER BY year;

/* ���K���Q�FOVERLAPS �Ŋ��Ԃ̏d���𒲂ׂ�(p.297) */
SELECT reserver, start_date, end_date
  FROM Reservations R1
 WHERE EXISTS
        (SELECT *
           FROM Reservations R2
          WHERE R1.reserver <> R2.reserver /* �����ȊO�̋q�Ɣ�r���� */
            AND (R1.start_date, R1.end_date) OVERLAPS (R2.start_date, R2.end_date));

SELECT R1.reserver, R1.start_date, R1.end_date
  FROM Reservations R1, Reservations R2
 WHERE R1.reserver <> R2.reserver /* �����ȊO�̋q�Ɣ�r���� */
   AND (R1.start_date, R1.end_date) OVERLAPS (R2.start_date, R2.end_date);

/* ���K���P�FUNION �������g���R���y�A�̉���(p.299) */
SELECT CASE WHEN COUNT(*) = (SELECT COUNT(*) FROM tbl_A )
             AND COUNT(*) = (SELECT COUNT(*) FROM tbl_B )
            THEN '������'
            ELSE '�قȂ�' END AS result
  FROM ( SELECT * FROM tbl_A
         UNION
         SELECT * FROM tbl_B ) TMP;

/* ���K���Q�F�����Ȋ֌W���Z(p.300) */
SELECT DISTINCT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         EXCEPT
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp)
  AND NOT EXISTS
        (SELECT skill
           FROM EmpSkills ES3
          WHERE ES1.emp = ES3.emp
         EXCEPT
         SELECT skill
           FROM Skills );

/* ���K���Q�F�����Ȋ֌W���Z(p.300) */
SELECT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         EXCEPT
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp)
 GROUP BY emp
HAVING COUNT(*) = (SELECT COUNT(*) FROM Skills);

/* 8-1�F�z��e�[�u���\�\�s�����̏ꍇ */
CREATE TABLE ArrayTbl2
 (key   CHAR(1) NOT NULL,
    i   INTEGER NOT NULL,
  val   INTEGER,
  PRIMARY KEY (key, i));

/* A�̓I�[��NULL�AB�͈�‚�����NULL�AC�̓I�[����NULL */
INSERT INTO ArrayTbl2 VALUES('A', 1, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('A',10, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 1, 3);
INSERT INTO ArrayTbl2 VALUES('B', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('B',10, NULL);
INSERT INTO ArrayTbl2 VALUES('C', 1, 1);
INSERT INTO ArrayTbl2 VALUES('C', 2, 1);
INSERT INTO ArrayTbl2 VALUES('C', 3, 1);
INSERT INTO ArrayTbl2 VALUES('C', 4, 1);
INSERT INTO ArrayTbl2 VALUES('C', 5, 1);
INSERT INTO ArrayTbl2 VALUES('C', 6, 1);
INSERT INTO ArrayTbl2 VALUES('C', 7, 1);
INSERT INTO ArrayTbl2 VALUES('C', 8, 1);
INSERT INTO ArrayTbl2 VALUES('C', 9, 1);
INSERT INTO ArrayTbl2 VALUES('C',10, 1);

/* ���K���P�F�z��e�[�u���\�\�s�����̏ꍇ(p.301) 
   �Ԉ�������� */
SELECT DISTINCT key
  FROM ArrayTbl2 AT1
 WHERE NOT EXISTS
        (SELECT *
           FROM ArrayTbl2 AT2
          WHERE AT1.key = AT2.key
            AND AT2.val <> 1);

/* ����������(p.301) */
SELECT DISTINCT key
  FROM ArrayTbl2 A1
 WHERE NOT EXISTS
        (SELECT *
           FROM ArrayTbl2 A2
          WHERE A1.key = A2.key
            AND (A2.val <> 1 OR A2.val IS NULL));

/* �ʉ��P�FALL �q��̗��p(p.303) */
SELECT DISTINCT key
  FROM ArrayTbl2 A1
 WHERE 1 = ALL
          (SELECT val
             FROM ArrayTbl2 A2
            WHERE A1.key = A2.key);

/* �ʉ��Q�FHAVING ��̗��p(p.303) */
SELECT key
  FROM ArrayTbl2
 GROUP BY key
HAVING SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) = 10;

/* �ʉ����̂R�FHAVING ��ŋɒl�֐��𗘗p����(p.303) */
SELECT key
  FROM ArrayTbl2
 GROUP BY key
HAVING MAX(val) = 1
   AND MIN(val) = 1;



/* ���K���Q�FALL �q��ɂ��S�̗ʉ�(p.304)
   �H��1 �Ԃ܂Ŋ����̃v���W�F�N�g��I���FALL �q��ɂ��� */
SELECT *
  FROM Projects P1
 WHERE '��' = ALL
             (SELECT CASE WHEN step_nbr <= 1 AND status = '����' THEN '��'
                          WHEN step_nbr > 1  AND status = '�ҋ@' THEN '��'
                          ELSE '�~' END
                FROM Projects P2
               WHERE P1.project_id = P2. project_id);

/* ���K���R�F�f�������߂�(p.305)
SELECT num AS prime
  FROM Numbers Dividend
 WHERE num > 1
   AND NOT EXISTS
        (SELECT *
           FROM Numbers Divisor
          WHERE Divisor.num <= Dividend.num / 2 /* �����ȊO�̖񐔂͎����̔����ȉ��ɂ������݂��Ȃ� */
            AND Divisor.num <> 1 /* 1 �͖񐔂Ɋ܂܂Ȃ� */
            AND MOD(Dividend.num, Divisor.num) = 0)  /*�u����؂�Ȃ��v�̔ے�����Ȃ̂Łu����؂��v */
ORDER BY prime;

/* ���K���P�F���Ԃ�S�ċ��߂�\�\NOT EXISTS �ƊO������(p.305) 
   NOT EXISTS �o�[�W����  */
SELECT seq
  FROM Sequence N
 WHERE seq BETWEEN 1 AND 12
   AND NOT EXISTS
        (SELECT *
           FROM SeqTbl S
          WHERE N.seq = S.seq );

/* NOT EXISTS �o�[�W����  */
SELECT N.seq
  FROM Sequence N LEFT OUTER JOIN SeqTbl S
    ON N.seq = S.seq
 WHERE N.seq BETWEEN 1 AND 12
   AND S.seq IS NULL;

/* ���K���Q�F�V�[�P���X�����߂�\�\�W���w���I���z(p.307) */
SELECT S1.seat AS start_seat, '�`' , S2.seat AS end_seat
  FROM Seats S1, Seats S2, Seats S3
 WHERE S2.seat = S1.seat + (:head_cnt -1)
   AND S3.seat BETWEEN S1.seat AND S2.seat
 GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '��' THEN 1 ELSE 0 END);

/* �s�ɐ܂�Ԃ�������ꍇ(p.307) */
SELECT S1.seat AS start_seat, ' �` ' , S2.seat AS end_seat
  FROM Seats2 S1, Seats2 S2, Seats2 S3
 WHERE S2.seat = S1.seat + (:head_cnt -1)
   AND S3.seat BETWEEN S1.seat AND S2.seat
 GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '��'
                            AND S3.row_id = S1.row_id THEN 1 ELSE 0 END);

/* ���K���R�F�V�[�P���X��S�ċ��߂�\�\�W���w���I���z(p.308) */
SELECT S1.seat AS start_seat,
       S2.seat AS end_seat,
       S2.seat - S1.seat + 1 AS seat_cnt
  FROM Seats3 S1, Seats3 S2, Seats3 S3
 WHERE S1.seat <= S2.seat /* �X�e�b�v1�F�n�_�ƏI�_�̑g�ݍ��킹����� */
   AND S3.seat BETWEEN S1.seat - 1 AND S2.seat + 1
 GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.seat BETWEEN S1.seat AND S2.seat
                            AND S3.status = '��' THEN 1 /* ����1 */
                           WHEN S3.seat = S2.seat + 1 AND S3.status = '��' THEN 1 /* ����2 */
                           WHEN S3.seat = S1.seat - 1 AND S3.status = '��' THEN 1 /* ����3 */
                           ELSE 0 END);

/* 10-1�F��ӏW���Ƒ��d�W���̈�ʉ� */
CREATE TABLE Materials2
(center VARCHAR(32) NOT NULL,
 receive_date DATE  NOT NULL,
 material VARCHAR(32) NOT NULL,
 orgland  VARCHAR(32) NOT NULL,
 PRIMARY KEY(center, receive_date, material));

INSERT INTO Materials2 VALUES('����',   '2007-04-01', '��', 		'�`��');
INSERT INTO Materials2 VALUES('����',   '2007-04-12', '����', 		'�^�C');
INSERT INTO Materials2 VALUES('����',   '2007-05-17', '�A���~�j�E��', 	'�u���W��');
INSERT INTO Materials2 VALUES('����',   '2007-05-20', '����', 		'�^�C');
INSERT INTO Materials2 VALUES('���',   '2007-04-20', '��', 		'�I�[�X�g�����A');
INSERT INTO Materials2 VALUES('���',   '2007-04-22', '�j�b�P��', 	'��A�t���J');
INSERT INTO Materials2 VALUES('���',   '2007-04-29', '��', 		'�C���h');
INSERT INTO Materials2 VALUES('����', '2007-03-15', '�`�^��', 	'�{���r�A');
INSERT INTO Materials2 VALUES('����', '2007-04-01', '�Y�f�|', 	'�`��');
INSERT INTO Materials2 VALUES('����', '2007-04-24', '�Y�f�|', 	'�A���[���`��');
INSERT INTO Materials2 VALUES('����', '2007-05-02', '�}�O�l�V�E��', 	'�`��');
INSERT INTO Materials2 VALUES('����', '2007-05-10', '�`�^��', 	'�^�C');
INSERT INTO Materials2 VALUES('����',   '2007-05-10', '����', 		'�A�����J');
INSERT INTO Materials2 VALUES('����',   '2007-05-28', '��',		'���V�A');

/* ���K���10-2�F�s�ɂ���ď������قȂ�����֐� */
CREATE TABLE TestScores
 (student_id INTEGER NOT NULL,
  subject    VARCHAR(16) NOT NULL,
  score      INTEGER NOT NULL,
    PRIMARY KEY (student_id, subject));

INSERT INTO TestScores VALUES(100, '�Z��', 100);
INSERT INTO TestScores VALUES(100, '����', 80);
INSERT INTO TestScores VALUES(100, '����', 80);
INSERT INTO TestScores VALUES(200, '�Z��', 80);
INSERT INTO TestScores VALUES(200, '����', 95);
INSERT INTO TestScores VALUES(300, '�Z��', 40);
INSERT INTO TestScores VALUES(300, '����', 50);
INSERT INTO TestScores VALUES(300, '�Љ�', 55);
INSERT INTO TestScores VALUES(400, '�Z��', 80);

/* ���K���P�F��ӏW���Ƒ��d�W���̈�ʉ�(p.309) 
 �i����, ���Y���j�Ń_�u���̂��鋒�_��I������ */
SELECT center
  FROM Materials2
 GROUP BY center
HAVING COUNT(material || orgland) <> COUNT(DISTINCT material || orgland);

/* ���K���Q�F�s�ɂ���ď������قȂ�����֐�(p.310) */
SELECT student_id
  FROM TestResults2
 WHERE subject IN ('�Z��', '����')
 GROUP BY student_id
HAVING SUM(CASE WHEN subject = '�Z��' AND score >= 80 THEN 1
                WHEN subject = '����' AND score >= 50 THEN 1
                ELSE 0 END) = 2;

• Absolute Playing!
• MySQL 5.1 ���t�@�����X�}�j���A���u11.11.1. GROUP BY ( �W�� ) �֐��v

• �uRDB(�֌W�f�[�^�x�[�X)�̖{����m�� �`�B�l�Ɋw�� SQL�O��w�쏑�`�v�iBUKURO-JIN�j
• �u�B�l�Ɋw�� SQL�O��w�쏑(�~�b�N����)�v�iUnofficial DB2 BLOG�j
• �u�B�l�Ɋw��SQL�O��w�쏑�v�isakaik�̓��X�G���`(T)�ҁj
• �u�B�l�Ɋw��SQL�O��w�쏑�Ǘ��v�iAbsolute Playing!�j
• �u�~�b�N����SQL�w�쏑�v�i�����q�����^�j

• �u�������낳����‚��݂Ƃ�m�b�̖{�v