Example 4.1:



CREATE DATABASE TENNIS

   DICTIONARY_PATH 'C:\TENNIS'



Example 4.2: 



SET SECURITY = 'master'



Example 4.3: 



CREATE USER BOOKSQL WITH PASSWORD BOOKPW



Example 4.4: 



GRANT CREATETAB

TO    BOOKSQL

;

GRANT CREATEVIEW

TO    BOOKSQL

;

GRANT ALL

ON    *

TO    BOOKSQL



Example 4.5: 



CREATE TABLE PLAYERS (

   PLAYERNO      INTEGER NOT NULL,

   NAME          CHAR(15) NOT NULL,

   INITIALS      CHAR(3) NOT NULL,

   BIRTH_DATE    DATE,

   SEX           CHAR(1) NOT NULL,

   JOINED        SMALLINT NOT NULL,

   STREET        VARCHAR(30) NOT NULL,

   HOUSENO       CHAR(4),

   POSTCODE      CHAR(6),

   TOWN          VARCHAR(30) NOT NULL,

   PHONENO       CHAR(13),

   LEAGUENO      CHAR(4),

   PRIMARY KEY   (PLAYERNO))

;

CREATE TABLE TEAMS (

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   DIVISION      CHAR(6) NOT NULL,

   PRIMARY KEY   (TEAMNO))

;

CREATE TABLE MATCHES (

   MATCHNO       INTEGER NOT NULL,

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   WON           SMALLINT NOT NULL,

   LOST          SMALLINT NOT NULL,

   PRIMARY KEY   (MATCHNO))

;

CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE NOT NULL,

   AMOUNT         DECIMAL(7,2) NOT NULL,

   PRIMARY KEY    (PAYMENTNO))

;

CREATE TABLE COMMITTEE_MEMBERS (

   PLAYERNO       INTEGER NOT NULL,

   BEGIN_DATE     DATE NOT NULL,

   END_DATE       DATE,

   POSITION       CHAR(20),

   PRIMARY KEY    (PLAYERNO, BEGIN_DATE))



Example 4.6: 



INSERT INTO PLAYERS VALUES (

  2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road',

    '43', '3575NH', 'Stratford', '070-237893', '2411')

;

INSERT INTO PLAYERS VALUES (

  6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane',

    '80', '1234KK', 'Stratford', '070-476537', '8467')

;

INSERT INTO PLAYERS VALUES (

  7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way',

    '39', '9758VB', 'Stratford', '070-347689', NULL)

;

INSERT INTO PLAYERS VALUES (

  8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road',

    '4', '6584WO', 'Inglewood', '070-458458', '2983')

;

INSERT INTO PLAYERS VALUES (

 27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive',

    '804', '8457DK', 'Eltham', '079-234857', '2513')

;

INSERT INTO PLAYERS VALUES (

 28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road',

    '10', '1294QK', 'Midhurst', '010-659599', NULL)

;

INSERT INTO PLAYERS VALUES (

 39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square',

    '78', '9629CD', 'Stratford', '070-393435', NULL)

;

INSERT INTO PLAYERS VALUES (

 44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street',

    '23', '4444LJ', 'Inglewood', '070-368753', '1124')

;

INSERT INTO PLAYERS VALUES (

 57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way',

    '16', '4377CB', 'Stratford', '070-473458', '6409')

;

INSERT INTO PLAYERS VALUES (

 83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road',

    '16A', '1812UP', 'Stratford', '070-353548', '1608')

;

INSERT INTO PLAYERS VALUES (

 95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street',

    '33A', '5746OP', 'Douglas', '070-867564', NULL)

;

INSERT INTO PLAYERS VALUES (

100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane',

    '80', '6494SG', 'Stratford', '070-494593', '6524')

;

INSERT INTO PLAYERS VALUES (

104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street',

    '65', '9437AO', 'Eltham', '079-987571', '7060')

;

INSERT INTO PLAYERS VALUES (

112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road',

    '8', '6392LK', 'Plymouth', '010-548745', '1319')

;

INSERT INTO TEAMS VALUES (1,  6, 'first')

;

INSERT INTO TEAMS VALUES (2, 27, 'second')

;

INSERT INTO MATCHES VALUES ( 1, 1,   6, 3, 1)

;

INSERT INTO MATCHES VALUES ( 2, 1,   6, 2, 3)

;

INSERT INTO MATCHES VALUES ( 3, 1,   6, 3, 0)

;

INSERT INTO MATCHES VALUES ( 4, 1,  44, 3, 2)

;

INSERT INTO MATCHES VALUES ( 5, 1,  83, 0, 3)

;

INSERT INTO MATCHES VALUES ( 6, 1,   2, 1, 3)

;

INSERT INTO MATCHES VALUES ( 7, 1,  57, 3, 0)

;

INSERT INTO MATCHES VALUES ( 8, 1,   8, 0, 3)

;

INSERT INTO MATCHES VALUES ( 9, 2,  27, 3, 2)

;

INSERT INTO MATCHES VALUES (10, 2, 104, 3, 2)

;

INSERT INTO MATCHES VALUES (11, 2, 112, 2, 3)

;

INSERT INTO MATCHES VALUES (12, 2, 112, 1, 3)

;

INSERT INTO MATCHES VALUES (13, 2,   8, 0, 3)

;

INSERT INTO PENALTIES VALUES (1,  6, '1980-12-08',100)

;

INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75)

;

INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100)

;

INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50)

;

INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25)

;

INSERT INTO PENALTIES VALUES (6,  8, '1980-12-08', 25)

;

INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30)

;

INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75)

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1990-1-1', '1990-12-31', 'Secretary')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1991-1-1', '1992-12-31', 'Member')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1992-1-1', '1993-12-31', 'Treasurer')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1993-1-1',  NULL, 'Chairman')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1990-1-1', '1992-12-31', 'Chairman')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1994-1-1',  NULL, 'Member')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-1-1', '1992-12-31', 'Member')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-1-1',  NULL, 'Secretary')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1990-1-1', '1990-12-31', 'Treasurer')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1991-1-1', '1991-12-31', 'Secretary')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1993-1-1', '1993-12-31', 'Member')

;

INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1994-1-1',  NULL, 'Member')

;

INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-1-1', '1992-12-31', 'Secretary')

;

INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-1-1', '1990-12-31', 'Member')

;

INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-1-1', '1991-12-31', 'Treasurer')

;

INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-1-1', '1993-12-31', 'Treasurer')

;

INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-1-1',  NULL, 'Treasurer')



Example 4.7: 



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

ORDER BY NAME



Example 4.8: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    JOINED > 1980

AND      TOWN = 'Stratford'

ORDER BY PLAYERNO



Example 4.9: 



SELECT   *

FROM     PENALTIES



Example 4.10: 



SELECT   33 * 121



Example 4.11: 



UPDATE   PENALTIES

SET      AMOUNT = 200

WHERE    PLAYERNO = 44

;

SELECT   PLAYERNO, AMOUNT

FROM     PENALTIES

WHERE    PLAYERNO = 44



Example 4.12: 



DELETE

FROM     PENALTIES

WHERE    AMOUNT > 100



Example 4.13: 



CREATE INDEX PENALTIES_AMOUNT

   ON PENALTIES (AMOUNT)



Example 4.14: 



CREATE   VIEW NUMBER_SETS (MATCHNO, DIFFERENCE) AS

SELECT   MATCHNO, ABS(WON - LOST)

FROM     MATCHES

;

SELECT   *

FROM     NUMBER_SETS



Example 4.15: 



GRANT   SELECT

ON      PLAYERS

TO      DIANE

;

GRANT   SELECT

ON      TEAMS

TO      PAUL

;

GRANT   UPDATE

ON      TEAMS

TO      PAUL



Example 4.16: 



DROP TABLE COMMITTEE_MEMBERS



Example 4.17: 



DROP VIEW NUMBER_SETS



Example 4.18: 



DROP INDEX PENALTIES_AMOUNT



Example 4.19: 



DROP DATABASE TENNIS



Example 4.20: 



CREATE   VIEW USERS (USER_NAME) AS

SELECT   XU$NAME

FROM     X$USER

;

CREATE   VIEW TABLES

        (TABLE_NAME, COMMENT) AS

SELECT   UPPER(Xf$NAME),

         UPPER(Xf$LOC)

FROM     X$FILE

WHERE    XF$NAME NOT LIKE 'X$%'

;

CREATE   VIEW COLUMNS (TABLE_NAME, COLUMN_NAME, COLUMN_NO, DATA_TYPE,

         CHAR_LENGTH, PRECISION, SCALE, NULLABLE) AS

SELECT   UPPER(TAB.XF$NAME), UPPER(COL.XE$NAME),

        (SELECT COUNT(*)

         FROM   X$FIELD AS F

         WHERE  F.XE$FILE = TAB.XF$ID

         AND    F.XE$OFFSET <= COL.XE$OFFSET

         AND    F.XE$DATATYPE NOT IN (227,255)), 

         CASE XE$DATATYPE

            WHEN 0 THEN 'STRING'

            WHEN 1 THEN

               CASE XE$SIZE

                  WHEN 1 THEN 'TINYINT'

                  WHEN 2 THEN 'SMALLINT'

                  WHEN 4 THEN 'INTEGER'

                  WHEN 8 THEN 'BIGINT'

                  ELSE 'UNKNOWN INTEGER'

               END

            WHEN 2 THEN

               CASE XE$SIZE

                  WHEN 4 THEN 'REAL'

                  WHEN 8 THEN 'DOUBLE'

                  ELSE 'UNKNOWN REAL'

               END

            WHEN 3 THEN 'DATE'

            WHEN 4 THEN 'TIME'

            WHEN 5 THEN 'DECIMAL'

            WHEN 8 THEN 'NUMERIC'

            WHEN 9 THEN

               CASE XE$SIZE

                  WHEN 4 THEN 'BFLOAT4'

                  WHEN 8 THEN 'BFLOAT8'

                  ELSE 'UNKNOWN BFLOAT'

               END

            WHEN 11 THEN 'VARCHAR'

            WHEN 14 THEN

               CASE XE$SIZE

                  WHEN 1 THEN 'UNSIGNED1 BINARY'

                  WHEN 2 THEN 'UNSIGNED2 BINARY'

                  WHEN 4 THEN 'UNSIGNED4 BINARY'

                  WHEN 8 THEN 'UNSIGNED8 BINARY'

                  ELSE 'UNKNOWN UNSIGNED BINARY'

               END

            WHEN 15 THEN

               CASE XE$SIZE

                  WHEN 2 THEN 'AUTOINC2'

                  WHEN 4 THEN 'AUTOINC4'

                  ELSE 'UNKNOWN AUTOINC'

               END

            WHEN 16 THEN 'BIT'

            WHEN 17 THEN 'NUMERICSTS'

            WHEN 18 THEN 'NUMERICSA'

            WHEN 19 THEN 'CURRENCY'

            WHEN 20 THEN 'TIMESTAMP'

            WHEN 21 THEN 'CLOB/BLOB'

            WHEN 25 THEN 'WSTRING'

            WHEN 26 THEN 'WZTRING'

            WHEN 27 THEN 'GUID'

            WHEN 30 THEN 'DATETIME'

            ELSE CONCAT('DATATYPE UNKNOWN = ',

                        CAST(XE$DATATYPE AS CHAR(3)))

         END,

         XE$SIZE, XE$SIZE, XE$DEC,

         CASE

            WHEN XE$FLAGS & 4 = 4   THEN 'YES'

            ELSE 'NO'

         END

FROM     X$FIELD AS COL, X$FILE AS TAB

WHERE    COL.XE$FILE = TAB.XF$ID

AND      TAB.XF$FLAGS & 16 <> 16

AND      COL.XE$DATATYPE NOT IN (227,255)

;

CREATE   VIEW VIEWS (VIEW_NAME, VIEWFORMULA) AS

SELECT   UPPER(XV$NAME), XV$MISC

FROM     X$VIEW

;

CREATE   VIEW INDEXES (INDEX_NAME, TABLE_NAME, UNIQUE_ID, MODIFIABILITY,

         INDEX_TYPE, CASE_SENSITIVITY, PARTIAL_INDEX) AS

SELECT   IND.XE$NAME, TAB.XF$NAME,

         MAX(CASE

                WHEN XI$FLAGS & 1 = 1 THEN 'NO'

                ELSE 'YES'

             END),

         MAX(CASE

                WHEN XI$FLAGS & 2 = 2 THEN 'YES'

                ELSE 'NO'

             END),

         MAX(CASE

                WHEN XI$FLAGS & 128 = 128 THEN 'REPEATING DUPLICATES'

                ELSE 'LINKED DUPLICATES'

             END),

         MAX(CASE

                WHEN XI$FLAGS & 32 = 32 THEN 'CASE INSENSITIVE'

                ELSE 'CASE SENSITIVE'

             END),

        MAX(CASE

                WHEN XI$FLAGS & 512 = 512 THEN 'YES'

                ELSE 'NO'

             END)

FROM     X$FIELD AS IND, X$FILE AS TAB, X$INDEX AS SEGMENT

WHERE    IND.XE$FILE = TAB.XF$ID

AND      IND.XE$DATATYPE = 255

AND      SEGMENT.XI$FILE = IND.XE$FILE

AND      SEGMENT.XI$NUMBER = IND.XE$OFFSET

GROUP BY IND.XE$NAME, TAB.XF$NAME

UNION

SELECT   PK.XE$NAME, TAB.XF$NAME, 'YES', 'NO', 'REPEATING DUPLICATES', 

         'CASE SENSITIVE', 'NO'

FROM     X$FIELD AS PK, X$FILE AS TAB

WHERE    PK.XE$FILE = TAB.XF$ID

AND     (XE$NAME LIKE 'PK_%' OR XE$NAME LIKE 'FK_%' OR XE$NAME LIKE 'UK_%')

AND      XE$DATATYPE = 227

;

CREATE   VIEW COLUMNS_IN_INDEX (INDEX_NAME, TABLE_NAME, COLUMN_NAME, 

         COLUMN_SEQ, ORDERING, UNIQUE_ID, PARTIAL_INDEX) AS 

SELECT   IND.XE$NAME, TAB.XF$NAME, COL.XE$NAME, SEGMENT.XI$PART+1,

         CASE

            WHEN SEGMENT.XI$FLAGS & 64 = 64 THEN 'DESCENDING'

            ELSE 'ASCENDING'

         END,

         CASE

            WHEN SEGMENT.XI$FLAGS & 1 = 1 THEN 'NO'

            ELSE 'YES'

         END,

         CASE

            WHEN SEGMENT.XI$FLAGS &512 = 512 THEN 'YES'

            ELSE 'NO'

         END

FROM     X$INDEX AS SEGMENT, X$FIELD AS IND, X$FIELD AS COL, X$FILE AS TAB

WHERE    SEGMENT.XI$FILE = IND.XE$FILE

AND      SEGMENT.XI$NUMBER = IND.XE$OFFSET

AND      IND.XE$DATATYPE IN (227, 255)

AND      SEGMENT.XI$FIELD = COL.XE$ID

AND      SEGMENT.XI$FILE = TAB.XF$ID

;

CREATE   VIEW TABLE_AUTHS

        (GRANTEE, TABLE_NAME, PRIVILEGE) AS

SELECT   USR.XU$NAME,

         CASE PRIV.XR$TABLE

            WHEN 0 THEN 'ALL_TABLES'

            ELSE TAB.XF$NAME

         END, 'SELECT'

FROM     X$RIGHTS AS PRIV LEFT OUTER JOIN X$FILE AS TAB

         ON PRIV.XR$TABLE = TAB.XF$ID,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$RIGHTS & 64 = 64

UNION ALL

SELECT   USR.XU$NAME,

         CASE PRIV.XR$TABLE

            WHEN 0 THEN 'ALL_TABLES'

            ELSE TAB.XF$NAME

         END, 'INSERT'

FROM     X$RIGHTS AS PRIV LEFT OUTER JOIN X$FILE AS TAB

         ON PRIV.XR$TABLE = TAB.XF$ID,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$RIGHTS & 132 = 132

UNION ALL

SELECT   USR.XU$NAME,

         CASE PRIV.XR$TABLE

            WHEN 0 THEN 'ALL_TABLES'

            ELSE TAB.XF$NAME

         END, 'UPDATE'

FROM     X$RIGHTS AS PRIV LEFT OUTER JOIN X$FILE AS TAB

         ON PRIV.XR$TABLE = TAB.XF$ID,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$RIGHTS & 130 = 130

UNION ALL

SELECT   USR.XU$NAME,

         CASE PRIV.XR$TABLE

            WHEN 0 THEN 'ALL_TABLES'

            ELSE TAB.XF$NAME

         END, 'DELETE'

FROM     X$RIGHTS AS PRIV LEFT OUTER JOIN X$FILE AS TAB

         ON PRIV.XR$TABLE = TAB.XF$ID,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$RIGHTS & 136 = 136

UNION ALL

SELECT   USR.XU$NAME,

         CASE PRIV.XR$TABLE

            WHEN 0 THEN 'ALL_TABLES'

            ELSE TAB.XF$NAME

         END, 'ALTER'

FROM     X$RIGHTS AS PRIV LEFT OUTER JOIN X$FILE AS TAB

         ON PRIV.XR$TABLE = TAB.XF$ID,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$RIGHTS & 160 = 160

UNION ALL

SELECT   USR.XU$NAME,

         CASE PRIV.XR$TABLE

            WHEN 0 THEN 'ALL_TABLES'

            ELSE TAB.XF$NAME

         END, 'REFERENCES'

FROM     X$RIGHTS AS PRIV LEFT OUTER JOIN X$FILE AS TAB

         ON PRIV.XR$TABLE = TAB.XF$ID,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$RIGHTS & 144 = 144

CREATE   VIEW COLUMN_AUTHS 

        (GRANTEE, TABLE_NAME,

         COLUMN_NAME, PRIVILEGE) AS

SELECT   USR.XU$NAME, TAB.XF$NAME,

        (SELECT   COL.XE$NAME

         FROM     X$FIELD AS COL

         WHERE    COL.XE$ID = PRIV.XR$COLUMN),

         'SELECT'

FROM     X$RIGHTS AS PRIV,

         X$FILE AS TAB,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$TABLE = TAB.XF$ID

AND      PRIV.XR$COLUMN <> 0

AND      PRIV.XR$RIGHTS & 64 = 64

UNION

SELECT   USR.XU$NAME, TAB.XF$NAME,

        (SELECT   COL.XE$NAME

         FROM     X$FIELD AS COL

         WHERE    COL.XE$ID = PRIV.XR$COLUMN),

         'UPDATE'

FROM     X$RIGHTS AS PRIV,

         X$FILE AS TAB,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$TABLE = TAB.XF$ID

AND      PRIV.XR$COLUMN <> 0

AND      PRIV.XR$RIGHTS & 130 = 130

UNION

SELECT   USR.XU$NAME, TAB.XF$NAME,

        (SELECT   COL.XE$NAME

         FROM     X$FIELD AS COL

         WHERE    COL.XE$ID = PRIV.XR$COLUMN),

         'INSERT'

FROM     X$RIGHTS AS PRIV,

         X$FILE AS TAB,

         X$USER AS USR

WHERE    PRIV.XR$USER = USR.XU$ID

AND      PRIV.XR$TABLE = TAB.XF$ID

AND      PRIV.XR$COLUMN <> 0

AND      PRIV.XR$RIGHTS & 132 = 132

;

CREATE   VIEW DATABASE_AUTHS

        (GRANTEE, PRIVILEGE) AS

SELECT   USR.XU$NAME AS GRANTEE, 'CREATETAB' AS PRIV

FROM     X$USER AS USR

WHERE    XU$FLAGS & 128 = 128

UNION ALL

SELECT   USR.XU$NAME AS GRANTEE, 'CREATEVIEW' AS PRIV

FROM     X$USER AS USR

WHERE    XU$FLAGS & 256 = 256

UNION ALL

SELECT   USR.XU$NAME AS GRANTEE, 'CREATESP' AS PRIV

FROM     X$USER AS USR

WHERE    XU$FLAGS & 512 = 512



Example 4.21: 



SELECT   COLUMN_NAME, DATA_TYPE, COLUMN_NO

FROM     COLUMNS

WHERE    TABLE_NAME = 'PLAYERS'

ORDER BY COLUMN_NO



Example 4.22: 



SELECT   INDEX_NAME

FROM     INDEXES

WHERE    TABLE_NAME = 'PENALTIES'



Example 4.23: 



CALL PSP_TABLES (,,'USER TABLE')



Example 4.24: 



CALL PSP_COLUMNS (,'PLAYERS',)



Example 5.1: 



CREATE TABLE TZ (

   COL1   TIMESTAMP)

;

INSERT INTO TZ VALUES ('2005-01-01 12:00:00')

;

SELECT * FROM TZ

;

SET TIME ZONE +10:00

;

SELECT * FROM TZ

;

SET TIME ZONE LOCAL



Example 5.2: 



SELECT   MATCHNO, WON - LOST

FROM     MATCHES

WHERE    WON = LOST + 2



Example 5.3: 



SELECT   TEAMNO, DIVISION

FROM     TEAMS



Example 5.4: 



SELECT   TEAMNO AS TEAM_NUMBER, DIVISION AS DIVISION_OF_TEAM

FROM     TEAMS



Example 5.5: 



SELECT   PAYMENTNO, AMOUNT * 100 AS CENTS

FROM     PENALTIES



Example 5.6: 



SELECT   MATCHNO AS PRIMKEY,

         80 AS EIGHTY,

         WON - LOST AS DIFFERENCE,

         '23:59:59' AS ALMOST_MIDNIGHT,

         'TEXT' AS TEXT

FROM     MATCHES

WHERE    MATCHNO <= 4



Example 5.7: 



SELECT   PAYMENTNO, AMOUNT * 100 AS CENTS

FROM     PENALTIES

ORDER BY CENTS



Example 5.8: 



SELECT   USER



Example 5.9: 



SELECT   PLAYERNO,

         CASE SEX 

            WHEN 'F' THEN 'Female'

            ELSE 'Male'

         END AS SEX,

         NAME

FROM     PLAYERS

WHERE    JOINED > 1980

;

SELECT   PLAYERNO,

         CASE SEX 

            WHEN 'F' THEN 'Female'

         END AS FEMALES,

         NAME

FROM     PLAYERS

WHERE    JOINED > 1980



Example 5.10: 

 

SELECT   PLAYERNO, TOWN, BIRTH_DATE, 

         CASE TOWN 

            WHEN 'Stratford' THEN 0

            WHEN 'Plymouth'  THEN 1

            WHEN 'Inglewood' THEN 2

            ELSE 3 

         END AS P,

         CASE TOWN 

            WHEN 'Stratford' THEN 

               CASE BIRTH_DATE 

                  WHEN '1948-09-01' THEN 'Old Stratforder'

                  ELSE 'Young Stratforder'

               END

            WHEN 'Inglewood' THEN

               CASE BIRTH_DATE

                  WHEN '1962-07-08' THEN 'Old Inglewooder'

                  ELSE 'Young Inglewooder'

               END

            ELSE 'Rest' END AS TYPE

FROM     PLAYERS



Example 5.11: 



SELECT   PLAYERNO, JOINED,

         CASE

            WHEN JOINED < 1980 THEN 'Seniors'

            WHEN JOINED < 1983 THEN 'Juniors'

            ELSE 'Children'

         END AS AGE_GROUP

FROM     PLAYERS

ORDER BY JOINED



Example 5.12: 



SELECT   PLAYERNO, JOINED, TOWN,

         CASE

            WHEN JOINED >= 1980 AND JOINED <= 1982 

               THEN 'Seniors'

            WHEN TOWN = 'Eltham' 

               THEN 'Elthammers'

            WHEN PLAYERNO < 10 

               THEN 'First members'

            ELSE 'Rest'

         END

FROM     PLAYERS



Example 5.13: 



SELECT   (PLAYERNO), (((NAME)))

FROM     PLAYERS



Example 5.14: 



SELECT   PAYMENTNO, YEAR(PAYMENT_DATE) AS YEAR

FROM     PENALTIES

WHERE    YEAR(PAYMENT_DATE) > 1980



Example 5.15: 



SELECT   PLAYERNO,

         CONCAT(CONCAT(LEFT(INITIALS, 1), '. '), NAME) AS FULL_NAME

FROM     PLAYERS

WHERE    LEFT(NAME, 1) = 'B'



Example 5.16: 



SELECT   INITIALS, NAME, COALESCE(LEAGUENO, '1')

FROM     PLAYERS

WHERE    Town = 'Stratford'



Example 5.17: 



SELECT   PLAYERNO, DAYNAME(BIRTH_DATE) AS DAYNAME,

         MONTHNAME(BIRTH_DATE) AS MONTHNAME,

         DAYOFYEAR(BIRTH_DATE) AS DAYOFYEAR

FROM     PLAYERS

WHERE    PLAYERNO < 10



Example 5.18: 



SELECT   PLAYERNO, BIRTH_DATE, DATEADD(DAY, 7, BIRTH_DATE) AS BIRTH_DATE_PLUS_7

FROM     PLAYERS

WHERE    DAYNAME(BIRTH_DATE) = 'Saturday'



Example 5.19: 



SELECT   PLAYERNO, BEGIN_DATE, END_DATE,

         DATEDIFF(DAY, BEGIN_DATE, END_DATE) AS NUMBEROFDAYS

FROM     COMMITTEE_MEMBERS

WHERE    DATEDIFF(DAY, BEGIN_DATE, END_DATE) > 500

OR      (END_DATE IS NULL AND DATEDIFF(DAY, BEGIN_DATE, CURRENT_DATE()) > 500)

ORDER BY PLAYERNO, BEGIN_DATE

;

SELECT   PLAYERNO, BEGIN_DATE, END_DATE,

         DATEDIFF(DAY, BEGIN_DATE, COALESCE(END_DATE, CURRENT_DATE()))

FROM     COMMITTEE_MEMBERS

WHERE    DATEDIFF(DAY, BEGIN_DATE, COALESCE(END_DATE, CURRENT_DATE())) > 500

ORDER BY PLAYERNO



Example 5.20: 



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT > 50



Example 5.21: 



SELECT   CONCAT(RTRIM(NAME), CAST(BIRTH_DATE AS CHAR(10))) AS NAME_PLUS_DATE

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'



Example 5.22: 



UPDATE   PLAYERS

SET      LEAGUENO = NULL

WHERE    PLAYERNO = 2



Example 5.23: 



SELECT   TEAMNO, CAST(NULL AS CHAR)

FROM     TEAMS



Example 5.24: 



SELECT   MATCHNO, WON, LOST

FROM     MATCHES

WHERE    WON >= LOST * 2



Example 5.25: 



SELECT   PAYMENTNO, PAYMENT_DATE, PAYMENT_DATE - CAST('1900-12-25' AS DATE) AS DIFFERENCE

FROM     PENALTIES

WHERE    PAYMENTNO < 5



Example 5.26: 



SELECT   CAST('2000-01-01' AS DATE) – CAST('2004-01-01' AS DATE) AS NEGATIVE,

         CAST('2004-01-01' AS DATE) – CAST('2000-01-01' AS DATE) AS POSITIVE



Example 5.27: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    PLAYERNO & 1 = 1



Example 5.28: 



SELECT   MATCHNO, TEAMNO, MATCHNO | TEAMNO, MATCHNO & TEAMNO, MATCHNO ^ TEAMNO

FROM     MATCHES



Example 5.29: 



SELECT   PLAYERNO, TOWN + ' ' + STREET + ' ' + HOUSENO AS ADDRESS

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



Example 5.30: 



SELECT   PAYMENTNO, PAYMENT_DATE, PAYMENT_DATE + 7 AS DATE_PLUS_7

FROM     PENALTIES

WHERE    PAYMENTNO > 5



Example 5.31: 



SELECT   PAYMENTNO, PAYMENT_DATE

FROM     PENALTIES

WHERE    PAYMENT_DATE > '1982-12-25' + 6

;

SELECT   PAYMENTNO, PAYMENT_DATE

FROM     PENALTIES

WHERE    PAYMENT_DATE > CAST('1982-12-25' AS DATE) + 6



Example 5.32: 



SELECT   PAYMENTNO, PAYMENT_DATE

FROM     PENALTIES

WHERE    PAYMENT_DATE >= '1982-12-25'

AND      PAYMENT_DATE <= CAST('1982-12-25' AS DATE) + 6



Example 5.33: 



INSERT   INTO COMMITTEE_MEMBERS

VALUES   (2 + 4, CURRENT_DATE(), CURRENT_DATE()+17, 'Member')



Exercise 5.4:



SELECT   MATCHNO, WON - LOST AS DIFFERENCE

FROM     MATCHES



Exercise 5.6:



SELECT   PLAYERS.PLAYERNO, PLAYERS.NAME, PLAYERS.INITIALS

FROM     PLAYERS

WHERE    PLAYERS.PLAYERNO > 6

ORDER BY PLAYERS.NAME



Exercise 5.8:



SELECT   PRIVILEGE

FROM     DATABASE_AUTHS

WHERE    GRANTEE = USER



Exercise 5.9:



SELECT   TEAMNO,

         CASE DIVISION

            WHEN 'first' then 'first division'

            WHEN 'second' THEN 'second division'

            ELSE 'unknown'

         END AS DIVISION

FROM     TEAMS



Exercise 5.10:



SELECT   PAYMENTNO, AMOUNT,

         CASE

            WHEN AMOUNT >= 0 AND AMOUNT <= 40 

               THEN 'low'

            WHEN AMOUNT >= 41 AND AMOUNT <= 80 

               THEN 'moderate'

            WHEN AMOUNT >= 81 

               THEN 'high'

            ELSE 'incorrect'

         END AS CATEGORY

FROM     PENALTIES



Exercise 5.11:



SELECT   PAYMENTNO, AMOUNT

FROM     PENALTIES

WHERE    CASE

            WHEN AMOUNT >= 0 AND AMOUNT <= 40 

               THEN 'low'

            WHEN AMOUNT > 40 AND AMOUNT <= 80 

               THEN 'moderate'

            WHEN AMOUNT > 80

               THEN 'high'

            ELSE 'incorrect'

         END = 'low'



Exercise 5.13:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    DAYNAME(PAYMENT_DATE) = 'Monday'



Exercise 5.14:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    YEAR(PAYMENT_DATE) = 1984



Exercise 5.21:



SELECT  PLAYERNO, SUBSTR(INITIALS,1,1) + '. ' + NAME

FROM    PLAYERS



Exercise 5.22:



SELECT  TEAMNO, RTRIM(DIVISION) + ' division'

FROM    TEAMS



Exercise 5.24:



SELECT   PAYMENTNO, PAYMENT_DATE,

         DATEADD(HOUR, 3, DATEADD(MINUTE, 50, PAYMENT_DATE)) + (1.0/(24*60*60))*0.99

FROM     PENALTIES



Example 6.1: 



SELECT   PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT > 25

GROUP BY PLAYERNO

HAVING   COUNT(*) > 1

ORDER BY PLAYERNO



Example 6.2: 



SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

ORDER BY LEAGUENO



Example 6.3: 



SELECT   89 * 73



Example 6.4: 



(SELECT   *

 FROM     TEAMS)

;

(((((SELECT   *

     FROM     TEAMS)))))



Example 6.5: 



SELECT   PLAYERNO

FROM     TEAMS

UNION

SELECT   PLAYERNO

FROM     PENALTIES

;

SELECT   PLAYERNO

FROM     TEAMS

ORDER BY PLAYERNO

UNION

SELECT   PLAYERNO

FROM     PENALTIES

;

SELECT   PLAYERNO

FROM     TEAMS

UNION

SELECT   PLAYERNO

FROM     PENALTIES 

ORDER BY PLAYERNO



Example 6.6: 



SELECT   PLAYERNO

FROM    (SELECT   PLAYERNO, SEX

         FROM     PLAYERS

         WHERE    PLAYERNO < 10) AS PLAYERS10

WHERE    SEX = 'M'



Example 6.7: 



SELECT   PLAYERNO

FROM    (SELECT   PLAYERNO, SEX

         FROM    (SELECT   PLAYERNO, SEX, JOINED

                  FROM    (SELECT   PLAYERNO, SEX, JOINED

                           FROM     PLAYERS

                           WHERE    PLAYERNO > 10) AS GREATER10

                  WHERE    PLAYERNO < 100) AS LESS100

         WHERE    JOINED > 1980) AS JOINED1980

WHERE    SEX = 'M'



Example 6.8: 



SELECT   PLAYERNO, JOINED -

                  (SELECT   JOINED

                   FROM     PLAYERS

                   WHERE    PLAYERNO = 100)

FROM     PLAYERS

WHERE    PLAYERNO < 60

;

SELECT   PLAYERNO, JOINED - 1979

FROM     PLAYERS

WHERE    PLAYERNO < 60

;

SELECT   TEAMNO

FROM     TEAMS

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     PLAYERS)



Example 6.9: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    YEAR(BIRTH_DATE) = 

        (SELECT   YEAR(BIRTH_DATE)

         FROM     PLAYERS

         WHERE    PLAYERNO = 27)

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    YEAR(BIRTH_DATE) = 1964



Example 6.10: 



SELECT   (SELECT   BIRTH_DATE

          FROM     PLAYERS

          WHERE    PLAYERNO = 27) AS BD27,

         (SELECT   BIRTH_DATE

          FROM     PLAYERS

          WHERE    PLAYERNO = 44) AS BD44,

         (SELECT   BIRTH_DATE

          FROM     PLAYERS

          WHERE    PLAYERNO = 100) AS BD100



Example 6.11: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    SEX = 

        (SELECT   SEX

         FROM     PLAYERS

         WHERE    PLAYERNO = 100)



Answer 6.8:



SELECT   PLAYERNO, BEGIN_DATE

FROM     COMMITTEE_MEMBERS

UNION

SELECT   PLAYERNO, END_DATE

FROM     COMMITTEE_MEMBERS

ORDER BY PLAYERNO



Answer 6.9:



SELECT   PLAYERNO, BEGIN_DATE, 'Begin date'

FROM     COMMITTEE_MEMBERS

UNION

SELECT   PLAYERNO, END_DATE, 'End date'

FROM     COMMITTEE_MEMBERS

ORDER BY PLAYERNO



Answer 6.10
:




SELECT  PLAYERNO

FROM   (SELECT  PLAYERNO

        FROM   (SELECT  PLAYERNO, END_DATE

                FROM   (SELECT  PLAYERNO, BEGIN_DATE, 

                                END_DATE

                        FROM    COMMITTEE_MEMBERS

                        WHERE   POSITION = 'Secretary')

                        AS SECRETARIES

                WHERE   BEGIN_DATE >= '1990-01-01')

                AS AFTER1989

        WHERE   END_DATE <= '1994-12-31') AS BEFORE1995



Answer 6.11:



SELECT   TEAMNO

FROM     TEAMS

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    NAME = 'Parmenter'

         AND      INITIALS = 'R')



Answer 6.12:



SELECT   NAME

FROM     PLAYERS

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     TEAMS

         WHERE    TEAMNO =

                 (SELECT   TEAMNO

                  FROM     MATCHES

                  WHERE    MATCHNO = 6))



Answer 6.13:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AMOUNT

         FROM     PENALTIES

         WHERE    PAYMENTNO = 4)



Answer 6.14:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    DAYNAME(BIRTH_DATE) =

        (SELECT   DAYNAME(BIRTH_DATE)

         FROM     PLAYERS

         WHERE    PLAYERNO = 2)



Answer 6.15
:




SELECT  (SELECT   DIVISION

         FROM     TEAMS

         WHERE    TEAMNO = 1),

        (SELECT   DIVISION

         FROM     TEAMS

         WHERE    TEAMNO = 2)



Answer 6.16:



SELECT  (SELECT   AMOUNT

         FROM     PENALTIES

         WHERE    PAYMENTNO = 1) +

        (SELECT   AMOUNT

         FROM     PENALTIES

         WHERE    PAYMENTNO = 2) +

        (SELECT   AMOUNT

         FROM     PENALTIES

         WHERE    PAYMENTNO = 3)



Example 7.1: 



SELECT   *

FROM     EXTRADB.TOWNS



Example 7.2: 



SELECT   TEAMNO

FROM     TEAMS

;

SELECT   TEAMS.TEAMNO

FROM     TEAMS

;

SELECT   TENNIS.TEAMS.TEAMNO

FROM     TENNIS.TEAMS



Example 7.3: 



SELECT   TEAMNO, NAME

FROM     TEAMS, PLAYERS

WHERE    TEAMS.PLAYERNO = PLAYERS.PLAYERNO



Example 7.4: 



SELECT   PAYMENTNO, PENALTIES.PLAYERNO, AMOUNT, NAME, INITIALS

FROM     PENALTIES, PLAYERS

WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO

;

SELECT   PLAYERS.PLAYERNO

FROM     PLAYERS, TEAMS

WHERE    PLAYERS.PLAYERNO = TEAMS.PLAYERNO

;

SELECT   PLAYERS.PLAYERNO

FROM     TEAMS, PLAYERS

WHERE    PLAYERS.PLAYERNO = TEAMS.PLAYERNO



Example 7.5: 



SELECT   PAYMENTNO, PEN.PLAYERNO, AMOUNT, NAME, INITIALS

FROM     PENALTIES AS PEN, PLAYERS AS P

WHERE    PEN.PLAYERNO = P.PLAYERNO

;

SELECT   PAYMENTNO, PEN.PLAYERNO, AMOUNT, NAME, INITIALS

FROM     PENALTIES PEN, PLAYERS P

WHERE    PEN.PLAYERNO = P.PLAYERNO



Example 7.6: 



SELECT   T.PLAYERNO

FROM     TEAMS AS T, PENALTIES AS PEN

WHERE    T.PLAYERNO = PEN.PLAYERNO



Example 7.7: 



SELECT   DISTINCT T.PLAYERNO

FROM     TEAMS AS T, PENALTIES AS PEN

WHERE    T.PLAYERNO = PEN.PLAYERNO



Example 7.8: 



SELECT   DISTINCT P.NAME, P.INITIALS

FROM     PLAYERS AS P, MATCHES AS M

WHERE    P.PLAYERNO = M.PLAYERNO



Example 7.9: 



SELECT   M.MATCHNO, M.PLAYERNO, M.TEAMNO, P.NAME, T.DIVISION

FROM     MATCHES AS M, PLAYERS AS P, TEAMS AS T

WHERE    M.PLAYERNO = P.PLAYERNO

AND      M.TEAMNO = T.TEAMNO



Example 7.10: 



SELECT   PEN.PAYMENTNO, PEN.PLAYERNO, PEN.PAYMENT_DATE

FROM     PENALTIES AS PEN, PLAYERS AS P

WHERE    PEN.PLAYERNO = P.PLAYERNO

AND      YEAR(PEN.PAYMENT_DATE) = P.JOINED



Example 7.11: 



SELECT   P.PLAYERNO

FROM     PLAYERS AS P, PLAYERS AS PAR

WHERE    PAR.NAME = 'Parmenter'

AND      PAR.INITIALS = 'R'

AND      P.BIRTH_DATE < PAR.BIRTH_DATE

;

SELECT   P.PLAYERNO

FROM     PLAYERS AS P, PLAYERS

WHERE    PLAYERS.NAME = 'Parmenter'

AND      PLAYERS.INITIALS = 'R'

AND      P.BIRTH_DATE < PLAYERS.BIRTH_DATE



Example 7.12: 



SELECT   P.PLAYERNO

FROM     PLAYERS AS P, EXTRA.CITIES AS TOWN

WHERE    P.TOWN = TOWN.CITYNAME



Example 7.13: 



SELECT   PLAYERS.PLAYERNO, NAME, AMOUNT

FROM     PLAYERS, PENALTIES

WHERE    PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

AND      BIRTH_DATE > '1920-06-30'

;

SELECT   PLAYERS.PLAYERNO, NAME, AMOUNT

FROM     PLAYERS INNER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

WHERE    BIRTH_DATE > '1920-06-30'



Example 7.14: 



SELECT   TEAMNO, NAME

FROM     TEAMS, PLAYERS

WHERE    TEAMS.PLAYERNO = PLAYERS.PLAYERNO

;

SELECT   TEAMNO, NAME

FROM     TEAMS INNER JOIN PLAYERS

         ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO

;

SELECT   TEAMNO, NAME

FROM     TEAMS JOIN PLAYERS

         ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO



Example 7.15: 



SELECT   PLAYERS.PLAYERNO, NAME, AMOUNT

FROM     PLAYERS, PENALTIES

WHERE    PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

ORDER BY PLAYERS.PLAYERNO

;

SELECT   PLAYERS.PLAYERNO, NAME, AMOUNT

FROM     PLAYERS LEFT OUTER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

ORDER BY PLAYERS.PLAYERNO



Example 7.16: 



SELECT   PAYMENTNO, NAME

FROM     PENALTIES LEFT OUTER JOIN PLAYERS

         ON PENALTIES.PLAYERNO = PLAYERS.PLAYERNO

ORDER BY PAYMENTNO



Example 7.17: 



SELECT   P.PLAYERNO, NAME, TEAMNO, DIVISION

FROM     PLAYERS AS P LEFT OUTER JOIN TEAMS AS T

         ON P.PLAYERNO = T.PLAYERNO

ORDER BY P.PLAYERNO



Example 7.18: 



SELECT   PLAYERS.PLAYERNO, NAME, AMOUNT, TEAMNO

FROM     PLAYERS LEFT OUTER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

            LEFT OUTER JOIN MATCHES

            ON PLAYERS.PLAYERNO = MATCHES.PLAYERNO

WHERE    TOWN = 'Inglewood'



Example 7.19: 



SELECT   PLAYERS.PLAYERNO

FROM     PLAYERS INNER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

         LEFT OUTER JOIN TEAMS

         ON PLAYERS.PLAYERNO = TEAMS.PLAYERNO

;

SELECT   PLAYERS.PLAYERNO

FROM    (PLAYERS INNER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)

         LEFT OUTER JOIN TEAMS

         ON PLAYERS.PLAYERNO = TEAMS.PLAYERNO



Example 7.20: 



SELECT   PLAYERS.PLAYERNO, NAME, TEAMNO

FROM     TEAMS RIGHT OUTER JOIN PLAYERS

         ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO



Example 7.21: 



SELECT   DISTINCT MATCHES.MATCHNO, MATCHES.PLAYERNO AS MATCH_PNO, 

         COMMITTEE_MEMBERS.PLAYERNO AS COMMITTEE_PNO

FROM     MATCHES FULL OUTER JOIN COMMITTEE_MEMBERS

         ON MATCHES.PLAYERNO = COMMITTEE_MEMBERS.PLAYERNO

ORDER BY MATCHES.MATCHNO, MATCHES.PLAYERNO, COMMITTEE_MEMBERS.PLAYERNO



Example 7.22: 



SELECT   TEAMS.PLAYERNO, TEAMS.TEAMNO, PENALTIES.PAYMENTNO

FROM     TEAMS LEFT OUTER JOIN PENALTIES

         ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO

WHERE    DIVISION = 'second'

;

SELECT   TEAMS.PLAYERNO, TEAMS.TEAMNO, PENALTIES.PAYMENTNO

FROM     TEAMS LEFT OUTER JOIN PENALTIES

         ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO

         AND DIVISION = 'second'



Example 7.23: 



SELECT   TEAMS.PLAYERNO, TEAMS.TEAMNO, PENALTIES.PAYMENTNO

FROM     TEAMS FULL OUTER JOIN PENALTIES

         ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO

         AND TEAMS.PLAYERNO > 1000



Example 7.24: 



SELECT   PLAYERNO

FROM    (SELECT   *

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford') AS STRATFORDERS



Example 7.25: 



SELECT   SMALL_TEAMS.PLAYERNO

FROM    (SELECT   PLAYERNO, DIVISION

         FROM     TEAMS) AS SMALL_TEAMS

WHERE    SMALL_TEAMS.DIVISION = 'first'



Example 7.26: 



SELECT   MATCHNO, DIFFERENCE

FROM    (SELECT   MATCHNO, 

                  ABS(WON – LOST) AS DIFFERENCE

         FROM     MATCHES) AS M

WHERE    DIFFERENCE > 2

;

SELECT   MATCHNO, DIFFERENCE

FROM    (SELECT   MATCHNO,

                  ABS(WON - LOST)

         FROM     MATCHES) AS M (MATCHNO, DIFFERENCE)

WHERE    DIFFERENCE > 2



Example 7.27: 



SELECT   *

FROM    (SELECT 'Stratford' AS TOWN, 4 AS NUMBER

         UNION

         SELECT 'Plymouth', 6

         UNION

         SELECT 'Inglewood', 1

         UNION

         SELECT 'Douglas', 2) AS TOWNS

ORDER BY TOWN



Example 7.28: 



SELECT   PLAYERNO, NAME, PLAYERS.TOWN, NUMBER * 1000

FROM     PLAYERS,

        (SELECT 'Stratford' AS TOWN, 4 AS NUMBER

         UNION

         SELECT 'Plymouth', 6

         UNION

         SELECT 'Inglewood', 1

         UNION

         SELECT 'Douglas', 2) AS TOWNS

WHERE    PLAYERS.TOWN = TOWNS.TOWN

ORDER BY PLAYERNO

;

SELECT   PLAYERNO, NAME, PLAYERS.TOWN, NUMBER * 1000

FROM     PLAYERS LEFT OUTER JOIN

        (SELECT 'Stratford' AS TOWN, 4 AS NUMBER

         UNION

         SELECT 'Plymouth', 6

         UNION

         SELECT 'Inglewood', 1

         UNION

         SELECT 'Douglas', 2) AS TOWNS

         ON PLAYERS.TOWN = TOWNS.TOWN

ORDER BY PLAYERNO



Example 7.29: 



SELECT   PLAYERNO

FROM     PLAYERS LEFT OUTER JOIN

        (SELECT 'Stratford' AS TOWN, 4 AS NUMBER

         UNION

         SELECT 'Plymouth', 6

         UNION

         SELECT 'Inglewood', 1

         UNION

         SELECT 'Douglas', 2) AS TOWNS

         ON PLAYERS.TOWN = TOWNS.TOWN

WHERE    TOWNS.NUMBER > 2



Example 7.30: 



SELECT   *

FROM    (SELECT 'John' AS FIRST_NAME

         UNION

         SELECT 'Mark'

         UNION

         SELECT 'Arnold') AS FIRST_NAMES,

        (SELECT 'Berg' AS LAST_NAME

         UNION

         SELECT 'Johnson'

         UNION

         SELECT 'Williams') AS LAST_NAMES



Example 7.31: 



SELECT   NUMBER, POWER(NUMBER,3) AS POWER3

FROM    (SELECT 10 AS NUMBER UNION SELECT 11 UNION SELECT 12

         UNION

         SELECT 13 UNION SELECT 14 UNION SELECT 15

         UNION

         SELECT 16 UNION SELECT 17 UNION SELECT 18

         UNION

         SELECT 19) AS NUMBERS

WHERE    POWER(NUMBER,3) <= 4000



Example 7.32: 



SELECT   NUMBER

FROM    (SELECT    CAST(DIGIT1.DIGIT + DIGIT2.DIGIT +

                   DIGIT3.DIGIT AS INTEGER) 

                   AS NUMBER

         FROM     (SELECT '0' AS DIGIT UNION SELECT '1' UNION 

                   SELECT '2' UNION SELECT '3' UNION 

                   SELECT '4' UNION SELECT '5' UNION

                   SELECT '6' UNION SELECT '7' UNION 

                   SELECT '8' UNION SELECT '9') AS DIGIT1,

                  (SELECT '0' AS DIGIT UNION SELECT '1' UNION 

                   SELECT '2' UNION SELECT '3' UNION 

                   SELECT '4' UNION SELECT '5' UNION

                   SELECT '6' UNION SELECT '7' UNION 

                   SELECT '8' UNION SELECT '9') AS DIGIT2,

                  (SELECT '0' AS DIGIT UNION SELECT '1' UNION 

                   SELECT '2' UNION SELECT '3' UNION 

                   SELECT '4' UNION SELECT '5' UNION

                   SELECT '6' UNION SELECT '7' UNION 

                   SELECT '8' UNION SELECT '9') AS DIGIT3)

                   AS NUMBERS

ORDER BY NUMBER



Example 7.33: 



SELECT   NUMBER AS SQUARE, ROUND(SQRT(NUMBER),0) AS BASIS

FROM    (SELECT    CAST(CONCAT(DIGIT1.DIGIT, 

                   CONCAT(DIGIT2.DIGIT,

                   DIGIT3.DIGIT)) AS INTEGER) 

                   AS NUMBER

         FROM     (SELECT '0' AS DIGIT UNION SELECT '1' UNION

                   SELECT '2' UNION SELECT '3' UNION

                   SELECT '4' UNION SELECT '5' UNION

                   SELECT '6' UNION SELECT '7' UNION

                   SELECT '8' UNION SELECT '9') AS DIGIT1,

                  (SELECT '0' AS DIGIT UNION SELECT '1' UNION

                   SELECT '2' UNION SELECT '3' UNION

                   SELECT '4' UNION SELECT '5' UNION

                   SELECT '6' UNION SELECT '7' UNION

                   SELECT '8' UNION SELECT '9') AS DIGIT2,

                  (SELECT '0' AS DIGIT UNION SELECT '1' UNION

                   SELECT '2' UNION SELECT '3' UNION

                   SELECT '4' UNION SELECT '5' UNION

                   SELECT '6' UNION SELECT '7' UNION

                   SELECT '8' UNION SELECT '9') AS DIGIT3)

                   AS NUMBERS

WHERE    SQRT(NUMBER) = ROUND(SQRT(NUMBER),0)

ORDER BY NUMBER



Answer 7.3:



SELECT   PAYMENTNO, AMOUNT, PLAYERS.PLAYERNO, NAME

FROM     PENALTIES, PLAYERS

WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO



Answer 7.4
:




SELECT   PAYMENTNO, NAME

FROM     PENALTIES, PLAYERS, TEAMS

WHERE    PENALTIES.PLAYERNO = TEAMS.PLAYERNO

AND      TEAMS.PLAYERNO = PLAYERS.PLAYERNO



Answer 7.5:



SELECT   T.TEAMNO, P.NAME

FROM     TEAMS AS T, PLAYERS AS P

WHERE    T.PLAYERNO = P.PLAYERNO



Answer 7.6:



SELECT   M.MATCHNO, P.NAME, T.DIVISION

FROM     MATCHES AS M, PLAYERS AS P, TEAMS AS T

WHERE    M.PLAYERNO = P.PLAYERNO

AND      M.TEAMNO = T.TEAMNO



Answer 7.7:



SELECT   P.PLAYERNO, P.NAME

FROM     PLAYERS AS P, COMMITTEE_MEMBERS AS C

WHERE    P.PLAYERNO = C.PLAYERNO

AND      C.POSITION = 'Chairman'



Answer 7.8:



SELECT   DISTINCT CM.PLAYERNO

FROM     COMMITTEE_MEMBERS AS CM, PENALTIES AS PEN

WHERE    CM.PLAYERNO = PEN.PLAYERNO

AND      CM.BEGIN_DATE = PEN.PAYMENT_DATE



Answer 7.9
:




SELECT   P.PLAYERNO, P.NAME

FROM     PLAYERS AS P, PLAYERS AS P27

WHERE    P.TOWN = P27.TOWN

AND      P27.PLAYERNO = 27

AND      P.PLAYERNO <> 27



Answer 7.10:



SELECT   DISTINCT P.PLAYERNO AS PLAYER_PLAYERNO, P.NAME AS PLAYER_NAME,

         CAP.PLAYERNO AS CAPTAIN_PLAYERNO, CAP.NAME AS CAPTAIN_NAME

FROM     PLAYERS AS P, PLAYERS AS CAP, MATCHES AS M, TEAMS AS T

WHERE    M.PLAYERNO = P.PLAYERNO

AND      T.TEAMNO = M.TEAMNO

AND      M.PLAYERNO <> T.PLAYERNO

AND      CAP.PLAYERNO = T.PLAYERNO



Answer 7.11:



SELECT   PEN1.PAYMENTNO, PEN1.PLAYERNO

FROM     PENALTIES AS PEN1, PENALTIES AS PEN2

WHERE    PEN1.AMOUNT = PEN2.AMOUNT

AND      PEN2.PLAYERNO = 44

AND      PEN1.PLAYERNO <> 44



Answer 7.12:



SELECT   T.TEAMNO, P.NAME

FROM     TEAMS AS T INNER JOIN PLAYERS AS P

         ON T.PLAYERNO = P.PLAYERNO



Answer 7.13:



SELECT   P.PLAYERNO, P.NAME

FROM     PLAYERS AS P INNER JOIN PLAYERS AS P27

         ON P.TOWN = P27.TOWN

AND      P27.PLAYERNO = 27

AND      P.PLAYERNO <> 27



Answer 7.14:



SELECT   M.MATCHNO, P.NAME, T.DIVISION

FROM     MATCHES AS M INNER JOIN PLAYERS AS P

         ON M.PLAYERNO = P.PLAYERNO

         INNER JOIN TEAMS AS T

         ON M.TEAMNO = T.TEAMNO



Answer 7.15:



SELECT   PLAYERS.PLAYERNO, PENALTIES.AMOUNT

FROM     PLAYERS LEFT OUTER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO



Answer 7.16:



SELECT   P.PLAYERNO, M.TEAMNO

FROM     PLAYERS AS P LEFT OUTER JOIN MATCHES AS M

         ON P.PLAYERNO = M.PLAYERNO



Answer 7.17:



SELECT   P.PLAYERNO, PEN.AMOUNT, M.TEAMNO

FROM     PLAYERS AS P LEFT OUTER JOIN MATCHES AS M

         ON P.PLAYERNO = M.PLAYERNO

         LEFT OUTER JOIN PENALTIES AS PEN

         ON P.PLAYERNO = PEN.PLAYERNO



Answer 7.21:



SELECT   PLAYERNO, DIFFERENCE

FROM    (SELECT   PLAYERNO, 

                  JOINED - YEAR(BIRTH_DATE) AS DIFFERENCE

         FROM     PLAYERS) AS DIFFERENCES

WHERE    DIFFERENCE > 20



Answer 7.22:



SELECT   LETTER1 + LETTER2 + LETTER3

FROM    (SELECT 'a' AS LETTER1 UNION SELECT 'b' 

         UNION SELECT 'c' UNION SELECT 'd') AS LETTERS1,

        (SELECT 'a' AS LETTER2 UNION SELECT 'b' 

         UNION SELECT 'c' UNION SELECT 'd') AS LETTERS2,

        (SELECT 'a' AS LETTER3 UNION SELECT 'b' 

         UNION SELECT 'c' UNION SELECT 'd') AS LETTERS3



Answer 7.23:



SELECT   CAST(SUBSTRING(CAST(RAND(NUMBER*1000) AS

              CHAR(10)),6,3) AS INTEGER)

FROM    (SELECT 0 AS NUMBER UNION SELECT 1 UNION SELECT 2

         UNION

         SELECT 3 UNION SELECT 4 UNION SELECT 5

         UNION

         SELECT 6 UNION SELECT 7 UNION SELECT 8

         UNION

         SELECT 9) AS NUMBERS



Example 8.1: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



Example 8.2: 



SELECT   PLAYERNO, BIRTH_DATE, JOINED

FROM     PLAYERS

WHERE    YEAR(BIRTH_DATE) + 17 = JOINED



Example 8.3: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    LEAGUENO = '7060'



Example 8.4: 



SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO = LEAGUENO



Example 8.5: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     TEAMS

         WHERE    TEAMNO = 1)

;

SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO = 6

;

SELECT   *

FROM     PLAYERS

WHERE    BIRTH_DATE <

        (SELECT   BIRTH_DATE

         FROM     PLAYERS)



Example 8.6: 



SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    BIRTH_DATE <

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    LEAGUENO = '8467')

;

SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    BIRTH_DATE <

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    LEAGUENO = '9999')



Example 8.7: 



SELECT   MATCHNO

FROM     MATCHES

WHERE    TEAMNO = 

        (SELECT   TEAMNO

         FROM     TEAMS

         WHERE    PLAYERNO = 27)



Example 8.8: 



SELECT   MATCHNO

FROM     MATCHES

WHERE    'Inglewood' =

        (SELECT   TOWN

         FROM     PLAYERS

         WHERE    PLAYERS.PLAYERNO = MATCHES.PLAYERNO)

;

SELECT   TOWN

FROM     PLAYERS

WHERE    PLAYERS.PLAYERNO = 6

;

SELECT   TOWN

FROM     PLAYERS

WHERE    PLAYERS.PLAYERNO = 44



Example 8.9: 



SELECT   MATCHNO, PLAYERNO, TEAMNO

FROM     MATCHES

WHERE    PLAYERNO = 

        (SELECT   PLAYERNO

         FROM     TEAMS

         WHERE    TEAMS.PLAYERNO = MATCHES.PLAYERNO)



Example 8.10: 



SELECT   MATCHNO

FROM     MATCHES

WHERE    SUBSTRING(

        (SELECT   DIVISION 

         FROM     TEAMS

         WHERE    TEAMS.TEAMNO = MATCHES.TEAMNO),3,1)

         =

         SUBSTRING(

        (SELECT   NAME

         FROM     PLAYERS

         WHERE    PLAYERS.PLAYERNO = MATCHES.PLAYERNO),3,1)



Example 8.11: 



SELECT   PLAYERNO, NAME, SEX, BIRTH_DATE

FROM     PLAYERS

WHERE    SEX = 'M'

AND      BIRTH_DATE > '1970-12-31'



Example 8.12: 



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Plymouth'

OR       TOWN = 'Eltham'



Example 8.13: 



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN <> 'Stratford'

;

SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    NOT (TOWN = 'Stratford')



Example 8.14: 



SELECT   PLAYERNO, TOWN, BIRTH_DATE

FROM     PLAYERS

WHERE   (TOWN = 'Stratford' OR  YEAR(BIRTH_DATE) = 1963)

AND NOT (TOWN = 'Stratford' AND YEAR(BIRTH_DATE) = 1963)



Example 8.15: 



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'

OR       TOWN = 'Plymouth'

OR       TOWN = 'Midhurst'

OR       TOWN = 'Douglas'

;

SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN IN ('Inglewood', 'Plymouth', 'Midhurst', 'Douglas')



Example 8.16: 



SELECT   PLAYERNO, YEAR(BIRTH_DATE) AS BIRTH_YEAR

FROM     PLAYERS

WHERE    YEAR(BIRTH_DATE) IN (1962, 1963, 1970)



Example 8.17: 



SELECT   MATCHNO

FROM     MATCHES

WHERE   (SELECT   SUBSTRING(NAME,1,1)

         FROM     PLAYERS

         WHERE    PLAYERS.PLAYERNO = MATCHES.PLAYERNO) IN ('B','C','E')



Example 8.18: 



SELECT   PLAYERNO

FROM     MATCHES

;

SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN (6, 6, 6, 44, 83, 2, 57, 8, 27, 104, 112, 112, 8)

;

SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES)



Example 8.19: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO = 1)



Example 8.20: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO NOT IN

                 (SELECT   TEAMNO

                  FROM     TEAMS

                  WHERE    PLAYERNO = 6))



Example 8.21: 



SELECT   *

FROM     COMMITTEE_MEMBERS

WHERE    PLAYERNO = 95

AND      END_DATE NOT IN

        (SELECT   END_DATE

         FROM     COMMITTEE_MEMBERS

         WHERE    PLAYERNO = 7)



Example 8.22: 



SELECT   PLAYERNO, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE >= '1962-01-01'

AND      BIRTH_DATE <= '1964-12-31'

;

SELECT   PLAYERNO, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE BETWEEN '1962-01-01' AND '1964-12-31'



Example 8.23: 



SELECT   MATCHNO, WON + LOST

FROM     MATCHES

WHERE    WON + LOST BETWEEN 2 AND 4



Example 8.24: 



SELECT   PLAYERNO, BIRTH_DATE, NAME, INITIALS

FROM     PLAYERS

WHERE    BIRTH_DATE BETWEEN

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    NAME = 'Newcastle'

         AND      INITIALS = 'B')

         AND

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    NAME = 'Miller'

         AND      INITIALS = 'P')



Example 8.25: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    NAME LIKE 'B%'



Example 8.26: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    NAME LIKE '%r'



Example 8.27: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    NAME LIKE '%e_'



Example 8.28: 



SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO IS NOT NULL



Example 8.29: 



SELECT   NAME, PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO <> '8467'

OR       LEAGUENO IS NULL



Example 8.30: 



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)

;

SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)

;

SELECT   *

FROM     PENALTIES

WHERE    PLAYERNO = 6



Example 8.31: 



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    NOT EXISTS

        (SELECT   *

         FROM     TEAMS

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)

;

SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    NOT EXISTS

        (SELECT   'nothing'

         FROM     TEAMS

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Example 8.32: 



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE <= ALL

        (SELECT   BIRTH_DATE

         FROM     PLAYERS)



Example 8.33: 



SELECT   PLAYERNO, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE < ALL

        (SELECT   BIRTH_DATE

         FROM     PLAYERS AS P INNER JOIN MATCHES AS M

                  ON P.PLAYERNO = M.PLAYERNO

         WHERE    M.TEAMNO = 2)



Example 8.34: 



SELECT   DISTINCT TEAMNO, PLAYERNO

FROM     MATCHES AS M1

WHERE    WON <= ALL

        (SELECT   WON

         FROM     MATCHES AS M2

         WHERE    M1.TEAMNO = M2.TEAMNO)



Example 8.35: 



SELECT   LEAGUENO, PLAYERNO

FROM     PLAYERS

WHERE    LEAGUENO >= ALL

        (SELECT   LEAGUENO

         FROM     PLAYERS)

;

SELECT   LEAGUENO, PLAYERNO

FROM     PLAYERS

WHERE    LEAGUENO >= ALL

        (SELECT   LEAGUENO

         FROM     PLAYERS

         WHERE    LEAGUENO IS NOT NULL)



Example 8.36: 



SELECT   PLAYERNO, TOWN, LEAGUENO

FROM     PLAYERS AS P1

WHERE    LEAGUENO <= ALL

        (SELECT   P2.LEAGUENO

         FROM     PLAYERS AS P2

         WHERE    P1.TOWN = P2.TOWN)

;

SELECT   PLAYERNO, TOWN, LEAGUENO

FROM     PLAYERS AS P1

WHERE    LEAGUENO <= ALL

        (SELECT   P2.LEAGUENO

         FROM     PLAYERS AS P2

         WHERE    P1.TOWN = P2.TOWN

         AND      LEAGUENO IS NOT NULL)

;

SELECT   PLAYERNO, TOWN, LEAGUENO

FROM     PLAYERS AS P1

WHERE    LEAGUENO <= ALL

        (SELECT   P2.LEAGUENO

         FROM     PLAYERS AS P2

         WHERE    P1.TOWN = P2.TOWN

         AND      LEAGUENO IS NOT NULL)

AND      TOWN IN

        (SELECT   TOWN

         FROM     PLAYERS

         WHERE    LEAGUENO IS NOT NULL)



Example 8.37: 



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE > ANY

        (SELECT   BIRTH_DATE

         FROM     PLAYERS)



Example 8.38: 



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES

WHERE    PLAYERNO <> 27

AND      AMOUNT > ANY

        (SELECT   AMOUNT

         FROM     PENALTIES

         WHERE    PLAYERNO = 27)



Example 8.39: 



SELECT   PLAYERNO, BIRTH_DATE, TOWN

FROM     PLAYERS AS P1

WHERE    BIRTH_DATE > ANY

        (SELECT   BIRTH_DATE

         FROM     PLAYERS AS P2

         WHERE    P1.TOWN = P2.TOWN)



Example 8.40: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERS.PLAYERNO = PLAYERNO)

;

SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERS.PLAYERNO = PLAYERS.PLAYERNO)

;

SELECT   PLAYERNO, NAME

FROM     PLAYERS AS P

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES AS PEN

         WHERE    P.PLAYERNO = PEN.PLAYERNO)



Example 8.41: 



SELECT   TEAMNO, DIVISION

FROM     TEAMS

WHERE    EXISTS

        (SELECT   *

         FROM     MATCHES

         WHERE    PLAYERNO = 44

         AND      TEAMNO = TEAMS.TEAMNO)



Example 8.42: 



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES AS PEN

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    PAYMENTNO <> PEN.PAYMENTNO)



Example 8.43: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    1 <> ALL

        (SELECT   TEAMNO

         FROM     MATCHES

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Example 8.44: 



SELECT   TEAMNO

FROM     TEAMS

WHERE    NOT EXISTS

        (SELECT   *

         FROM     MATCHES

         WHERE    PLAYERNO = 57 AND TEAMNO = TEAMS.TEAMNO)



Example 8.45: 



SELECT   PLAYERNO

FROM     PLAYERS AS P

WHERE    NOT EXISTS

        (SELECT   *

         FROM     TEAMS AS T

         WHERE    NOT EXISTS

                 (SELECT   *

                  FROM     MATCHES AS M

                  WHERE    T.TEAMNO = M.TEAMNO

                  AND      P.PLAYERNO = M.PLAYERNO))

;

SELECT   *

FROM     TEAMS AS T

WHERE    NOT EXISTS

        (SELECT   *

         FROM     MATCHES AS M

         WHERE    T.TEAMNO = M.TEAMNO

         AND      M.PLAYERNO = 27)



Example 8.46: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    NOT EXISTS

        (SELECT   *

         FROM     MATCHES AS M1

         WHERE    PLAYERNO = 57

         AND      NOT EXISTS

                 (SELECT   *

                  FROM     MATCHES AS M2

                  WHERE    M1.TEAMNO = M2.TEAMNO

                  AND      PLAYERS.PLAYERNO = M2.PLAYERNO))



Example 8.47: 



SELECT   PLAYERNO

FROM     MATCHES

WHERE    TEAMNO IN

        (SELECT   TEAMNO

         FROM     TEAMS

         WHERE    TEAMNO NOT IN

                 (SELECT   TEAMNO

                  FROM     MATCHES

                  WHERE    PLAYERNO = 57))

;

SELECT   PLAYERNO

FROM     PLAYERS AS P

WHERE    NOT EXISTS

        (SELECT   *

         FROM     MATCHES AS M1

         WHERE    PLAYERNO = 57

         AND      NOT EXISTS

                 (SELECT   *

                  FROM     MATCHES AS M2

                  WHERE    M1.TEAMNO = M2.TEAMNO

                  AND      P.PLAYERNO = M2.PLAYERNO))

AND      PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO IN

                 (SELECT   TEAMNO

                  FROM     TEAMS

                  WHERE    TEAMNO NOT IN

                          (SELECT   TEAMNO

                           FROM     MATCHES

                           WHERE    PLAYERNO = 57)))



Example 8.48: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    NOT (TOWN = 'Stratford')

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN <> 'Stratford'



Example 8.49: 



SELECT   PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT = 25

;

SELECT   PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT <> 25

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    AMOUNT = 25)

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    AMOUNT = 25)



Answer 8.1:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT > 60

;

SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    60 < AMOUNT

;

SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT - 60 > 0



Answer 8.2:



SELECT   TEAMNO

FROM     TEAMS

WHERE    PLAYERNO <> 27



Answer 8.4:



SELECT   DISTINCT PLAYERNO

FROM     MATCHES

WHERE    WON > LOST



Answer 8.5:



SELECT   DISTINCT PLAYERNO

FROM     MATCHES

WHERE    WON + LOST = 5



Answer 8.6:



SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    PAYMENTNO = 4)



Answer 8.7:



SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     TEAMS

         WHERE    TEAMNO =

                 (SELECT   TEAMNO

                  FROM     MATCHES

                  WHERE    MATCHNO = 2))



Answer 8.8:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    BIRTH_DATE =

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    NAME = 'Parmenter'

         AND      INITIALS = 'R')

AND      NOT (NAME = 'Parmenter'

              AND INITIALS = 'R')



Answer 8.9:



SELECT   MATCHNO

FROM     MATCHES

WHERE    WON =

        (SELECT   WON

         FROM     MATCHES

         WHERE    MATCHNO = 6)

AND      MATCHNO <> 6

AND      TEAMNO = 2



Answer 8.10:



SELECT   MATCHNO

FROM     MATCHES

WHERE    WON =

        (SELECT   WON

         FROM     MATCHES

         WHERE    MATCHNO = 2)

AND      LOST = 

        (SELECT   LOST

         FROM     MATCHES

         WHERE    MATCHNO = 8)



Answer 8.11:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    1965 <

        (SELECT   YEAR(BIRTH_DATE)

         FROM     PLAYERS

         WHERE    PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)



Answer 8.12:



SELECT   PAYMENTNO, PLAYERNO

FROM     PENALTIES

WHERE    PLAYERNO =

        (SELECT   PLAYERNO

         FROM     TEAMS

         WHERE    TEAMS.PLAYERNO = PENALTIES.PLAYERNO)



Answer 8.13:



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    SEX = 'F'

AND      TOWN <> 'Stratford'

;

SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    SEX = 'F'

AND      NOT (TOWN = 'Stratford')



Answer 8.14:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    JOINED >= 1970

AND      JOINED <= 1980

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    NOT (JOINED < 1970 OR JOINED > 1980)



Answer 8.15:



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    MOD(YEAR(BIRTH_DATE), 400) = 0

OR      (MOD(YEAR(BIRTH_DATE), 4) = 0

         AND NOT(MOD(YEAR(BIRTH_DATE), 100) = 0))



Answer 8.16:



SELECT   MATCHNO, NAME, INITIALS, DIVISION

FROM     MATCHES AS M, PLAYERS AS P, TEAMS AS T

WHERE    M.PLAYERNO = P.PLAYERNO

AND      M.TEAMNO = T.TEAMNO

AND      YEAR(BIRTH_DATE) > 1965

AND      WON > LOST



Answer 8.17:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT IN (50, 75, 100)



Answer 8.18:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN NOT IN ('Stratford', 'Douglas')

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    NOT (TOWN IN ('Stratford', 'Douglas'))

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN <> 'Stratford'

AND      TOWN <> 'Douglas'



Answer 8.19:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)



Answer 8.20:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    AMOUNT > 50)



Answer 8.21:



SELECT   TEAMNO, PLAYERNO

FROM     TEAMS

WHERE    DIVISION = 'first'

AND      PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')



Answer 8.22:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)

AND      PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     TEAMS

         WHERE    DIVISION = 'first')

;

SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    PLAYERNO NOT IN

                 (SELECT   PLAYERNO

                  FROM     TEAMS

                  WHERE    DIVISION = 'first'))



Answer 8.24:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT BETWEEN 50 AND 100



Answer 8.25:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    NOT (AMOUNT BETWEEN 50 AND 100)

;

SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT NOT BETWEEN 50 AND 100

;

SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT < 50

OR       AMOUNT > 100



Answer 8.26:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    JOINED BETWEEN YEAR(DATEADD(DAY, 1, DATEADD(YEAR, 16, BIRTH_DATE)))

         AND YEAR(DATEADD(DAY, 1, DATEADD(YEAR, 40, BIRTH_DATE)))



Answer 8.27:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    NAME LIKE '%is%'



Answer 8.28:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    RTRIM(NAME) LIKE '______'



Answer 8.29:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    RTRIM(NAME) LIKE '______%'



Answer 8.30:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    NAME LIKE '__l%l___'



Answer 8.31:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    LEAGUENO IS NULL



Answer 8.33:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     TEAMS

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Answer 8.34:



SELECT   NAME, INITIALS

FROM     PLAYERS AS P

WHERE    NOT EXISTS

        (SELECT   *

         FROM     TEAMS AS T

         WHERE    T.PLAYERNO = P.PLAYERNO

         AND      EXISTS

                 (SELECT   *

                  FROM     MATCHES AS M

                  WHERE    M.TEAMNO = T.TEAMNO

                  AND      M.PLAYERNO = 112))



Answer 8.35:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    BIRTH_DATE <= ALL

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')

AND      TOWN = 'Stratford'



Answer 8.36:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO = ANY

        (SELECT   PLAYERNO

         FROM     PENALTIES)



Answer 8.37:



SELECT   PAYMENTNO, AMOUNT, PAYMENT_DATE

FROM     PENALTIES AS PEN1

WHERE    AMOUNT >= ALL

        (SELECT   AMOUNT

         FROM     PENALTIES AS PEN2

         WHERE    YEAR(PEN1.PAYMENT_DATE) = YEAR(PEN2.PAYMENT_DATE))



Answer 8.38:



SELECT  (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    PLAYERNO <= ALL

                 (SELECT   PLAYERNO

                  FROM     PLAYERS)),

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    PLAYERNO >= ALL

                 (SELECT   PLAYERNO

                  FROM     PLAYERS))



Answer 8.40:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO IN

                 (SELECT   TEAMNO

                  FROM     TEAMS

                  WHERE    DIVISION = 'first'))

AND      PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    WON > LOST)

AND      PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)



Answer 8.41:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO = 1)

AND      PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO = 2)



Answer 8.42:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Answer 8.43:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT  PLAYERNO

         FROM    MATCHES AS M1

         WHERE   WON > LOST

         AND     EXISTS

                (SELECT  *

                 FROM    MATCHES AS M2

                 WHERE   M1.PLAYERNO = M2.PLAYERNO

                 AND     WON > LOST

                 AND     M1.MATCHNO <> M2.MATCHNO))

;

SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    1 < (SELECT   COUNT(*)

              FROM     MATCHES

              WHERE    WON > LOST

              AND      PLAYERS.PLAYERNO = PLAYERNO)



Answer 8.44:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    NOT EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERS.PLAYERNO = PLAYERNO

         AND      PAYMENT_DATE BETWEEN '1980-01-01' AND '1980-12-31')



Answer 8.45:



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES AS PEN1

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES AS PEN2

         WHERE    PEN1.AMOUNT = PEN2.AMOUNT

         AND      PEN1.PAYMENTNO <> PEN2.PAYMENTNO)



Answer 8.46:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     MATCHES WHERE WON = 3)



Answer 8.47:



SELECT   TEAMNO, DIVISION

FROM     TEAMS

WHERE    TEAMNO NOT IN

        (SELECT   TEAMNO

         FROM     MATCHES

         WHERE    PLAYERNO = 6)



Answer 8.48:



SELECT   DISTINCT PLAYERNO

FROM     MATCHES

WHERE    PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO IN

                 (SELECT   TEAMNO

                  FROM     MATCHES

                  WHERE    PLAYERNO = 57))



Example 9.1: 



SELECT   *

FROM     PENALTIES

;

SELECT   PAYMENTNO, PLAYERNO, PAYMENT_DATE, AMOUNT

FROM     PENALTIES

;

SELECT   PENALTIES.*

FROM     PENALTIES



Example 9.2: 



SELECT   PENALTIES.*

FROM     PENALTIES INNER JOIN TEAMS

         ON PENALTIES.PLAYERNO = TEAMS.PLAYERNO

;

SELECT   PENALTIES.PAYMENTNO, PENALTIES.PLAYERNO,

         PENALTIES.PAYMENT_DATE, PENALTIES.AMOUNT

FROM     PENALTIES INNER JOIN TEAMS

         ON PENALTIES.PLAYERNO = TEAMS.PLAYERNO

;

SELECT   PEN.*

FROM     PENALTIES AS PEN INNER JOIN TEAMS

         ON PEN.PLAYERNO = TEAMS.PLAYERNO



Example 9.3: 



SELECT   MATCHNO, 'Tally', WON - LOST, WON * 10

FROM     MATCHES



Example 9.4: 



SELECT   TOWN

FROM     PLAYERS

;

SELECT   DISTINCT TOWN

FROM     PLAYERS



Example 9.5: 



SELECT   STREET, TOWN

FROM     PLAYERS

;

SELECT   DISTINCT STREET, TOWN

FROM     PLAYERS

;

SELECT   TOWN

FROM     PLAYERS

;

SELECT   ALL TOWN

FROM     PLAYERS



Example 9.6: 



SELECT   DISTINCT LEAGUENO

FROM     PLAYERS



Example 9.7: 



SELECT   DISTINCT *

FROM    (SELECT   1 AS A, 'Hello' AS B, 4 AS C UNION

         SELECT   1, 'Hello', NULL UNION

         SELECT   1, 'Hello', NULL UNION

         SELECT   1, NULL, NULL) AS X



Example 9.8: 



SELECT   COUNT(*)

FROM     PLAYERS



Example 9.9: 



SELECT   COUNT(*)

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

;

SELECT   COUNT(*), PLAYERNO

FROM     PLAYERS

;

SELECT   'The number of players is', COUNT(*)

FROM     PLAYERS



Example 9.10: 



SELECT   COUNT(LEAGUENO)

FROM     PLAYERS

;

SELECT   COUNT(ALL LEAGUENO)

FROM     PLAYERS



Example 9.11: 



SELECT   COUNT(DISTINCT TOWN)

FROM     PLAYERS



Example 9.12: 



SELECT   COUNT(DISTINCT SUBSTRING(NAME, 1, 1)) AS CHARS

FROM     PLAYERS



Example 9.13: 



SELECT   COUNT(DISTINCT YEAR(PAYMENT_DATE)) AS YEARS

FROM     PENALTIES



Example 9.14: 



SELECT   COUNT(DISTINCT TOWN), COUNT(DISTINCT SEX)

FROM     PLAYERS



Example 9.15: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS AS P

WHERE   (SELECT   COUNT(*)

         FROM     PENALTIES AS PEN

         WHERE    P.PLAYERNO = PEN.PLAYERNO)

         >

        (SELECT   COUNT(*)

         FROM     MATCHES AS M

         WHERE    P.PLAYERNO = M.PLAYERNO)



Example 9.16: 



SELECT   PLAYERNO, NAME,

        (SELECT   COUNT(*)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)

         AS NUMBER

FROM     PLAYERS

WHERE   (SELECT   COUNT(*)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) >= 2

;

SELECT   PLAYERNO, NAME, NUMBER

FROM    (SELECT   PLAYERNO, NAME,

                 (SELECT   COUNT(*)

                  FROM     PENALTIES

                  WHERE    PENALTIES.PLAYERNO = 

                           PLAYERS.PLAYERNO)

                  AS NUMBER

         FROM     PLAYERS) AS PN

WHERE    NUMBER >= 2



Example 9.17: 



SELECT (SELECT   COUNT(*)

        FROM     PENALTIES) AS NUMBER_PENALTIES,

       (SELECT   COUNT(*)

        FROM     MATCHES) AS NUMBER_MATCHES



Example 9.18: 



SELECT   MAX(AMOUNT)

FROM     PENALTIES



Example 9.19: 



SELECT   MIN(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')



Example 9.20: 



SELECT   COUNT(*)

FROM     PENALTIES

WHERE    AMOUNT =

        (SELECT   MIN(AMOUNT)

         FROM     PENALTIES)



Example 9.21: 



SELECT   DISTINCT TEAMNO, PLAYERNO

FROM     MATCHES AS M1

WHERE    WON =

        (SELECT   MAX(WON)

         FROM     MATCHES AS M2

         WHERE    M1.TEAMNO = M2.TEAMNO)



Example 9.22: 



SELECT   (MAX(AMOUNT) - MIN(AMOUNT)) * 100

FROM     PENALTIES



Example 9.23: 



SELECT   SUBSTRING(MAX(NAME), 1, 1)

FROM     PLAYERS



Example 9.24: 



SELECT   MAX(LEAGUENO)

FROM     PLAYERS

WHERE    TOWN = 'Midhurst'



Example 9.25: 



SELECT   CASE WHEN MIN(LEAGUENO) IS NULL 

            THEN 'Unknown' 

            ELSE MIN(LEAGUENO)

         END

FROM     PLAYERS

WHERE    TOWN = 'Amsterdam'



Example 9.26: 



SELECT   PLAYERNO, AMOUNT, PAYMENT_DATE

FROM     PENALTIES AS PEN1

WHERE    AMOUNT =

        (SELECT   MAX(AMOUNT)

         FROM     PENALTIES AS PEN2

         WHERE    PEN2.PLAYERNO = PEN1.PLAYERNO)



Example 9.27: 



SELECT   PLAYERNO,

        (SELECT   MAX(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)

         AS HIGHESTPENALTY,

        (SELECT   MAX(WON)

         FROM     MATCHES

         WHERE    MATCHES.PLAYERNO = PLAYERS.PLAYERNO)

         AS NUMBEROFSETS

FROM     PLAYERS



Example 9.28: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE   (SELECT   MIN(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) =

        (SELECT   MAX(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)



Example 9.29: 



SELECT   SUM(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Inglewood')

;

SELECT   SUM(DISTINCT AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Inglewood')



Example 9.30: 



SELECT   AVG(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO = 44



Example 9.31: 



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES)



Example 9.32: 



SELECT   AVG(DISTINCT AMOUNT)

FROM     PENALTIES



Example 9.33: 



SELECT   AVG(LENGTH(RTRIM(NAME))), MAX(LENGTH(RTRIM(NAME)))

FROM     PLAYERS



Example 9.34: 



SELECT   PAYMENTNO, AMOUNT, 

         ABS(AMOUNT - (SELECT AVG(AMOUNT)

                       FROM   PENALTIES)) AS DIFFERENCE

FROM     PENALTIES AS P



Example 9.35: 



SELECT   MAX(PLAYERNO)

FROM     PLAYERS

;

SELECT   PLAYERNO, NAME

FROM     PLAYERS AS P1

WHERE    4 >

        (SELECT   COUNT(*)

         FROM     PLAYERS AS P2

         WHERE    P1.PLAYERNO < P2.PLAYERNO)

ORDER BY PLAYERNO DESC

;

SELECT   TOP 4 PLAYERNO, NAME

FROM     PLAYERS

ORDER BY PLAYERNO DESC



Example 9.36: 



SELECT   TOP 5 LEAGUENO, PLAYERNO, NAME

FROM     PLAYERS

ORDER BY LEAGUENO ASC



Example 9.37: 



SELECT   TOP 3 PLAYERNO, COUNT(*) AS NUMBER

FROM     MATCHES

WHERE    WON > LOST

GROUP BY PLAYERNO

ORDER BY NUMBER DESC



Example 9.38: 



SELECT   TOP 3 PLAYERNO, COUNT(*) AS NUMBER

FROM     MATCHES

WHERE    WON > LOST

GROUP BY PLAYERNO

ORDER BY NUMBER DESC, PLAYERNO DESC



Answer 9.4:



SELECT   COUNT(*), MAX(AMOUNT)

FROM     PENALTIES



Answer 9.5:



SELECT   COUNT(DISTINCT POSITION)

FROM     COMMITTEE_MEMBERS



Answer 9.6:



SELECT   COUNT(LEAGUENO)

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'



Answer 9.7:



SELECT   TEAMNO, DIVISION,

        (SELECT   COUNT(*)

         FROM     MATCHES

         WHERE    TEAMS.TEAMNO = MATCHES.TEAMNO)

FROM     TEAMS



Answer 9.8:



SELECT   PLAYERNO, NAME,

        (SELECT   COUNT(*)

         FROM     MATCHES

         WHERE    MATCHES.PLAYERNO = PLAYERS.PLAYERNO

         AND      WON > LOST)

FROM     PLAYERS



Answer 9.9:



SELECT 'Number of players' AS TABLES,

        (SELECT COUNT(*) FROM PLAYERS) AS NUMBERS

UNION

SELECT 'Number of teams',

        (SELECT COUNT(*) FROM TEAMS)

UNION

SELECT 'Number of matches',

        (SELECT COUNT(*) FROM MATCHES)



Answer 9.10:



SELECT   MIN(WON)

FROM     MATCHES

WHERE    WON > LOST



Answer 9.11:



SELECT   PLAYERNO,

        (SELECT   MAX(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO =

                  PLAYERS.PLAYERNO) -

        (SELECT   MIN(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO =

                  PLAYERS.PLAYERNO)

FROM     PLAYERS



Answer 9.12:



SELECT   PLAYERNO, BIRTH_DATE

FROM     PLAYERS

WHERE    YEAR(BIRTH_DATE) =

        (SELECT   MAX(YEAR(BIRTH_DATE))

         FROM     PLAYERS

         WHERE    PLAYERNO IN

                 (SELECT   PLAYERNO

                  FROM     MATCHES

                  WHERE    TEAMNO = 1))



Answer 9.14:



SELECT   AVG(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO = 1)



Answer 9.15:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE   (SELECT   SUM(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) > 100



Answer 9.16:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    WON >

                 (SELECT   SUM(WON)

                  FROM     MATCHES

                  WHERE    PLAYERNO = 27))



Answer 9.17:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE   (SELECT   SUM(WON)

         FROM     MATCHES

         WHERE    MATCHES.PLAYERNO = 

                  PLAYERS.PLAYERNO) = 8



Answer 9.18:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    LENGTH(RTRIM(NAME)) >

        (SELECT   AVG(LENGTH(RTRIM(NAME)))

         FROM     PLAYERS)



Answer 9.19:



SELECT   PLAYERNO,

        (SELECT   MAX(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO =

                  PLAYERS.PLAYERNO) -

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO =

                  PLAYERS.PLAYERNO)

FROM     PLAYERS



Answer 9.20:



SELECT   PLAYERNO,

         REPLICATE('*',

            (SELECT   AVG(AMOUNT)

             FROM     PENALTIES

             WHERE    PENALTIES.PLAYERNO =

                      PLAYERS.PLAYERNO)/10)

FROM     PLAYERS



Answer 9.21:



SELECT   TOP 4 PAYMENTNO, AMOUNT, PAYMENT_DATE

FROM     PENALTIES

ORDER BY PAYMENT_DATE DESC 



Answer 9.22:



SELECT   FIRST 2 MATCHNO

FROM     MATCHES

ORDER BY MATCHNO ASC 



Example 10.1: Get all the different town names from the PLAYERS table.



SELECT   TOWN

FROM     PLAYERS

GROUP BY TOWN



Example 10.2: 



SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN



Example 10.3: 



SELECT   TEAMNO, COUNT(*), SUM(WON)

FROM     MATCHES

GROUP BY TEAMNO



Example 10.4: 



SELECT   TEAMNO, COUNT(*)

FROM     MATCHES

WHERE    TEAMNO IN

        (SELECT   TEAMNO

         FROM     TEAMS INNER JOIN PLAYERS

                  ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO

         WHERE    TOWN = 'Eltham')

GROUP BY TEAMNO



Example 10.5: 



SELECT   AMOUNT, COUNT(*), SUM(AMOUNT)

FROM     PENALTIES

GROUP BY AMOUNT



Example 10.6: 



SELECT   TEAMNO, PLAYERNO

FROM     MATCHES

GROUP BY TEAMNO, PLAYERNO

;

SELECT   TEAMNO, PLAYERNO

FROM     MATCHES

GROUP BY PLAYERNO, TEAMNO

;

SELECT   TEAMNO, PLAYERNO, SUM(WON),

         COUNT(*), MIN(LOST)

FROM     MATCHES

GROUP BY TEAMNO, PLAYERNO



Example 10.7: 



SELECT   P.PLAYERNO, NAME, SUM(AMOUNT)

FROM     PLAYERS AS P INNER JOIN PENALTIES AS PEN

         ON P.PLAYERNO = PEN.PLAYERNO

GROUP BY P.PLAYERNO, NAME



Example 10.8: 



SELECT   YEAR(PAYMENT_DATE) AS YEAR, COUNT(*)

FROM     PENALTIES

GROUP BY YEAR(PAYMENT_DATE)



Example 10.9: 



SELECT   TRUNCATE(PLAYERNO/25,0) AS PLAYERS_GROUP, COUNT(*), MAX(PLAYERNO)

FROM     PLAYERS

GROUP BY TRUNCATE(PLAYERNO/25,0)



Example 10.10: 



SELECT   LEAGUENO

FROM     PLAYERS

GROUP BY LEAGUENO

;

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY SEX



Example 10.11: 



SELECT   CAST(AMOUNT * 100 AS INTEGER) AS AMOUNT_IN_CENTS

FROM     PENALTIES

GROUP BY AMOUNT



Example 10.12: 



SELECT   AVG(TOTAL)

FROM    (SELECT   PLAYERNO, SUM(AMOUNT) AS TOTAL

         FROM     PENALTIES

         GROUP BY PLAYERNO) AS TOTALS

WHERE    PLAYERNO IN 

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford' OR TOWN = 'Inglewood')



Example 10.13: 



SELECT   PLAYERS.PLAYERNO, NAME, NUMBER_OF_PENALTIES, NUMBER_OF_TEAMS

FROM     PLAYERS,

        (SELECT   PLAYERNO, COUNT(*) AS NUMBER_OF_PENALTIES

         FROM     PENALTIES

         GROUP BY PLAYERNO) AS NUMBER_PENALTIES,

        (SELECT   PLAYERNO, COUNT(*) AS NUMBER_OF_TEAMS

         FROM     TEAMS

         GROUP BY PLAYERNO) AS NUMBER_TEAMS

WHERE    PLAYERS.PLAYERNO = NUMBER_PENALTIES.PLAYERNO

AND      PLAYERS.PLAYERNO = NUMBER_TEAMS.PLAYERNO

;

SELECT   PLAYERS.PLAYERNO, NAME,

        (SELECT   COUNT(*) 

         FROM     PENALTIES

         WHERE    PLAYERS.PLAYERNO =

                  PENALTIES.PLAYERNO) AS NUMBER_OF_PENALTIES,

        (SELECT   COUNT(*)

         FROM     TEAMS

         WHERE    PLAYERS.PLAYERNO =

                  TEAMS.PLAYERNO) AS NUMBER_OF_TEAMS

FROM     PLAYERS



Example 10.14: 



SELECT   DISTINCT M.PLAYERNO, NUMBERP

FROM     MATCHES AS M LEFT OUTER JOIN

           (SELECT   PLAYERNO, COUNT(*) AS NUMBERP

            FROM     PENALTIES

            GROUP BY PLAYERNO) AS NP

         ON M.PLAYERNO = NP.PLAYERNO



Example 10.15: 



SELECT   GROUPS.PGROUP, SUM(P.AMOUNT)

FROM     PENALTIES AS P,

        (SELECT 1 AS PGROUP, '1980-01-01' AS STARTDATE, 

                '1981-06-30' AS ENDDATE

         UNION

         SELECT 2, '1981-07-01', '1982-12-31'

         UNION

         SELECT 3, '1983-01-01', '1984-12-31') AS GROUPS

WHERE    P.PAYMENT_DATE BETWEEN STARTDATE AND ENDDATE

GROUP BY GROUPS.PGROUP

ORDER BY GROUPS.PGROUP



Example 10.16: 



SELECT   CASE

            WHEN AMOUNT > 70.00 THEN 'SERIOUS'

            WHEN AMOUNT > 35.00 THEN 'REGRETTABLE'

            ELSE 'PETTY'

         END AS SERIOUSNESS,

         SUM(AMOUNT) AS TOTAL_PENALTY_AMOUNT

FROM     PENALTIES

GROUP BY CASE

            WHEN AMOUNT > 70.00 THEN 'SERIOUS'

            WHEN AMOUNT > 35.00 THEN 'REGRETTABLE'

            ELSE 'PETTY'

         END

ORDER BY TOTAL_PENALTY_AMOUNT



Example 10.17: 



SELECT   P1.PAYMENTNO, P1.AMOUNT, SUM(P2.AMOUNT)

FROM     PENALTIES AS P1, PENALTIES AS P2

WHERE    P1.PAYMENTNO >= P2. PAYMENTNO

GROUP BY P1. PAYMENTNO, P1.AMOUNT

ORDER BY P1. PAYMENTNO



Example 10.18: 



SELECT   P1.PAYMENTNO, P1.AMOUNT, (P1.AMOUNT * 100) / SUM(P2.AMOUNT)

FROM     PENALTIES AS P1, PENALTIES AS P2

GROUP BY P1.PAYMENTNO, P1.AMOUNT

ORDER BY P1.PAYMENTNO



Answer 10.1:



SELECT   JOINED

FROM     PLAYERS

GROUP BY JOINED



Answer 10.2:



SELECT   JOINED, COUNT(*)

FROM     PLAYERS

GROUP BY JOINED



Answer 10.3:



SELECT   PLAYERNO, AVG(AMOUNT), COUNT(*)

FROM     PENALTIES

GROUP BY PLAYERNO



Answer 10.4:



SELECT   TEAMNO, COUNT(*), SUM(WON)

FROM     MATCHES

WHERE    TEAMNO IN

        (SELECT   TEAMNO

         FROM     TEAMS

         WHERE    DIVISION = 'first')

GROUP BY TEAMNO



Answer 10.5:



SELECT   WON, LOST, COUNT(*)

FROM     MATCHES

WHERE    WON > LOST

GROUP BY WON, LOST

ORDER BY WON, LOST



Answer 10.6:



SELECT   P.TOWN, T.DIVISION, SUM(WON)

FROM    (MATCHES AS M INNER JOIN PLAYERS AS P

         ON M.PLAYERNO = P.PLAYERNO) 

         INNER JOIN TEAMS AS T

         ON M.TEAMNO = T.TEAMNO

GROUP BY P.TOWN, T.DIVISION

ORDER BY P.TOWN



Answer 10.7:



SELECT   NAME, INITIALS, COUNT(*)

FROM     PLAYERS AS P INNER JOIN PENALTIES AS PEN

         ON P.PLAYERNO = PEN.PLAYERNO

WHERE    P.TOWN = 'Inglewood'

GROUP BY P.PLAYERNO, NAME, INITIALS



Answer 10.8:



SELECT   T.TEAMNO, DIVISION, SUM(WON)

FROM     TEAMS AS T, MATCHES AS M

WHERE    T.TEAMNO = M.TEAMNO

GROUP BY T.TEAMNO, DIVISION



Answer 10.9:



SELECT   LENGTH(NAME), COUNT(*)

FROM     PLAYERS

GROUP BY LENGTH(NAME)



Answer 10.10:



SELECT   ABS(WON - LOST), COUNT(*)

FROM     MATCHES

GROUP BY ABS(WON – LOST)





Answer 10.11:



SELECT   YEAR(BEGIN_DATE), MONTH(BEGIN_DATE), COUNT(*)

FROM     COMMITTEE_MEMBERS

GROUP BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE)

ORDER BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE)



Answer 10.14:



SELECT   AVG(NUMBERS)

FROM    (SELECT   COUNT(*) AS NUMBERS

         FROM     PLAYERS

         GROUP BY TOWN) AS TOWNS



Answer 10.15;



SELECT   TEAMS.TEAMNO, DIVISION, NUMBER_PLAYERS

FROM     TEAMS LEFT OUTER JOIN

        (SELECT   TEAMNO, COUNT(*) AS NUMBER_PLAYERS

         FROM     MATCHES

         GROUP BY TEAMNO) AS M

         ON (TEAMS.TEAMNO = M.TEAMNO)



Answer 10.16:



SELECT   PLAYERS.PLAYERNO, NAME, SUM_AMOUNT, 

         NUMBER_TEAMS

FROM    (PLAYERS LEFT OUTER JOIN

        (SELECT   PLAYERNO, SUM(AMOUNT) AS SUM_AMOUNT

         FROM     PENALTIES

         GROUP BY PLAYERNO) AS TOTALS

         ON (PLAYERS.PLAYERNO = TOTALS.PLAYERNO))

            LEFT OUTER JOIN

           (SELECT   PLAYERNO, COUNT(*) AS NUMBER_TEAMS

            FROM     TEAMS

            WHERE    DIVISION = 'first'

            GROUP BY PLAYERNO) AS NUMBERS

            ON (PLAYERS.PLAYERNO = NUMBERS.PLAYERNO)



Answer 10.17:



SELECT   TEAMNO, COUNT(DISTINCT PLAYERNO)

FROM     MATCHES

WHERE    TEAMNO IN

        (SELECT   TEAMNO

         FROM     PLAYERS AS P INNER JOIN TEAMS AS T

                  ON P.PLAYERNO = T.PLAYERNO

         AND      TOWN = 'Stratford')

AND      WON > LOST

GROUP BY TEAMNO



Answer 10.18:



SELECT   PLAYERNO, NAME, JOINED - AVERAGE

FROM     PLAYERS,

        (SELECT   AVG(JOINED) AS AVERAGE

         FROM     PLAYERS) AS T



Answer 10.19:



SELECT   PLAYERNO, NAME, JOINED - AVERAGE

FROM     PLAYERS,

        (SELECT   TOWN, AVG(JOINED) AS AVERAGE

         FROM     PLAYERS

         GROUP BY TOWN) AS TOWNS

WHERE    PLAYERS.TOWN = TOWNS.TOWN



Example 11.1: 



SELECT   PLAYERNO

FROM     PENALTIES

GROUP BY PLAYERNO

HAVING   COUNT(*) > 1



Example 11.2: 



SELECT   PLAYERNO

FROM     PENALTIES

GROUP BY PLAYERNO

HAVING   MAX(YEAR(PAYMENT_DATE)) = 1984



Example 11.3: 



SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

GROUP BY PLAYERNO

HAVING   SUM(AMOUNT) > 150



Example 11.4: 



SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     TEAMS)

GROUP BY PLAYERNO

HAVING   SUM(AMOUNT) > 80



Example 11.5: 



SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

GROUP BY PLAYERNO

HAVING   SUM(AMOUNT) >= ALL

        (SELECT   SUM(AMOUNT)

         FROM     PENALTIES

         GROUP BY PLAYERNO)



Example 11.6: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         GROUP BY PLAYERNO

         HAVING   SUM(WON) >= ALL

                 (SELECT   SUM(WON)

                  FROM     MATCHES

                  GROUP BY PLAYERNO))

;

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN

HAVING   BIRTH_DATE > '1970-01-01'



Answer 11.1:



SELECT   TOWN

FROM     PLAYERS

GROUP BY TOWN

HAVING   COUNT(*) > 4



Answer 11.2:



SELECT   PLAYERNO

FROM     PENALTIES

GROUP BY PLAYERNO

HAVING   SUM(AMOUNT) > 150



Answer 11.3:



SELECT   NAME, INITIALS, COUNT(*)

FROM     PLAYERS INNER JOIN PENALTIES

         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

GROUP BY PLAYERS.PLAYERNO, NAME, INITIALS

HAVING   COUNT(*) > 1



Answer 11.4:



SELECT   TEAMNO, COUNT(*)

FROM     MATCHES

GROUP BY TEAMNO

HAVING   COUNT(*) >= ALL

        (SELECT   COUNT(*)

         FROM     MATCHES

         GROUP BY TEAMNO)



Answer 11.5:



SELECT   TEAMNO, DIVISION

FROM     TEAMS

WHERE    TEAMNO IN

        (SELECT   TEAMNO

         FROM     MATCHES

         GROUP BY TEAMNO

         HAVING   COUNT(DISTINCT PLAYERNO) > 4)



Answer 11.6:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    AMOUNT > 40

         GROUP BY PLAYERNO

         HAVING   COUNT(*) >= 2)



Answer 11.7:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         GROUP BY PLAYERNO

         HAVING   SUM(AMOUNT) >= ALL

                 (SELECT   SUM(AMOUNT)

                  FROM     PENALTIES

                  GROUP BY PLAYERNO))



Answer 11.8:



SELECT   PLAYERNO

FROM     PENALTIES

WHERE    PLAYERNO <> 104

GROUP BY PLAYERNO

HAVING   SUM(AMOUNT)  =

        (SELECT   SUM(AMOUNT) * 2

         FROM     PENALTIES

         WHERE    PLAYERNO = 104)



Answer 11.9:



SELECT   PLAYERNO

FROM     PENALTIES

WHERE    PLAYERNO <> 6

GROUP BY PLAYERNO

HAVING   COUNT(*) =

        (SELECT   COUNT(*)

         FROM     PENALTIES

         WHERE    PLAYERNO = 6)



Example 12.1: 



SELECT   PAYMENTNO, PLAYERNO

FROM     PENALTIES

ORDER BY PLAYERNO



Example 12.2: 



SELECT   PLAYERNO, AMOUNT

FROM     PENALTIES

ORDER BY PLAYERNO, AMOUNT



Example 12.3: 



SELECT   AMOUNT

FROM     PENALTIES

ORDER BY PLAYERNO, AMOUNT



Example 12.4: 



SELECT   NAME, INITIALS, PLAYERNO

FROM     PLAYERS

ORDER BY SUBSTRING(NAME, 1, 1)



Example 12.5: 



SELECT   PLAYERNO, AMOUNT

FROM     PENALTIES

ORDER BY ABS(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES))



Example 12.6: 



SELECT    PLAYERNO, AMOUNT

FROM      PENALTIES AS P1

ORDER BY (SELECT   AVG(AMOUNT) 

          FROM     PENALTIES AS P2

          WHERE    P1.PLAYERNO = P2.PLAYERNO)

;

SELECT   PAYMENTNO, PLAYERNO

FROM     PENALTIES

ORDER BY PLAYERNO

;

SELECT   PAYMENTNO, PLAYERNO

FROM     PENALTIES

ORDER BY 2



Example 12.7: 



SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

GROUP BY PLAYERNO

ORDER BY 2



Example 12.8: 



SELECT   PLAYERNO, NAME,

        (SELECT   SUM(AMOUNT) 

         FROM     PENALTIES AS PEN 

         WHERE    PEN.PLAYERNO = P.PLAYERNO)

FROM     PLAYERS AS P

ORDER BY 3

;

SELECT   PLAYERNO, NAME,

        (SELECT   SUM(AMOUNT) 

         FROM     PENALTIES AS PEN 

         WHERE    PEN.PLAYERNO = P.PLAYERNO) AS TOTAL

FROM     PLAYERS AS P

ORDER BY TOTAL



Example 12.9: 



SELECT   PLAYERNO, AMOUNT

FROM     PENALTIES

ORDER BY PLAYERNO DESC, AMOUNT ASC



Example 12.10: Create the following CODES table, add the six rows, and see how the different val-ues are sorted.



CREATE TABLE CODES (

   CODE   CHAR(4) NOT NULL)

;

INSERT INTO CODES VALUES ('abc')

;

INSERT INTO CODES VALUES ('ABC')

;

INSERT INTO CODES VALUES ('-abc')

;

INSERT INTO CODES VALUES ('a bc')

;

INSERT INTO CODES VALUES ('ab')

;

INSERT INTO CODES VALUES ('9abc')

;

SELECT   *

FROM     CODES

ORDER BY CODE



Example 12.11: 



SELECT   DISTINCT LEAGUENO

FROM     PLAYERS

ORDER BY LEAGUENO DESC



Example 12.12: 



CREATE TABLE TABLE_CASE (

   COL1   VARCHAR(15) NOT NULL)

;

INSERT INTO TABLE_CASE (COL1) VALUES ('database')

;

INSERT INTO TABLE_CASE (COL1) VALUES ('datawarehouse')

;

INSERT INTO TABLE_CASE (COL1) VALUES ('DATABASE')

;

INSERT INTO TABLE_CASE (COL1) VALUES ('DATAWAREHOUSE')

;

SELECT   *

FROM     TABLE_CASE

ORDER BY COL1 CASE ASC



Answer 12.3
:




SELECT   PLAYERNO, TEAMNO, WON - LOST

FROM     MATCHES

ORDER BY 3 ASC



Example 13.1: 



SELECT   PLAYERNO, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'

UNION

SELECT   PLAYERNO, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Plymouth'

;

SELECT   PLAYERNO, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'

OR       TOWN = 'Plymouth'



Example 13.2: 



SELECT   BIRTH_DATE AS DATES

FROM     PLAYERS

UNION

SELECT   PAYMENT_DATE

FROM     PENALTIES



Example 13.3: 



SELECT   PLAYERNO

FROM     PENALTIES

UNION

SELECT   PLAYERNO

FROM     TEAMS



Example 13.4: 



SELECT   PLAYERNO

FROM     PENALTIES

UNION

SELECT   PLAYERNO

FROM     TEAMS

UNION

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

;

SELECT   *

FROM     PLAYERS

UNION

SELECT   *

FROM     PENALTIES

;

SELECT   PLAYERNO, TOWN

FROM     PLAYERS

UNION

SELECT   PLAYERNO, PAYMENT_DATE

FROM     PENALTIES

;

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

ORDER BY PLAYERNO

UNION

SELECT   PLAYERNO

FROM     TEAMS

ORDER BY PLAYERNO



Example 13.5: 



SELECT   CAST(TEAMNO AS CHAR(4)) AS TEAMNO, CAST(PLAYERNO AS CHAR(4)) AS PLAYERNO,

         SUM(WON + LOST) AS TOTAL

FROM     MATCHES

GROUP BY TEAMNO, PLAYERNO

UNION

SELECT   CAST(TEAMNO AS CHAR(4)), 'subtotal', SUM(WON + LOST)

FROM     MATCHES

GROUP BY TEAMNO

UNION

SELECT   'total', 'total', SUM(WON + LOST)

FROM     MATCHES

ORDER BY TEAMNO, PLAYERNO



Example 13.6: 



SELECT   PLAYERNO

FROM     PENALTIES

UNION ALL

SELECT   PLAYERNO

FROM     TEAMS

;

SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    PLAYERNO = 27

UNION

SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    PLAYERNO = 27



Answer 13.1:



SELECT   PLAYERNO

FROM     COMMITTEE_MEMBERS

UNION

SELECT   PLAYERNO

FROM     PENALTIES

GROUP BY PLAYERNO

HAVING   COUNT(*) >= 2



Answer 13.2
:




SELECT   MAX(ADATE)

FROM    (SELECT   MAX(BIRTH_DATE) AS ADATE

         FROM     PLAYERS

         UNION

         SELECT   MAX(PAYMENT_DATE) AS ADATE

         FROM     PENALTIES) AS TWODATES



Answer 13.5:



SELECT   SUM(NUMBER)

FROM    (SELECT   COUNT(*) AS NUMBER

         FROM     PLAYERS

         UNION ALL

         SELECT   COUNT(*) AS NUMBER

         FROM     TEAMS) AS NUMBERS



Answer 13.6:



SELECT   POWER(DIGIT,2)

FROM    (SELECT 0 AS DIGIT UNION SELECT 1 UNION

         SELECT 2 UNION SELECT 3 UNION

         SELECT 4 UNION SELECT 5 UNION

         SELECT 6 UNION SELECT 7 UNION

         SELECT 8 UNION SELECT 9) AS DIGITS1

UNION ALL

SELECT   POWER(DIGIT,3)

FROM    (SELECT 0 AS DIGIT UNION SELECT 1 UNION

         SELECT 2 UNION SELECT 3 UNION

         SELECT 4 UNION SELECT 5 UNION

         SELECT 6 UNION SELECT 7 UNION

         SELECT 8 UNION SELECT 9) AS DIGITS2

ORDER BY 1



Example 14.1: 



INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION)

VALUES (3, 100, 'third')

;

INSERT INTO TEAMS

VALUES (3, 100, 'third')

;

INSERT INTO TEAMS (PLAYERNO, DIVISION, TEAMNO)

VALUES (100, 'third', 3)

;

INSERT INTO TEAMS

       (TEAMNO, DIVISION)

VALUES (3, 'third')



Example 14.2:



INSERT INTO PLAYERS

       (PLAYERNO, NAME, INITIALS, SEX, JOINED, STREET, TOWN)

VALUES (611, 'Jones', 'GG', 'M', 1977, 'Green Way', 'Stratford')

;

INSERT INTO PLAYERS

       (PLAYERNO, NAME, INITIALS, BIRTH_DATE, SEX, JOINED, STREET, HOUSENO, POSTCODE,

        TOWN, PHONENO, LEAGUENO)

VALUES (611, 'Jones', 'GG', NULL, 'M', 1977,

        'Green Way', NULL, NULL, 'Stratford', NULL, NULL)



Example 14.3: 



CREATE TABLE TABLE_DEFAULTS (

   COL1   INTEGER NOT NULL,

   COL2   INTEGER DEFAULT 100 NOT NULL)

;

INSERT INTO TABLE_DEFAULTS (COL1, COL2)

VALUES (1, DEFAULT)

;

SELECT * FROM TABLE_DEFAULTS

;

INSERT INTO TABLE_DEFAULTS (COL1) VALUES (1)



Example 14.4: 



CREATE TABLE RECR_PLAYERS (

   PLAYERNO      SMALLINT NOT NULL,

   NAME          CHAR(15) NOT NULL,

   TOWN          CHAR(10) NOT NULL,

   PHONENO       CHAR(13),

   PRIMARY KEY   (PLAYERNO))

;

INSERT   INTO RECR_PLAYERS

        (PLAYERNO, NAME, TOWN, PHONENO)

SELECT   PLAYERNO, NAME, TOWN, PHONENO

FROM     PLAYERS

WHERE    LEAGUENO IS NULL

;

INSERT   INTO RECR_PLAYERS

SELECT   PLAYERNO, NAME, TOWN, PHONENO

FROM     PLAYERS

WHERE    LEAGUENO IS NULL

;

INSERT   INTO RECR_PLAYERS

        (TOWN, PHONENO, NAME, PLAYERNO)

SELECT   TOWN, PHONENO, NAME, PLAYERNO

FROM     PLAYERS

WHERE    LEAGUENO IS NULL



Example 14.5: 



INSERT   INTO RECR_PLAYERS

        (PLAYERNO, NAME, TOWN, PHONENO)

SELECT   PLAYERNO + 1000, NAME, TOWN, PHONENO

FROM     RECR_PLAYERS



Example 14.6: 



INSERT   INTO PENALTIES

SELECT   PAYMENTNO + 100, PLAYERNO, PAYMENT_DATE, AMOUNT

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES)



Example 14.7: 



CREATE TABLE NUMBERS (

   NUMBER   INTEGER NOT NULL)

;

INSERT   INTO NUMBERS

SELECT   2 UNION ALL SELECT 1 UNION ALL SELECT 3

;

SELECT   * FROM NUMBERS

;

INSERT   INTO NUMBERS

SELECT   2 UNION ALL SELECT 1 UNION ALL SELECT 3

ORDER BY 1



Example 14.8: 



UPDATE   PLAYERS

SET      LEAGUENO = '2000'

WHERE    PLAYERNO = 95

;

UPDATE   PLAYERS AS P

SET      LEAGUENO = '2000'

WHERE    P.PLAYERNO = 95



Example 14.9: 



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT * 1.05



Example 14.10: 



UPDATE   MATCHES

SET      WON = 0

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')



Example 14.11: 



UPDATE   PLAYERS

SET      STREET   = 'Palmer Street',

         HOUSENO  = '83',

         TOWN     = 'Inglewood',

         POSTCODE = '1234UU',

         PHONENO  = NULL

WHERE    NAME     = 'Parmenter'



Example 14.12: 



UPDATE   PLAYERS

SET      STREET   = TOWN,

         TOWN     = STREET

WHERE    PLAYERNO = 44



Example 14.13: 



CREATE TABLE PLAYERS_DATA (

   PLAYERNO        INTEGER NOT NULL PRIMARY KEY,

   NUMBER_MAT      INTEGER,

   SUM_PENALTIES   DECIMAL(7,2))

;

INSERT INTO PLAYERS_DATA (PLAYERNO)

SELECT PLAYERNO FROM PLAYERS

;

UPDATE   PLAYERS_DATA AS PD

SET      NUMBER_MAT =

       (SELECT   COUNT(*)

        FROM     MATCHES AS M

        WHERE    M.PLAYERNO = PD.PLAYERNO),

        SUM_PENALTIES = 

       (SELECT   SUM(AMOUNT)

        FROM     PENALTIES AS PEN

        WHERE    PEN.PLAYERNO = PD.PLAYERNO)



Example 14.14: 



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + (SELECT   AVG(AMOUNT)

                            FROM     PENALTIES)



Example 14.15: 



CREATE TABLE TABle_DEFAULTS (

   COL1   INTEGER NOT NULL,

   COL2   INTEGER DEFAULT 100 NOT NULL)

;

INSERT INTO TABLE_DEFAULTS (COL1, COL2) VALUES (1, 50)

;

UPDATE   TABLE_DEFAULTS

SET      COL2 = DEFAULT

WHERE    COL1 = 1

;

SELECT * FROM TABLE_DEFAULTS



Example 14.16: 



CREATE TABLE TAB1 (

   COL1  INTEGER NOT NULL PRIMARY KEY,

   COL2  INTEGER,

   COL3  INTEGER)

;

INSERT INTO TAB1 VALUES (1,1,1)

;

INSERT INTO TAB1 VALUES (2,2,2)

;

INSERT INTO TAB1 VALUES (3,3,3)

;

CREATE TABLE TAB2 (

   COL1  INTEGER NOT NULL PRIMARY KEY,

   COL2  INTEGER,

   COL3  INTEGER)

;

INSERT INTO TAB2 VALUES (1,100,100)

;

UPDATE   TAB1

SET      COL2 =

        (SELECT   COL2

         FROM     TAB2

         WHERE    TAB2.COL1 = TAB1.COL1),

         COL3 =

        (SELECT   COL3

         FROM     TAB2

         WHERE    TAB2.COL1 = TAB1.COL1)

;

SELECT   *

FROM     TAB1

;

UPDATE   TAB1

SET      COL2 = TAB2.COL2,

         COL3 = TAB2.COL3

FROM     TAB2

WHERE    TAB1.COL1 = TAB2.COL1



Example 14.17: 



DELETE

FROM     PENALTIES

WHERE    PLAYERNO = 44

;

DELETE

FROM     PENALTIES AS PEN

WHERE    PEN.PLAYERNO = 44



Example 14.18: 



DELETE

FROM     MATCHES

WHERE    WON >

        (SELECT   AVG(WON)

         FROM     MATCHES

         WHERE    PLAYERNO = 83)



Answer 14.1
:




INSERT INTO PENALTIES

VALUES (15, 27, '1985-11-08', 75)



Answer 14.2:



INSERT   INTO PENALTIES

SELECT   PAYMENTNO + 1000, PLAYERNO, PAYMENT_DATE, AMOUNT

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES)

UNION

SELECT   PAYMENTNO + 2000, PLAYERNO, PAYMENT_DATE, AMOUNT

FROM     PENALTIES

WHERE    PLAYERNO = 27



Answer 14.3:



UPDATE   PLAYERS

SET      SEX = 'W'

WHERE    SEX = 'F'



Answer 14.4

UPDATE   PLAYERS

SET      SEX = 'X'

WHERE    SEX = 'F'

;

UPDATE   PLAYERS

SET      SEX = 'F'

WHERE    SEX = 'M'

;

UPDATE   PLAYERS

SET      SEX = 'M'

WHERE    SEX = 'X'

;

UPDATE   PLAYERS

SET      SEX = CASE SEX 

                  WHEN 'F' THEN 'M'

                  ELSE 'F' 

               END



Answer 14.5:



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT * 1.2

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES)



Answer 14.6:



DELETE

FROM     PENALTIES

WHERE    PLAYERNO = 44

AND      YEAR(PAYMENT_DATE) = 1980



Answer 14.7:



DELETE

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO IN

                 (SELECT   TEAMNO

                  FROM     TEAMS

                  WHERE    DIVISION = 'second'))



Answer 14.8:



DELETE

FROM     PLAYERS

WHERE    TOWN =

        (SELECT   TOWN

         FROM     PLAYERS

         WHERE    PLAYERNO = 28)

AND      PLAYERNO <> 28



Example 15.1: 



CREATE DATABASE TENNIS2

   DICTIONARY_PATH 'C:\TENNIS2'



Example 15.2: 



CREATE DATABASE TENNIS3

   DICTIONARY_PATH 'C:\TENNIS3_CAT'

   DATA_PATH 'C:\TENNIS3_DATA'



Example 15.3: 



CREATE DATABASE TENNIS4

   DICTIONARY_PATH 'C:\TENNIS4_CAT'

   DATA_PATH 'C:\TENNIS4A_DATA; C:\TENNIS4B_DATA'



Example 15.4: Create a new database called TENNIS5. Store the user tables in the new directory C:\TENNIS5_DATA, and the catalog tables in the existing directory C:\TENNIS3_CAT.



CREATE DATABASE TENNIS5

   DICTIONARY_PATH 'C:\TENNIS3_CAT'

   DATA_PATH 'C:\TENNIS5_DATA'

   REUSE_DDF

;

SET SECURITY = ON

;

SET SECURITY = OFF



Example 15.5: 



DROP DATABASE TENNIS2



Example 15.6: 



DROP DATABASE TENNIS3 WITH FILES



Example 16.1: 



CREATE TABLE PLAYERS (

   PLAYERNO     INTEGER NOT NULL PRIMARY KEY,

   NAME         CHAR(15) NOT NULL,

   INITIALS     CHAR(3) NOT NULL,

   BIRTH_DATE   DATE,

   SEX          CHAR(1) NOT NULL,

   JOINED       SMALLINT NOT NULL,

   STREET       VARCHAR(30) NOT NULL,

   HOUSENO      CHAR(4),

   POSTCODE     CHAR(6),

   TOWN         VARCHAR(30) NOT NULL,

   PHONENO      CHAR(13),

   LEAGUENO     CHAR(4))



Example 16.2: 



SET DECIMALSEPARATORCOMMA = ON

;

SET DECIMALSEPARATORCOMMA = OFF



Example 16.3: 



CREATE TABLE MEASUREMENTS (

   NR                 INTEGER,

   MEASUREMENT_VALUE  REAL)

;

INSERT INTO MEASUREMENTS VALUES (1, 99.99)

;

INSERT INTO MEASUREMENTS VALUES (2, 99999.99)

;

INSERT INTO MEASUREMENTS VALUES (3, 99999999.99)

;

INSERT INTO MEASUREMENTS VALUES (4, 99999999999.99)

;

INSERT INTO MEASUREMENTS VALUES (5, 99999999999999.99)

;

INSERT INTO MEASUREMENTS VALUES (6, 0.999999)

;

INSERT INTO MEASUREMENTS VALUES (7, 0.9999999)

;

INSERT INTO MEASUREMENTS VALUES (8, 99999999.9999)

;

INSERT INTO MEASUREMENTS VALUES (9, (1.0/3))

;

SELECT * FROM MEASUREMENTS



Example 16.4: 



CREATE TABLE MANY_IDENTIFIERS (

   COL1  SMALLIDENTITY,

   COL2  IDENTITY,

   COL3  UNIQUEIDENTIFIER)

;

INSERT INTO MANY_IDENTIFIERS (COL3) VALUES (NEWID())

;

SELECT * FROM MANY_IDENTIFIERS



Example 16.5: 



CREATE TABLE #SUMPENALTIES (

   TOTAL   DECIMAL(10,2))

;

INSERT INTO #SUMPENALTIES

SELECT SUM(AMOUNT)

FROM   PENALTIES



Example 16.6: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL PRIMARY KEY,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE DEFAULT '2007-01-01' NOT NULL,

   AMOUNT         DECIMAL(7,2) DEFAULT 50.00 NOT NULL)

;

INSERT   INTO PENALTIES

        (PAYMENTNO, PLAYERNO)

VALUES  (15, 27)



Example 16.7: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL PRIMARY KEY,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE DEFAULT CURRENT_DATE() NOT NULL,

   AMOUNT         DECIMAL(7,2) DEFAULT 50.00 NOT NULL)

;

INSERT   INTO PENALTIES

        (PAYMENTNO, PLAYERNO, PAYMENT_DATE, AMOUNT)

VALUES  (15, 27, DEFAULT, DEFAULT)



Example 16.8: 



UPDATE   PENALTIES

SET      AMOUNT = DEFAULT



Example 16.9: 



SELECT   UPPER(TAB.XF$NAME) AS TABLE_NAME, UPPER(COL.XE$NAME) AS COLUMN_NAME,

        (SELECT   A.XA$ATTRS

         FROM     X$ATTRIB AS A

         WHERE    A.XA$TYPE='D'

         AND      A.XA$ID = COL.XE$ID) AS DEFAULT_VALUE

FROM     X$FIELD AS COL, X$FILE AS TAB

WHERE    COL.XE$FILE = TAB.XF$ID

AND      TAB.XF$FLAGS & 16 <> 16

AND      COL.XE$DATATYPE NOT IN (227,255)

AND      UPPER(TAB.XF$NAME) = 'PENALTIES2'



Example 16.10: 



CREATE TABLE DIGITS (DIGIT TINYINT);

;

INSERT INTO DIGITS

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION

SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION

SELECT 8 UNION SELECT 9

;

CREATE TABLE NUMBERS (

   NUMBER   INTEGER NOT NULL PRIMARY KEY,

   TEXT     VARCHAR(100))

;

INSERT   INTO NUMBERS

SELECT   D1.DIGIT*10000 + D2.DIGIT*1000 + D3.DIGIT*100 +

         D4.DIGIT*10 + D5.DIGIT, REPLICATE('X',100)

FROM     DIGITS AS D1, DIGITS AS D2, DIGITS AS D3,

         DIGITS AS D4, DIGITS AS D5

;

ALTER TABLE NUMBERS

   PCOMPRESS



Example 16.11: 



CREATE TABLE TUSING1 USING 'ABCD.EFG' (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER)



Example 16.12: 



CREATE TABLE TUSING2 USING '\TENNIS\TUSING2.MKD' (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER)



Example 16.13: 



CREATE TABLE TUSING3 USING '.\TUSING3.MKD' (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER)



Example 16.14: 



SELECT   COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, NULLABLE

FROM     COLUMNS

WHERE    TABLE_NAME = 'PLAYERS'

ORDER BY COLUMN_NO



Example 16.15: 



SELECT   'PLAYERS' AS TABLE_NAME, COUNT(*) AS NUMBER_OF_ROWS,

        (SELECT   COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'PLAYERS') AS P

FROM     PLAYERS

UNION

SELECT   'TEAMS', COUNT(*),

        (SELECT   COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'TEAMS') AS T

FROM     TEAMS

UNION

SELECT   'PENALTIES', COUNT(*),

        (SELECT   COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'PENALTIES') AS PEN

FROM     PENALTIES

UNION

SELECT   'MATCHES', COUNT(*),

        (SELECT   COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'MATCHES') AS M

FROM     MATCHES

UNION      

SELECT   'COMMITTEE_MEMBERS', COUNT(*),

        (SELECT   COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'COMMITTEE_MEMBERS') AS CM

FROM     COMMITTEE_MEMBERS

ORDER BY TABLE_NAME



Answer 16.6
:




CREATE TABLE DEPARTMENT (

   DEPNO      CHAR(5) NOT NULL PRIMARY KEY,

   BUDGET     DECIMAL(8,2),

   LOCATION   VARCHAR(30))



Example 17.1: 



CREATE TABLE PLAYERS (

   PLAYERNO     INTEGER NOT NULL PRIMARY KEY,

   NAME         CHAR(15) NOT NULL,

   INITIALS     CHAR(3) NOT NULL,

   BIRTH_DATE   DATE,

   SEX          CHAR(1) NOT NULL,

   JOINED       SMALLINT NOT NULL,

   STREET       VARCHAR(30) NOT NULL,

   HOUSENO      CHAR(4),

   POSTCODE     CHAR(6),

   TOWN         VARCHAR(30) NOT NULL,

   PHONENO      CHAR(13),

   LEAGUENO     CHAR(4))

;

CREATE TABLE PLAYERS (

   PLAYERNO     INTEGER NOT NULL,

   NAME         CHAR(15) NOT NULL,

   INITIALS     CHAR(3) NOT NULL,

   BIRTH_DATE   DATE,

   SEX          CHAR(1) NOT NULL,

   JOINED       SMALLINT NOT NULL,

   STREET       VARCHAR(30) NOT NULL,

   HOUSENO      CHAR(4),

   POSTCODE     CHAR(6),

   TOWN         VARCHAR(30) NOT NULL,

   PHONENO      CHAR(13),

   LEAGUENO     CHAR(4),

   PRIMARY KEY  (PLAYERNO))



Example 17.2: 



CREATE TABLE DIPLOMAS (

   STUDENT       INTEGER NOT NULL,

   COURSE        INTEGER NOT NULL,

   DIP_DATE      DATE NOT NULL,

   SUCCESSFUL    CHAR(1),

   LOCATION      VARCHAR(50),

   PRIMARY KEY   (STUDENT, COURSE, DIP_DATE))



Example 17.3: 



CREATE TABLE TEAMS (

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL UNIQUE,

   DIVISION      CHAR(6) NOT NULL,

   PRIMARY KEY   (TEAMNO))

;

CREATE TABLE TEAMS (

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   DIVISION      CHAR(6) NOT NULL,

   PRIMARY KEY   (TEAMNO),

   UNIQUE        (PLAYERNO))



Example 17.4: 



CREATE TABLE PLAYERS (

   PLAYERNO      INTEGER NOT NULL,

   NAME          CHAR(15) NOT NULL,

   INITIALS      CHAR(3) NOT NULL,

   BIRTH_DATE    DATE,

   SEX           CHAR(1) NOT NULL,

   JOINED        SMALLINT NOT NULL,

   STREET        VARCHAR(30) NOT NULL,

   HOUSENO       CHAR(4),

   POSTCODE      CHAR(6),

   TOWN          VARCHAR(30) NOT NULL,

   PHONENO       CHAR(13),

   LEAGUENO      CHAR(4) UNIQUE,

   PRIMARY KEY   (PLAYERNO))



Example 17.5: 



CREATE TABLE TEAMS (

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   DIVISION      CHAR(6) NOT NULL,

   PRIMARY KEY   (TEAMNO),

   FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))

;

CREATE TABLE TEAMS (

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   DIVISION      CHAR(6) NOT NULL,

   PRIMARY KEY   (TEAMNO),

   FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS)

;

SELECT   *

FROM     TEAMS

WHERE    PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     PLAYERS)

Example 17.6: Create the TEAMS table, including all relevant primary and foreign keys.



CREATE TABLE TEAMS (

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   DIVISION      CHAR(6) NOT NULL,

   PRIMARY KEY   (TEAMNO),

   FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))



Example 17.7: 



CREATE TABLE MATCHES (

   MATCHNO       INTEGER NOT NULL,

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   WON           INTEGER NOT NULL,

   LOST          INTEGER NOT NULL,

   PRIMARY KEY   (MATCHNO),

   FOREIGN KEY   (TEAMNO) REFERENCES TEAMS (TEAMNO),

   FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))



Example 17.8: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE NOT NULL,

   AMOUNT         DECIMAL(7,2) NOT NULL,

   PRIMARY KEY    (PAYMENTNO),

   FOREIGN KEY    (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))

;

CREATE TABLE EMPLOYEES (

   EMPLOYEE_NO   CHAR(10) NOT NULL,

   MANAGER_NO    CHAR(10),

   PRIMARY KEY   (EMPLOYEE_NO),

   FOREIGN KEY   (MANAGER_NO) REFERENCES EMPLOYEES (EMPLOYEE_NO))



Example 17.9: 



CREATE TABLE PLAYERS_WITH_PASS (

   PLAYERNO          INTEGER NOT NULL PRIMARY KEY,

   PASSPORTNO        CHAR(10) NOT NULL UNIQUE,

   NAME              CHAR(15) NOT NULL,

   INITIALS          CHAR(3) NOT NULL,

   BIRTH_DATE        DATE,

   SEX               CHAR(1) NOT NULL,

   JOINED            SMALLINT NOT NULL,     

   STREET            VARCHAR(30) NOT NULL,

   HOUSENO           CHAR(4),

   POSTCODE          CHAR(6),

   TOWN              VARCHAR(30) NOT NULL,

   PHONENO           CHAR(13),

   LEAGUENO          CHAR(4))

;

CREATE TABLE PASSPORT_VISA (

   PASSPORTNO        CHAR(10) NOT NULL,

   VISA_CODE         CHAR(10) NOT NULL,

   EXPIRATION_DATE   DATE NOT NULL,

   PRIMARY KEY       (PASSPORTNO, VISA_CODE),

   FOREIGN KEY       (PASSPORTNO) REFERENCES PLAYERS_WITH_PASS (PASSPORTNO))



Example 17.10: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE NOT NULL,

   AMOUNT         DECIMAL(7,2) NOT NULL,

   PRIMARY KEY    (PAYMENTNO),

   FOREIGN KEY    (PLAYERNO) REFERENCES PLAYERS (PLAYERNO)

                  ON UPDATE  RESTRICT

                  ON DELETE  RESTRICT)



Example 17.11: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE NOT NULL,

   AMOUNT         DECIMAL(7,2) NOT NULL,

   PRIMARY KEY    (PAYMENTNO),

   FOREIGN KEY    (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) ON DELETE CASCADE)

;

DELETE

FROM      PLAYERS

WHERE     PLAYERNO = 127

;

DELETE

FROM      PENALTIES

WHERE     PLAYERNO = 127



Example 17.12: 



CREATE TABLE DIPLOMAS (

   STUDENT      INTEGER NOT NULL,

   COURSE       INTEGER NOT NULL,

   DDATE        DATE NOT NULL,

   SUCCESSFUL   CHAR(1),

   LOCATION     VARCHAR(50),

   CONSTRAINT   PRIMARY_KEY_DIPLOMAS PRIMARY KEY (STUDENT, COURSE, DDATE))



Example 17.13: 



CREATE TABLE TEST1 (

   COL1   INTEGER NOT NULL PRIMARY KEY NOT MODIFIABLE,

   COL2   INTEGER)

;

INSERT INTO TEST1 VALUES (1, 2)

;

UPDATE TEST1

SET    COL1 = 100

;

SELECT   FK.XR$NAME, PK_TAB.XF$NAME, FK_TAB.XF$NAME,

         CASE FK.XR$UPDATERULE

            WHEN 1 THEN 'RESTRICT'

            ELSE 'UNKNOWN'

         END AS UPDATERULE,

         CASE FK.XR$DELETERULE

            WHEN 1 THEN 'RESTRICT'

            WHEN 2 THEN 'CASCADE'

            ELSE 'UNKNOWN'

         END AS DELETERULE

FROM     X$RELATE AS FK, X$FILE AS PK_TAB, X$FILE AS FK_TAB

WHERE    FK.XR$PID = PK_TAB.XF$ID

AND      FK.XR$FID = FK_TAB.XF$ID



Answer 17.3
:




CREATE TABLE MATCHES (

   MATCHNO       INTEGER NOT NULL,

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   WON           INTEGER NOT NULL,

   LOST          INTEGER NOT NULL,

   PRIMARY KEY   (MATCHNO))

;

CREATE TABLE MATCHES (

   MATCHNO    INTEGER NOT NULL PRIMARY KEY,

   TEAMNO     INTEGER NOT NULL,

   PLAYERNO   INTEGER NOT NULL,

   WON        INTEGER NOT NULL,

   LOST       INTEGER NOT NULL)



Example 18.1: 



CREATE DATABASE DBCP1

   DICTIONARY_PATH 'C:\DBCP1'

   ENCODING 'CP1254'



Example 18.2: 



CREATE DATABASE DBCP2

   DICTIONARY_PATH 'C:\DBCP2'

   ENCODING DEFAULT



Example 18.3: 



CREATE TABLE T1 (

   COL1   CHAR(10),

   COL2   CHAR(10) COLLATE 

   'C:\PROGRAMDATA\PERVASIVE SOFTWARE\PSQL\SAMPLES\UPPER.ALT')

;

INSERT INTO T1 VALUES ('a', 'a')

;

INSERT INTO T1 VALUES ('A', 'A')



Example 18.4: 



SET DEFAULTCOLLATE = 'C:\PROGRAMDATA\PERVASIVE SOFTWARE\PSQL\SAMPLES\UPPER.ALT'



Example 18.5: 



SET DEFAULTCOLLATE = NULL



Example 19.1: 



DROP TABLE PLAYERS



Example 19.2: 



ALTER TABLE RENAME PLAYERS TO TENNIS_PLAYERS

;

CALL PSP_RENAME('PLAYERS', 'TENNIS_PLAYERS', 'TABLE')



Example 19.3: 



ALTER TABLE PLAYERS

   PAGESIZE = 512



Example 19.4: 



ALTER TABLE PLAYERS

   DCOMPRESS



Example 19.5: 



ALTER TABLE TUSING2

   USING 'TUSING2X.MKD'



Example 19.6: 



ALTER TABLE TUSING2

   USING '.\TEST\TUSING2.MKD'



Example 19.7: 



ALTER TABLE TEAMS

   ADD COLUMN TYPE CHAR(1)



Example 19.8: 



ALTER TABLE TEAMS (

   ADD CATEGORY  VARCHAR(20) NOT NULL,

   ADD IMAGE     INTEGER DEFAULT 10)



Example 19.9: 



ALTER TABLE MATCHES

   ( PSQL_MOVE COLUMN PLAYERNO TO 5 )

;

SELECT   *

FROM     MATCHES

WHERE    MATCHNO = 1



Example 19.10: 



ALTER TABLE TEAMS

   ( PSQL_MOVE COLUMN PLAYERNO TO PSQL_PHYSICAL 3 )

;

SELECT   *

FROM     TEAMS



Example 19.11: 



SELECT   UPPER(TAB.XF$NAME) AS TABLE_NAME, UPPER(COL.XE$NAME) AS COLUMN_NAME,

        (SELECT COUNT(*)

         FROM   X$FIELD AS F

         WHERE  F.XE$FILE = TAB.XF$ID

         AND    F.XE$OFFSET <= COL.XE$OFFSET

         AND    F.XE$DATATYPE NOT IN (227,255)) AS PHYSICAL_ORDER,

         IFNULL ((SELECT A.XA$ATTRS

              FROM   X$ATTRIB AS A

              WHERE  A.XA$TYPE='L'

              AND    A.XA$ID = COL.XE$ID),

             (SELECT COUNT(*)

              FROM   X$FIELD AS F

              WHERE  F.XE$FILE = TAB.XF$ID

              AND    F.XE$OFFSET <= COL.XE$OFFSET

              AND    F.XE$DATATYPE NOT IN (227,255))) AS LOGICAL_ORDER

FROM     X$FIELD AS COL, X$FILE AS TAB

WHERE    COL.XE$FILE = TAB.XF$ID

AND      TAB.XF$FLAGS & 16 <> 16

AND      COL.XE$DATATYPE NOT IN (227,255)

AND      UPPER(TAB.XF$NAME) = 'MATCHES'



Example 19.12: 



ALTER TABLE SPELERS

   RENAME COLUMN BIRTH_DATE TO DATE_OF_BIRTH



Example 19.13: 



ALTER TABLE PLAYERS

   ALTER COLUMN TOWN VARCHAR(40) NOT NULL



Example 19.14: 



ALTER TABLE PLAYERS

   ALTER COLUMN TOWN VARCHAR(10)



Example 19.15: 



ALTER TABLE MATCHES

   ALTER COLUMN WON SMALLINT



Example 19.16: 



CREATE TABLE NUMBERS (COL1 CHAR(1))

;

INSERT INTO NUMBERS VALUES ('1')

;

INSERT INTO NUMBERS VALUES ('2')

;

ALTER TABLE NUMBERS

   ALTER COLUMN COL1 SMALLINT



Example 19.17: 



ALTER TABLE COMMITTEE_MEMBERS

   ALTER COLUMN POSITION CHAR(20) DEFAULT 'Member'



Example 19.18: 



ALTER TABLE PLAYERS

   ALTER TOWN VARCHAR(30) NULL



Example 19.19: 



ALTER TABLE TEAMS

   DROP COLUMN TYPE



Example 19.20: 



CREATE TABLE PRIMKEY (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER)

;

CREATE TABLE FORKEY (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER REFERENCES PRIMKEY)

;

ALTER TABLE PRIMKEY

   DROP COLUMN COL1 RESTRICT



Example 19.21: 



CREATE TABLE T1 (

   COL1   INTEGER NOT NULL,

   COL2   INTEGER NOT NULL)

; 

CREATE TABLE T2 (

   COL1  INTEGER NOT NULL,

   COL2   INTEGER NOT NULL)

;

ALTER TABLE T1

   ADD CONSTRAINT T1_PK1 PRIMARY KEY (COL1)

;

ALTER TABLE T2 (

   ADD CONSTRAINT T2_PK1 PRIMARY KEY (COL1),

   ADD CONSTRAINT T2_FK1 FOREIGN KEY (COL2) REFERENCES T1 (COL1))



Example 19.22: 



ALTER TABLE PENALTIES

   DROP PRIMARY KEY



Example 19.23: 



CREATE TABLE T1 (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER NOT NULL)

;

CREATE TABLE T2 (

   COL1   INTEGER NOT NULL CONSTRAINT PKEY PRIMARY KEY,

   COL2   INTEGER NOT NULL,

   CONSTRAINT FK1 FOREIGN KEY (COL2) REFERENCES T1 (COL1))

;

ALTER TABLE T2

   DROP CONSTRAINT FK1



Example 19.24: 



ALTER TABLE T2

   DROP CONSTRAINT PKEY



Example 19.25: 



CREATE TABLE DECOUPLED_TABLE IN DICTIONARY (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   VARCHAR(100))



Example 19.26: 



CREATE TABLE COUPLED_TABLE (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   VARCHAR(100))

;

INSERT INTO COUPLED_TABLE VALUES (1, 'X')

;

DROP TABLE COUPLED_TABLE IN DICTIONARY



Example 19.27: 



CREATE TABLE TEST1 (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   VARCHAR(100))

;

INSERT INTO TEST1 VALUES (1, 'X')

;

ALTER TABLE TEST1 USING 'TEST1_COPY.MKD'

;

SELECT * FROM TEST1



Example 19.28: 



INSERT INTO TEST1 VALUES (2, 'Y')

;

ALTER TABLE TEST1 IN DICTIONARY USING 'TEST1.MKD'

;

SELECT * FROM TEST1



Example 19.29: 



ALTER TABLE TEST1 IN DICTIONARY USING 'TEST1_COPY.MKD'

;

SELECT * FROM TEST1



Example 19.30: 



CREATE TABLE TEST2 USING 'SUB\TEST2.MKD' (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   VARCHAR(100))



Answer 19.1
:




ALTER TABLE COMMITTEE_MEMBERS

   RENAME COLUMN POSITION TO COMMITTEE_POSITION



Answer 19.2
:




ALTER TABLE COMMITTEE_MEMBERS

   ALTER COMMITTEE_POSITION CHAR(30)



Answer 19.3:



ALTER TABLE PLAYERS

   ALTER TOWN VARCHAR(30) DEFAULT 'Stratford'



Example 20.4: 



SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO = 44



Example 20.5: 



SELECT   PLAYERNO, TOWN

FROM     PLAYERS

WHERE    PLAYERNO < 10

AND      TOWN = 'Stratford'

ORDER BY PLAYERNO



Example 20.6: 



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    TOWN =

        (SELECT   TOWN

         FROM     PLAYERS

         WHERE    PLAYERNO = 44)



Example 20.7: 



CREATE   INDEX PLAY_PC

ON       PLAYERS (POSTCODE ASC)



Example 20.8: 



CREATE   INDEX MAT_WL

ON       MATCHES (WON, LOST)



Example 20.9: 



CREATE   UNIQUE INDEX NAMEINIT

ON       PLAYERS (NAME, INITIALS)



Example 20.10: 



ALTER TABLE TEAMS

   ADD INDEX TEAMS_DIVISION USING BTREE (DIVISION)



Example 20.11: 



CREATE INDEX INDEXDATE ON COMMITTEE_MEMBERS (BEGIN_DATE)

;

CREATE INDEX INDEXDATE ON PENALTIES (PAYMENT_DATE)



Example 20.12: 



CREATE TABLE WIDE (COL1 CHAR(1000))

;

CREATE PARTIAL INDEX INDEX_WIDE ON WIDE (COL1)



Example 20.13: 



CREATE TABLE TNOTMODIFIABLE (

   COL1   INTEGER NOT NULL,

   COL2   INTEGER NOT NULL)

;

CREATE NOT MODIFIABLE INDEX NONMOD_INDEX ON TNOTMODIFIABLE (COL2)

;

SELECT   INDEX_NAME, TABLE_NAME, MODIFIABILITY

FROM     INDEXES 

WHERE    INDEX_NAME='NONMOD_INDEX'

;

UPDATE   TNONMODIFIABLE

SET      COL2 = 100



Example 20.14: 



CREATE INDEX INDEX_STREET IN DICTIONARY ON PLAYERS (STREET)



Example 20.15: 



CREATE TABLE T1 LINKDUP = 2 (

   COL1   INTEGER NOT NULL,

   COL2   INTEGER NOT NULL,

   COL3   INTEGER NOT NULL)

;

CREATE INDEX T1_INDEX1 ON T1 (COL1)

;

CREATE INDEX T1_INDEX2 ON T1 (COL2)

;

CREATE INDEX T1_INDEX3 ON T1 (COL3)

;

SELECT   INDEX_NAME, INDEX_TYPE

FROM     INDEXES

WHERE    TABLE_NAME = 'T1'

;

DROP INDEX T1_INDEX2

;

CREATE INDEX T1_INDEX4 ON T1 (COL1, COL2)

;

SELECT   INDEX_NAME, INDEX_TYPE

FROM     INDEXES

WHERE    TABLE_NAME = 'T1'



Example 20.16: 



DROP INDEX PLAY_PC

;

DROP INDEX MAT_WL

;

DROP INDEX NAMEINIT



Example 20.17: 



DROP INDEX PENALTIES.INDEXDATE

;

DROP INDEX PLAYERS.PLAY_PC



Example 20.18: 



CREATE INDEX MATCHES_WON ON MATCHES (WON)

;

DROP INDEX MATCHES_WON IN DICTIONARY

;

CREATE INDEX MATWON ON MATCHES (WON)



Example 20.19: 



CREATE TABLE T1 (

   COL1   INTEGER NOT NULL,

   COL2   DATE NOT NULL UNIQUE,

   COL3   INTEGER NOT NULL,

   COL4   INTEGER NOT NULL,

   PRIMARY KEY (COL1, COL4),

   UNIQUE (COL3, COL4),

   UNIQUE (COL3, COL1))

;

SELECT   INDEX_NAME, UNIQUE_ID, MODIFIABILITY, INDEX_TYPE

FROM     INDEXES

WHERE    TABLE_NAME = 'T1'

;

SELECT   INDEX_NAME, COLUMN_NAME, COLUMN_SEQ, ORDERING, UNIQUE_ID

FROM     COLUMNS_IN_INDEX

WHERE    TABLE_NAME = 'T1'

;

CREATE UNIQUE INDEX UK_2COL3

ON     T1 (COL1, COL3)



CREATE UNIQUE INDEX PK_COL1

ON     T1 (COL1, COL4)

;

CREATE UNIQUE INDEX UK_1COL3

ON     T1 (COL3, COL4)

;

CREATE UNIQUE INDEX UK_COL2

ON     T1 (COL2)



Example 20.20: 



CREATE TABLE T2 (

   COL1    INTEGER NOT NULL PRIMARY KEY,

   COL2    INTEGER,

   COL3    INTEGER,

   COL4    INTEGER,

   FOREIGN KEY (COL2,COL4) REFERENCES T1 (COL1, COL4),

   FOREIGN KEY (COL2,COL3) REFERENCES T1 (COL1, COL4))

;

SELECT   INDEX_NAME, COLUMN_NAME, COLUMN_SEQ, ORDERING, UNIQUE_ID

FROM     COLUMNS_IN_INDEX

WHERE    TABLE_NAME = 'T2'

AND      INDEX_NAME LIKE 'FK_%'



Example 20.21: 



CREATE TABLE PLAYERS_XXL (

   PLAYERNO     INTEGER NOT NULL PRIMARY KEY,

   NAME         CHAR(15) NOT NULL,

   INITIALS     CHAR(3) NOT NULL,

   BIRTH_DATE   DATE,

   SEX          CHAR(1) NOT NULL,

   JOINED       SMALLINT NOT NULL,

   STREET       VARCHAR(30) NOT NULL,

   HOUSENO      CHAR(4),

   POSTCODE     CHAR(6),

   TOWN         VARCHAR(30) NOT NULL,

   PHONENO      CHAR(13),

   LEAGUENO     CHAR(8))



Example 20.22: 



CREATE PROCEDURE FILL_PLAYERS_XXL

   (IN :NUMBER_PLAYERS INTEGER);

BEGIN

   DECLARE :COUNTER INTEGER;

   DELETE FROM PLAYERS_XXL;

   COMMIT;

   SET :COUNTER = 1;

   WHILE :COUNTER <= :NUMBER_PLAYERS DO

      INSERT INTO PLAYERS_XXL VALUES(

         :COUNTER,

         'name'+CAST(:COUNTER AS CHAR(10)),

         CASE MOD(:COUNTER,2) WHEN 0 THEN 'in1' ELSE 'in2' END,

         DATEADD(MONTH, MOD(:COUNTER,300), '1960-01-01'),

         CASE MOD(:COUNTER,20) WHEN 0 THEN 'F' ELSE 'M' END,

         1980 + MOD(:COUNTER,20),

         'street'+CAST(CAST(:COUNTER /10 AS INTEGER) AS VARCHAR(10)),

         CAST(CAST(:COUNTER /10 AS INTEGER)+1 AS CHAR(4)),

         'p'+CAST(MOD(:COUNTER,50) AS VARCHAR(10)),

         'town'+CAST(MOD(:COUNTER,10) AS VARCHAR(10)),

         '070-6868689',

         CASE MOD(:COUNTER,3) WHEN 0

            THEN NULL ELSE CAST(:COUNTER AS CHAR(8)) END);    

         IF MOD(:COUNTER,1000) = 0 THEN

            COMMIT;

         END IF;

      SET :COUNTER = :COUNTER + 1;

   END WHILE;

   COMMIT;

END



Example 20.23: 



CALL FILL_PLAYERS_XXL(100000)



Example 20.24: 



CREATE INDEX PLAYERS_XXL_INITIALS

   ON PLAYERS_XXL(INITIALS)

;

CREATE INDEX PLAYERS_XXL_POSTCODE

   ON PLAYERS_XXL(POSTCODE)

;

CREATE INDEX PLAYERS_XXL_STREET

   ON PLAYERS_XXL(STREET)



Example 20.25: 



CREATE TABLE THINTS (

   COL1   INTEGER NOT NULL,

   COL2   INTEGER NOT NULL)

;

CREATE INDEX THINTS_INDEX_COL1 ON THINTS (COL1)

;

CREATE INDEX THINTS_INDEX_COL2 ON THINTS (COL2)

;

SELECT   *

FROM     THINTS

WHERE    COL1 = 2

AND      COL2 = 4

;

SELECT   *

FROM     THINTS WITH (INDEX(THINTS_INDEX_COL1))

WHERE    COL1 = 2

AND      COL2 = 4

;

SELECT   *

FROM     THINTS WITH (INDEX(0))

WHERE    COL1 = 2

AND      COL2 = 4

;

SELECT   COL1, COL2, 

        (SELECT   COUNT(*) 

         FROM     THINTS WITH (INDEX(THINTS_INDEX_COL1)))

FROM     THINTS WITH (INDEX(o))

WHERE    COL1 = 2

AND      COL2 = 4

;

SELECT   * 

FROM    (SELECT   *

         FROM     THINTS WITH (INDEX(THINTS_INDEX_COL1))) AS T

WHERE    COL1 = 2

AND      COL2 = 4



Example 20.26: Determine which base tables have more than one index.



SELECT   TABLE_NAME, COUNT(*)

FROM     INDEXES

GROUP BY TABLE_NAME

HAVING   COUNT(*) > 1



Example 20.27: 



SELECT   TABLE_NAME

FROM     TABLES AS TAB

WHERE    NOT EXISTS

        (SELECT   *

         FROM     INDEXES AS IDX

         WHERE    TAB.TABLE_NAME = TAB.TABLE_NAME

         AND      IDX.UNIQUE_ID = 'YES')



Example 21.1: 



CREATE   VIEW TOWNS AS

SELECT   DISTINCT TOWN

FROM     PLAYERS



Example 21.2:



SELECT   *

FROM     TOWNS



Example 21.3: 



CREATE   VIEW CPLAYERS AS

SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO IS NOT NULL

;

SELECT   *

FROM     CPLAYERS



Example 21.4: 



SELECT   *

FROM     CPLAYERS

WHERE    PLAYERNO BETWEEN 6 AND 44

;

SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO IS NOT NULL

AND      PLAYERNO BETWEEN 6 AND 44



Example 21.5: 



DELETE

FROM     CPLAYERS

WHERE    LEAGUENO = '7060'



Example 21.6: 



CREATE   VIEW SEVERAL AS

SELECT   *

FROM     CPLAYERS

WHERE    PLAYERNO BETWEEN 6 AND 27

;

SELECT   *

FROM     SEVERAL



Example 21.7: 



CREATE VIEW DIGITS AS

SELECT 0 DIGIT UNION SELECT 1 UNION

SELECT 2 UNION SELECT 3 UNION

SELECT 4 UNION SELECT 5 UNION

SELECT 6 UNION SELECT 7 UNION

SELECT 8 UNION SELECT 9

;

SELECT * FROM DIGITS



Example 21.8: 



CREATE   VIEW STRATFORDERS (PLAYERNO, NAME, INIT, BORN) AS

SELECT   PLAYERNO, NAME, INITIALS, BIRTH_DATE

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

;

SELECT   *

FROM     STRATFORDERS

WHERE    PLAYERNO > 90



Example 21.9: 



CREATE   VIEW RESIDENTS AS

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN



Example 21.10: 



CREATE DATABASE SPECIAL

   DICTIONARY_PATH 'C:\SPECIAL' V2_METADATA

;

SET SECURITY = 'master'

;

CREATE TABLE PENALTIES (

   PAYMENTNO     INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   PAY_DATE      DATE NOT NULL,

   AMOUNT        DECIMAL(7,2) NOT NULL,

   PRIMARY KEY   (PAYMENTNO))

;

CREATE   VIEW HIGH_PENALTIES

WITH     EXECUTE AS 'MASTER' AS

SELECT   *

FROM     PENALTIES

WHERE    AMOUNT > 60

;

CREATE   USER TIM

;

GRANT    SELECT

ON       VIEW HIGH_PENALTIES

TO       TIM

;

SELECT   *

FROM     HIGH_PENALTIES



Example 21.11: 



DROP VIEW CPLAYERS



Example 21.12: 



SELECT   TABLE_NAME

FROM     TABLES

WHERE    TABLE_NAME = 'STOCK'

UNION

SELECT   VIEW_NAME

FROM     VIEWS

WHERE    VIEW_NAME = 'STOCK'

;

CREATE   VIEW AGES (PLAYERNO, BEGIN_AGE) AS

SELECT   PLAYERNO, JOINED – YEAR(BIRTH_DATE)

FROM     PLAYERS

;

CREATE   VIEW PLAYERS_NAMES AS

SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS



Example 21.13: 



CREATE   VIEW COST_RAISERS AS

SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO IN 

        (SELECT   PLAYERNO

         FROM     PENALTIES)

;

SELECT   PLAYERNO

FROM     COST_RAISERS

WHERE    TOWN = 'Stratford'

;

SELECT   PLAYERNO

FROM    (SELECT   *

         FROM     PLAYERS

         WHERE    PLAYERNO IN 

                 (SELECT   PLAYERNO

                  FROM     PENALTIES)) AS VIEWFORMULA

WHERE    TOWN = 'Stratford'



Example 21.14: 



DELETE

FROM     STRATFORDERS

WHERE    BORN > '1965-12-31'

;

DELETE

FROM     PLAYERS

WHERE    BIRTH_DATE > '1965-12-31'

AND      TOWN = 'Stratford'

;

SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO IN 

        (SELECT   PLAYERNO

         FROM     PENALTIES)



Example 21.15: 



SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)

AND      TOWN = 'Stratford'

;

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)

GROUP BY TOWN

;

CREATE   VIEW PPLAYERS AS

SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES)

;

SELECT   *

FROM     PPLAYERS

WHERE    TOWN = 'Stratford'

;

SELECT   TOWN, COUNT(*)

FROM     PPLAYERS

GROUP BY TOWN



Example 21.17: 



SELECT   DISTINCT NAME, INITIALS, DIVISION

FROM     PLAYERS AS P, MATCHES AS M, TEAMS AS T

WHERE    P.PLAYERNO = M.PLAYERNO

AND      M.TEAMNO = T.TEAMNO

;

CREATE   VIEW TEAMS (TEAMNO, PLAYERNO, DIVISION) AS

SELECT   DISTINCT TEAMNO, CAPTAIN, DIVISION

FROM     RESULT

;

CREATE   VIEW MATCHES AS

SELECT   MATCHNO, TEAMNO, PLAYERNO, WON, LOST

FROM     RESULT

;

CREATE   VIEW GREATER AS

SELECT   DISTINCT PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES

         WHERE    PLAYERNO IN

                 (SELECT   PLAYERNO

                  FROM     MATCHES

                  WHERE    TEAMNO = 2))

;

CREATE   VIEW FIRST AS

SELECT   DISTINCT PLAYERNO

FROM     MATCHES

WHERE    TEAMNO IN

        (SELECT   TEAMNO

         FROM     TEAMS

         WHERE    DIVISION = 'first')

;

SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

AND      PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     GREATER)

AND      PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     FIRST)



Answer 21.1:



CREATE   VIEW NUMBERPLS (TEAMNO, NUMBER) AS

SELECT   TEAMNO, COUNT(*)

FROM     MATCHES

GROUP BY TEAMNO



Answer 21.2:



CREATE   VIEW WINNERS AS

SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN (SELECT   PLAYERNO

                      FROM     MATCHES

                      WHERE    WON > LOST)



Answer 21.3:



CREATE   VIEW TOTALS (PLAYERNO, SUM_PENALTIES) AS

SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

GROUP BY PLAYERNO



Answer 21.5.1:



SELECT   YEAR(BORN) – 1900 AS DIFFERENCE, COUNT(*)

FROM    (SELECT   PLAYERNO, NAME, INITIALS, BIRTH_DATE AS BORN

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford') AS STRATFORDERS

GROUP BY DIFFERENCE



Answer 21.5.2:



SELECT   EXPENSIVE.PLAYERNO

FROM    (SELECT   *

         FROM     PLAYERS

         WHERE    PLAYERNO IN 

                 (SELECT   PLAYERNO

                  FROM     PENALTIES)) AS EXPENSIVE,

        (SELECT   PLAYERNO, NAME, INITIALS, BIRTH_DATE AS BORN

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford') AS STRATFORDERS 

WHERE    EXPENSIVE.PLAYERNO = STRATFORDERS.PLAYERNO



Answer 21.5.3:



UPDATE   PLAYERS

SET      BIRTH_DATE = '1950-04-04'

WHERE    PLAYERNO = 7



Example 22.1: 



CREATE USER CHRIS WITH PASSWORD CHRISSEC

;

CREATE USER PAUL WITH PASSWORD LUAP



Example 22.2: 



SELECT   *

FROM     USERS

ORDER    BY 1



Example 22.3: 



DROP USER JIM



Example 22.4: 



ALTER USER CHRIS RENAME TO COMBO1

;

ALTER USER PAUL RENAME TO COMBO2

;

SELECT   *

FROM     USERS

WHERE    USER_NAME LIKE 'COMBO%'



Example 22.5: 



CREATE USER JOHN

;

ALTER USER JOHN WITH PASSWORD JOHN1



Example 22.6: 



ALTER USER JOHN WITH PASSWORD ''



Example 22.7: 



SET PASSWORD FOR JOHN = JOHN1

;

SET PASSWORD = JOHN1



Example 22.8: 



GRANT   SELECT

ON      PLAYERS

TO      JAMIE



Example 22.9: 



SELECT   GRANTEE, TABLE_NAME, PRIVILEGE

FROM     TABLE_AUTHS

WHERE    GRANTEE = 'JAMIE'



Example 22.10: 



GRANT   INSERT

ON      TEAMS

TO      JAMIE, PETE



Example 22.11: 



GRANT   SELECT

ON      PENALTIES

TO      PUBLIC

;

SELECT  GRANTEE, TABLE_NAME, PRIVILEGE

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'PUBLIC'



Example 22.12: 



GRANT   INSERT

ON      *

TO      PAT

;

SELECT  GRANTEE, TABLE_NAME, PRIVILEGE

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'PAT'



Example 22.13: 



GRANT   ALL

ON      PENALTIES

TO      PAT

;

SELECT  GRANTEE, TABLE_NAME, PRIVILEGE

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'PAT'

AND     TABLE_NAME = 'PENALTIES'



Example 22.14: 



GRANT   UPDATE (PLAYERNO, DIVISION)

ON      TEAMS

TO      PETE

;

SELECT  GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE

FROM    COLUMN_AUTHS

WHERE   GRANTEE = 'PETE'



Example 22.15: 



GRANT   CREATETAB

TO      PETE

;

SELECT  GRANTEE, PRIVILEGE

FROM    DATABASE_AUTHS

WHERE   GRANTEE = 'PETE'



Example 22.16: 



CREATE USER U1

;

CREATE USER U2

;

CREATE USER U3

;

CREATE USER U4

;

CREATE USER U5

;

CREATE GROUP GROUP1



Example 22.17: 



ALTER GROUP GROUP1 ADD USER U1, U2



Example 22.18: 



ALTER GROUP GROUP1 DROP USER U2



Example 22.19: 



CREATE GROUP GROUP2

;

CREATE USER U6 IN GROUP GROUP2



Example 22.20: 



GRANT  SELECT

ON     PLAYERS

TO     GROUP2

;

GRANT  CREATETAB

TO     GROUP2



Example 22.21: 



SELECT   GROUPS.XU$NAME AS GROUPNAME, USERS.XU$NAME AS USERNAME

FROM     X$USER AS GROUPS, X$USER AS USERS

WHERE    GROUPS.XU$ID = USERS.XU$ID

AND      GROUPS.XU$FLAGS = 64

AND      USERS.XU$FLAGS <> 64

AND      GROUPS.XU$NAME IN ('GROUP1', 'GROUP2')



Example 22.22: 



DROP GROUP GROUP1, GROUP2



Example 22.23: 



SELECT   GRANTEE

FROM     TABLE_AUTHS

WHERE    TABLE_NAME = 'PLAYERS'

AND      PRIVILEGE = 'SELECT'



Example 22.24: 



CREATE USER JONI

;

GRANT    SELECT

ON       PLAYERS

TO       JONI

;

REVOKE   SELECT

ON       PLAYERS

FROM     JONI



Example 22.25: 



REVOKE   CREATETAB

FROM     PETE



Example 22.26: 



CREATE USER DIANE WITH PASSWORD SECRET

;

CREATE   VIEW NAME_ADDRESS AS

SELECT   NAME, INITIALS, STREET, HOUSENO, TOWN

FROM     PLAYERS

WHERE    LEAGUENO IS NULL

;

GRANT   SELECT

ON      VIEW NAME_ADDRESS

TO      DIANE



Example 22.27: 



CREATE USER GERARD

;

CREATE   VIEW RESIDENTS (TOWN, NUMBER_OF) AS

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN

;

GRANT   SELECT

ON      RESIDENTS

TO      GERARD



Example 22.28: 



CREATE   VIEW PERSONAL_TABLES AS

SELECT   TABLE_NAME

FROM     TABLES

WHERE    TABLE_NAME IN

        (SELECT   TABLE_NAME

         FROM     TABLE_AUTHS

         WHERE    GRANTEE = USER OR GRANTEE = 'PUBLIC')

OR       TABLE_NAME IN

        (SELECT   TABLE_NAME

         FROM     COLUMN_AUTHS

         WHERE    GRANTEE = USER OR GRANTEE = 'PUBLIC')

UNION

SELECT   TABLE_NAME

FROM     TABLES

WHERE    EXISTS

        (SELECT   *

         FROM     TABLE_AUTHS

         WHERE    GRANTEE = USER

         AND      TABLE_NAME = 'ALL_TABLES')

;

CREATE   VIEW PERSONAL_COLUMNS AS

SELECT   TABLE_NAME, COLUMN_NAME, COLUMN_NO, DATA_TYPE, CHAR_LENGTH, PRECISION,

         SCALE, NULLABLE

FROM     COLUMNS

WHERE    TABLE_NAME IN

        (SELECT   TABLE_NAME

         FROM     PERSONAL_TABLES)

OR       COLUMN_NAME IN

        (SELECT   COLUMN_NAME

         FROM     COLUMN_AUTHS

         WHERE   (GRANTEE = USER OR GRANTEE = 'PUBLIC')

         AND      COLUMN_AUTHS.TABLE_NAME = COLUMNS.TABLE_NAME)

;

GRANT   SELECT

ON      PERSONAL_TABLES

TO      PUBLIC

;

GRANT   SELECT

ON      PERSONAL_COLUMNS

TO      PUBLIC



Answer 22.1:



CREATE  USER RONALDO WITH PASSWORD NIKE



Answer 22.2:



DROP    USER RONALDO



Answer 22.3:



GRANT   SELECT, INSERT

ON      PLAYERS

TO      RONALDO



Answer 22.4:



GRANT   ALL

ON      COMMITTEE_MEMBERS

TO      PUBLIC



Answer 22.5:



GRANT   UPDATE(STREET, HOUSENO, POSTCODE, TOWN)

ON      PLAYERS

TO      RONALDO



Example 23.1: 



CREATE PROCEDURE DELETE_MATCHES

   (IN :P_PLAYERNO INTEGER);

BEGIN

   DELETE

   FROM   MATCHES

   WHERE  PLAYERNO = :P_PLAYERNO;

END



Example 23.2: 



CALL DELETE_MATCHES (8)



Example 23.3: 



CREATE PROCEDURE PRINTTEXT (

   IN :TEXTTAG CHAR(10),

   IN :TEXTSTRING VARCHAR(100));

BEGIN

   DECLARE :TABLE_EXISTS TINYINT;

   SELECT   COUNT(*)

   INTO     :TABLE_EXISTS

   FROM     TABLES 

   WHERE    TABLE_NAME = 'TEXTSTRING';

   IF :TABLE_EXISTS = 0 THEN

      CREATE TABLE TEXTSTRING (

         TEXTNO      IDENTITY NOT NULL,

         TEXTTAG     CHAR(10) NOT NULL,

         TEXTSTRING  VARCHAR(100));

   END IF;

   INSERT INTO TEXTSTRING (TEXTTAG, TEXTSTRING)

   VALUES (:TEXTTAG, :TEXTSTRING);

   SELECT TEXTSTRING 

   FROM   TEXTSTRING;

END



Example 23.4: 



CREATE PROCEDURE SHOWTEXT (

   IN :TEXTTAG CHAR(10))

RETURNS (

   TEXTNO     IDENTITY,

   TEXTTAG    CHAR(10),

   TEXTSTRING VARCHAR(100));

BEGIN

   SELECT   TEXTNO, TEXTTAG, TEXTSTRING

   FROM     TEXTSTRING

   WHERE    TEXTTAG = :TEXTTAG

   ORDER BY TEXTNO ASC;

END



Example 23.5: 



CREATE PROCEDURE HELLOWORLD();

BEGIN

   CALL PRINTTEXT('TEST', 'Hello world!');

END

;

CALL HELLOWORLD()

;

CALL SHOWTEXT('TEST')



Example 23.6: 



CREATE PROCEDURE TOTAL

  (IN  :NUMBER1 INTEGER,

   IN  :NUMBER2 INTEGER,

   OUT :NUMBER3 INTEGER);

BEGIN

   SET :NUMBER3 = :NUMBER1 + :NUMBER2;

   SET :NUMBER1 = 100;

END

;

CREATE PROCEDURE CALL_TOTAL();

BEGIN

   DECLARE :VAR1 INTEGER;

   DECLARE :VAR2 INTEGER;

   DECLARE :VAR3 INTEGER;        

   SET :VAR1 = 20;

   SET :VAR2 = 30;

   CALL TOTAL(:VAR1, :VAR2, :VAR3);

   CALL PRINTTEXT('TOTAL',CAST(:VAR1 AS VARCHAR(100)));

   CALL PRINTTEXT('TOTAL',CAST(:VAR3 AS VARCHAR(100)));

END

;

CALL CALL_TOTAL()

;

CALL SHOWTEXT('TOTAL')



Example 23.8: 



CREATE PROCEDURE GETDEFAULT();

BEGIN

   DECLARE :NUMBER INTEGER DEFAULT 100;

   CALL PRINTTEXT('GETDEFAULT', CAST(:NUMBER AS VARCHAR(100)));

END

;

CALL GETDEFAULT()

;

CALL SHOWTEXT('GETDEFAULT')



Example 23.9: 



CREATE PROCEDURE TEST(OUT :NUMBER1 INTEGER);

BEGIN

   DECLARE :NUMBER2 INTEGER DEFAULT (SELECT COUNT(*) FROM PLAYERS);

   SET :NUMBER1 = :NUMBER2;

END

;

CREATE PROCEDURE CALL_TEST();

BEGIN

   DECLARE :ANSWER INTEGER

   CALL TEST(:ANSWER);

   CALL PRINTTEXT('TEST',:ANSWER);

END

;

CALL CALL_TEST

;

CALL SHOWTEXT('TEST')



Example 23.10: 



CREATE PROCEDURE HIGHEST

   (IN :P1 INTEGER,

    IN :P2 INTEGER,

    OUT :P3 INTEGER);

BEGIN

   IF :P1 > :P2 THEN

      SET :P3 = 1;

   ELSE

      IF :P1 = :P2 THEN

         SET :P3 = 2;

      ELSE

         SET :P3 = 3;

      END IF;

   END IF;

END

;

CREATE PROCEDURE CALL_HIGHEST();

BEGIN

   DECLARE :VAR1 INTEGER;

   CALL HIGHEST(200, 100, :VAR1)

   CALL PRINTTEXT('HIGHEST', :VAR1);

END

;

CALL CALL_HIGHEST

;

CALL SHOWTEXT('HIGHEST')



Example 23.11: 



CREATE PROCEDURE FIBONACCI

   (INOUT :NUMBER1 INTEGER,

    INOUT :NUMBER2 INTEGER,

    INOUT :NUMBER3 INTEGER);

BEGIN

   SET :NUMBER3 = :NUMBER1 + :NUMBER2;

   IF :NUMBER3 > 10000 THEN

      SET :NUMBER3 = :NUMBER3 - 10000;

   END IF;

   SET :NUMBER1 = :NUMBER2;

   SET :NUMBER2 = :NUMBER3;

END

;

CREATE PROCEDURE CALL_FIBONACCI();

BEGIN

   DECLARE :VAR1 INTEGER = 16;

   DECLARE :VAR2 INTEGER = 27;

   DECLARE :VAR3 INTEGER;

   CALL FIBONACCI(:VAR1, :VAR2, :VAR3);

   CALL PRINTTEXT('FIBON1', CAST(:VAR3 AS VARCHAR(100)));

   CALL FIBONACCI(:VAR1, :VAR2, :VAR3);

   CALL PRINTTEXT('FIBON1', CAST(:VAR3 AS VARCHAR(100)));

   CALL FIBONACCI(:VAR1, :VAR2, :VAR3);

   CALL PRINTTEXT('FIBON1', CAST(:VAR3 AS VARCHAR(100)));

END

;

CALL CALL_FIBONACCI()

;

CALL SHOWTEXT('FIBON1')



Example 23.12: 



CREATE PROCEDURE LARGEST

   (OUT :T CHAR(10));

BEGIN

   IF (SELECT COUNT(*) FROM PLAYERS) >

      (SELECT COUNT(*) FROM PENALTIES) THEN

      SET :T = 'PLAYERS';

   ELSE

      IF (SELECT COUNT(*) FROM PLAYERS) =

         (SELECT COUNT(*) FROM PENALTIES) THEN

         SET :T = 'EQUAL';  

      ELSE

         SET :T = 'PENALTIES';

      END IF;

   END IF;

END

;

CREATE PROCEDURE CALL_LARGEST();

BEGIN

   DECLARE :ANSWER VARCHAR(20);

   CALL LARGEST(:ANSWER);

   CALL PRINTTEXT('LARGEST',:ANSWER);

END

;

CALL CALL_LARGEST

;

CALL SHOWTEXT('LARGEST')



Example 23.13: 



CREATE PROCEDURE AGE

   (IN  :START_DATE  DATE,

    IN  :END_DATE    DATE,

    OUT :YEARS       INTEGER,

    OUT :MONTHS      INTEGER,

    OUT :DAYS        INTEGER);

BEGIN

   DECLARE :NEXT_DATE DATE;

   DECLARE :PREVIOUS_DATE DATE;

   SET :YEARS = 0;

   SET :PREVIOUS_DATE = :START_DATE;

   SET :NEXT_DATE = DATEADD(YEAR, 1, :START_DATE);

   WHILE :NEXT_DATE <= :END_DATE DO

      SET :YEARS = :YEARS + 1;

      SET :PREVIOUS_DATE = :NEXT_DATE;

      SET :NEXT_DATE = DATEADD(YEAR, 1, :NEXT_DATE);

   END WHILE;                     

   SET :MONTHS = 0;

   SET :NEXT_DATE = DATEADD(MONTH, 1, :PREVIOUS_DATE);

   WHILE :NEXT_DATE <= :END_DATE DO

      SET :MONTHS = :MONTHS + 1;

      SET :PREVIOUS_DATE = :NEXT_DATE;

      SET :NEXT_DATE = DATEADD(MONTH, 1, :NEXT_DATE);

   END WHILE;

   SET :DAYS = 0;

   SET :NEXT_DATE = DATEADD(DAY, 1, :PREVIOUS_DATE);

   WHILE :NEXT_DATE <= :END_DATE DO

      SET :DAYS = :DAYS + 1;

      SET :PREVIOUS_DATE = :NEXT_DATE;

      SET :NEXT_DATE = DATEADD(DAY, 1, :NEXT_DATE);

   END WHILE;

END

;

CREATE PROCEDURE CALL_AGE();

BEGIN

   DECLARE :VAR1 INTEGER;

   DECLARE :VAR2 INTEGER;

   DECLARE :VAR3 INTEGER;

   CALL AGE('1991-01-12', '1999-07-09', :VAR1, :VAR2, :VAR3);

   CALL PRINTTEXT('AGE', 

      CAST(:VAR1 AS VARCHAR(100))+' '+

      CAST(:VAR2 AS VARCHAR(100))+' '+

      CAST(:VAR3 AS VARCHAR(100)));

END

;

CALL CALL_AGE

;

CALL SHOWTEXT('AGE')



Example 23.14: 



CREATE PROCEDURE STOPIF () AS

BEGIN

   L1: IF 1 = 1 THEN

      LEAVE L1;

      CALL PRINTTEXT('STOPWHILE', 'Inside the WHILE statement');

   END IF;

   CALL PRINTTEXT('STOPIF', 'Ready');

END



Example 23.15: 



CREATE PROCEDURE WAIT

   (IN :WAIT_SECONDS INTEGER);

BEGIN

   DECLARE :END_TIME TIMESTAMP;

   SET :END_TIME =

      TIMESTAMPADD(SQL_TSI_SECOND, 5, CURRENT_TIMESTAMP());

   WAIT_LOOP: LOOP

      IF CURRENT_TIMESTAMP() > :END_TIME THEN

         LEAVE WAIT_LOOP;

      END IF;

   END LOOP;

END



Example 23.16: 



CREATE TABLE PLAYERS_WITH_PARENTS (

   PLAYERNO          INTEGER NOT NULL PRIMARY KEY,

   FATHER_PLAYERNO   INTEGER,

   MOTHER_PLAYERNO   INTEGER)

;

ALTER TABLE PLAYERS_WITH_PARENTS ADD

   FOREIGN KEY (FATHER_PLAYERNO)

      REFERENCES PLAYERS_WITH_PARENTS (PLAYERNO)

;

ALTER TABLE PLAYERS_WITH_PARENTS ADD

   FOREIGN KEY (MOTHER_PLAYERNO)

      REFERENCES PLAYERS_WITH_PARENTS (PLAYERNO)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(9, NULL, NULL)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(8, NULL, NULL)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(7, NULL, NULL)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(6, NULL, NULL)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(5, NULL, NULL)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(4, 8, 9)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(3, 6, 7)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(2, 4, 5)

;

INSERT INTO PLAYERS_WITH_PARENTS VALUES(1, 2, 3)

;

CREATE PROCEDURE TOTAL_NUMBER_OF_PARENTS

   (IN    :PLAYERNO INTEGER,

    INOUT :NUMBER INTEGER);

BEGIN

   DECLARE :V_FATHER INTEGER;

   DECLARE :V_MOTHER INTEGER;

   SET :V_FATHER =

      (SELECT   FATHER_PLAYERNO

       FROM     PLAYERS_WITH_PARENTS

       WHERE    PLAYERNO = :PLAYERNO);

   SET :V_MOTHER =

      (SELECT   MOTHER_PLAYERNO

       FROM     PLAYERS_WITH_PARENTS

       WHERE    PLAYERNO = :PLAYERNO);

   IF :V_FATHER IS NOT NULL THEN

      CALL TOTAL_NUMBER_OF_PARENTS (:V_FATHER, :NUMBER);

      SET :NUMBER = :NUMBER + 1;

   END IF;               

   IF :V_MOTHER IS NOT NULL THEN

      CALL TOTAL_NUMBER_OF_PARENTS (:V_MOTHER, :NUMBER);

      SET :NUMBER = :NUMBER + 1;

   END IF;

END

;

CREATE PROCEDURE CALL_TOTAL_NUMBER_OF_PARENTS();

BEGIN

   DECLARE :NUMBER INTEGER;

   DECLARE :V_MOTHER INTEGER;

   SET :NUMBER = 0;

   CALL TOTAL_NUMBER_OF_PARENTS (1, :NUMBER);

   CALL PRINTTEXT('PARENTS', CAST(:NUMBER AS VARCHAR(10)));

END

;

CALL CALL_TOTAL_NUMBER_OF_PARENTS();

;

CALL SHOWTEXT('PARENTS')



Example 23.17: 



CREATE PROCEDURE SUM_PENALTIES_PLAYER

   (IN :PLAYERNO INTEGER);

BEGIN

   DECLARE :SUM_PENALTIES DECIMAL(10,2);

   SELECT SUM(AMOUNT)

   INTO   :SUM_PENALTIES

   FROM   PENALTIES

   WHERE  PLAYERNO = :PLAYERNO;

   CALL PRINTTEXT('SUMPENALTIES', :SUM_PENALTIES);

END

;

CALL SUM_PENALTIES_PLAYER(27)

;

CALL SHOWTEXT('SUMPENALTIES')

;

SELECT   FATHER_PLAYERNO, MOTHER_PLAYERNO

INTO     :V_FATHER, :V_MOTHER

FROM     PLAYERS_WITH_PARENTS

WHERE    PLAYERNO = :PLAYERNO



Example 23.18: 



CREATE PROCEDURE GIVE_ADDRESS

   (IN :PLAYERNO INTEGER);

BEGIN

   DECLARE :TOWN     VARCHAR(30);

   DECLARE :STREET   VARCHAR(30);

   DECLARE :HOUSENO  VARCHAR(4);

   DECLARE :POSTCODE VARCHAR(6);

   SELECT TOWN, STREET, HOUSENO, POSTCODE

   INTO   :TOWN, :STREET, :HOUSENO, :POSTCODE

   FROM   PLAYERS

   WHERE  PLAYERNO = :PLAYERNO;

   CALL PRINTTEXT('ADDRESS', :STREET+','+RTRIM(:HOUSENO)+

          ','+RTRIM(:TOWN)+IFNULL(','+RTRIM(:POSTCODE),''));

END

;

CALL GIVE_ADDRESS(27)

;

CALL SHOWTEXT('ADDRESS')



Example 23.19: 



CREATE TABLE FIBON (

   NUMBER1   INTEGER NOT NULL PRIMARY KEY,

   NUMBER2   INTEGER NOT NULL)

;

CREATE PROCEDURE FIBONACCI_START();

BEGIN

   DELETE FROM FIBON;

   INSERT INTO FIBON (NUMBER, NUMBER2) VALUES (16, 27);

END

;

CREATE PROCEDURE FIBONACCI_GIVE

   (INOUT :NUMBER INTEGER);

BEGIN

   DECLARE :N1 INTEGER;

   DECLARE :N2 INTEGER;

   SELECT NUMBER1, NUMBER2

   INTO   :N1, :N2

   FROM   FIBON;

   SET :NUMBER = :N1 + :N2;

   IF :NUMBER > 10000 THEN

      SET :NUMBER = :NUMBER - 10000;

   END IF;

   SET :N1 = :N2;

   SET :N2 = :NUMBER;

   UPDATE FIBON

   SET    NUMBER1 = :N1,

          NUMBER2 = :N2;

END

;

CREATE PROCEDURE CALL_FIBONACCI2();

BEGIN

   DECLARE :VAR1 INTEGER;

   CALL FIBONACCI_START;

   CALL FIBONACCI_GIVE(:VAR1);

   CALL PRINTTEXT('FIBON2', :VAR1);

   CALL FIBONACCI_GIVE(:VAR1);

   CALL PRINTTEXT('FIBON2', :VAR1);

   CALL FIBONACCI_GIVE(:VAR1);

   CALL PRINTTEXT('FIBON2', :VAR1);

END

;

CALL CALL_FIBONACCI2

;

CALL SHOWTEXT('FIBON2')



Example 23.20: 



CREATE PROCEDURE DELETE_PLAYER

   (IN :PLAYERNO INTEGER);

BEGIN

   DECLARE :NUMBER_OF_PLAYERS   INTEGER;

   DECLARE :NUMBER_OF_PENALTIES INTEGER;

   DECLARE :NUMBER_OF_TEAMS     INTEGER;

   DECLARE :NUMBER_OF_MEMBERS   INTEGER;

   SELECT COUNT(*)

   INTO   :NUMBER_OF_PLAYERS

   FROM   PLAYERS

   WHERE  PLAYERNO = :PLAYERNO;

   IF :NUMBER_OF_PLAYERS > 0 THEN

      SELECT COUNT(*)

      INTO   :NUMBER_OF_PENALTIES

      FROM   PENALTIES

      WHERE  PLAYERNO = :PLAYERNO;

      SELECT COUNT(*)

      INTO   :NUMBER_OF_TEAMS

      FROM   TEAMS

      WHERE  PLAYERNO = :PLAYERNO;

      SELECT COUNT(*)

      INTO   :NUMBER_OF_MEMBERS

      FROM   COMMITTEE_MEMBERS

      WHERE  PLAYERNO = :PLAYERNO;    

      IF :NUMBER_OF_PENALTIES = 0 AND

         :NUMBER_OF_TEAMS = 0 AND

         :NUMBER_OF_MEMBERS = 0 THEN

         CALL DELETE_MATCHES(:PLAYERNO);

         DELETE FROM PLAYERS

         WHERE  PLAYERNO = :PLAYERNO;

      END IF;

   END IF;

END



Example 23.21: 



CREATE PROCEDURE INSERT_LONG_VALUE();

BEGIN

   INSERT INTO TEAMS VALUES (10, 27, 'Third division team');

   CALL PRINTTEXT('TOOLONG', SQLSTATE);

END

;

CALL INSERT_LONG_VALUE

;

CALL SHOWTEXT('TOOLONG')



Example 23.22: 



CREATE PROCEDURE INSERT_DUPLICATE();

BEGIN

   INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (1000, 6, 'third');

   INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (1000, 6, 'third');

   INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (2000, 6, 'fourth');

END

;

CALL INSERT_DUPLICATE

;

SELECT * FROM TEAMS



Example 23.23: 



CREATE PROCEDURE RETURN_SIGNAL

   (IN :VAR INTEGER);

BEGIN

   IF :VAR > 100 THEN

      SIGNAL 'A0001', 'Value is greater than 100';

   ELSE

      SIGNAL 'A0002', 'Value is not greater than 100';

   END IF;

END

;

CALL RETURN_SIGNAL(105)



Example 23.24: 



CREATE PROCEDURE NEW_TEAM_AND_MATCHES1 ();

BEGIN ATOMIC

   INSERT INTO TEAMS VALUES (200, 27, 'first');

   INSERT INTO MATCHES VALUES (200, 200, 8, 1, 3);

   INSERT INTO MATCHES VALUES (201, 200, 112, 3, 2);

END

;

CALL NEW_TEAM_AND_MATCHES1

;

SELECT   MATCHES.MATCHNO, MATCHES.TEAMNO, TEAMS.DIVISION

FROM     MATCHES INNER JOIN TEAMS

         ON MATCHES.TEAMNO = TEAMS.TEAMNO

WHERE    MATCHES.TEAMNO = 200



Example 23.25: 



CREATE PROCEDURE NEW_TEAM_AND_MATCHES2 ();

BEGIN ATOMIC

   INSERT INTO TEAMS VALUES (201, 27, 'first');

   INSERT INTO MATCHES VALUES (200, 201, 8, 1, 3);

   INSERT INTO MATCHES VALUES (200, 201, 112, 3, 2);

END

;

CALL NEW_TEAM_AND_MATCHES2

;

SELECT * FROM TEAMS

;

SELECT * FROM MATCHES WHERE TEAMNO = 201



Example 23.26: 



CREATE PROCEDURE NEW_TEAM_AND_MATCHES3 ();

BEGIN 

   INSERT INTO TEAMS VALUES (202, 27, 'first');

   BEGIN ATOMIC

      INSERT INTO MATCHES VALUES (200, 202, 8, 1, 3);

      INSERT INTO MATCHES VALUES (200, 202, 112, 3, 2);

   END;

END

;

CALL NEW_TEAM_AND_MATCHES3

;

SELECT * FROM TEAMS WHERE TEAMNO = 202

;

SELECT * FROM MATCHES WHERE TEAMNO = 202



Example 23.27: 



CREATE PROCEDURE NEW_TEAM_AND_MATCHES4 ();

BEGIN ATOMIC

   INSERT INTO TEAMS VALUES (203, 27, 'first');

   BEGIN ATOMIC

      INSERT INTO MATCHES VALUES (202, 203, 8, 1, 3);

      INSERT INTO MATCHES VALUES (202, 203, 112, 3, 2);

   END;

END

;

CALL NEW_TEAM_AND_MATCHES4

;

SELECT * FROM TEAMS WHERE TEAMNO = 202

;

SELECT * FROM MATCHES WHERE TEAMNO = 202



Example 23.28: 



CREATE PROCEDURE GIVE_ROWS

   (IN :PAR1 INTEGER)

   RETURNS(PLAYERNO INTEGER, NAME CHAR(15));

BEGIN

   SELECT   PLAYERNO, NAME

   FROM     PLAYERS

   WHERE    PLAYERNO >= :PAR1;

END

;

CALL GIVE_ROWS(100)



Example 23.29: 



CREATE PROCEDURE GIVE_ROWS2()

   RETURNS(COL1 VARCHAR(15));

BEGIN

   SELECT   'first row';

   SELECT   'second row';

END

;

CALL GIVE_ROWS2



Example 23.30: 



CREATE PROCEDURE GIVE_ROWS3()

   RETURNS(COL1 VARCHAR(15));

BEGIN

   CREATE TABLE #TEMPORARY (COL1 VARCHAR(15));

   INSERT INTO #TEMPORARY SELECT 'first row';

   INSERT INTO #TEMPORARY SELECT 'second row';

   SELECT * FROM #TEMPORARY;

END

;

CALL GIVE_ROWS3



Example 23.31: 



CREATE PROCEDURE NUMBER_OF_PLAYERS();

BEGIN

   DECLARE :ONE_PLAYERNO INTEGER;

   DECLARE :NUMBER INTEGER;

   DECLARE C_PLAYERS CURSOR FOR

      SELECT  PLAYERNO

      FROM    PLAYERS;

   SET :NUMBER = 0;

   OPEN C_PLAYERS;

   FETCH NEXT FROM C_PLAYERS INTO :ONE_PLAYERNO;

   WHILE SQLSTATE = '00000' DO

      SET :NUMBER = :NUMBER + 1;

      FETCH NEXT FROM C_PLAYERS INTO :ONE_PLAYERNO;

   END WHILE;

   CLOSE C_PLAYERS;

   CALL PRINTTEXT('NUMBER',:NUMBER);

END

;

CALL NUMBER_OF_PLAYERS()

;

CALL SHOWTEXT('NUMBER')



Example 23.32: 



CREATE PROCEDURE DELETE_OLDER_THAN_30 ();

BEGIN

   DECLARE :AGE INTEGER;

   DECLARE :PLAYERNO INTEGER;

   DECLARE :YEARS INTEGER;

   DECLARE :MONTHS INTEGER; 

   DECLARE :DAYS INTEGER;

   DECLARE :BIRTH_DATE DATE;

   DECLARE C_PLAYERS CURSOR FOR

      SELECT PLAYERNO, BIRTH_DATE

      FROM   PLAYERS;

   OPEN C_PLAYERS;

   FETCH NEXT FROM C_PLAYERS INTO :PLAYERNO, :BIRTH_DATE;

   WHILE SQLSTATE = '00000' DO

      IF DATEDIFF(YEAR, :BIRTH_DATE,CURRENT_DATE()) > 30 THEN

         DELETE FROM PENALTIES WHERE PLAYERNO = :PLAYERNO;

         CALL PRINTTEXT('OLDER30', 'Player removed '+

            CAST(:PLAYERNO AS VARCHAR(4)));

      END IF;

      FETCH NEXT FROM C_PLAYERS INTO :PLAYERNO, :BIRTH_DATE;

   END WHILE;

   CLOSE C_PLAYERS;

END



Example 23.33: 



CREATE PROCEDURE TOP_THREE

   (IN  :IN_PLAYERNO INTEGER);

BEGIN

   DECLARE :OK CHAR(1);

   DECLARE :PLAYERNO INTEGER;

   DECLARE :BALANCE INTEGER;

   DECLARE :SEQNO INTEGER;

   DECLARE BALANCE_PLAYERS CURSOR FOR

      SELECT   PLAYERNO, SUM(WON) - SUM(LOST)

      FROM     MATCHES

      GROUP BY PLAYERNO

      ORDER BY 2;

   SET :SEQNO = 0;

   SET :OK = 'N';

   OPEN BALANCE_PLAYERS;

   FETCH NEXT FROM BALANCE_PLAYERS INTO :PLAYERNO, :BALANCE;

   WHILE SQLSTATE = '00000' AND :SEQNO < 3 AND :OK = 'N' DO

      SET :SEQNO = :SEQNO + 1;

      IF :PLAYERNO = :IN_PLAYERNO THEN

         SET :OK = 'Y';

      END IF;

      FETCH NEXT FROM BALANCE_PLAYERS INTO :PLAYERNO, :BALANCE;

   END WHILE;

   CLOSE BALANCE_PLAYERS;

   CALL PRINTTEXT('TOP_THREE', :OK);

END

;

CALL TOP_THREE(6)

;

CALL SHOWTEXT('TOP_THREE')



Example 23.34: 



CREATE PROCEDURE NUMBER_PENALTIES

   (IN  :IN_PLAYERNO INTEGER);

BEGIN

   DECLARE :PLAYERNO INTEGER;

   DECLARE :NUMBEROF INTEGER;

   DECLARE C_PLAYERS CURSOR FOR

      SELECT PLAYERNO

      FROM   PENALTIES

      WHERE  PLAYERNO = :IN_PLAYERNO;

   SET :NUMBEROF = 0;

   OPEN C_PLAYERS;

   FETCH NEXT FROM C_PLAYERS INTO :PLAYERNO;

   WHILE SQLSTATE = '00000'  DO

      SET :NUMBEROF = :NUMBEROF + 1;

      FETCH NEXT FROM C_PLAYERS INTO :PLAYERNO;

   END WHILE;

   CLOSE C_PLAYERS;

   CALL PRINTTEXT('PENALTIES', :NUMBEROF);

END

;

CALL NUMBER_PENALTIES(44)

;

CALL SHOWTEXT('PENALTIES')



Example 23.35: 



CREATE PROCEDURE UPDATE_PLAYER_27 ();

BEGIN

   DECLARE :PNO INTEGER;

   DECLARE :WON INTEGER;

   DECLARE C_MAT CURSOR FOR

      SELECT  PLAYERNO, WON

      FROM    MATCHES

      WHERE   TEAMNO = 1

      FOR     UPDATE;

   OPEN C_MAT;

   FETCH NEXT FROM C_MAT INTO :PNO, :WON;

   WHILE SQLSTATE = 0 DO

      IF :PNO = 27 THEN

         UPDATE   MATCHES

         SET      WON = WON + 1

         WHERE    CURRENT OF C_MAT;

      END IF;

      FETCH NEXT FROM C_MAT INTO :PNO, :WON;

   END WHILE;

   CLOSE C_MAT;

END



Example 23.36: 



CREATE PROCEDURE DELETE_PENALTIES() AS

BEGIN

   DECLARE :AMOUNT DECIMAL(7,2);

   DECLARE C_PENALTIES CURSOR FOR

      SELECT  AMOUNT

      FROM    PENALTIES

      FOR     UPDATE;

   OPEN C_PENALTIES;

   FETCH NEXT FROM C_PENALTIES INTO :AMOUNT;

   WHILE SQLSTATE = 0 DO

      IF :AMOUNT> 30 THEN

         DELETE

         FROM     PENALTIES

         WHERE    CURRENT OF C_PENALTIES;

      END IF;

      FETCH NEXT FROM C_PENALTIES INTO :AMOUNT;

   END WHILE;

   CLOSE C_PENALTIES;

END



Example 23.37: 



CREATE PROCEDURE NUMBER_OF_PLAYERS(

   OUT :NUMBER INTEGER)

   WITH EXECUTE AS 'MASTER';

BEGIN

   SELECT COUNT(*)

   INTO   :NUMBER

   FROM   PLAYERS;

END



Example 23.38: 



CREATE PROCEDURE PROC_NO_HANDLER()

   WITH EXECUTE AS 'MASTER';

BEGIN

   DECLARE:NUMBER INTEGER;

   CALL PRINTTEXT('NO_HANDLER', 'The procedure has started.');

   SELECT COUNT(*)

   INTO   :NUMBER

   FROM   PLAYERSX;

   CALL PRINTTEXT('NO_HANDLER', 'The procedure has stopped.');

END

;

CALL PROC_NO_HANDLER()

;

CALL SHOWTEXT('NO_HANDLER')

;

CREATE PROCEDURE PROC_WITH_HANDLER()

   WITH EXECUTE AS 'MASTER',

        DEFAULT HANDLER;

BEGIN

   DECLARE:NUMBER INTEGER;

   CALL PRINTTEXT('WITH_HAND', 'The procedure has started.');

   SELECT COUNT(*)

   INTO   :NUMBER

   FROM   PLAYERSX;

   CALL PRINTTEXT('WITH_HAND', 'The procedure has stopped.');

END

;

CALL PROC_WITH_HANDLER()

;

CALL SHOWTEXT('WITH_HAND')



Example 23.39: 



SET CACHED_PROCEDURES 100



Example 23.40: 



SET PROCEDURES_CACHE 100



Example 23.41: 



DROP PROCEDURE DELETE_PLAYER



Example 23.42: 



GRANT EXECUTE

ON    PROCEDURE DELETE_MATCHES

TO    JOHN



Example 24.1: 



CREATE FUNCTION EUROS(:AMOUNT DECIMAL(7,2))

   RETURNS DECIMAL(7,2);

BEGIN

   RETURN :AMOUNT * 0.75;

END

;

SELECT   PAYMENTNO, AMOUNT, EUROS(AMOUNT)

FROM     PENALTIES

WHERE    PAYMENTNO <= 3



Example 24.2: 



CREATE FUNCTION NUMBER_OF_PLAYERS()

   RETURNS INTEGER;

BEGIN

   RETURN (SELECT COUNT(*) FROM PLAYERS);

END

;

SELECT NUMBER_OF_PLAYERS()



Example 24.3: 

 

CREATE FUNCTION NUMBER_OF_PENALTIES

   (IN :PLAYERNO INTEGER)

   RETURNS INTEGER;

BEGIN

   RETURN (SELECT   COUNT(*)

           FROM     PENALTIES

           WHERE    PLAYERNO = :PLAYERNO);

END

;

CREATE FUNCTION NUMBER_OF_MATCHES

   (IN :PLAYERNO INTEGER)

   RETURNS INTEGER;

BEGIN

   RETURN (SELECT   COUNT(*)

           FROM     MATCHES

           WHERE    PLAYERNO = :PLAYERNO);

END

;

SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

WHERE    NUMBER_OF_PENALTIES(PLAYERNO) > NUMBER_OF_MATCHES(PLAYERNO)



Example 24.4: 



CREATE FUNCTION DAYS_IN_BETWEEN(

   IN :START_DATE DATE,

   IN :END_DATE DATE)

RETURNS INTEGER;

BEGIN

   DECLARE :DAYS INTEGER;

   DECLARE :NEXT_DATE DATE;

   DECLARE :PREVIOUS_DATE DATE;

   SET :DAYS = 0;

   SET :NEXT_DATE = DATEADD(DAY, 1, :START_DATE);

   WHILE :NEXT_DATE <= :END_DATE DO

      SET :DAYS = :DAYS + 1;

      SET :NEXT_DATE = :NEXT_DATE;

      SET :NEXT_DATE = DATEADD(DAY, 1, :NEXT_DATE);

   END WHILE;

   RETURN :DAYS;

END

;

SELECT DAYS_IN_BETWEEN('2009-12-01','2010-12-01')



Example 24.5: 



CREATE FUNCTION OVERLAP_BETWEEN_PERIODS

   (IN :PERIOD1_START TIMESTAMP,

    IN :PERIOD1_END TIMESTAMP,

    IN :PERIOD2_START TIMESTAMP,

    IN :PERIOD2_END TIMESTAMP)

RETURNS CHAR(1);

BEGIN

   DECLARE :ANSWER CHAR(1);

   DECLARE :TEMPORARY_DATE TIMESTAMP;

   IF :PERIOD1_START > :PERIOD1_END THEN

      SET :TEMPORARY_DATE = :PERIOD1_START;

      SET :PERIOD1_START = :PERIOD1_END;

      SET :PERIOD1_END = :TEMPORARY_DATE;

   END IF;

   IF :PERIOD2_START > :PERIOD2_END THEN

      SET :TEMPORARY_DATE = :PERIOD2_START;

      SET :PERIOD2_START = :PERIOD2_END;

      SET :PERIOD2_END = :TEMPORARY_DATE;

   END IF;

   IF :PERIOD1_END < :PERIOD2_START OR :PERIOD2_END < :PERIOD1_START THEN

      SET :ANSWER = 'N';

   ELSE

      SET :ANSWER = 'Y';

   END IF;

   RETURN :ANSWER;

END

;

SELECT OVERLAP_BETWEEN_PERIODS('2009-01-01','2009-03-31','2009-02-01','2009-04-30'),

       OVERLAP_BETWEEN_PERIODS('2009-03-31','2009-01-01','2009-02-01','2009-04-30'),

       OVERLAP_BETWEEN_PERIODS('2009-01-01','2009-03-31','2009-04-01','2009-04-30'),

       OVERLAP_BETWEEN_PERIODS('2009-01-01','2009-03-31','2009-02-01','2009-02-15')



Example 24.6: 



SELECT   *

FROM     COMMITTEE_MEMBERS

WHERE    OVERLAP_BETWEEN_PERIODS(BEGIN_DATE, END_DATE, '1991-06-30', '1992-06-30') = 'Y'

ORDER BY 1, 2



Example 24.7: 



DROP FUNCTION GET_NUMBER_OF_PLAYERS



Example 25.1: 



CREATE TABLE CHANGES (

   CHA_SEQNO          IDENTITY,

   CHA_USER           CHAR(30) NOT NULL,

   CHA_TIME           TIMESTAMP NOT NULL,

   CHA_PLAYERNO       INTEGER NOT NULL,

   CHA_TYPE           CHAR(1) NOT NULL,

   CHA_LEAGUENO_OLD   CHAR(4),

   CHA_LEAGUENO_NEW   CHAR(4))



Example 25.2: 



CREATE TRIGGER INSERT_PLAYERS

   AFTER

   INSERT ON PLAYERS FOR EACH ROW

   INSERT INTO CHANGES

      (CHA_USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW)

   VALUES (USER, CURRENT_TIMESTAMP(), NEW.PLAYERNO, 'I', NEW.LEAGUENO);

;

INSERT INTO PLAYERS VALUES

   (2000, 'Brown', 'C', '1959-06-25', 'M', 1978, 'Moscow Avenue', '80', '5674BF',

    'Stratford', '070-346734', '6377')

;

SELECT   *

FROM     CHANGES

WHERE    CHA_USER = USER

AND      CHA_PLAYERNO = 2000

AND      CHA_TIME = 

        (SELECT   MAX(CHA_TIME)

         FROM     CHANGES

         WHERE    CHA_USER = USER

         AND      CHA_PLAYERNO = 2000)



Example 25.3: 



CREATE TRIGGER DELETE_PLAYERS

   AFTER

   DELETE ON PLAYERS FOR EACH ROW

   INSERT INTO CHANGES (CHA_USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW)

   VALUES (USER, CURRENT_TIMESTAMP(), OLD.PLAYERNO, 'D', NULL, OLD.LEAGUENO);



Example 25.4: 



DELETE

FROM     PLAYERS

WHERE    PLAYERNO = 2000

;

SELECT   *

FROM     CHANGES

WHERE    CHA_USER = USER

AND      CHA_PLAYERNO = 2000

AND      CHA_TIME = 

        (SELECT   MAX(CHA_TIME)

         FROM     CHANGES

         WHERE    CHA_USER = USER

         AND      CHA_PLAYERNO = 2000)



Example 25.5: 



CREATE TRIGGER UPDATE_PLAYERS

   AFTER

   UPDATE ON PLAYERS FOR EACH ROW

   INSERT INTO CHANGES

      (CHA_USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)

   VALUES (USER, CURRENT_TIMESTAMP(), OLD.PLAYERNO, 'U', NEW.LEAGUENO, OLD.LEAGUENO);



Example 25.6: 



UPDATE   PLAYERS

SET      LEAGUENO = '4444'

WHERE    PLAYERNO = 6

;

SELECT   *

FROM     CHANGES

WHERE    CHA_USER = USER

AND      CHA_PLAYERNO = 6

AND      CHA_TIME = 

        (SELECT   MAX(CHA_TIME)

         FROM     CHANGES

         WHERE    CHA_USER = USER

         AND      CHA_PLAYERNO = 6)



Example 25.7: 



CREATE TABLE PLAYERS_MAT (

   PLAYERNO            INTEGER NOT NULL PRIMARY KEY,

   NUMBER_OF_MATCHES   INTEGER NOT NULL)

;

INSERT INTO PLAYERS_MAT (PLAYERNO, NUMBER_OF_MATCHES)

SELECT   PLAYERNO, 

        (SELECT   COUNT(*)

         FROM     MATCHES AS M

         WHERE    P.PLAYERNO = M.PLAYERNO)

FROM     PLAYERS AS P



Example 25.8: 



CREATE TRIGGER INSERT_PLAYERS_MAT

   AFTER INSERT ON PLAYERS FOR EACH ROW

  INSERT INTO PLAYERS_MAT VALUES(NEW.PLAYERNO, 0);



Example 25.9: 



INSERT INTO PLAYERS VALUES

   (16, 'Parmenter', 'S', '1964-06-25', 'F', 1977, 'Haseltine Lane',

   '80', '1234KK', 'Stratford', '070-476537', '8467')

;

SELECT   *

FROM     PLAYERS_MAT

WHERE    PLAYERNO = 16



Example 25.10: 



CREATE TRIGGER DELETE_PLAYERS_MAT

   AFTER DELETE ON PLAYERS FOR EACH ROW

   DELETE FROM PLAYERS_MAT WHERE PLAYERNO = OLD.PLAYERNO;



Example 25.11: 



CREATE TRIGGER INSERT_MATCHES

   AFTER INSERT ON MATCHES FOR EACH ROW

   UPDATE PLAYERS_MAT

   SET    NUMBER_OF_MATCHES = NUMBER_OF_MATCHES + 1

   WHERE  PLAYERNO = NEW.PLAYERNO;



Example 25.12: 



CREATE TRIGGER DELETE_MATCHES

   AFTER DELETE ON MATCHES FOR EACH ROW

   UPDATE PLAYERS_MAT

   SET    NUMBER_OF_MATCHES = NUMBER_OF_MATCHES - 1

   WHERE  PLAYERNO = OLD.PLAYERNO;



Example 25.13: 



CREATE TRIGGER SUM_PENALTIES_INSERT

   AFTER INSERT ON PENALTIES FOR EACH ROW

   BEGIN

      DECLARE :TOTAL DECIMAL(8,2);

      SELECT   SUM(AMOUNT)

      INTO     :TOTAL

      FROM     PENALTIES

      WHERE    PLAYERNO = NEW.PLAYERNO;

      UPDATE   PLAYERS

      SET      SUM_PENALTIES = :TOTAL

      WHERE    PLAYERNO = NEW.PLAYERNO;

   END

;

CREATE TRIGGER SUM_PENALTIES_UPDATE

   AFTER UPDATE ON PENALTIES FOR EACH ROW

   BEGIN

      DECLARE :TOTAL DECIMAL(8,2);

      SELECT   SUM(AMOUNT)

      INTO     :TOTAL

      FROM     PENALTIES

      WHERE    PLAYERNO = NEW.PLAYERNO;

      UPDATE   PLAYERS

      SET      SUM_PENALTIES = :TOTAL

      WHERE    PLAYERNO = NEW.PLAYERNO;

   END

;

CREATE TRIGGER SUM_PENALTIES_DELETE

   AFTER DELETE ON PENALTIES FOR EACH ROW

   BEGIN

      DECLARE :TOTAL DECIMAL(8,2);            

      SELECT   SUM(AMOUNT)

      INTO     :TOTAL

      FROM     PENALTIES

      WHERE    PLAYERNO = OLD.PLAYERNO;

      UPDATE   PLAYERS

      SET      SUM_PENALTIES = :TOTAL

      WHERE    PLAYERNO = OLD.PLAYERNO;

   END

;

UPDATE   PLAYERS

SET      SUM_PENALTIES = (SELECT   SUM(AMOUNT)

                          FROM     PENALTIES

                          WHERE    PLAYERNO = NEW.PLAYERNO)

WHERE    PLAYERNO = NEW.PLAYERNO



Example 25.14: 



CREATE TRIGGER BORN_VS_JOINED_INSERT

   BEFORE INSERT ON PLAYERS FOR EACH ROW

   BEGIN

      IF YEAR(NEW.BIRTH_DATE) >= NEW.JOINED THEN

         ROLLBACK WORK;

      END IF;

   END

; 

CREATE TRIGGER BORN_VS_JOINED_UPDATE

   BEFORE UPDATE ON PLAYERS FOR EACH ROW

   BEGIN

      IF YEAR(NEW.BIRTH_DATE) >= NEW.JOINED THEN

         ROLLBACK WORK;

      END IF;

   END



Example 25.15: 



CREATE TRIGGER FOREIGN_KEY1

   BEFORE INSERT OF PENALTIES FOR EACH ROW

   BEGIN

      DECLARE :NUMBER INTEGER;

      SELECT  COUNT(*)

      INTO    :NUMBER

      FROM    PLAYERS

      WHERE   PLAYERNO = NEW.PLAYERNO;

      IF :NUMBER = 0 THEN

         ROLLBACK WORK;

      END IF;

   END

;

CREATE TRIGGER FOREIGN_KEY2

   BEFORE UPDATE OF PENALTIES FOR EACH ROW

   BEGIN

      DECLARE :NUMBER INTEGER;

      SELECT  COUNT(*)

      INTO    :NUMBER

      FROM    PLAYERS

      WHERE   PLAYERNO = NEW.PLAYERNO;

      IF :NUMBER = 0 THEN

         ROLLBACK WORK;

      END IF;

   END

;

CREATE TRIGGER FOREIGN_KEY3

   BEFORE DELETE OF PLAYERS FOR EACH ROW

   BEGIN

      DECLARE :NUMBER INTEGER;

      SELECT  COUNT(*)

      INTO    :NUMBER

      FROM    PENALTIES

      WHERE   PLAYERNO = NEW.PLAYERNO;

      IF :NUMBER > 0 THEN

         ROLLBACK WORK;

      END IF;

   END

;

CREATE TRIGGER FOREIGN_KEY4

   BEFORE UPDATE OF PLAYERS FOR EACH ROW

   BEGIN

      DECLARE :NUMBER INTEGER;

      SELECT  COUNT(*)

      INTO    :NUMBER

      FROM    PENALTIES

      WHERE   PLAYERNO = NEW.PLAYERNO;

      IF :NUMBER > 0 THEN

         ROLLBACK WORK;

      END IF;

   END



Example 25.16: 



DROP TRIGGER BORN_VS_JOINED



Example 26.1: 



DELETE

FROM     PENALTIES

WHERE    PLAYERNO = 44

;

SELECT   *

FROM     PENALTIES

;

ROLLBACK WORK

;

COMMIT



Example 26.3: 



DELETE FROM PLAYERS WHERE PLAYERNO = 6

;

DELETE FROM PENALTIES WHERE PLAYERNO = 6

;

DELETE FROM MATCHES WHERE PLAYERNO = 6

;

DELETE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 6

;

UPDATE TEAMS SET PLAYERNO = 83 WHERE PLAYERNO = 6



Example 26.5: 



CREATE PROCEDURE NEW_TEAM ();

BEGIN

   INSERT INTO TEAMS VALUES (100,27,'first');

END

;

INSERT INTO TEAMS VALUES (200,27,'first')

;

CALL NEW_TEAM()

;

ROLLBACK WORK



Example 26.6: 



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + 25

WHERE    PAYMENTNO = 4

;

SELECT   *

FROM     PENALTIES

WHERE    PAYMENTNO = 4

	

Example 26.7: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

;

UPDATE   PLAYERS

SET      TOWN = 'Eltham'

WHERE    PLAYERNO = 7

;

SELECT   PLAYERNO, NAME, INITIALS, STREET, HOUSENO, POSTCODE, TOWN

FROM     PLAYERS

WHERE    PLAYERNO IN (6, 83, 2, 7, 57, 39, 100)



Example 26.8: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



Example 26.9: 



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + 25

WHERE    PAYMENTNO = 4

;

UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + 30

WHERE    PAYMENTNO = 4