Example 4.1: 



GRANT DB_ADMIN

ON    DATABASE TENNIS

TO    BOOKSQL





Example 4.2: 

   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.3: 



INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

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

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

;

INSERT INTO PLAYERS VALUES (

112, 'Bailey', 'IP', DATE '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, DATE '1980-12-08',100)

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;



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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;

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

;



Example 4.4: 



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    TOWN = 'Stratford'

ORDER BY NAME



Example 4.5: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    JOINED > 1980

AND      TOWN = 'Stratford'

ORDER BY PLAYERNO



Example 4.6: 



SELECT   *

FROM     PENALTIES



Example 4.7: 



SELECT   33 * 121



Example 4.8: 



UPDATE   PENALTIES

SET      AMOUNT = 200

WHERE    PLAYERNO = 44



SELECT   PLAYERNO, AMOUNT

FROM     PENALTIES

WHERE    PLAYERNO = 44



Example 4.9: 



DELETE

FROM     PENALTIES

WHERE    AMOUNT > 100



Example 4.10: 



CREATE INDEX PENALTIES_AMOUNT

   ON PENALTIES (AMOUNT)



Example 4.11: 



CREATE   VIEW NUMBER_SETS (MATCHNO, DIFFERENCE) AS

SELECT   MATCHNO, ABS(WON - LOST)

FROM     MATCHES



SELECT   *

FROM     NUMBER_SETS



Example 4.12: 



GRANT   SELECT

ON      PLAYERS

TO      DIANE

 

GRANT   SELECT, UPDATE

ON      PLAYERS

TO      PAUL



GRANT   SELECT, UPDATE

ON      TEAMS

TO      PAUL



SELECT   *

FROM     BOOKSQL.TEAMS



Example 4.13: 



DROP TABLE COMMITTEE_MEMBERS



Example 4.14: 



DROP VIEW NUMBER_SETS



Example 4.15: 



DROP INDEX PENALTIES_AMOUNT



Example 4.16: 



These catalog views can be created in the TENNIS database:



CREATE   VIEW USERS (USER_NAME) AS

SELECT   DISTINCT TABLE_OWNER

FROM     IITABLES

WHERE    TABLE_OWNER <> '$ingres'

;

CREATE   VIEW TABLES

        (TABLE_CREATOR, TABLE_NAME, CREATE_TIMESTAMP, COMMENT) AS

SELECT   UPPER(T.TABLE_OWNER), UPPER(T.TABLE_NAME), UPPER(T.CREATE_DATE), C.LONG_REMARK

FROM     IITABLES AS T LEFT OUTER JOIN IIDB_COMMENTS AS C

         ON (T.TABLE_NAME = C.OBJECT_NAME

         AND T.TABLE_OWNER = C.OBJECT_OWNER

         AND C.OBJECT_TYPE = 'T')

WHERE    T.TABLE_OWNER <> '$ingres'

AND      T.TABLE_TYPE = 'T'

;

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

         CHAR_LENGTH, PRECISION, SCALE, NULLABLE, COMMENT) AS

SELECT   UPPER(C.TABLE_OWNER), UPPER(C.TABLE_NAME), 

         UPPER(C.COLUMN_NAME), C.COLUMN_SEQUENCE,

         CASE 

            WHEN C.COLUMN_DATATYPE = 'INGRESDATE' THEN 'DATE'

            WHEN C.COLUMN_DATATYPE = 'ANSIDATE' THEN 'DATE'

            WHEN C.COLUMN_DATATYPE = 'TIME WITH LOCAL TIME ZONE' THEN 'TIME'

            WHEN C.COLUMN_DATATYPE = 'TIME WITH TIME ZONE' THEN 'TIME'

            WHEN C.COLUMN_DATATYPE = 'TIME WITHOUT TIME ZONE' THEN 'TIME'

            WHEN C.COLUMN_DATATYPE = 'CHAR' THEN 'CHARACTER'

            WHEN C.COLUMN_DATATYPE = 'INTEGER' AND 

               C.COLUMN_LENGTH = 1 THEN 'TINYINT'

            WHEN C.COLUMN_DATATYPE = 'INTEGER' AND

               C.COLUMN_LENGTH = 2 THEN 'SMALLINT'

            WHEN C.COLUMN_DATATYPE = 'INTEGER' AND

               C.COLUMN_LENGTH = 4 THEN 'INTEGER'

            WHEN C.COLUMN_DATATYPE = 'INTEGER' AND

               C.COLUMN_LENGTH = 8 THEN 'BIGINT'

            ELSE C.COLUMN_DATATYPE

         END,

         C.COLUMN_LENGTH, C.COLUMN_LENGTH, C.COLUMN_SCALE,

         CASE

            WHEN C.COLUMN_NULLS = 'Y' THEN 'YES'

            ELSE 'NO'

         END, S.LONG_REMARK

FROM     IIOCOLUMNS AS C INNER JOIN IITABLES AS T

         ON (C.TABLE_OWNER = T.TABLE_OWNER

         AND C.TABLE_NAME = T.TABLE_NAME

         AND T.TABLE_TYPE = 'T')

         LEFT OUTER JOIN IIDB_SUBCOMMENTS AS S

         ON (C.TABLE_OWNER = S.OBJECT_OWNER

         AND C.TABLE_NAME = S.OBJECT_NAME

         AND C.COLUMN_NAME = S.SUBOBJECT_NAME)

WHERE    C.TABLE_OWNER <> '$ingres'

CREATE   VIEW VIEWS (VIEW_CREATOR, VIEW_NAME, CREATE_TIMESTAMP,

         WITHCHECKOPT, IS_UPDATABLE, VIEWFORMULA, COMMENT) AS

SELECT   UPPER(V.TABLE_OWNER), UPPER(V.TABLE_NAME), T.CREATE_DATE,

         CASE

            WHEN V.CHECK_OPTION = 'Y' THEN 'YES'

            ELSE 'NO'

         END,

         'MAYBE', V.TEXT_SEGMENT, C.LONG_REMARK

FROM     IIVIEWS AS V INNER JOIN IITABLES AS T

         ON (V.TABLE_OWNER = T.TABLE_OWNER

         AND V.TABLE_NAME = T.TABLE_NAME)

         LEFT OUTER JOIN IIDB_COMMENTS AS C

         ON (V.TABLE_OWNER = C.OBJECT_OWNER

         AND V.TABLE_NAME = C.OBJECT_NAME)

WHERE    V.TEXT_SEQUENCE = 1

AND      V.TABLE_OWNER <> '$ingres'

;

CREATE   VIEW INDEXES (INDEX_CREATOR, INDEX_NAME, CREATE_TIMESTAMP,

         TABLE_CREATOR, TABLE_NAME, UNIQUE_ID, INDEX_TYPE) AS

SELECT   UPPER(INDEX_OWNER), UPPER(INDEX_NAME), 

         CREATE_DATE, UPPER(BASE_OWNER), UPPER(BASE_NAME),

         CASE

            WHEN UNIQUE_RULE = 'U' THEN 'YES'

            ELSE 'NO'

         END,

         STORAGE_STRUCTURE

FROM     IIINDEXES

WHERE    INDEX_OWNER <> '$ingres'



CREATE   VIEW COLUMNS_IN_INDEX (INDEX_CREATOR, INDEX_NAME,

         TABLE_CREATOR, TABLE_NAME, COLUMN_NAME, COLUMN_SEQ, ORDERING) AS

SELECT   UPPER(C.INDEX_OWNER), UPPER(C.INDEX_NAME),

         UPPER(I.BASE_OWNER), UPPER(I.BASE_NAME),

         UPPER(C.COLUMN_NAME), C.KEY_SEQUENCE,

         CASE

            WHEN C.SORT_DIRECTION = 'A' THEN 'ASC'

            WHEN C.SORT_DIRECTION = 'D' THEN 'DESC'

            ELSE 'OTHER'

         END

FROM     IIINDEX_COLUMNS AS C, IIINDEXES AS I

WHERE    C.INDEX_OWNER = I.INDEX_OWNER

AND      C.INDEX_NAME = I.INDEX_NAME

AND      C.INDEX_OWNER <> '$ingres'

;

These catalog views have to be created in the IIDBDB database:



CREATE   VIEW USER_AUTHS

        (GRANTOR, GRANTEE, PRIVILEGE, WITHGRANTOPTION) AS

SELECT   'UNKNOWN', UPPER(USER_NAME), 'CREATEDB', 'NO'

FROM     IIUSERS

WHERE    CREATEDB = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'TRACE', 'NO'

FROM     IIUSERS

WHERE    TRACE = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'AUDIT_ALL', 'NO'

FROM     IIUSERS

WHERE    AUDIT_ALL = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'SECURITY', 'NO'

FROM     IIUSERS

WHERE    SECURITY = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'MAINTAIN_LOCATIONS', 'NO'

FROM     IIUSERS

WHERE    MAINTAIN_LOCATIONS= 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'OPERATOR', 'NO'

FROM     IIUSERS

WHERE    OPERATOR = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'MAINTAIN_USERS', 'NO'

FROM     IIUSERS

WHERE    MAINTAIN_USERS = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'MAINTAIN_AUDIT', 'NO'

FROM     IIUSERS

WHERE    MAINTAIN_AUDIT = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'AUDITOR', 'NO'

FROM     IIUSERS

WHERE    AUDITOR = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'AUDIT_QUERY_TEXT', 'NO'

FROM     IIUSERS

WHERE    AUDIT_QUERY_TEXT = 'Y'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOCREATEDB', 'NO'

FROM     IIUSERS

WHERE    CREATEDB = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOTRACE', 'NO'

FROM     IIUSERS

WHERE    TRACE = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOAUDIT_ALL', 'NO'

FROM     IIUSERS

WHERE    AUDIT_ALL = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOSECURITY', 'NO'

FROM     IIUSERS

WHERE    SECURITY = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOMAINTAIN_LOCATIONS', 'NO'

FROM     IIUSERS

WHERE    MAINTAIN_LOCATIONS= 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOOPERATOR', 'NO'

FROM     IIUSERS

WHERE    OPERATOR = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOMAINTAIN_USERS', 'NO'

FROM     IIUSERS

WHERE    MAINTAIN_USERS = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOMAINTAIN_AUDIT', 'NO'

FROM     IIUSERS

WHERE    MAINTAIN_AUDIT = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOAUDITOR', 'NO'

FROM     IIUSERS

WHERE    AUDITOR = 'N'

UNION

SELECT   'UNKNOWN', UPPER(USER_NAME), 'NOAUDIT_QUERY_TEXT', 'NO'

FROM     IIUSERS

WHERE    AUDIT_QUERY_TEXT = 'N'

;

CREATE   VIEW DATABASE_AUTHS

        (GRANTOR, GRANTEE, DATABASE_NAME, PRIVILEGE, WITHGRANTOPTION) AS

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'CREATE_TABLE', 'NO'

FROM     IIDBPRIVILEGES

WHERE    CR_TAB = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'CREATE_PROCEDURE', 'NO'

FROM     IIDBPRIVILEGES

WHERE    CR_PROC = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'LOCKMODE', 'NO'

FROM     IIDBPRIVILEGES

WHERE    LK_MODE = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'ACCESS', 'NO'

FROM     IIDBPRIVILEGES

WHERE    DB_ACCESS = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'UPDATE_SYSCAT', 'NO'

FROM     IIDBPRIVILEGES

WHERE    UP_SYSCAT = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'DB_ADMIN', 'NO'

FROM     IIDBPRIVILEGES

WHERE    DB_ADMIN = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'SELECT_SYSCAT', 'NO'

FROM     IIDBPRIVILEGES

WHERE    SEL_SYSCAT = 'Y'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NOCREATE_TABLE', 'NO'

FROM     IIDBPRIVILEGES

WHERE    CR_TAB = 'N'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NOCREATE_PROCEDURE', 'NO'

FROM     IIDBPRIVILEGES

WHERE    CR_PROC = 'N'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NOLOCKMODE', 'NO'

FROM     IIDBPRIVILEGES

WHERE    LK_MODE = 'N'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NOACCESS', 'NO'

FROM     IIDBPRIVILEGES

WHERE    DB_ACCESS = 'N'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NOUPDATE_SYSCAT', 'NO'

FROM     IIDBPRIVILEGES

WHERE    UP_SYSCAT = 'N'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NODB_ADMIN', 'NO'

FROM     IIDBPRIVILEGES

WHERE    DB_ADMIN = 'N'

UNION

SELECT   'UNKNOWN',

         CASE WHEN GRANTEE_NAME = '' THEN 'PUBLIC'

            ELSE UPPER(GRANTEE_NAME) END,

         DATABASE_NAME, 'NOSELECT_SYSCAT', 'NO'

FROM     IIDBPRIVILEGES

WHERE    SEL_SYSCAT = 'N'

;

CREATE   VIEW TABLE_AUTHS

        (GRANTOR, GRANTEE, TABLE_CREATOR, TABLE_NAME,

         GRANT_DATE, PRIVILEGE, WITHGRANTOPTION) AS

SELECT   UPPER(PERMIT_GRANTOR),

         CASE

            WHEN PERMIT_USER = '' THEN 'PUBLIC'

            ELSE UPPER(PERMIT_USER)

         END,

         UPPER(OBJECT_OWNER), UPPER(OBJECT_NAME),

         CREATE_DATE,

         CASE

            WHEN LOCATE(TEXT_SEGMENT,'select') <=

                 SIZE(TEXT_SEGMENT) THEN 'SELECT'

            WHEN LOCATE(TEXT_SEGMENT,'insert') <=

                 SIZE(TEXT_SEGMENT) THEN 'INSERT'

            WHEN LOCATE(TEXT_SEGMENT,'update') <=

                 SIZE(TEXT_SEGMENT) THEN 'UPDATE'

            WHEN LOCATE(TEXT_SEGMENT,'delete') <=

                 SIZE(TEXT_SEGMENT) THEN 'DELETE'

            WHEN LOCATE(TEXT_SEGMENT,'references') <=

                 SIZE(TEXT_SEGMENT) THEN 'REFERENCES'

            WHEN LOCATE(TEXT_SEGMENT,'copy_into') <=

                 SIZE(TEXT_SEGMENT) THEN 'COPY_INTO'

            WHEN LOCATE(TEXT_SEGMENT,'copy_from') <=

                 SIZE(TEXT_SEGMENT) THEN 'COPY_FROM'

            ELSE 'UNKNOWN'

         END,

         CASE

            WHEN LOCATE(TEXT_SEGMENT,'with grant option') >

                 SIZE(TEXT_SEGMENT) THEN 'NO'

            ELSE 'YES'

         END

FROM     IIPERMITS

WHERE    OBJECT_TYPE IN ('T','V')

AND      PERMIT_GRANTOR <> '$ingres'

;

CREATE   VIEW COLUMN_AUTHS 

        (GRANTOR, GRANTEE, TABLE_CREATOR, TABLE_NAME,

         COLUMN_NAME, GRANT_DATE, PRIVILEGE, 

         WITHGRANTOPTION) AS

SELECT   UPPER(P.PERMIT_GRANTOR), UPPER(P.PERMIT_USER), 

         UPPER(P.OBJECT_OWNER), UPPER(P.OBJECT_NAME), 

         UPPER(C.COLUMN_NAME), CREATE_DATE,

         CASE

            WHEN LOCATE(TEXT_SEGMENT,'update') <=

                 LENGTH(TEXT_SEGMENT) THEN 'UPDATE'

            WHEN LOCATE(TEXT_SEGMENT,'references') <=

                 LENGTH(TEXT_SEGMENT) THEN 'REFERENCES'

            ELSE 'UNKNOWN'

         END,

         CASE

            WHEN LOCATE(TEXT_SEGMENT,'with grant option') >

                 SIZE(TEXT_SEGMENT) THEN 'NO'

            ELSE 'YES'

         END

FROM     IIPERMITS AS P, IICOLUMNS AS C

WHERE    P.OBJECT_OWNER = C.TABLE_OWNER

AND      P.OBJECT_NAME = C.TABLE_NAME

AND      LOCATE(P.TEXT_SEGMENT,C.COLUMN_NAME) 

         <= LENGTH(P.TEXT_SEGMENT)

AND      LOCATE(P.TEXT_SEGMENT, 'excluding') 

         > LENGTH(P.TEXT_SEGMENT)

AND      P.PERMIT_GRANTOR <> '$ingres'

UNION

SELECT   UPPER(P.PERMIT_GRANTOR), UPPER(P.PERMIT_USER), 

         UPPER(P.OBJECT_OWNER), UPPER(P.OBJECT_NAME), 

         UPPER(C.COLUMN_NAME), CREATE_DATE,

         CASE

            WHEN LOCATE(TEXT_SEGMENT,'update') <=

                 LENGTH(TEXT_SEGMENT) THEN 'UPDATE'

            WHEN LOCATE(TEXT_SEGMENT,'references') <=

                 LENGTH(TEXT_SEGMENT) THEN 'REFERENCES'

            ELSE 'UNKNOWN'

         END,

         CASE

            WHEN LOCATE(TEXT_SEGMENT,'with grant option') >

                 SIZE(TEXT_SEGMENT) THEN 'NO'

            ELSE 'YES'

         END

FROM     IIPERMITS AS P, IICOLUMNS AS C

WHERE    P.OBJECT_OWNER = C.TABLE_OWNER

AND      P.OBJECT_NAME = C.TABLE_NAME

AND      LOCATE(P.TEXT_SEGMENT,C.COLUMN_NAME) 

         > LENGTH(P.TEXT_SEGMENT)

AND      LOCATE(P.TEXT_SEGMENT, 'excluding') 

         <= LENGTH(P.TEXT_SEGMENT)

AND      P.PERMIT_GRANTOR <> '$ingres'

AND      EXISTS

        (SELECT   *

         FROM     IIPERMITS AS P2

         WHERE    P2.OBJECT_OWNER = C.TABLE_OWNER



Example 4.17: 



SELECT   COLUMN_NAME, DATA_TYPE, COLUMN_NO

FROM     COLUMNS

WHERE    TABLE_NAME = 'PLAYERS'

AND      TABLE_CREATOR = 'BOOKSQL'

ORDER BY COLUMN_NO



Example 4.18: 



SELECT   INDEX_NAME

FROM     INDEXES

WHERE    TABLE_NAME = 'PENALTIES'

AND      TABLE_CREATOR = 'BOOKSQL'



Example 5.1: 



SELECT   INTERVAL '10' DAY,  INTERVAL '100' SECOND,

         INTERVAL '1' MONTH, INTERVAL '3' YEAR,

         INTERVAL '+3' YEAR, INTERVAL '-3' YEAR



Example 5.2: 



SELECT   INTERVAL '10-5' YEAR TO MONTH



Example 5.3: 



SELECT   INTERVAL '10 12:5:6' DAY TO SECOND



Example 5.4: 



SELECT   INTERVAL '10' DAY, '10 DAYS'



Example 5.5: 



SELECT   MATCHNO, WON - LOST

FROM     MATCHES

WHERE    WON = LOST + 2



Example 5.6: 



SELECT   TEAMNO, DIVISION

FROM     TEAMS



Example 5.7: 



SELECT   TEAMNO AS TEAM_NUMBER, DIVISION AS DIVISION_OF_TEAM

FROM     TEAMS



Example 5.8: 



SELECT   PAYMENTNO, AMOUNT * 100 AS CENTS

FROM     PENALTIES



Example 5.9: 



SELECT   MATCHNO AS PRIMKEY,

         80 AS EIGHTY,

         WON - LOST AS DIFFERENCE,

         TIME('23:59:59') AS ALMOST_MIDNIGHT,

         'TEXT' AS TEXT

FROM     MATCHES

WHERE    MATCHNO <= 4



Example 5.10: 



SELECT   PAYMENTNO, AMOUNT * 100 AS CENTS

FROM     PENALTIES

ORDER BY CENTS



Example 5.11: 



SELECT   *

FROM     USER_AUTHS

WHERE    GRANTEE = CURRENT_USER



Example 5.12: 



SELECT   CURRENT_USER



Example 5.13:



SELECT   *

FROM     PENALTIES

WHERE    PAYMENT_DATE = CURRENT_DATE



Example 5.14: 



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.15: 



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 DATE '1948-09-01' THEN 'Old Stratforder'

                  ELSE 'Young Stratforder' END

            WHEN 'Inglewood' THEN

               CASE BIRTH_DATE

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

                  ELSE 'Young Inglewooder' END

            ELSE 'Rest' 

         END AS TYPE

FROM     PLAYERS



Example 5.16: 



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.17: 



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.18: 



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

FROM     PLAYERS



Example 5.19: 



SELECT   PAYMENTNO, YEAR(PAYMENT_DATE) AS YEAR

FROM     PENALTIES

WHERE    YEAR(PAYMENT_DATE) > 1980



Example 5.20: 



SELECT   PLAYERNO,

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

FROM     PLAYERS

WHERE    LEFT(NAME, 1) = 'B'



Example 5.21: 



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

FROM     PLAYERS

WHERE    Town = 'Stratford'



Example 5.22: 



SELECT   PLAYERNO, WEEK(BIRTH_DATE) AS WEEKNUMBER,

         MONTH(BIRTH_DATE) AS MONTHNUMBER,

         DAY(BIRTH_DATE) AS DAYINMONTHNUMBER

FROM     PLAYERS

WHERE    PLAYERNO < 10



Example 5.23: 



SELECT   PLAYERNO, BIRTH_DATE,

         BIRTH_DATE + INTERVAL '7' DAY AS BIRTH_DATE_PLUS_7

FROM     PLAYERS

WHERE    DOW(BIRTH_DATE) = 'Sat'



Example 5.24: 



SELECT   PLAYERNO, BEGIN_DATE, END_DATE,

         INT2(SUBSTR(VARCHAR(END_DATE - BEGIN_DATE),1,

            POSITION(' ',VARCHAR(END_DATE - BEGIN_DATE))-1))

            AS NUMBEROFDAYS

FROM     COMMITTEE_MEMBERS

WHERE   (END_DATE - BEGIN_DATE) > '500 00:00:00'

OR      (END_DATE IS NULL AND (CURRENT_DATE - BEGIN_DATE) > '500 00:00:00')

ORDER BY PLAYERNO, BEGIN_DATE



Example 5.25: 



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT > 50



Example 5.26: 



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

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'



Example 5.27: 



UPDATE   PLAYERS

SET      LEAGUENO = NULL

WHERE    PLAYERNO = 2



Example 5.28: 



SELECT   TEAMNO, CAST(NULL AS CHAR)

FROM     TEAMS



Example 5.29: 



SELECT   MATCHNO, WON, LOST

FROM     MATCHES

WHERE    WON >= LOST * 2



Example 5.30: 



SELECT   PLAYERNO, TOWN || ' ' || RTRIM(STREET) || ' ' || HOUSENO AS ADDRESS

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



Example 5.31: 



SELECT   PAYMENTNO, PAYMENT_DATE, PAYMENT_DATE + INTERVAL '7' DAY AS DATE_PLUS_7

FROM     PENALTIES

WHERE    PAYMENTNO > 5



Example 5.32: 



SELECT   PAYMENTNO, PAYMENT_DATE

FROM     PENALTIES

WHERE    PAYMENT_DATE >= DATE '1982-12-25'

AND      PAYMENT_DATE <= DATE '1982-12-25' + INTERVAL '6' DAY



Example 5.33: 



SELECT   PAYMENTNO, PAYMENT_DATE,

         CAST(PAYMENT_DATE AS INGRESDATE) + '7 DAYS' AS PAY_DATE_PLUS_7

FROM     PENALTIES

WHERE    PAYMENTNO > 5



Example 5.34: 



CREATE TABLE MATCHES_SPECIAL (

   MATCHNO       INTEGER NOT NULL,

   TEAMNO        INTEGER NOT NULL,

   PLAYERNO      INTEGER NOT NULL,

   WON           SMALLINT NOT NULL,

   LOST          SMALLINT NOT NULL,

   START_DATE    DATE NOT NULL,

   START_TIME    TIME NOT NULL,

   END_TIME      TIME NOT NULL,

   PRIMARY KEY   (MATCHNO))

;

INSERT INTO MATCHES_SPECIAL VALUES 

   (1, 1, 6, 3, 1, DATE '2004-10-25', TIME '14:10:12', TIME '16:50:09')

;

INSERT INTO MATCHES_SPECIAL VALUES 

   (2, 1, 44, 3, 2, DATE '2004-10-25', TIME '17:00:00', TIME '17:55:48')



Example 5.35: 



SELECT   MATCHNO, START_TIME, START_TIME + INTERVAL '8' HOUR AS START_TIME_PLUS_8

FROM     MATCHES_SPECIAL



Example 5.36: 



SELECT   MATCHNO, END_TIME

FROM     MATCHES_SPECIAL

WHERE    END_TIME <= TIME '00:00:00' – (INTERVAL '6' HOUR + INTERVAL '30' MINUTE)



Example 5.37: 



INSERT   INTO COMMITTEE_MEMBERS

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



Answer 5.4
:




SELECT   MATCHNO, WON - LOST AS DIFFERENCE

FROM     MATCHES



Answer 5.6:



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

FROM     PLAYERS

WHERE    PLAYERS.PLAYERNO > 6

ORDER BY PLAYERS.NAME



Answer 5.8:



SELECT   PRIVILEGE, WITHGRANTOPTION

FROM     USER_AUTHS

WHERE    GRANTEE = CURRENT_USER



Answer 5.9:



SELECT   INDEX_NAME

FROM     INDEXES

WHERE    INDEX_CREATOR <> CURRENT_USER



Answer 5.10:



SELECT   PLAYERNO

FROM     COMMITTEE_MEMBERS

WHERE    BEGIN_DATE = CURRENT_DATE



Answer 5.11:



SELECT   TEAMNO,

         CASE DIVISION

            WHEN 'first' then 'first division'

            WHEN 'second' THEN 'second division'

            ELSE 'unknown'

         END AS DIVISION

FROM     TEAMS



Answer 5.12:



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



Answer 5.13:



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'



Answer 5.15:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    DOW(PAYMENT_DATE) = 'Mon'



Answer 5.16:



SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    YEAR(PAYMENT_DATE) = 1984



Answer 5.23:



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

FROM    PLAYERS



Answer 5.24:



SELECT  TEAMNO, RTRIM(DIVISION) || ' division'

FROM    TEAMS



Answer 5.26:



SELECT   PLAYERNO, BEGIN_DATE, BEGIN_DATE + INTERVAL '2' MONTH + INTERVAL '3' DAY

FROM     COMMITTEE_MEMBERS



Answer 5.30:



SELECT   PAYMENTNO, PAYMENT_DATE,

         CAST(PAYMENT_DATE AS TIMESTAMP) +

         INTERVAL '3' HOUR + INTERVAL '50.99' SECOND

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

FROM     PLAYERS

WHERE    YEAR(BIRTH_DATE) = 

        (SELECT   YEAR(BIRTH_DATE)

         FROM     PLAYERS

         WHERE    PLAYERNO = 27)



Example 6.9: 



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 >= DATE '1990-01-01') AS AFTER1989

        WHERE   END_DATE <= 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    DOW(BIRTH_DATE) =

        (SELECT   DOW(BIRTH_DATE)

         FROM     PLAYERS

         WHERE    PLAYERNO = 2)



Example 7.1: 



SELECT   *

FROM     BOB.PENALTIES



SELECT   *

FROM     PENALTIES



Example 7.2: 



SELECT   TEAMNO

FROM     TEAMS



and



SELECT   TEAMS.TEAMNO

FROM     TEAMS



and



SELECT   BOB.TEAMS.TEAMNO

FROM     BOB.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   PLAYERS.PLAYERNO, NAME, AMOUNT

FROM     PLAYERS, PENALTIES

WHERE    PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

AND      BIRTH_DATE > 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.13: 



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.14: 



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.15: 



SELECT   PAYMENTNO, NAME

FROM     PENALTIES LEFT OUTER JOIN PLAYERS

         ON PENALTIES.PLAYERNO = PLAYERS.PLAYERNO

ORDER BY PAYMENTNO



Example 7.16: 



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.17: 



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.18: 



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.19: 



SELECT   PLAYERS.PLAYERNO, NAME, TEAMNO

FROM     TEAMS RIGHT OUTER JOIN PLAYERS

         ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO



Example 7.20: 



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.21: 



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.22: 



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

FROM     TEAMS FULL OUTER JOIN PENALTIES

         ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO

         AND TEAMS.PLAYERNO > 1000



SELECT   TEAMS.*, PENALTIES.*

FROM     TEAMS, PENALTIES



SELECT   *

FROM     TEAMS CROSS JOIN PENALTIES



SELECT   TEAMS.TEAMNO, PLAYERS.PLAYERNO

FROM     TEAMS INNER JOIN PLAYERS

         ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO



SELECT   TEAMS.TEAMNO, PLAYERS.PLAYERNO

FROM     TEAMS INNER JOIN PLAYERS

         USING (PLAYERNO)





Example 7.23: 



SELECT   PLAYERNO

FROM    (SELECT   *

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford') AS STRATFORDERS



Example 7.24: 



SELECT   SMALL_TEAMS.PLAYERNO

FROM    (SELECT   PLAYERNO, DIVISION

         FROM     TEAMS) AS SMALL_TEAMS

WHERE    SMALL_TEAMS.DIVISION = 'first'



Example 7.25: 



SELECT   MATCHNO, DIFFERENCE

FROM    (SELECT   MATCHNO, 

                  ABS(WON – LOST) AS DIFFERENCE

         FROM     MATCHES) AS M

WHERE    DIFFERENCE > 2



Example 7.26: 



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.27: 



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.28: 



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.29: 



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.30: 



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.31:



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.32: 



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   RANDOM(0,1000)

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)



SET NOFLATTEN



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   PLAYERNO, NAME, SEX, BIRTH_DATE

FROM     PLAYERS

WHERE    SEX = 'M'

AND      BIRTH_DATE > DATE '1970-12-31'



Example 8.11: 



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Plymouth'

OR       TOWN = 'Eltham'



Example 8.12: 



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN <> 'Stratford'



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    NOT (TOWN = 'Stratford')



Example 8.13: 



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.14: 



SELECT   PLAYERNO, NAME, TOWN

FROM     PLAYERS

WHERE    TOWN = 'Inglewood'

OR       TOWN = 'Plymouth'

OR       TOWN = 'Midhurst'

OR       TOWN = 'Douglas'



Example 8.15: 



SELECT   PLAYERNO, YEAR(BIRTH_DATE) AS BIRTH_YEAR

FROM     PLAYERS

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



Example 8.16: 



SELECT   MATCHNO, WON, LOST

FROM     MATCHES

WHERE    2 IN (WON, LOST)



Example 8.17: 



SELECT   MATCHNO

FROM     MATCHES

WHERE   (SELECT   SUBSTR(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)



SELECT   PLAYERNO, NAME, INITIALS

FROM     PLAYERS

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



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 >= DATE '1962-01-01'

AND      BIRTH_DATE <= DATE '1964-12-31'



SELECT   PLAYERNO, BIRTH_DATE

FROM     PLAYERS

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



Example 8.23: 



SELECT   MATCHNO, WON + LOST

FROM     MATCHES

WHERE    WON + LOST BETWEEN 2 AND 4



Example 8.24: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    NAME LIKE 'B%'



Example 8.25: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '%r'



Example 8.26: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '%e_'



Example 8.27: 



SELECT   NAME, TOWN, PLAYERNO

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '%' || SUBSTR(TOWN,3,1)



Example 8.28: 



SELECT   NAME, PLAYERNO

FROM     PLAYERS

WHERE    NAME LIKE '%#_%' ESCAPE '#'



Example 8.29: 



SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO IS NOT NULL



Example 8.30: 



SELECT   NAME, PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO <> '8467'

OR       LEAGUENO IS NULL



Example 8.31: 



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.32: 



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.33: 



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE <= ALL

        (SELECT   BIRTH_DATE

         FROM     PLAYERS)



Example 8.34: 



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.35: 



SELECT   DISTINCT TEAMNO, PLAYERNO

FROM     MATCHES AS M1

WHERE    WON <= ALL

        (SELECT   WON

         FROM     MATCHES AS M2

         WHERE    M1.TEAMNO = M2.TEAMNO)



Example 8.36: 



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.37: 



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.38: 



SELECT   PLAYERNO, NAME, BIRTH_DATE

FROM     PLAYERS

WHERE    BIRTH_DATE > ANY

        (SELECT   BIRTH_DATE

         FROM     PLAYERS)



Example 8.39: 



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES

WHERE    PLAYERNO <> 27

AND      AMOUNT > ANY

        (SELECT   AMOUNT

         FROM     PENALTIES

         WHERE    PLAYERNO = 27)



Example 8.40: 



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.41: 



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.42: 



SELECT   TEAMNO, DIVISION

FROM     TEAMS

WHERE    EXISTS

        (SELECT   *

         FROM     MATCHES

         WHERE    PLAYERNO = 44

         AND      TEAMNO = TEAMS.TEAMNO)



Example 8.43: 



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES AS PEN

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PENALTIES

         WHERE    PAYMENTNO <> PEN.PAYMENTNO)



Example 8.44: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    1 <> ALL

        (SELECT   TEAMNO

         FROM     MATCHES

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Example 8.45: 



SELECT   TEAMNO

FROM     TEAMS

WHERE    NOT EXISTS

        (SELECT   *

         FROM     MATCHES

         WHERE    PLAYERNO = 57

         AND      TEAMNO = TEAMS.TEAMNO)



Example 8.46: 



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.47: 



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))



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.49: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    NOT (TOWN = 'Stratford')



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN <> 'Stratford'



Example 8.50: 



SELECT   PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT = 25



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(BIRTH_DATE + INTERVAL '16' YEAR + INTERVAL '1' DAY)

         AND YEAR(BIRTH_DATE + INTERVAL '40' YEAR – INTERVAL '1' DAY)



Answer 8.27:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '%is%'



Answer 8.28:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '______'



Answer 8.29:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '______%'



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '%______'



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '%______%'



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    LENGTH(RTRIM(NAME)) > 6



Answer 8.30:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    VARCHAR(NAME) LIKE '__l%l___'



Answer 8.31:



SELECT   COL1

FROM    (SELECT 'A%B%C' AS COL1

         UNION

         SELECT '%ABC%'

         UNION

         SELECT 'ABC%D') AS T1

WHERE    COL1 LIKE '_@%%@%_' ESCAPE '@'



Answer 8.32:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    LEAGUENO IS NULL



Answer 8.34:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     TEAMS

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Answer 8.35:



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.36:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    BIRTH_DATE <= ALL

        (SELECT   BIRTH_DATE

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')

AND      TOWN = 'Stratford'



Answer 8.37:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO = ANY

        (SELECT   PLAYERNO

         FROM     PENALTIES)



Answer 8.38:



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.39:



SELECT   T1.PLAYERNO, T2.PLAYERNO

FROM    (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    PLAYERNO <= ALL

                 (SELECT   PLAYERNO

                  FROM     PLAYERS)) AS T1

         CROSS JOIN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    PLAYERNO >= ALL

                 (SELECT   PLAYERNO

                  FROM     PLAYERS)) AS T2



Answer 8.41:



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.42:



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.43:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERNO = PLAYERS.PLAYERNO)



Answer 8.44:



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.45:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    NOT EXISTS

        (SELECT   *

         FROM     PENALTIES

         WHERE    PLAYERS.PLAYERNO = PLAYERNO

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



Answer 8.46:



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.47:



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    PLAYERNO NOT IN

        (SELECT   PLAYERNO

         FROM     MATCHES WHERE WON = 3)



Answer 8.48:



SELECT   TEAMNO, DIVISION

FROM     TEAMS

WHERE    TEAMNO NOT IN

        (SELECT   TEAMNO

         FROM     MATCHES

         WHERE    PLAYERNO = 6)



Answer 8.49:



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   *, *

FROM     TEAMS



Example 9.4: 



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

FROM     MATCHES



Example 9.5: 



SELECT   TOWN

FROM     PLAYERS



SELECT   DISTINCT TOWN

FROM     PLAYERS



Example 9.6: 



SELECT   STREET, TOWN

FROM     PLAYERS



SELECT   DISTINCT STREET, TOWN

FROM     PLAYERS



SELECT   TOWN

FROM     PLAYERS



SELECT   ALL TOWN

FROM     PLAYERS



Example 9.7: 



SELECT   DISTINCT LEAGUENO

FROM     PLAYERS



Example 9.8: 



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.9: 



SELECT   COUNT(*)

FROM     PLAYERS



Example 9.10: 



SELECT   COUNT(*)

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



SELECT   COUNT(*), PLAYERNO

FROM     PLAYERS



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

FROM     PLAYERS



Example 9.11: 



SELECT   COUNT(LEAGUENO)

FROM     PLAYERS



SELECT   COUNT(ALL LEAGUENO)

FROM     PLAYERS



Example 9.12: 



SELECT   COUNT(DISTINCT TOWN)

FROM     PLAYERS



Example 9.13: 



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

FROM     PLAYERS



Example 9.14: 



SELECT   COUNT(DISTINCT YEAR(PAYMENT_DATE)) AS YEARS

FROM     PENALTIES



Example 9.15: 



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

FROM     PLAYERS



Example 9.16: 



SELECT   MAX(AMOUNT)

FROM     PENALTIES



Example 9.17: 



SELECT   MIN(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')



Example 9.18: 



SELECT   COUNT(*)

FROM     PENALTIES

WHERE    AMOUNT =

        (SELECT   MIN(AMOUNT)

         FROM     PENALTIES)



Example 9.19: 



SELECT   DISTINCT TEAMNO, PLAYERNO

FROM     MATCHES AS M1

WHERE    WON =

        (SELECT   MAX(WON)

         FROM     MATCHES AS M2

         WHERE    M1.TEAMNO = M2.TEAMNO)



Example 9.20: 



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

FROM     PENALTIES



Example 9.21: 



SELECT   SUBSTR(MAX(NAME), 1, 1)

FROM     PLAYERS



Example 9.22: 



SELECT   MAX(LEAGUENO)

FROM     PLAYERS

WHERE    TOWN = 'Midhurst'



Example 9.23: 



SELECT   CASE WHEN MIN(LEAGUENO) IS NULL 

            THEN 'Unknown' 

            ELSE MIN(LEAGUENO)

         END

FROM     PLAYERS

WHERE    TOWN = 'Amsterdam'



Example 9.24: 



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.25: 



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.26: 



SELECT   AVG(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO = 44



Example 9.27: 



SELECT   DISTINCT PLAYERNO

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES)



Example 9.28: 



SELECT   AVG(DISTINCT AMOUNT)

FROM     PENALTIES



Example 9.29: 



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

FROM     PLAYERS



Example 9.30: 



SELECT   PAYMENTNO, AMOUNT, ABS(AMOUNT – AVGAMOUNT)

FROM     PENALTIES CROSS JOIN

        (SELECT   AVG(AMOUNT) AS AVGAMOUNT

         FROM     PENALTIES) AS T1



Example 9.31: 



SELECT   CASE ANY(PAYMENTNO)

            WHEN 1 THEN 'Yes'

            WHEN 0 THEN 'No'

         END AS ANSWER

FROM     PENALTIES

WHERE    PLAYERNO > 100



Example 9.32: 



SELECT   PLAYERNO, NAME

FROM     PLAYERS AS P

WHERE    1 =

        (SELECT   ANY(PAYMENTNO)

         FROM     PENALTIES AS PEN

         WHERE    PEN.PLAYERNO = P.PLAYERNO)



Example 9.33: 



SELECT   VAR_POP(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO = 44



Example 9.34: 



SELECT   STDDEV_POP(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO = 44



Example 9.35: 



SELECT   VAR_SAMP(AMOUNT), VAR_POP(AMOUNT)

FROM     PENALTIES



Example 9.36: 



SELECT   STDDEV_SAMP(AMOUNT), STDDEV_POP(AMOUNT)

FROM     PENALTIES





Example 9.37: 



CREATE TABLE REGRESSION (

   X   INTEGER NOT NULL PRIMARY KEY,

   Y   INTEGER)



INSERT INTO REGRESSION VALUES (3,2)



INSERT INTO REGRESSION VALUES (4,4)



INSERT INTO REGRESSION VALUES (5,6)



INSERT INTO REGRESSION VALUES (6,8)



INSERT INTO REGRESSION VALUES (7,NULL)



SELECT   REGR_INTERCEPT(Y,X), REGR_SLOPE(Y,X)

FROM     REGRESSION



Example 9.38: 



SELECT   REGR_SLOPE(Y,X) * 8 + REGR_INTERCEPT(Y,X)

FROM     REGRESSION



Example 9.39: 



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   FIRST 4 PLAYERNO, NAME

FROM     PLAYERS

ORDER BY PLAYERNO DESC



Example 9.40: 



SELECT   FIRST 5 LEAGUENO, PLAYERNO, NAME

FROM     PLAYERS

ORDER BY LEAGUENO DESC



Example 9.41: 



SELECT   FIRST 3 PLAYERNO, COUNT(*) AS NUMBER

FROM     MATCHES

WHERE    WON > LOST

GROUP BY PLAYERNO

ORDER BY NUMBER DESC



Example 9.42: 



SELECT   FIRST 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   *

FROM    (SELECT 'Number of players' AS TABLES) AS T1

         CROSS JOIN

        (SELECT COUNT(*) AS NUMBERS FROM PLAYERS) AS T2

UNION

SELECT   *

FROM    (SELECT 'Number of teams') AS T1

         CROSS JOIN

        (SELECT COUNT(*) AS NUMBERS FROM TEAMS) AS T2

UNION

SELECT   *

FROM    (SELECT 'Number of matches') AS T1

         CROSS JOIN

        (SELECT COUNT(*) AS NUMBERS FROM MATCHES) AS T2



Answer 9.8:



SELECT   MIN(WON)

FROM     MATCHES

WHERE    WON > LOST



Answer 9.9:



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.11:



SELECT   AVG(AMOUNT)

FROM     PENALTIES

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    TEAMNO = 1)



Answer 9.12:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE   (SELECT   SUM(AMOUNT)

         FROM     PENALTIES

         WHERE    PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) > 100



Answer 9.13:



SELECT   NAME, INITIALS

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    WON >

                 (SELECT   SUM(WON)

                  FROM     MATCHES

                  WHERE    PLAYERNO = 27))



Answer 9.14:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE   (SELECT   SUM(WON)

         FROM     MATCHES

         WHERE    MATCHES.PLAYERNO = 

                  PLAYERS.PLAYERNO) = 8



Answer 9.15:



SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    LENGTH(NAME) >

        (SELECT   AVG(LENGTH(NAME))

         FROM     PLAYERS)



Answer 9.16:



SELECT   FIRST 4 PAYMENTNO, AMOUNT, PAYMENT_DATE

FROM     PENALTIES

ORDER BY PAYMENT_DATE DESC 



Answer 9.17:



SELECT   FIRST 2 MATCHNO

FROM     MATCHES

ORDER BY MATCHNO ASC 



Example 10.1: 



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



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



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, 

                DATE '1980-01-01' AS START, 

                DATE '1981-06-30' AS END

         UNION

         SELECT 2, DATE '1981-07-01', 

                DATE '1982-12-31'

         UNION

         SELECT 3, DATE '1983-01-01', 

                DATE '1984-12-31') AS GROUPS

WHERE    P.PAYMENT_DATE BETWEEN START AND END

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(RTRIM(NAME)), COUNT(*)

FROM     PLAYERS

GROUP BY LENGTH(RTRIM(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))



Example 11.7: 



SELECT   SUM(AMOUNT)

FROM     PENALTIES

HAVING   SUM(AMOUNT) >= 250



SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN

HAVING   BIRTH_DATE > 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 SUBSTR(NAME, 1, 1)



Example 12.5: 



SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

GROUP BY PLAYERNO

ORDER BY 2



Example 12.6: 



SELECT   PLAYERNO, AMOUNT

FROM     PENALTIES

ORDER BY PLAYERNO DESC, AMOUNT ASC



Example 12.7: 



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.8: 



SELECT   DISTINCT LEAGUENO

FROM     PLAYERS

ORDER BY LEAGUENO DESC



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'



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



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 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.4: 



INSERT   INTO RECR_PLAYERS

        (PLAYERNO, NAME, TOWN, PHONENO)

SELECT   PLAYERNO + 1000, NAME, TOWN, PHONENO

FROM     RECR_PLAYERS



Example 14.5: 



INSERT   INTO PENALTIES

SELECT   PAYMENTNO + 100, PLAYERNO, PAYMENT_DATE, AMOUNT

FROM     PENALTIES

WHERE    AMOUNT >

        (SELECT   AVG(AMOUNT)

         FROM     PENALTIES)



Example 14.6: 



UPDATE   PLAYERS

SET      LEAGUENO = '2000'

WHERE    PLAYERNO = 95



UPDATE   PLAYERS AS P

SET      LEAGUENO = '2000'

WHERE    P.PLAYERNO = 95

Example 14.7: Increase all penalties by 5 percent.



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT * 1.05



Example 14.8: 



UPDATE   MATCHES

SET      WON = 0

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     PLAYERS

         WHERE    TOWN = 'Stratford')



Example 14.9: 



UPDATE   PLAYERS

SET      STREET   = 'Palmer Street',

         HOUSENO  = '83',

         TOWN     = 'Inglewood',

         POSTCODE = '1234UU',

         PHONENO  = NULL

WHERE    NAME     = 'Parmenter'



Example 14.10: 



UPDATE   PLAYERS

SET      STREET   = TOWN,

         TOWN     = STREET

WHERE    PLAYERNO = 44



Example 14.11: 



DELETE

FROM     PENALTIES

WHERE    PLAYERNO = 44



DELETE

FROM     PENALTIES AS PEN

WHERE    PEN.PLAYERNO = 44



Example 14.12: 



DELETE

FROM     MATCHES

WHERE    WON >

        (SELECT   AVG(WON)

         FROM     MATCHES

         WHERE    PLAYERNO = 83)



Answer 14.1:



INSERT INTO PENALTIES VALUES (15, 27, DATE '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: 



COPY TABLE TEAMS ()

   INTO 'C:\TEST\TEAMS.TXT'



Example 15.2: 



CREATE TABLE SOURCE1 (

   COL1   CHAR(5) NOT NULL,

   COL2   CHAR(10) NOT NULL)



INSERT INTO SOURCE1 VALUES ('one', 'comment')



INSERT INTO SOURCE1 VALUES ('two', 'text')



INSERT INTO SOURCE1 VALUES ('three', 'long text')



COPY TABLE SOURCE1

   (COL1=CHAR(5), COL2=CHAR(10))

   INTO 'C:\TEST\TEST.TXT'



COPY TABLE SOURCE1

   (COL1=CHAR(0), COL2=CHAR(0))

   INTO 'C:\TEST\TEST.TXT'



Example 15.3: 



COPY TABLE SOURCE1

   (COL1=CHAR(5), COL1=CHAR(5))

   INTO 'C:\TEST\TEST.TXT'



Example 15.4: 



COPY TABLE SOURCE1

   (COL1=CHAR(5) COMMA, COL2=CHAR(10) COMMA, COL1=CHAR(0) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.5: 



COPY TABLE SOURCE1

   (COL1=CHAR(5) NL, COL2=CHAR(10) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.6: 



COPY TABLE SOURCE1

   (COL1=CHAR(5) a, COL1=CHAR(5) '$', COL1=CHAR(5) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.7: 



COPY TABLE SOURCE1

   (COL1=CHAR(10), COL2=CHAR(5) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.8: 



COPY TABLE SOURCE1

   (COL1=D0LPAREN, COL1=CHAR(0)RPAREN, COL1='d0,',

    COL2=D0LPAREN, COL2=CHAR(0)RPAREN, COL2=D0NL)

   INTO 'C:\TEST\TEST.TXT'



COPY TABLE SOURCE1

   (COL1='d0(', COL1=CHAR(0), COL1='d0)', COL1='d0,',

    COL2='d0(', COL2=CHAR(0), COL2='d0)', COL2=D0NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.9: 



COPY TABLE SOURCE1

   (COL1=D0z, COL1=CHAR(0), COL1=D0z, COL1=D0COMMA,

    COL2='d0[', COL2=CHAR(0), COL1='d0]', COL1=D0NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.10: 



COPY TABLE SOURCE1

   (X=D10, COL1=CHAR(0) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.11: 



CREATE TABLE SOURCE2 (

   COL1   CHAR(5) NOT NULL,

   COL2   CHAR(10) WITH NULL)



INSERT INTO SOURCE2 VALUES ('one', 'comment')



INSERT INTO SOURCE2 VALUES ('two', NULL)



INSERT INTO SOURCE2 VALUES ('three', NULL)



COPY TABLE SOURCE2

   (COL1=CHAR(5),

    COL2=CHAR(10) COMMA WITH NULL ('----------'),

    NL=D1)

   INTO 'C:\TEST\TEST.TXT'



Example 15.12: 



CREATE TABLE SOURCE3 (

   COL1   INTEGER NOT NULL,

   COL2   DECIMAL(7,2) NOT NULL,

   COL3   DATE NOT NULL,

   COL4   TIME NOT NULL)



INSERT INTO SOURCE3 VALUES

   (1, 123.45, DATE '2008-12-31', TIME '09:15:40')



INSERT INTO SOURCE3 VALUES

   (2, 90, DATE '2008-01-01', TIME '19:15:40')



INSERT INTO SOURCE3 VALUES

   (3, 23562.2, DATE '2008-06-15', TIME '23:15:40')



COPY TABLE SOURCE3

   (COL1=CHAR(5) COMMA, COL2=CHAR(8) COMMA,

    COL3=CHAR(10) COMMA, COL4=CHAR(8),

    COL1='d0#', NL=D1)

   INTO 'C:\TEST\TEST.TXT'



Example 15.13: 



COPY TABLE SOURCE3

   (COL1=INTEGER COMMA, COL2=DECIMAL,

    COL1='d0#', NL=D1)

   INTO 'C:\TEST\TEST.TXT'



Example 15.14: 



COPY TABLE SOURCE1

   (COL1=VARCHAR(5), COL2=VARCHAR(10) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.15: 



DECLARE GLOBAL TEMPORARY TABLE SESSION.HIGHERTHAN60

       (PAYMENTNO INTEGER)

   ON COMMIT PRESERVE ROWS

   WITH NORECOVERY



INSERT INTO SESSION.HIGHERTHAN60

SELECT   PAYMENTNO

FROM     PENALTIES

WHERE    AMOUNT > 60



COPY TABLE SESSION.HIGHERTHAN60

   (PAYMENTNO=CHAR(10) NL)

   INTO 'C:\TEST\TEST.TXT'



Example 15.16: 



DELETE FROM TEAMS



COPY TABLE TEAMS ()

   FROM 'C:\TEST\TEAMS.TXT'



Example 15.17: 



CREATE TABLE TARGET1 (

   COL1   CHAR(5) NOT NULL,

   COL2   CHAR(10) NOT NULL)



COPY TABLE TARGET1

   (COL1=CHAR(5), COL2=CHAR(10))

   FROM 'C:\TEST\TEST.TXT'



SELECT   *

FROM     TARGET1



Example 15.18: 



DELETE FROM TARGET1



COPY TABLE TARGET1

   (COL1=CHAR(5) COMMA, COL2=CHAR(10) COMMA, COL1=CHAR(0) NL)

   FROM 'C:\TEST\TEST.TXT'



Example 15.19: 



CREATE TABLE TARGET2 (

   COL1   CHAR(5) NOT NULL,

   COL2   CHAR(10) WITH NULL)



COPY TABLE TARGET2

   (COL1=CHAR(5),

    COL2=CHAR(10) COMMA WITH NULL ('----------'),

    NL=D1)

   FROM 'C:\TEST\TEST.TXT'



SELECT   *

FROM     TARGET2



Example 15.20: 



CREATE TABLE TARGET3 (

   COL1   INTEGER NOT NULL,

   COL2   DECIMAL(7,2) NOT NULL,

   COL3   DATE NOT NULL,

   COL4   TIME NOT NULL)



COPY TABLE TARGET3

   (COL1=CHAR(5) COMMA, COL2=CHAR(8) COMMA,

    COL3=CHAR(10) COMMA, COL4=CHAR(8),

    COL1='d0#', NL=D1)

   FROM 'C:\TEST\TEST.TXT'



Example 15.21: 



SET NOLOGGING



Example 15.22: 



COPY TABLE TARGET3

   (COL1=CHAR(5) COMMA, COL2=CHAR(8) COMMA,

    COL3=CHAR(10) COMMA, COL4=CHAR(8),

    COL1='d0#', NL=D1)

   FROM 'C:\TEST\TEST.TXT'

   WITH ON_ERROR = CONTINUE,

        LOG = 'C:\TEST\LOG.TXT'



Example 15.23: 



COPY TABLE TARGET3

   (COL1=CHAR(5) COMMA, COL2=CHAR(8) COMMA,

    COL3=CHAR(10) COMMA, COL4=CHAR(8),

    COL1='d0#', NL=D1)

   FROM 'C:\TEST\TEST.TXT'

   WITH MAXPAGES = 100



Example 16.1: 



CREATE LOCATION TEST

   WITH AREA = 'C:\INGRESDATA',

   USAGE = ( DATABASE )



Example 16.2: 



SELECT   LOCATION_NAME, LOCATION_AREA

FROM     IILOCATION_INFO



Example 16.3: 



SELECT   LOCATION_NAME, DATA_USAGE, JRNL_USAGE, CKPT_USAGE, WORK_USAGE, DUMP_USAGE

FROM     IILOCATION_INFO

WHERE    LOCATION_NAME IN ('ii_database','test')



CREATE LOCATION TEST2

   WITH AREA = 'C:\INGRESDATA2',

   USAGE = ( ALL ),

   RAWPCT = 50



Example 16.4: 



ALTER LOCATION TEST

   WITH USAGE = ( DATABASE , WORK )



Example 16.5: 



SELECT   LNAME, DNAME

FROM     IIEXTEND



Example 16.6: 



EXTENDDB -lTEST TENNIS -Udata



Example 16.7: 



SELECT   EXT.LOCATION_NAME, LOC.DATA_USAGE, LOC.JRNL_USAGE,

         LOC.CKPT_USAGE, LOC.WORK_USAGE, LOC.DUMP_USAGE

FROM     IIEXTEND_INFO AS EXT INNER JOIN IILOCATION_INFO AS LOC

         ON (EXT.LOCATION_NAME = LOC.LOCATION_NAME)

WHERE    EXT.DATABASE_NAME = 'tennis'



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))



Example 17.2: 



CREATE TABLE MEASUREMENTS (

   NR                 INTEGER,

   MEASUREMENT_VALUE  FLOAT(1))



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 MEETGEGEVENS VALUES (9, (1.0/3))



SELECT * FROM MEASUREMENTS



Example 17.3: 

 

CREATE TABLE LOGKEY (

   COL1   TABLE_KEY WITH SYSTEM_MAINTAINED,

   COL2   CHAR(1))



INSERT INTO LOGKEY (COL2) VALUES ('A')



INSERT INTO LOGKEY (COL2) VALUES ('B')



INSERT INTO LOGKEY (COL2) VALUES ('C')



SELECT   HEX(COL1), COL2

FROM     LOGKEY



Example 17.4: 



CREATE TABLE LOGKEY2 (

   COL1   TABLE_KEY WITH SYSTEM_MAINTAINED,

   COL2   TABLE_KEY WITH SYSTEM_MAINTAINED,

   COL3   OBJECT_KEY WITH SYSTEM_MAINTAINED,

   COL4   CHAR(1))



INSERT INTO LOGKEY2 (COL4) VALUES ('A')



INSERT INTO LOGKEY2 (COL4) VALUES ('B')



INSERT INTO LOGKEY2 (COL4) VALUES ('C')



SELECT   HEX(COL1), HEX(COL2), HEX(COL3), COL4

FROM     LOGKEY2



Example 17.5: 



DECLARE GLOBAL TEMPORARY TABLE SUMPENALTIES (

   TOTAL   DECIMAL(10,2))

ON COMMIT PRESERVE ROWS

WITH NORECOVERY



INSERT INTO SUMPENALTIES

SELECT SUM(AMOUNT)

FROM   PENALTIES



DECLARE GLOBAL TEMPORARY TABLE SUMPENALTIES2

   SELECT   SUM(AMOUNT)

   FROM     PENALTIES

   ON COMMIT PRESERVE ROWS

   WITH NORECOVERY



Example 17.6: 



CREATE   TABLE TEAMS_COPY1 AS

SELECT   *

FROM     TEAMS



Example 17.7: 



CREATE   TABLE TEAMS_COPY2 AS

SELECT   TEAMNO AS TNO, PLAYERNO AS PNO, DIVISION

FROM     TEAMS



SELECT   *

FROM     TEAMS_COPY2



Example 17.8: 



CREATE   TABLE TEAMS_COPY3 AS

SELECT   TEAMNO, PLAYERNO

FROM     TEAMS

WHERE    PLAYERNO = 27



Example 17.9: 



CREATE   TABLE TEAMS_COPY4 (TNO, PNO) AS

SELECT   TEAMNO, PLAYERNO

FROM     TEAMS



Example 17.10: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL PRIMARY KEY,

   PLAYERNO       INTEGER NOT NULL,

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

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



INSERT   INTO PENALTIES

        (PAYMENTNO, PLAYERNO)

VALUES  (15, 27)



Example 17.11: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL PRIMARY KEY,

   PLAYERNO       INTEGER NOT NULL,

   PAYMENT_DATE   DATE NOT NULL DEFAULT CURRENT_DATE,

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



Example 17.12: 



CREATE TABLE DEFAULTS (

   COL0   INTEGER PRIMARY KEY,

   COL1   INTEGER WITH DEFAULT,

   COL2   FLOAT WITH DEFAULT,

   COL3   VARCHAR(10) WITH DEFAULT,

   COL4   DATE WITH DEFAULT,

   COL5   TIME WITH DEFAULT,

   COL6   TIMESTAMP WITH DEFAULT)



INSERT INTO DEFAULTS (COL0) VALUES (1)



SELECT   *

FROM     DEFAULTS



Example 17.13: 



Log on to the IIDBDB database:



CREATE LOCATION LOC1

   WITH AREA = 'C:\INGRESDATA',

   USAGE = ( DATABASE )



extenddb –lLOC1 tennis –Udata



Log on to the TENNIS database:



CREATE TABLE MATCHES2 (

   MATCHNO    INTEGER NOT NULL PRIMARY KEY,

   TEAMNO     INTEGER NOT NULL,

   PLAYERNO   INTEGER NOT NULL,

   WON        SMALLINT NOT NULL,

   LOST       SMALLINT NOT NULL)

WITH LOCATION = (LOC1)



Example 17.14: 



CREATE TABLE PENALTIES_COPY AS

SELECT * FROM PENALTIES

WITH STRUCTURE=HASH



Example 17.15: 



CREATE TABLE PLAYERS_COPY AS

SELECT * FROM PLAYERS

WITH ALLOCATION=64, EXTEND=32



Example 17.16: 



REGISTER TABLE TOWNS

   AS LINK FROM BOB.TOWNS

      WITH NODE = BIGSYSTEM, DATABASE = EXTRADB



SELECT   *

FROM     TOWNS

WHERE    NAME LIKE 'A%'



Example 17.17: 



REGISTER TABLE TOWNS

   AS LINK WITH REFRESH



Example 17.18: 



REMOVE TABLE TOWNS



Example 17.19: 



CREATE LOCATION LOC1

   WITH AREA = 'C:\TEST', USAGE = ( DATABASE )



CREATE LOCATION LOC2

   WITH AREA = 'C:\TEST', USAGE = ( DATABASE )



CREATE LOCATION LOC3

   WITH AREA = 'C:\TEST', USAGE = ( DATABASE )



extenddb -lloc1 tennis -Udata



extenddb –lloc2 tennis -Udata



extenddb –lloc3 tennis -Udata



CREATE TABLE PART1 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC

   PARTITION P1 WITH LOCATION = (LOC1),

   PARTITION P2 WITH LOCATION = (LOC2),

   PARTITION P3 WITH LOCATION = (LOC3))



Examples 17.20: 



CREATE TABLE PART2 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC

   PARTITION WITH LOCATION = (LOC1),

   PARTITION WITH LOCATION = (LOC2),

   PARTITION WITH LOCATION = (LOC3))



CREATE TABLE PART2 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC

   3 PARTITIONS WITH LOCATION = (LOC1, LOC2, LOC3))



Example 17.21: 



CREATE TABLE PART3 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC

   3 PARTITIONS WITH LOCATION = (LOC1))



Example 17.22: 



CREATE TABLE PART4 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC

   3 PARTITIONS WITH LOCATION = (LOC1),

   2 PARTITIONS WITH LOCATION = (LOC2))



Example 17.23: 



CREATE TABLE PART5 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC

   PARTITION WITH LOCATION = (LOC1, LOC2),

   PARTITION WITH LOCATION = (LOC2, LOC3),

   PARTITION WITH LOCATION = (LOC3))



Example 17.24: 



CREATE TABLE PART6 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (AUTOMATIC 3 PARTITIONS)



Example 17.25: 



CREATE TABLE PART7 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (HASH ON COL1

   PARTITION P1 WITH LOCATION = (LOC1),

   PARTITION P2 WITH LOCATION = (LOC2),

   PARTITION P3 WITH LOCATION = (LOC3))



Example 17.26: 



CREATE TABLE PART8 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (LIST ON COL1

   PARTITION P1 VALUES (1) WITH LOCATION = (LOC1),

   PARTITION P2 VALUES (2) WITH LOCATION = (LOC2),

   PARTITION P3 VALUES (DEFAULT) WITH LOCATION = (LOC3))



Example 17.27: 



CREATE TABLE PART9 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (LIST ON COL1

   PARTITION P1 VALUES (1,2,3) WITH LOCATION = (LOC1),

   PARTITION P2 VALUES (4,5,6) WITH LOCATION = (LOC2),

   PARTITION P3 VALUES (DEFAULT) WITH LOCATION = (LOC3))



Example 17.28: 



CREATE TABLE PART10 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (LIST ON COL1, COL2, COL3

   PARTITION P1 VALUES ((1,1,1),(2,2,2)) WITH LOCATION = (LOC1),

   PARTITION P2 VALUES ((3,3,3),(4,4,4)) WITH LOCATION = (LOC2),

   PARTITION P3 VALUES (DEFAULT) WITH LOCATION = (LOC3))



Example 17.29: 



CREATE TABLE PART11 (

   COL1  INTEGER,

   COL2  INTEGER,

   COL3  INTEGER)

WITH PARTITION = (RANGE ON COL1

   PARTITION P1 VALUES < 4   WITH LOCATION = (LOC1),

   PARTITION P2 VALUES < 10  WITH LOCATION = (LOC2),

   PARTITION P3 VALUES >= 10 WITH LOCATION = (LOC3))



Example 17.30: 



CREATE TABLE PART12 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (RANGE ON COL1, COL2

   PARTITION P1 VALUES < (4,4)    WITH LOCATION = (LOC1),

   PARTITION P2 VALUES < (10,10)  WITH LOCATION = (LOC2),

   PARTITION P3 VALUES >= (10,10) WITH LOCATION = (LOC3))



Example 17.31: 



CREATE TABLE PART13 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (

  (RANGE ON COL1

   PARTITION P1 VALUES < 4,

   PARTITION P2 VALUES < 10,

   PARTITION P3 VALUES >= 10)

   SUBPARTITION (LIST ON COL2

   PARTITION P4 VALUES (1) WITH LOCATION = (LOC1),

   PARTITION P5 VALUES (2) WITH LOCATION = (LOC2),

   PARTITION P6 VALUES (DEFAULT) WITH LOCATION = (LOC3)))



Example 17.32: 



CREATE TABLE PART14 (

   COL1   INTEGER,

   COL2   INTEGER,

   COL3   INTEGER)

WITH PARTITION = (

  (RANGE ON COL1

   PARTITION P1 VALUES < 4,

   PARTITION P2 VALUES < 10,

   PARTITION P3 VALUES >= 10)

   SUBPARTITION (LIST ON COL2

   PARTITION P4 VALUES (1) WITH LOCATION = (LOC1, LOC2, LOC3),

   PARTITION P5 VALUES (2) WITH LOCATION = (LOC4, LOC5, LOC6),

   PARTITION P6 VALUES (DEFAULT) WITH LOCATION = (LOC7, LOC8, LOC9)))



Example 17.33: 



COMMENT ON

TABLE   PLAYERS

IS      'Recreational and competition players'



COMMENT ON

COLUMN  PLAYERS.PLAYERNO

IS      'primary key'



Example 17.34: 



SELECT   COMMENT

FROM     COLUMNS

WHERE    TABLE_NAME = 'PLAYERS'

AND      COLUMN_NAME = 'PLAYERNO'



COMMENT   ON

TABLE     PLAYERS

IS        ''



Example 17.35: 



SAVE TEAMS UNTIL 1 15 2010



Example 17.36: 



SELECT   GMT_TIMESTAMP(EXPIRE_DATE)

FROM     IITABLES

WHERE    TABLE_NAME = 'teams'



Example 17.37: 



SAVE TEAMS



Example 17.38: 



SELECT   COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, NULLABLE

FROM     COLUMNS

WHERE    TABLE_NAME = 'PLAYERS'

AND      TABLE_CREATOR = 'BOOKSQL'

ORDER BY COLUMN_NO



Example 17.39: 



SELECT   ROWS.TABLE_NAME, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS

        (SELECT   'PLAYERS' AS TABLE_NAME,

                  COUNT(*) AS NUMBER_OF_COLUMNS

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'PLAYERS'

         AND      TABLE_CREATOR = 'BOOKSQL'

         UNION

         SELECT   'TEAMS', COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'TEAMS'

         AND      TABLE_CREATOR = 'BOOKSQL'

         UNION

         SELECT   'PENALTIES', COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'PENALTIES'

         AND      TABLE_CREATOR = 'BOOKSQL'

         UNION

         SELECT   'MATCHES', COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'MATCHES'

         AND      TABLE_CREATOR = 'BOOKSQL'

         UNION

         SELECT   'COMMITTEE_MEMBERS', COUNT(*)

         FROM     COLUMNS

         WHERE    TABLE_NAME = 'COMMITTEE_MEMBERS'

         AND      TABLE_CREATOR = 'BOOKSQL') AS COLUMNS

WHERE    ROWS.TABLE_NAME = COLUMNS.TABLE_NAME

ORDER BY TABLE_NAME



Example 17.40: 



CREATE SYNONYM MEMBERS FOR PLAYERS



SELECT   *

FROM     PLAYERS



SELECT   *

FROM     MEMBERS



Example 17.41: 



SELECT   *

FROM     DIANE.ADDRESSES



CREATE SYNONYM ADR FOR DIANE.ADDRESSES



SELECT   *

FROM     ADR



Example 17.42: 



DROP SYNONYM MEMBERS



Answer 17.6
:




CREATE TABLE DEPARTMENT (

   DEPNO      CHAR(5) NOT NULL PRIMARY KEY,

   BUDGET     DECIMAL(8,2),

   LOCATION   VARCHAR(30))



Answer 17.7:



CREATE   TABLE P_COPY AS

SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO IS NULL



Answer 17.8:



CREATE TABLE P2_COPY AS SELECT * FROM PLAYERS



Answer 17.9
:




CREATE   TABLE NUMBERS AS

SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



Example 18.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 18.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, DDATE))



Example 18.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 18.4:



CREATE TABLE T_HIDDEN (

   COL1   CHARACTER NOT NULL,

   COL2   CHARACTER NOT NULL)



INSERT INTO T_HIDDEN VALUES ('a', 'b')



INSERT INTO T_HIDDEN VALUES ('c', 'd')



SELECT TID, * FROM T_HIDDEN



Example 18.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 18.6: 



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 18.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 18.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 18.9: 



CREATE TABLE PLAYERS_WITH_PASSPORT (

   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_PASSPORT (PASSPORTNO))



Example 18.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 18.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 18.12: 



CREATE TABLE PENALTIES (

   PAYMENTNO      INTEGER NOT NULL,

   PLAYERNO       INTEGER,

   PAYMENT_DATE   DATE NOT NULL,

   AMOUNT         DECIMAL(7,2) NOT NULL,

   PRIMARY KEY    (PAYMENTNO),

   FOREIGN KEY    (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) ON DELETE SET NULL)



Example 18.13: 



CREATE TABLE PLAYERS_X (

   PLAYERNO   INTEGER NOT NULL,

   SEX        CHAR(1) NOT NULL CHECK(SEX IN ('M', 'F')))



CREATE TABLE PLAYERS_WITH_NULL (

   PLAYERNO   INTEGER NOT NULL,

   SEX        CHAR(1) CHECK(SEX IN ('M', 'F')))



Example 18.14: 



CREATE TABLE PLAYERS_Y (

   PLAYERNO     INTEGER NOT NULL,

   BIRTH_DATE   DATE NOT NULL CHECK(BIRTH_DATE > DATE '1920-01-01'))



Example 18.15: 



CREATE TABLE PLAYERS_Z (

   PLAYERNO     INTEGER NOT NULL,

   BIRTH_DATE   DATE,

   JOINED       INTEGER NOT NULL,

   CHECK(YEAR(BIRTH_DATE) < JOINED))



CREATE TABLE PLAYERS_W (

   PLAYERNO     INTEGER,

   BIRTH_DATE   DATE NOT NULL,

   JOINED       INTEGER NOT NULL,

   CHECK(YEAR(BIRTH_DATE) < JOINED),

   CHECK(BIRTH_DATE > DATE '1920-01-01'),

   CHECK(JOINED  < 1880))



Example 18.16: 



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),

   HOUSENO       CHAR(4),

   POSTCODE      CHAR(6),

   TOWN          VARCHAR(30),

   PHONE         CHAR(13),

   LEAGUENO      CHAR(4),

   PRIMARY KEY   (PLAYERNO),

   CHECK (((STREET IS NULL) AND (HOUSENO IS NULL) AND (POSTCODE IS NULL) AND

           (TOWN IS NULL)) OR

          ((STREET IS NOT NULL) AND (HOUSENO IS NOT NULL) AND

           (POSTCODE IS NOT NULL) AND (TOWN IS NOT NULL))) )



Example 18.17: 



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 18.18: 



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,

   PHONE        CHAR(13),

   LEAGUENO     CHAR(4),

   CONSTRAINT   PRIMARY_KEY_PLAYERS PRIMARY KEY(PLAYERNO),

   CONSTRAINT   JOINED CHECK(JOINED > 1969),

   CONSTRAINT   POSTCODE_SIX_CHARACTERS_LONG CHECK(POSTCODE LIKE '______'),

   CONSTRAINT   ALLOWED_VALUES_SEX CHECK(SEX IN ('M', 'F')))



Example 18.19: 



CREATE TABLE PLAYERS_X (

   PLAYERNO   INTEGER NOT NULL,

   SEX        CHAR(1) NOT NULL)



CREATE INTEGRITY ON PLAYERS_X

   IS SEX IN ('M', 'F')



Example 18.20: 



CREATE TABLE TABLE1 (

   COL1   SMALLINT,

   COL2   SMALLINT,

   CHECK(COL1 > COL2))



INSERT INTO TABLE1 VALUES (NULL,NULL)



CREATE TABLE TABLE2 (

   COL1   SMALLINT,

   COL2   SMALLINT)



CREATE INTEGRITY ON TABLE2

   IS COL1 > COL2



INSERT INTO TABLE2 VALUES (NULL,NULL)



Example 18.21: 



DROP INTEGRITY ON TABLE2 1



Example 18.22: 



SELECT   INTEGRITY_NUMBER, TEXT_SEGMENT

FROM     IIINTEGRITIES

WHERE    UPPER(TABLE_NAME) = 'TABLE2'

AND      UPPER(TABLE_OWNER) = 'BOOKSQL'



Answer 18.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 19.1: 



SELECT DBMSINFO('charset') AS CHARACTER SET



Example 19.2: 



createdb –nudefault DB1



Example 19.3: 



createdb –lspanish DB2



Example 19.4: 



createdb –lspanish –iudefault DB3



Example 19.5: 



CREATE TABLE COLLATION_TABLE (

   COL1   NCHAR(10) NOT NULL COLLATE UNICODE,

   COL2   NCHAR(10) NOT NULL COLLATE UNICODE_CASE_INSENSITIVE)



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



SELECT   *

FROM     COLLATION_TABLE

WHERE    COL2 = 'a'



Example 20.1: 



DROP TABLE PLAYERS



Example 20.2: 



DROP TABLE COMMITTEE_MEMBERS, MATCHES, TEAMS, PENALTIES, PLAYERS



Example 20.3: 



ALTER TABLE TEAMS

   ADD COLUMN TYPE CHAR(1)



Example 20.4: 



ALTER TABLE TEAMS

   ADD COLUMN SOORT2 CHAR(1) CHECK (SOORT2 IN ('D', 'H'))



Example 20.5: 



ALTER TABLE PLAYERS

   CHANGE BIRTH_DATE DATE_OF_BIRTH DATE



Example 20.6: 



ALTER TABLE PLAYERS

   ALTER COLUMN TOWN VARCHAR(40) NOT NULL



Example 20.7: 



ALTER TABLE PLAYERS

   ALTER TOWN VARCHAR(5) NOT NULL



Example 20.8: 



ALTER TABLE MATCHES

   ALTER COLUMN WON SMALLINT



Example 20.9: 



CREATE TABLE NUMBERS (COL1 CHAR(1))



INSERT INTO NUMBERS VALUES ('1')



INSERT INTO NUMBERS VALUES ('2')



ALTER TABLE NUMBERS

   ALTER COLUMN COL1 SMALLINT



Example 20.10: 



ALTER TABLE COMMITTEE_MEMBERS

   ALTER COLUMN POSITION CHAR(20) DEFAULT 'Member'



Example 20.11: 



ALTER TABLE TEAMS

   DROP COLUMN TYPE CASCADE



Example 20.12: 



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 20.13: 



ALTER TABLE PENALTIES

   ADD CONSTRAINT PAYNO_GREATER_THAN_0 CHECK (PAYMENTNO > 0)



Example 20.14: 



ALTER TABLE MATCHES

   ADD CONSTRAINT SUM_WON_LOST CHECK (WON + LOST = 5)



Example 20.15: 



ALTER TABLE PENALTIES

   DROP CONSTRAINT PAYNO_GREATER_THAN_0 CASCADE



Example 20.16: 



SELECT   CONSTRAINT_NAME

FROM     IICONSTRAINTS

WHERE    TABLE_NAME = 'penalties'

AND      CONSTRAINT_TYPE = 'P'



ALTER TABLE PENALTIES

   DROP CONSTRAINT "$penalty_u0000018000000000" RESTRICT



Answer 20.1:



ALTER TABLE COMMITTEE_MEMBERS

   ALTER POSITION CHAR(30)



Answer 20.2:



ALTER TABLE PLAYERS

   ALTER TOWN VARCHAR(30) DEFAULT 'Stratford'



Example 21.1: 



SELECT   TABLE_NAME, STORAGE_STRUCTURE

FROM     IITABLES

WHERE    TABLE_NAME IN ('players', 'teams', 'penalties',

                        'matches', 'committee_members')



Example 21.2: 



CREATE TABLE T1 (COL1 INTEGER)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't1'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.3: 



CREATE TABLE T2 (COL1 INTEGER NOT NULL PRIMARY KEY)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't2'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.4: 



CREATE TABLE T3 (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER NOT NULL UNIQUE)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't3'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.5: 



CREATE TABLE T4 (

   COL1 INTEGER NOT NULL PRIMARY KEY WITH STRUCTURE = HASH)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't4'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.6: 



CREATE TABLE T5 (COL1 INTEGER)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't5'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.7: 



CREATE TABLE T6 (COL1 INTEGER NOT NULL PRIMARY KEY)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't6'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.8: 



CREATE TABLE T7 (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER NOT NULL UNIQUE)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't7'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.9: 



CREATE TABLE T8 (

   COL1     INTEGER NOT NULL,

   COL2     INTEGER NOT NULL,

   COL3     INTEGER NOT NULL,

   UNIQUE   (COL1, COL2),

   UNIQUE   (COL3))



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't8'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



SELECT   COLUMN_NAME

FROM     COLUMNS_IN_INDEX

WHERE    TABLE_NAME = 'T8'



Example 21.10: 



CREATE TABLE T9 (

   COL1 INTEGER NOT NULL PRIMARY KEY WITH STRUCTURE = HASH)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't9'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.11: 



CREATE TABLE T10 (

   COL1   INTEGER NOT NULL PRIMARY KEY,

   COL2   INTEGER NOT NULL UNIQUE WITH STRUCTURE = HASH)



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't10'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.12: 



MODIFY T1 TO ISAM UNIQUE ON COL1



SELECT   T.TABLE_NAME AS TAB, T.STORAGE_STRUCTURE AS TSTORAGE,

         I.INDEX_NAME, I.STORAGE_STRUCTURE AS ISTORAGE,

         I.UNIQUE_RULE AS UNIQUE

FROM     IITABLES AS T LEFT OUTER JOIN IIINDEXES AS I

         ON (T.TABLE_OWNER = I.BASE_OWNER AND T.TABLE_NAME = I.BASE_NAME)

WHERE    TABLE_NAME = 't1'

ORDER BY T.TABLE_NAME, I.INDEX_NAME



Example 21.13: 



MODIFY T1 TO HEAP



Example 21.14: 



MODIFY T2

   TO ISAM UNIQUE ON COL1

   WITH NODEPENDENCY_CHECK



Example 21.15: 



CREATE INDEX PLAYERSTOWN ON PLAYERS (TOWN)



MODIFY PLAYERSTOWN

   TO ISAM ON TOWN

   WITH NODEPENDENCY_CHECK



Example 21.16: 



MODIFY T2 TO HASH WITH MAXPAGES = 5000



Example 21.17: 



MODIFY PLAYERS

   TO RECONSTRUCT

   WITH EXTEND = 2



Example 21.18: 



CREATE INDEX PLAYERS_NAME ON PLAYERS (NAME)

   WITH STRUCTURE = BTREE



MODIFY PLAYERS_NAME TO MERGE



Example 21.19: 



MODIFY PLAYERS TO TRUNCATED



Example 21.20: 



Log on to the IIDBDB database:



CREATE LOCATION LOCNEW1

   WITH AREA = 'C:\INGRESDATA',

   USAGE = ( DATABASE )



CREATE LOCATION LOCNEW2

   WITH AREA = 'C:\INGRESDATA',

   USAGE = ( DATABASE )



Log on to the TENNIS database:



MODIFY PLAYERS TO REORGANIZE

   WITH LOCATION = (LOCNEW1, LOCNEW2)



Example 21.21: 



Log on to the IIDBDB database:



CREATE LOCATION LOCNEW3

   WITH AREA = 'C:\INGRESDATA',

   USAGE = ( DATABASE )



CREATE LOCATION LOCNEW4

   WITH AREA = 'C:\INGRESDATA',

   USAGE = ( DATABASE )



Log on to the TENNIS database:



MODIFY PLAYERS TO RELOCATE

   WITH OLDLOCATION = (LOCNEW1, LOCNEW2),

        NEWLOCATION = (LOCNEW3, LOCNEW4)



Example 21.22: 



MODIFY PLAYERS TO RELOCATE

   WITH OLDLOCATION = (LOCNEW3),

        NEWLOCATION = (LOCNEW1)



Example 21.23: 



CREATE TABLE T1 (COL1 INTEGER NOT NULL PRIMARY KEY)



MODIFY T1 TO UNIQUE_SCOPE = ROW



Example 21.24: 



MODIFY PLAYERS TO READONLY



Example 21.25: 



MODIFY PLAYERS TO ADD_EXTEND

   WITH EXTEND = 100



Example 21.26: 



MODIFY PLAYERS TO PHYS_INCONSISTENT



Example 21.27: 



MODIFY PLAYERS TO LOG_CONSISTENT



Example 21.28: 



MODIFY PLAYERS TO TABLE_RECOVERY_ALLOWED



Example 21.29: 



MODIFY PLAYERS TO PRIORITY = 6



Example 21.30: 



CREATE   INDEX PLAY_PC

ON       PLAYERS (POSTCODE ASC)



Example 21.31: 



CREATE   INDEX MAT_WL

ON       MATCHES (WON, LOST)



Example 21.32: 



CREATE   UNIQUE INDEX NAMEINIT

ON       PLAYERS (NAME, INITIALS)



SELECT   C

FROM     T

GROUP BY C

HAVING   COUNT(*) > 1



Example 21.33: 



CREATE INDEX

      (MATPLAYERS ON MATCHES (PLAYERNO)),

      (MATWON     ON MATCHES (WON)),

      (MATLOST    ON MATCHES (LOST))



SELECT   INDEX_NAME, UNIQUE_ID, INDEX_TYPE

FROM     INDEXES

WHERE    TABLE_NAME = 'MATCHES'

AND      INDEX_NAME LIKE 'MAT%'



CREATE UNIQUE INDEX

      (INDEX1 ON TABLE1(COL1) UNIQUE)



Example 21.34: 



SELECT   *

FROM     MATPLAYERS

ORDER BY PLAYERNO



Example 21.35: 



SELECT   FIRST 5 TAB.TID, TAB.MATCHNO

FROM     MATCHES AS TAB, MATPLAYERS AS INDEX

WHERE    TAB.TID = INDEX.TIDP

ORDER BY MATCHNO



Example 21.36: 



DROP INDEX PLAY_PC, MAT_WL, NAMEINIT



Example 21.37: 



SELECT   TABLE_CREATOR, TABLE_NAME, COUNT(*)

FROM     INDEXES

GROUP BY TABLE_CREATOR, TABLE_NAME

HAVING   COUNT(*) > 1



Example 21.38: 



SELECT   TABLE_CREATOR, TABLE_NAME

FROM     TABLES AS TAB

WHERE    NOT EXISTS

        (SELECT   *

         FROM     INDEXES AS IDX

         WHERE    TAB.TABLE_CREATOR = IDX.TABLE_CREATOR

         AND      TAB.TABLE_NAME = TAB.TABLE_NAME

         AND      IDX.UNIQUE_ID = 'YES')



Example 22.1: 



CREATE   VIEW TOWNS AS

SELECT   DISTINCT TOWN

FROM     PLAYERS



Example 22.2: 



SELECT   *

FROM     TOWNS



Example 22.3: 



CREATE   VIEW CPLAYERS AS

SELECT   PLAYERNO, LEAGUENO

FROM     PLAYERS

WHERE    LEAGUENO IS NOT NULL



SELECT   *

FROM     CPLAYERS



Example 22.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 22.5: 



DELETE

FROM     CPLAYERS

WHERE    LEAGUENO = '7060'



Example 22.6: 



CREATE   VIEW SEVERAL AS

SELECT   *

FROM     CPLAYERS

WHERE    PLAYERNO BETWEEN 6 AND 27



SELECT   *

FROM     SEVERAL



Example 22.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 22.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 22.9: 



CREATE   VIEW RESIDENTS AS

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN



Example 22.10: 



CREATE   VIEW VETERANS AS

SELECT   *

FROM     PLAYERS

WHERE    BIRTH_DATE < DATE '1960-01-01'



UPDATE   VETERANS

SET      BIRTH_DATE = DATE '1970-09-01'

WHERE    PLAYERNO = 2



CREATE   VIEW VETERANS AS

SELECT   *

FROM     PLAYERS

WHERE    BIRTH_DATE < DATE '1960-01-01'

WITH     CHECK OPTION



Example 22.11: 



DROP VIEW CPLAYERS



Example 22.12: 



DROP VIEW RESIDENTS, STRATFORDERS, TOWNS



DROP RESIDENTS, STRATFORDERS, TOWNS



Example 22.14: 



SELECT   TABLE_NAME

FROM     TABLES

WHERE    TABLE_NAME = 'STOCK'

AND      TABLE_CREATOR = 'JIM'

UNION

SELECT   VIEW_NAME

FROM     VIEWS

WHERE    VIEW_NAME = 'STOCK'

AND      VIEW_CREATOR = 'JIM'



Example 22.15: 



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 22.16: 



DELETE

FROM     STRATFORDERS

WHERE    BORN > DATE '1965-12-31'



DELETE

FROM     PLAYERS

WHERE    BIRTH_DATE > DATE '1965-12-31'

AND      TOWN = 'Stratford'



SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO IN 

        (SELECT   PLAYERNO

         FROM     PENALTIES)



Example 22.17: 



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 22.19: 



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)



Example 22.20: 



CREATE   VIEW PLAYERSS AS

SELECT   *

FROM     PLAYERS

WHERE    SEX IN ('M', 'F')

WITH     CHECK OPTION



Answer 22.1:



CREATE   VIEW NUMBERPLS (TEAMNO, NUMBER) AS

SELECT   TEAMNO, COUNT(*)

FROM     MATCHES

GROUP BY TEAMNO



Answer 22.2:



CREATE   VIEW WINNERS AS

SELECT   PLAYERNO, NAME

FROM     PLAYERS

WHERE    PLAYERNO IN

        (SELECT   PLAYERNO

         FROM     MATCHES

         WHERE    WON > LOST)



Answer 22.3:



CREATE   VIEW TOTALS (PLAYERNO, SUM_PENALTIES) AS

SELECT   PLAYERNO, SUM(AMOUNT)

FROM     PENALTIES

GROUP BY PLAYERNO



Answer 22.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 22.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 22.5.3:



UPDATE   PLAYERS

SET      BIRTH_DATE = DATE '1950-04-04'

WHERE    PLAYERNO = 7



Example 23.1: 



CREATE USER CHRIS WITH

   PASSWORD = 'CHRISSEC'



Example 23.2: 



CREATE USER U10

   WITH EXPIRE_DATE = '12-31-2010'



Example 23.3: 



DROP USER JIM



Example 23.4: 



CREATE USER BEN WITH

   NOPASSWORD,

   PRIVILEGES = (CREATEDB, TRACE, SECURITY, AUDITOR),

   DEFAULT_PRIVILEGES = (CREATEDB, TRACE)



Example 23.5: 



CREATE USER JESSICA WITH

   NOPASSWORD,

   PRIVILEGES = (CREATEDB, TRACE, SECURITY, AUDITOR),

   DEFAULT_PRIVILEGES = ALL



Example 23.6: 



SET SESSION ADD PRIVILEGES (SECURITY, AUDITOR)



Example 23.7: 



SET SESSION WITH PRIVILEGES =

   (CREATEDB, TRACE, SECURITY, AUDITOR)



Example 23.8: 



SET SESSION WITH PRIVILEGES = ALL



SET SESSION WITH PRIVILEGES = DEFAULT



Example 23.9: 



GRANT   SELECT

ON      PLAYERS

TO      JAMIE



Example 23.10: 



SELECT   GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE

FROM     TABLE_AUTHS

WHERE    GRANTEE = 'JAMIE'



Example 23.11: 



GRANT   INSERT, UPDATE

ON      TEAMS

TO      JAMIE, PETE



Example 23.12: 



GRANT   SELECT, INSERT

ON      PENALTIES

TO      PUBLIC



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'PUBLIC'



Example 23.13: 



GRANT   SELECT, INSERT

ON      PENALTIES, PLAYERS, TEAMS

TO      PAT



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'PAT'



Example 23.14: 

 

GRANT   UPDATE (PLAYERNO, DIVISION)

ON      TEAMS

TO      PETE



SELECT  GRANTOR, GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE

FROM    COLUMN_AUTHS

WHERE   GRANTEE = 'PETE'



Example 23.15: 



GRANT   REFERENCES EXCLUDING (WON)

ON      MATCHES

TO      PAUL



SELECT  GRANTOR, GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE

FROM    COLUMN_AUTHS

WHERE   TABLE_CREATOR = 'BOOKSQL'

AND     TABLE_NAME = 'MATCHES'

AND     GRANTEE = 'PAUL'



Example 23.16: 



GRANT   CREATE_TABLE

ON      DATABASE TENNIS

TO      PETE



Example 23.17: 



GRANT   NOCREATE_PROCEDURE

ON      DATABASE TENNIS

TO      PETE



Example 23.18: 



GRANT   QUERY_PAGE_LIMIT 100, QUERY_ROW_LIMIT 1000

ON      DATABASE TENNIS

TO      PETE



Example 23.19: 



GRANT   CREATE_SEQUENCE

ON      CURRENT INSTALLATION

TO      PETE



Example 23.20: 



CREATE USER JOHN WITH

   PASSWORD = 'JOHN1'



ALTER USER JOHN WITH

   PASSWORD = 'JOHN2',

   OLDPASSWORD = 'JOHN1'



Example 23.21: 



ALTER USER JOHN ADD PRIVILEGES (CREATEDB)



Example 23.22: 



ALTER USER JOHN

   WITH EXPIRE_DATE = '01-01-2015'



Example 23.23: 



GRANT   REFERENCES

ON      TEAMS

TO      JIM

WITH    GRANT OPTION



GRANT   REFERENCES

ON      TEAMS

TO      PETE



Example 23.24: 



Log on to the IIDBDB database:



CREATE USER JILL WITH NOPASSWORD



Log on to the TENNIS database:



GRANT   SELECT

ON      PLAYERS

TO      JILL



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE, WITHGRANTOPTION

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'JILL'



GRANT   SELECT

ON      PLAYERS

TO      JILL

WITH    GRANT OPTION



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE, WITHGRANTOPTION

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'JILL'



Example 23.25: 



CREATE USER U1 WITH NOPASSWORD



CREATE USER U2 WITH NOPASSWORD



CREATE USER U3 WITH NOPASSWORD



CREATE USER U4 WITH NOPASSWORD



CREATE USER U5 WITH NOPASSWORD



CREATE GROUP GROUP1



Example 23.26: 



CREATE GROUP GROUP2 WITH USERS = (U1, U2, U3)



Example 23.27: 



ALTER GROUP GROUP1 ADD USERS (U1, U2)



Example 23.28: 



ALTER GROUP GROUP2 DROP USERS (U1, U2)



Example 23.29: 



ALTER GROUP GROUP1, GROUP2 ADD USERS (U5)



Example 23.30: 



ALTER GROUP GROUP1 DROP ALL



Example 23.31: 



GRANT  SELECT

ON     PLAYERS

TO     GROUP2



GRANT  CREATE_TABLE

ON     DATABASE TENNIS

TO     GROUP2



Example 23.32: 



DROP GROUP GROUP1, GROUP2



CREATE PROFILE PROF1 WITH

   PRIVILEGES = (CREATEDB, TRACE, SECURITY),

   DEFAULT_PRIVILEGES = (TRACE),

   EXPIRE_DATE = '12-31-2010',

   SECURITY_AUDIT = (ALL_EVENTS)



Example 23.34: 



CREATE USER MELODY WITH

   NOPASSWORD,

   PROFILE = PROF1



Example 23.35: 



ALTER PROFILE PROF1 WITH

   EXPIRE_DATE = '12-31-2012'



Example 23.36: 



ALTER DEFAULT PROFILE 

   ADD PRIVILEGES (CREATEDB)



Example 23.37: 



DROP PROFILE PROF1 CASCADE



Example 23.38: 



DROP PROFILE PROF1 RESTRICT



Example 23.39: 



CREATE ROLE ROLE1 WITH NOPASSWORD



Example 23.40: 



GRANT   INSERT, DELETE

ON      PLAYERS

TO      ROLE1



Example 23.41: 



GRANT ROLE1

TO    PHIL



Example 23.42: 



CONNECT SPORTDB IDENTIFIED BY BOOKSQL OPTIONS = ( -RROLE1 )



Example 23.43: 



DROP ROLE ROLE1



Example 23.44: 



Log on to the IIDBDB database:



CREATE USER JONI WITH NOPASSWORD



Log on to the TENNIS database:



GRANT    SELECT

ON       PLAYERS

TO       JONI



REVOKE   SELECT

ON       PLAYERS

FROM     JONI RESTRICT



Example 23.45: 



Log on to the IIDBDB database as BOOKSQL:



CREATE USER BRANDON WITH NOPASSWORD



CREATE USER RUDY WITH NOPASSWORD



CREATE USER TIM WITH NOPASSWORD



Log on to the TENNIS database as BOOKSQL:



GRANT   SELECT

ON      PLAYERS

TO      BRANDON

WITH    GRANT OPTION



Log on to the TENNIS database as BRANDON:



GRANT   SELECT

ON      BOOKSQL.PLAYERS

TO      RUDY

WITH    GRANT OPTION



Log on to the TENNIS database as RUDY:



GRANT   SELECT

ON      BOOKSQL.PLAYERS

TO      TIM



Log on to the TENNIS database as BOOKSQL:



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE, WITHGRANTOPTION

FROM    TABLE_AUTHS

WHERE   GRANTEE IN ('BRANDON', 'RUDY', 'TIM')



REVOKE  SELECT

ON      PLAYERS

FROM    BRANDON CASCADE



Example 23.46: 



REVOKE   REFERENCES

ON       TEAMS

FROM     JIM CASCADE



Example 23.47: 



GRANT   DELETE

ON      COMMITTEE_MEMBERS

TO      MARK



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE, WITHGRANTOPTION

FROM    TABLE_AUTHS

WHERE   GRANTEE = 'MARK'



Example 23.48: 



REVOKE  GRANT OPTION FOR SELECT

ON      COMMITTEE_MEMBERS

FROM    BRANDON CASCADE



SELECT  GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE, WITHGRANTOPTION

FROM    TABLE_AUTHS

WHERE   GRANTEE IN ('BRANDON', 'RUDY', 'TIM')



Example 23.49: 



Log on to the IIDBDB database as BOOKSQL:



CREATE USER MICHAEL WITH NOPASSWORD



CREATE USER BESS WITH NOPASSWORD



Log on to the TENNIS database as BOOKSQL:



GRANT   SELECT

ON      PLAYERS

TO      MICHAEL

WITH    GRANT OPTION



Log on to the TENNIS database as MICHAEL:



GRANT   SELECT

ON      BOOKSQL.PLAYERS

TO      BESS

WITH    GRANT OPTION



Log on to the TENNIS database as BESS:

 

GRANT   SELECT

ON      BOOKSQL.PLAYERS

TO      BOOKSQL

WITH    GRANT OPTION



Example 23.50: 



CREATE USER DIANE WITH NOPASSWORD



CREATE   VIEW NAME_ADDRESS AS

SELECT   NAME, INITIALS, STREET, HOUSENO, TOWN

FROM     PLAYERS

WHERE    LEAGUENO IS NULL



GRANT   SELECT

ON      NAME_ADDRESS

TO      DIANE



Example 23.51: 



CREATE USER GERARD WITH NOPASSWORD



CREATE   VIEW RESIDENTS (TOWN, NUMBER_OF) AS

SELECT   TOWN, COUNT(*)

FROM     PLAYERS

GROUP BY TOWN



GRANT   SELECT

ON      RESIDENTS

TO      GERARD



Example 23.52: 



CREATE   VIEW PERSONAL_TABLES AS

SELECT   TABLE_NAME, CREATE_TIMESTAMP, COMMENT

FROM     TABLES

WHERE    TABLE_CREATOR = UPPER(CURRENT_USER)



CREATE   VIEW PERSONAL_COLUMNS AS

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

         SCALE, NULLABLE, COMMENT

FROM     COLUMNS

WHERE    TABLE_CREATOR = UPPER(CURRENT_USER)



GRANT   SELECT

ON      PERSONAL_TABLES

TO      PUBLIC



GRANT   SELECT

ON      PERSONAL_COLUMNS

TO      PUBLIC



REVOKE   SELECT

ON       TABLES

FROM     PUBLIC



Answer 23.1:



CREATE  USER RONALDO WITH PASSWORD = 'NIKE'



Answer 23.2:



DROP    USER RONALDO



Answer 23.3:



GRANT   SELECT, INSERT

ON      PLAYERS

TO      RONALDO



Answer 23.4:



GRANT   ALL

ON      COMMITTEE_MEMBERS

TO      PUBLIC



Answer 23.5:



GRANT   UPDATE(STREET, HOUSENO, POSTCODE, TOWN)

ON      PLAYERS

TO      RONALDO



Answer 23.6:



GRANT  INSERT

ON     PLAYERS, TEAMS, PENALTIES, MATCHES, COMMITTEE_MEMBERS

TO     JACO, DIANE



Example 24.1: 



SELECT DBMSINFO('security_audit_state') AS AUDITING



Example 24.1: 



SELECT DBMSINFO('security_audit_log') AS AUDITFILE



Example 24.2: 



SELECT   FIRST 5 *

FROM     IIAUDIT

WHERE    USER_NAME = 'booksql'

ORDER BY AUDITTIME DESC



Example 24.3: 



REGISTER TABLE AUDITTABLE (

   AUDITTIME           CHAR(20) NOT NULL,

   USER_NAME           CHAR(32) NOT NULL,

   REAL_NAME           CHAR(32) NOT NULL,

   USERPRIVILEGES      CHAR(32) NOT NULL,

   OBJPRIVILEGES       CHAR(32) NOT NULL,

   DATABASE            CHAR(32) NOT NULL,

   AUDITSTATUS         CHAR(1)  NOT NULL,

   AUDITEVENT          CHAR(24) NOT NULL,

   OBJECTTYPE          CHAR(24) NOT NULL,

   OBJECTNAME          CHAR(32) NOT NULL,

   DESCRIPTION         CHAR(80) NOT NULL,

   OBJECTOWNER         CHAR(32) NOT NULL,

   DETAILNUM           INTEGER4 NOT NULL,

   DETAILINFO          VARCHAR(256) NOT NULL,

   SESSIONID           CHAR(16) NOT NULL,

   QUERYTEXT_SEQUENCE  INTEGER4 NOT NULL)

AS IMPORT FROM 'c:\test\audit.2'

WITH DBMS = SXA;



Example 24.4: 



Log on to the IIDBDB database and enable auditing for procedures:



ENABLE SECURITY_AUDIT PROCEDURE



Log on to the TENNIS database and create a procedure, call it, and remove the procedure:



CREATE PROCEDURE DO_NOTHING AS

DECLARE

   VAR1 VARCHAR(1) NOT NULL;

BEGIN

   VAR1 = 'X';

END



EXECUTE PROCEDURE DO_NOTHING



DROP PROCEDURE DO_NOTHING



SELECT   AUDITTIME, AUDITEVENT

FROM     IIAUDIT

WHERE    OBJECTTYPE = 'PROCEDURE'

AND      OBJECTNAME = 'do_nothing'



Example 24.5: 



SELECT   CODES.NAME,

         CASE ONOFF.STATE

            WHEN 0 THEN 'Off'

            WHEN 1 THEN 'On'

            ELSE 'Unknown'

         END AS STATUS

FROM     IISECURITYSTATE AS ONOFF INNER JOIN

         IISECURITY_STATE AS CODES

         ON ONOFF.ID = CODES.NUMBER

ORDER BY CODES.NUMBER



Example 24.6: 



DISABLE SECURITY_AUDIT PROCEDURE



Example 24.7: 



ENABLE SECURITY_AUDIT TABLE



Example 24.8: 



CREATE TABLE AUDIT1 (

   COL1   INTEGER NOT NULL PRIMARY KEY)

WITH SECURITY_AUDIT = (TABLE)



INSERT INTO AUDIT1 VALUES (1)



INSERT INTO AUDIT1 VALUES (2)



INSERT INTO AUDIT1 VALUES (3)



SELECT   AUDITTIME, AUDITEVENT, DESCRIPTION

FROM     IIAUDIT

WHERE    OBJECTTYPE = 'TABLE'

AND      OBJECTNAME = 'audit1'



Example 24.9: 



CREATE TABLE AUDIT2 (

   COL1   INTEGER NOT NULL PRIMARY KEY)

WITH SECURITY_AUDIT = (TABLE)



INSERT INTO AUDIT2 VALUES (1)



INSERT INTO AUDIT2 VALUES (2)



INSERT INTO AUDIT2 VALUES (3)



DELETE

FROM     AUDIT2



SELECT   AUDITTIME, AUDITEVENT, DESCRIPTION

FROM     IIAUDIT

WHERE    OBJECTTYPE = 'TABLE'

AND      OBJECTNAME = 'audit2'



Example 24.10: 



EBABLE SECURITY_AUDIT ROW



CREATE TABLE AUDIT3 (

   COL1   INTEGER NOT NULL PRIMARY KEY)

WITH SECURITY_AUDIT = (ROW)



INSERT INTO AUDIT3 VALUES (1)



INSERT INTO AUDIT3 VALUES (2)



INSERT INTO AUDIT3 VALUES (3)



UPDATE   AUDIT3

SET      COL1 = COL1 + 10



DELETE

FROM     AUDIT3



SELECT   AUDITTIME, AUDITEVENT, DESCRIPTION

FROM     IIAUDIT

WHERE    OBJECTTYPE IN ('TABLE', 'ROW')

AND      OBJECTNAME = 'audit3'



Example 24.11: 



CREATE TABLE AUDIT4 (

   COL1   INTEGER NOT NULL PRIMARY KEY)

WITH SECURITY_AUDIT = (ROW),

     SECURITY_AUDIT_KEY = (COL1)



INSERT INTO AUDIT4 VALUES (1)



INSERT INTO AUDIT4 VALUES (2)



INSERT INTO AUDIT4 VALUES (3)



UPDATE   AUDIT4

SET      COL1 = COL1 + 10



DELETE

FROM     AUDIT4



SELECT   AUDITTIME, AUDITEVENT, DETAILINFO

FROM     IIAUDIT

WHERE    OBJECTTYPE IN ('TABLE', 'ROW')

AND      OBJECTNAME = 'audit4'



Example 24.12: 



DISABLE SECURITY_AUDIT ROW



INSERT INTO AUDIT4 VALUES (1)



INSERT INTO AUDIT4 VALUES (2)



INSERT INTO AUDIT4 VALUES (3)



UPDATE   AUDIT4

SET      COL1 = COL1 + 10



SELECT   AUDITTIME, AUDITEVENT, DETAILINFO

FROM     IIAUDIT

WHERE    OBJECTTYPE IN ('TABLE', 'ROW')

AND      OBJECTNAME = 'audit4'



Example 24.13: 



DISABLE SECURITY_AUDIT TABLE



Example 24.14: 



CREATE USER U10

   WITH NOPASSWORD,

   SECURITY_AUDIT=(ALL_EVENTS)



CREATE TABLE AUDIT5 (

   COL1   INTEGER NOT NULL PRIMARY KEY)

WITH SECURITY_AUDIT = (TABLE)



INSERT INTO AUDIT5 VALUES (1)



INSERT INTO AUDIT5 VALUES (2)



SELECT   AUDITTIME, AUDITEVENT

FROM     IIAUDIT

WHERE    OBJECTTYPE = 'TABLE'

AND      OBJECTNAME = 'audit5'



Example 24.15: 



Log on to the IIDBDB database as BOOKSQL:



ENABLE SECURITY_AUDIT QUERY_TEXT



CREATE USER U11

   WITH NOPASSWORD,

   SECURITY_AUDIT=(QUERY_TEXT)



Log on to the TENNIS database as BOOKSQL:



GRANT   SELECT

ON      PLAYERS

TO      U11



Log on to the TENNIS database as U11:



SELECT   *

FROM     PLAYERS

WHERE    PLAYERNO > 100



Log on to the IIDBDB database as BOOKSQL:



SELECT   AUDITTIME, DETAILINFO

FROM     IIAUDIT

WHERE    UPPER(DETAILINFO) LIKE 'SELECT * FROM PLAYERS%'



ALTER SECURITY_AUDIT SUSPEND



ALTER SECURITY_AUDIT RESUME



ALTER SECURITY_AUDIT STOP



ALTER SECURITY_AUDIT RESTART



Example 24.16: 



ENABLE SECURITY_AUDIT ALARM



CREATE SECURITY_ALARM SA1

   ON TABLE PLAYERS

   IF FAILURE

   WHEN SELECT

   BY PUBLIC



CREATE USER U20 WITH NOPASSWORD



SELECT * FROM PLAYERS



SELECT   AUDITTIME, USER_NAME, AUDITSTATUS, AUDITEVENT,

         OBJECTTYPE, DESCRIPTION

FROM     IIAUDIT

WHERE    OBJECTNAME = 'players'



CREATE SECURITY_ALARM SA2

   ON TABLE PLAYERS

   IF FAILURE, SUCCESS

   WHEN SELECT, DELETE, INSERT, UPDATE

   BY PUBLIC



CREATE SECURITY_ALARM SA3

   ON TABLE PLAYERS

   BY PUBLIC



Example 24.17: 



CREATE SECURITY_ALARM SA4

   ON TABLE PENALTIES

   IF FAILURE

   WHEN SELECT

   BY USER U1



Example 24.18: 



CREATE SECURITY_ALARM SA5

   ON TABLE PENALTIES

   IF FAILURE

   WHEN SELECT

   BY GROUP G1



Example 24.19: 



CREATE SECURITY_ALARM SA6 ON DATABASE TENNIS

   IF FAILURE, SUCCESS

   WHEN CONNECT

   BY PUBLIC



Example 24.20: 



SELECT   USER_NAME, DATABASE, AUDITSTATUS, AUDITEVENT, OBJECTTYPE, DESCRIPTION

FROM     IIAUDIT

WHERE    AUDITTIME = CURRENT_DATE



Example 24.21: Record all user logons to Ingres.



CREATE SECURITY_ALARM SA7 ON CURRENT INSTALLATION

   IF FAILURE, SUCCESS

   WHEN CONNECT

   BY PUBLIC



Example 24.22: 



CREATE SECURITY_ALARM SA8 ON DATABASE TENNIS

   IF SUCCESS, FAILURE

   WHEN CONNECT

   BY PUBLIC

   RAISE DBEVENT DBE1



Example 24.23: 



DROP SECURITY_ALARM ON TABLE PLAYERS SA1



Example 24.24: 



DROP SECURITY_ALARM ON TABLE PLAYERS ALL



Example 24.25: 



DROP SECURITY_ALARM ON DATABASE TENNIS ALL



Example 24.26: 



DROP SECURITY_ALARM ON CURRENT INSTALLATION SA7



Example 24.27: 



SELECT   SECURITY_NUMBER

FROM     IISECURITY_ALARMS

WHERE    ALARM_NAME = 'sa1'



Example 24.28: 



SELECT   DISTINCT USER_NAME

FROM     IIAUDIT

WHERE    AUDITEVENT = 'CONNECT'

AND      OBJECTTYPE = 'ALARM'

AND      AUDITSTATUS = 'Y'

AND      AUDITTIME >= CURRENT_TIMESTAMP – INTERVAL '2' HOUR



Examples 24.29: 



SELECT   DOW(AUDITTIME), COUNT(*)

FROM     IIAUDIT

WHERE    OBJECTNAME = 'players'

GROUP BY DOW(AUDITTIME)



Example 24.30: 



SELECT   TABLE_NAME

FROM     IITABLES

WHERE    TABLE_OWNER = 'booksql'

AND      TABLE_NAME NOT IN

        (SELECT   OBJECTNAME

         FROM     IIAUDIT

         WHERE    DATABASE = 'tennis'

         AND      AUDITEVENT IN 

                 ('SELECT', 'DELETE', 'INSERT', 'UPDATE')

         AND      OBJECTTYPE = 'TABLE'

         AND      OBJECTOWNER = 'booksql'

         AND      AUDITTIME >= 

                  CURRENT_DATE - INTERVAL '2' MONTH)



Example 24.31: 



SELECT   USER_NAME, COUNT(*)

FROM     IIAUDIT

WHERE    AUDITTIME = CURRENT_DATE

AND      OBJECTNAME = 'players'

AND      OBJECTTYPE = 'ALARM'

GROUP BY USER_NAME



Example 24.32: 



SELECT   FIRST 10 USER_NAME, NUMBER

FROM    (SELECT   USER_NAME, COUNT(*) AS NUMBER

         FROM     IIAUDIT

         WHERE    AUDITSTATUS = 'N'

         AND      DATABASE = 'tennis'

         AND      OBJECTTYPE = 'ALARM'

         GROUP BY USER_NAME) AS T1

ORDER BY NUMBER



Example 25.1: 



CREATE TABLE COUNTRIES (

   COUNTRYNO     INTEGER NOT NULL PRIMARY KEY,

   COUNTRYNAME   VARCHAR(30) NOT NULL)



CREATE SEQUENCE COUNTRYNUMBERS



INSERT   INTO COUNTRIES

        (COUNTRYNO, COUNTRYNAME)

VALUES  (NEXT VALUE FOR COUNTRYNUMBERS, 'China')



UPDATE   COUNTRIES

SET      COUNTRYNO = NEXT VALUE FOR COUNTRYNUMBERS

WHERE    COUNTRYNO = 1



Example 25.2: 



SELECT   PLAYERNO, NEXT VALUE FOR COUNTRYNUMBERS

FROM     PLAYERS



Example 25.3: 



CREATE SEQUENCE DECIMALS AS DECIMAL(10,0)



SELECT   NEXT VALUE FOR DECIMALS AS NUMBERS

FROM     MATCHES

WHERE    MATCHNO <= 5



Example 25.4: 



CREATE SEQUENCE HUNDRED START WITH 100



SELECT   NEXT VALUE FOR HUNDRED AS NUMBERS

FROM     MATCHES

WHERE    MATCHES <= 5



Example 25.5: 



CREATE SEQUENCE BIG_STEPS

   INCREMENT BY 100



SELECT   NEXT VALUE FOR BIG_STEPS AS NUMBERS

FROM     MATCHES

WHERE    MATCHNO <= 5



Example 25.6: 



CREATE SEQUENCE BACKWARDS INCREMENT BY -10



SELECT   NEXT VALUE FOR BACKWARDS AS NUMBERS

FROM     MATCHES

WHERE    MATCHNO <= 5   



Example 25.7: 



CREATE SEQUENCE MIN98

   START WITH 100

   INCREMENT BY –1

   MINVALUE 98



SELECT   NEXT VALUE FOR MIN98 AS NUMBERS

FROM     MATCHES

WHERE    MATCHNO <= 5



Example 25.8: 



CREATE SEQUENCE CYCLE3

   START WITH 1

   INCREMENT BY 1

   MAXVALUE 3

   CYCLE



SELECT   NEXT VALUE FOR CYCLE3 AS NUMBERS

FROM     MATCHES



Example 25.9: 



CREATE SEQUENCE STANDARD

   MINVALUE 1

   MAXVALUE 2147483647

   START WITH 1

   INCREMENT BY 1

   NOCYCLE

   CACHE 20

   ORDER



Example 25.10: 



CREATE SEQUENCE LEAGUENUMBERS

   START WITH 1000



UPDATE   PLAYERS

SET      LEAGUENO = CHAR(NEXT VALUE FOR LEAGUENO,4)

WHERE    LEAGUENO IS NOT NULL



Example 25.11: 



CREATE SEQUENCE TRIPLE



SELECT   NEXT VALUE FOR TRIPLE AS NUMBER1,

         NEXT VALUE FOR TRIPLE AS NUMBER2,

         NEXT VALUE FOR TRIPLE AS NUMBER3

FROM     MATCHES

WHERE    MATCHNO <= 5



Example 25.12: 



CREATE SEQUENCE SMATH



SELECT   1000 * (NEXT VALUE FOR SMATH) AS NUMBER1,

         NEXT VALUE FOR SMATH * NEXT VALUE FOR SMATH

            AS NUMBER2,

         MOD(NEXT VALUE FOR SMATH,4) AS NUMBER3

FROM     MATCHES

WHERE    MATCHNO <= 5



Example 25.13: 



CREATE SEQUENCE TEAMNUMBERS



INSERT   INTO TEAMS

        (TEAMNO, PLAYERNO, DIVISION)

VALUES  (NEXT VALUE FOR TEAMNUMBERS, 6, 'first')



UPDATE   MATCHES

SET      TEAMNO = CURRENT VALUE FOR TEAMNUMBERS

WHERE    MATCHNO = 10



Example 25.14: 



CREATE SEQUENCE SEQNO1



CREATE SEQUENCE SEQNO2



SELECT   NEXT VALUE FOR SEQNO1 AS NUMBER1,

         CURRENT VALUE FOR SEQNO1 AS NUMBER2,

         CURRENT VALUE FOR SEQNO2 AS NUMBER3,

         NEXT VALUE FOR SEQNO2 AS NUMBER4

FROM     MATCHES

WHERE    MATCHNO <= 5



Example 25.15: 



ALTER SEQUENCE COUNTRYNUMBERS

   RESTART WITH 100



Example 25.16: 



ALTER SEQUENCE FIVES

   MAXVALUE 800



Example 25.17: 



DROP SEQUENCE DECREASE



Example 25.18: 



GRANT   NEXT

ON      SEQUENCE COUNTRYNUMBERS

TO      BEN



Example 25.19: 



GRANT   CREATE_SEQUENCE

ON      DATABASE TENNIS

TO      BEN



Answer 25.1.1:



CREATE SEQUENCE EVEN_NUMBERS

   START WITH 2

   INCREMENT BY 2



Answer 25.1.2:



CREATE SEQUENCE TENS

   START WITH 80

   INCREMENT BY –10



Answer 25.1.3:



CREATE SEQUENCE FROM_1_TO_4

   START WITH 1

   INCREMENT BY 1

   MINVALUE 1

   MAXVALUE 4

   NOCACHE

   CYCLE



Answer 25.1.4:



CREATE SEQUENCE BIT

   START WITH 0

   MINVALUE 0

   MAXVALUE 1

   NOCACHE

   CYCLE



Example 26.1:



CREATE SCHEMA AUTHORIZATION TENNIS_SCHEMA



Example 26.2: 



CREATE VIEW TENNIS_SCHEMA.SEVERAL_MATCHES AS

SELECT   *

FROM     MATCHES

WHERE    MATCHENO < 5



Example 26.3: 



CREATE SCHEMA AUTHORIZATION TWO_TABLES

   CREATE TABLE TABLE1 (COL1 INTEGER)

   CREATE TABLE TABLE2 (COL1 INTEGER)

   CREATE INDEX INDEX1 ON TABLE1(COL1)



Example 27.1: 



CREATE PROCEDURE DELETE_MATCHES

   (IN P_PLAYERNO INTEGER) AS

BEGIN

   DELETE

   FROM   MATCHES

   WHERE  PLAYERNO = :P_PLAYERNO;

END



Example 27.2: 



EXECUTE PROCEDURE DELETE_MATCHES (P_PLAYERNO=8)



Exampe 27.3: 



CREATE PROCEDURE HELLOWORLD AS

BEGIN

   MESSAGE 'Hello world!';

END



EXECUTE PROCEDURE HELLOWORLD



Example 27.4: 



CREATE PROCEDURE TOTAL

  (IN  NUMBER1 INTEGER NOT NULL,

   IN  NUMBER2 INTEGER NOT NULL,

   OUT NUMBER3 INTEGER NOT NULL) AS

BEGIN

   NUMBER3 = NUMBER1+NUMBER2;

   NUMBER1 = 100;

END



CREATE PROCEDURE CALL_TOTAL AS

DECLARE

   VAR1 INTEGER NOT NULL;

   VAR2 INTEGER NOT NULL;

   VAR3 INTEGER NOT NULL;

BEGIN

   VAR1 = 20;

   VAR2 = 30;

   EXECUTE PROCEDURE TOTAL

      (NUMBER1=VAR1, NUMBER2=VAR2, NUMBER3=VAR3);

   MESSAGE VAR1;

   MESSAGE VAR3;

END



EXECUTE PROCEDURE CALL_TOTAL



Example 27.5: 

 

CREATE PROCEDURE MULTIPLYMUCH

   (IN NUMBER1 INTEGER NOT NULL,

    IN NUMBER2 INTEGER NOT NULL,

    IN NUMBER3 INTEGER WITH NULL,

    IN NUMBER4 INTEGER WITH NULL,

    IN NUMBER5 INTEGER WITH NULL,

    IN NUMBER6 INTEGER WITH NULL,

    IN NUMBER7 INTEGER WITH NULL,

    IN NUMBER8 INTEGER WITH NULL,

    IN NUMBER9 INTEGER WITH NULL,

    IN NUMBER10 INTEGER WITH NULL) AS

BEGIN

   IF NUMBER3 IS NULL THEN NUMBER3 = 1 ENDIF;

   IF NUMBER4 IS NULL THEN NUMBER4 = 1 ENDIF;

   IF NUMBER5 IS NULL THEN NUMBER5 = 1 ENDIF;

   IF NUMBER6 IS NULL THEN NUMBER6 = 1 ENDIF;

   IF NUMBER7 IS NULL THEN NUMBER7 = 1 ENDIF;

   IF NUMBER8 IS NULL THEN NUMBER8 = 1 ENDIF;

   IF NUMBER9 IS NULL THEN NUMBER9 = 1 ENDIF;

   IF NUMBER10 IS NULL THEN NUMBER10 = 1 ENDIF;

   NUMBER1 = NUMBER1*NUMBER2*NUMBER3*NUMBER4*NUMBER5*

          NUMBER6*NUMBER7*NUMBER8*NUMBER9*NUMBER10;

   MESSAGE NUMBER1;

END



EXECUTE PROCEDURE MULTIPLYMUCH

   (NUMBER1=10, NUMBER2=12, NUMBER3=15)



Example 27.6: 



CREATE PROCEDURE CONCATENATE

   (IN TEXT1 VARCHAR(20) NOT NULL, 

    IN TEXT2 VARCHAR(20) NOT NULL) AS

DECLARE

   VAR1 VARCHAR(40) NOT NULL;

BEGIN

   VAR1 = TEXT1 || TEXT2;

   MESSAGE 1 VAR1;

END



EXECUTE PROCEDURE CONCATENATE(TEXT1='data', TEXT2='base')



Example 27.7: 



CREATE PROCEDURE DEFAULTINTEGER AS

DECLARE

   VAR1 INTEGER NOT NULL;

BEGIN

   MESSAGE VAR1;

END



EXECUTE PROCEDURE DEFAULTINTEGER



Example 27.8: 



CREATE PROCEDURE HIGHEST

   (IN P1 INTEGER,

    IN P2 INTEGER,

    OUT P3 INTEGER) AS

BEGIN

   IF P1 > P2 THEN

      P3 = 1;

   ELSEIF P1 = P2 THEN

      P3 = 2;

   ELSE

      P3 = 3;

   ENDIF;

END



Example 27.9: 



CREATE PROCEDURE FIBONACCI

   (INOUT NUMBER1 INTEGER,

    INOUT NUMBER2 INTEGER,

    INOUT NUMBER3 INTEGER) AS

BEGIN

   NUMBER3 = NUMBER1 + NUMBER2;

   IF NUMBER3 > 10000 THEN

      NUMBER3 = NUMBER3 - 10000;

   ENDIF;

   NUMBER1 = NUMBER2;

   NUMBER2 = NUMBER3;

END



CREATE PROCEDURE CALL_FIBONACCI AS

DECLARE

   VAR1, VAR2, VAR3 INTEGER NOT NULL;

BEGIN

   VAR1 = 16;

   VAR2 = 27;

   EXECUTE PROCEDURE FIBONACCI (NUMBER1=VAR1, NUMBER2=VAR2, NUMBER3=VAR3);

   MESSAGE VAR3;

   EXECUTE PROCEDURE FIBONACCI (NUMBER1=VAR1, NUMBER2=VAR2, NUMBER3=VAR3);

   MESSAGE VAR3;

   EXECUTE PROCEDURE FIBONACCI (NUMBER1=VAR1, NUMBER2=VAR2, NUMBER3=VAR3);

   MESSAGE VAR3;

END



Example 27.10: 



CREATE PROCEDURE AGE

   (IN  START_DATE  DATE,

    IN  END_DATE    DATE,

    OUT YEARS       INTEGER,

    OUT MONTHS      INTEGER,

    OUT DAYS        INTEGER) AS

DECLARE 

   NEXT_DATE, PREVIOUS_DATE DATE;

BEGIN

   YEARS = 0;

   PREVIOUS_DATE = START_DATE;

   NEXT_DATE = START_DATE + INTERVAL '1' YEAR;

   WHILE NEXT_DATE <= END_DATE DO

      YEARS = YEARS + 1;

      PREVIOUS_DATE = NEXT_DATE;

      NEXT_DATE = NEXT_DATE + INTERVAL '1' YEAR;

   ENDWHILE;

   MONTHS = 0;

   NEXT_DATE = PREVIOUS_DATE + INTERVAL '1' MONTH;

   WHILE NEXT_DATE <= END_DATE DO

      MONTHS = MONTHS + 1;

      PREVIOUS_DATE = NEXT_DATE;

      NEXT_DATE = NEXT_DATE + INTERVAL '1' MONTH;

   ENDWHILE;

   DAYS = 0;

   NEXT_DATE = PREVIOUS_DATE + INTERVAL '1' DAY;

   WHILE NEXT_DATE <= END_DATE DO

      DAYS = DAYS + 1;

      PREVIOUS_DATE = NEXT_DATE;

      NEXT_DATE = NEXT_DATE + INTERVAL '1' DAY;

   ENDWHILE;

END



CREATE PROCEDURE CALL_AGE AS

DECLARE

   VAR1, VAR2, VAR3 INTEGER NOT NULL;

BEGIN

   EXECUTE PROCEDURE AGE (START_DATE='1991-01-12',

      END_DATE='1999-07-09', YEARS=VAR1, MONTHS=VAR2, DAYS=VAR3);

   MESSAGE VAR1;

   MESSAGE VAR2;

   MESSAGE VAR3;

END



Example 27.11: 



CREATE PROCEDURE STOPWHILE AS

BEGIN

   WHILE 1 = 1 DO

      ENDLOOP;

      MESSAGE 'Inside the WHILE statement';

   ENDWHILE;

   MESSAGE 'Ready';

END



Example 27.12: 



CREATE PROCEDURE ALL_NAMES AS

DECLARE

   VAR1 VARCHAR(20) NOT NULL;

BEGIN

   FOR SELECT   DISTINCT TOWN

       INTO     :VAR1

       FROM     PLAYERS

       ORDER BY TOWN

   DO

      MESSAGE VAR1;

   ENDFOR;

END



Example 27.13: 



CREATE PROCEDURE GET_PLAYERS

  (IN PNO INTEGER NOT NULL) AS

DECLARE

   VAR_NAME     VARCHAR(15) NOT NULL;

   VAR_TOWN     VARCHAR(30) NOT NULL;

   VAR_POSTCODE CHAR(6) NOT NULL;

BEGIN

   FOR SELECT   NAME, TOWN, POSTCODE

       INTO     :VAR_NAME, :VAR_TOWN, :VAR_POSTCODE

       FROM     PLAYERS

       WHERE    PLAYERNO = :PNO

   DO

      MESSAGE VAR_NAME;

      MESSAGE VAR_TOWN;

      MESSAGE VAR_POSTCODE;

   ENDFOR;

END



Example 27.14: 



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 P_PLAYERNO INTEGER NOT NULL,

    INOUT NUMBER INTEGER NOT NULL) AS

DECLARE V_FATHER, V_MOTHER INTEGER;

BEGIN

   FOR SELECT   FATHER_PLAYERNO

       INTO     :V_FATHER

       FROM     PLAYERS_WITH_PARENTS

       WHERE    PLAYERNO = P_PLAYERNO

   DO V_FATHER = V_FATHER;

   ENDFOR;

   FOR SELECT   MOTHER_PLAYERNO

       INTO     :V_MOTHER

       FROM     PLAYERS_WITH_PARENTS

       WHERE    PLAYERNO = P_PLAYERNO

   DO V_MOTHER = V_MOTHER;

   ENDFOR;

   IF V_FATHER IS NOT NULL THEN

      EXECUTE PROCEDURE TOTAL_NUMBER_OF_PARENTS

         (P_PLAYERNO=V_FATHER, NUMBER=NUMBER);

      NUMBER = NUMBER + 1;

   ENDIF;

   IF V_MOTHER IS NOT NULL THEN

      EXECUTE PROCEDURE TOTAL_NUMBER_OF_PARENTS

         (P_PLAYERNO=V_MOTHER, NUMBER=NUMBER);

      NUMBER = NUMBER + 1;

   ENDIF;

END



CREATE PROCEDURE CALL_PARENTS AS

DECLARE

   PLAYER, NUMBER INTEGER NOT NULL;

BEGIN

   NUMBER = 0;

   PLAYER = 1;

   EXECUTE PROCEDURE TOTAL_NUMBER_OF_PARENTS

     (P_PLAYERNO=PLAYER, NUMBER=NUMBER);

   MESSAGE PLAYER;

   MESSAGE NUMBER;

END



EXECUTE PROCEDURE CALL_PARENTS



Example 27.15: 



CREATE PROCEDURE TOTAL_PENALTIES_PLAYER

   (IN P_PLAYERNO INTEGER NOT NULL, OUT TOTAL_PENALTIES DECIMAL(8,2)) AS

DECLARE

   VAR1 VARCHAR(20) NOT NULL;

BEGIN

   SELECT SUM(AMOUNT)

   INTO   :TOTAL_PENALTIES

   FROM   PENALTIES

   WHERE  PLAYERNO = P_PLAYERNO;

   VAR1 = CAST(TOTAL_PENALTIES AS VARCHAR(20));

   MESSAGE VAR1;

END



EXECUTE PROCEDURE TOTAL_PENALTIES_PLAYER(P_PLAYERNO=27)



SELECT   FATHER_PLAYERNO, MOTHER_PLAYERNO

INTO     :V_FATHER, :V_MOTHER

FROM     PLAYERS_WITH_PARENTS

WHERE    PLAYERNO = P_PLAYERNO



Example 27.16: 



CREATE PROCEDURE GET_ADDRESS

   (IN  P_PLAYERNO INTEGER,

    OUT P_STREET   VARCHAR(30),

    OUT P_HOUSENO  CHAR(4),

    OUT P_TOWN     VARCHAR(30),

    OUT P_POSTCODE CHAR(6)) AS

DECLARE

   VAR1 VARCHAR(70) NOT NULL;

BEGIN

   SELECT TOWN, STREET, HOUSENO, POSTCODE

   INTO   :P_TOWN, :P_STREET, :P_HOUSENO, :P_POSTCODE

   FROM   PLAYERS

   WHERE  PLAYERNO = P_PLAYERNO;

   VAR1 = P_TOWN||','||P_STREET||','||P_HOUSENO||

          IFNULL(','||P_POSTCODE,'');

   MESSAGE VAR1;

END



EXECUTE PROCEDURE GET_ADDRESS(P_PLAYERNO=27)



Example 27.17: 



CREATE TABLE FIBON (

   NUMBER1   INTEGER NOT NULL PRIMARY KEY,

   NUMBER2   INTEGER NOT NULL)



CREATE PROCEDURE FIBONACCI_START AS

BEGIN

   DELETE FROM FIBON;

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

END



CREATE PROCEDURE FIBONACCI_GIVE

   (INOUT NUMBER INTEGER NOT NULL) AS

DECLARE 

   N1, N2 INTEGER;

BEGIN

   SELECT NUMBER1, NUMBER2

   INTO   :N1, :N2

   FROM   FIBON;

   NUMBER = N1 + N2;

   IF NUMBER > 10000 THEN

      NUMBER = NUMBER - 10000;

   ENDIF;

   N1 = N2;

   N2 = NUMBER;

   UPDATE FIBON

   SET    NUMBER1 = N1,

          NUMBER2 = N2;

END



CREATE PROCEDURE CALL_FIBONACCI AS

DECLARE

   VAR1 INTEGER NOT NULL;

BEGIN

   EXECUTE PROCEDURE FIBONACCI_START;

   EXECUTE PROCEDURE FIBONACCI_GIVE(NUMBER=VAR1);

   MESSAGE VAR1;

   EXECUTE PROCEDURE FIBONACCI_GIVE(NUMBER=VAR1);

   MESSAGE VAR1;

   EXECUTE PROCEDURE FIBONACCI_GIVE(NUMBER=VAR1);

   MESSAGE VAR1;

END



Example 27.18: 



CREATE PROCEDURE DELETE_PLAYER

   (IN P_PLAYERNO INTEGER NOT NULL) AS

DECLARE

   NUMBER_OF_PLAYERS   INTEGER;

   NUMBER_OF_PENALTIES INTEGER;

   NUMBER_OF_TEAMS     INTEGER;

   NUMBER_OF_MEMBERS   INTEGER;

BEGIN

   SELECT COUNT(*)

   INTO   :NUMBER_OF_PLAYERS

   FROM   PLAYERS

   WHERE  PLAYERNO = :P_PLAYERNO;

   IF NUMBER_OF_PLAYERS > 0 THEN

      SELECT COUNT(*)

      INTO   :NUMBER_OF_PENALTIES

      FROM   PENALTIES

      WHERE  PLAYERNO = :P_PLAYERNO;



      SELECT COUNT(*)

      INTO   :NUMBER_OF_TEAMS

      FROM   TEAMS

      WHERE  PLAYERNO = :P_PLAYERNO;



      SELECT COUNT(*)

      INTO   :NUMBER_OF_MEMBERS

      FROM   COMMITTEE_MEMBERS

      WHERE  PLAYERNO = :P_PLAYERNO;



      IF NUMBER_OF_PENALTIES = 0 AND

         NUMBER_OF_TEAMS = 0 AND

         NUMBER_OF_MEMBERS = 0 THEN

         EXECUTE PROCEDURE DELETE_MATCHES (P_PLAYERNO=P_PLAYERNO);

         DELETE FROM PLAYERS

         WHERE  PLAYERNO = :P_PLAYERNO;

         MESSAGE 'Players removed.'

      ENDIF;

   ELSE

      MESSAGE 'No players have been deleted.'

   ENDIF;

END



Example 27.19: 



CREATE PROCEDURE DELETE_AND_INSERT

  (OUT PAR1 INTEGER NOT NULL) AS

DECLARE

   VAR1 VARCHAR(20) NOT NULL;

BEGIN

   PAR1 = 100;

   VAR1 = CAST(PAR1 AS VARCHAR(20));

   MESSAGE 1 VAR1;

   DELETE FROM MATCHES;

   MESSAGE 2 'DELETE statement has been processed.';

   INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (1, 6, 'first');

   VAR1 = CAST(PAR1 AS VARCHAR(20));

   MESSAGE 3 VAR1;

   MESSAGE 4 'INSERT statement has been processed.';

END



EXECUTE PROCEDURE DELETE_EN_INSERT



Example 27.20: 



CREATE PROCEDURE DIVIDE_BY_ZERO

  (OUT PAR1 INTEGER NOT NULL) AS

DECLARE

   VAR1 VARCHAR(20) NOT NULL;

BEGIN

   PAR1 = 100;

   VAR1 = CAST(PAR1 AS VARCHAR(20));

   MESSAGE 1 VAR1;

   DELETE FROM MATCHES;

   MESSAGE 2 'DELETE statement has been processed.';

   PAR1 = PAR1 / 0;

   VAR1 = CAST(PAR1 AS VARCHAR(20));

   MESSAGE 3 VAR1;

   MESSAGE 4 'Assignment statement has been processed.';

END



EXECUTE PROCEDURE DIVIDE_BY_ZERO



Example 27.21: 



CREATE PROCEDURE ERRORMESSAGE AS

BEGIN

   UPDATE PLAYERS SET PLAYERNO=6;

   MESSAGE IIERRORNUMBER;

END



Example 27.22: 



CREATE PROCEDURE ERRORMESSAGE2

   (OUT ERROR INTEGER NOT NULL) AS

BEGIN

   UPDATE PLAYERS SET PLAYERNO=6;

   ERROR = IIERRORNUMBER;

END



CREATE PROCEDURE CALL_ERRORMESSAGE2 AS

DECLARE VAR1 INTEGER NOT NULL;

BEGIN

   EXECUTE PROCEDURE ERRORMESSAGE2(ERROR=VAR1);

   MESSAGE VAR1;

END



EXECUTE PROCEDURE CALL_ERRORMESSAGE2



Example 27.23: 



CREATE PROCEDURE REMOVEALLPENALTIES AS

BEGIN

   DELETE FROM PENALTIES;

   MESSAGE IIROWCOUNT;

END



Example 27.24: 



CREATE PROCEDURE ROWCOUNT_ERROR AS

DECLARE

   VAR1, VAR2 INTEGER NOT NULL;

BEGIN

   UPDATE PLAYERS SET PLAYERNO=6;

   SELECT IIROWCOUNT INTO :VAR1;

   SELECT IIERRORNUMBER INTO :VAR2;

   MESSAGE VAR1;

   MESSAGE VAR2;

END



Example 27.25: 



CREATE PROCEDURE STOP_PROC AS

BEGIN

   MESSAGE 1;

   RAISE ERROR 100 'Error';

   MESSAGE 2;

END



EXECUTE PROCEDURE STOP_PROC



Example 27.26: 



CREATE TABLE NUMBERS_AND_NAMES (

   PLAYERNO   INTEGER NOT NULL PRIMARY KEY,

   NAME       CHAR(15) NOT NULL)



CREATE PROCEDURE FILL

   (INPUTTABLE = SET OF (PLAYERNO  INTEGER  NOT NULL,

                         NAME      CHAR(15) NOT NULL)) AS

BEGIN

   INSERT INTO NUMBERS_AND_NAMES

   SELECT * FROM INPUTTABLE;

END



DECLARE GLOBAL TEMPORARY TABLE TEMPTAB (PLAYERNO, NAME) AS

(SELECT PLAYERNO, NAME FROM PLAYERS)

ON COMMIT PRESERVE ROWS WITH NORECOVERY



EXECUTE PROCEDURE FILL (INPUTTABLE=SESSION.TEMPTAB)



SELECT * FROM NUMBERS_AND_NAMES



Example 27.27: Develop a stored procedure that takes a set of numbers as input and prints them. Next, call the procedure. 



CREATE PROCEDURE SHOWNUMBERS

   (INPUTTABLE = SET OF (NUMBER INTEGER NOT NULL)) AS

DECLARE

   VAR1 INTEGER NOT NULL;

BEGIN

   FOR SELECT NUMBER INTO :VAR1 FROM INPUTTABLE DO

      MESSAGE VAR1;

   ENDFOR;

END



DECLARE GLOBAL TEMPORARY TABLE TEMPTAB (NUMBER) AS

(SELECT CAST(1 AS INTEGER) UNION

 SELECT 2 UNION

 SELECT 3 UNION

 SELECT 4)

ON COMMIT PRESERVE ROWS WITH NORECOVERY



EXECUTE PROCEDURE SHOWNUMBERS (INPUTTABLE=SESSION.TEMPTAB)



Example 27.28: 



CREATE PROCEDURE GIVE_ROWS

   (IN PAR1 INTEGER NOT NULL)

   RESULT ROW (INTEGER, CHAR(15)) AS

DECLARE

   PNO INTEGER NOT NULL;

   NAME CHAR(15) NOT NULL;

BEGIN

   FOR SELECT   PLAYERNO, NAME

       INTO     :PNO, :NAME

       FROM     PLAYERS

       WHERE    PLAYERNO >= :PAR1

   DO

      RETURN ROW (:PNO, :NAME);

      MESSAGE PNO NAME;

   ENDFOR;

END



Example 27.29: 



CREATE PROCEDURE RETURN1

   (OUT PAR1 INTEGER) AS

BEGIN

   PAR1 = 100;

   RETURN 200;

   PAR1 = 300;

END



CREATE PROCEDURE CALL_RETURN1 AS

DECLARE

   VAR1 INTEGER NOT NULL;

   VAR2 INTEGER NOT NULL;

BEGIN

   EXECUTE PROCEDURE RETURN1 (PAR1=VAR1) INTO :VAR2;

   MESSAGE VAR1;

   MESSAGE VAR2;

END



EXECUTE PROCEDURE CALL_RETURN1



Example 27.30: 



SELECT   COLUMN_NAME

FROM     IICOLUMNS

WHERE    TABLE_OWNER = '$ingres'

AND      TABLE_NAME = 'iiprocedures'

ORDER BY COLUMN_SEQUENCE



Example 27.31: 



DROP PROCEDURE DELETE_PLAYER



Example 27.32: 



CREATE PROCEDURE BASIS AS

BEGIN

   MESSAGE 'Procedure 1';

END



CREATE PROCEDURE TOP AS

BEGIN

   EXECUTE PROCEDURE BASIS;

END



DROP PROCEDURE BASIS



EXECUTE PROCEDURE TOP



Example 27.33: 



CREATE TABLE BASIS (COL1 INTEGER)



CREATE PROCEDURE EMPTYBASIS AS

BEGIN

   DELETE FROM BASIS;

END



DROP TABLE BASIS



EXECUTE PROCEDURE BASIS



Example 27.34: 



GRANT EXECUTE

ON    PROCEDURE DELETE_MATCHES

TO    JOHN



Example 28.1: 



CREATE TABLE CHANGES (

   CHA_SEQNO          INTEGER NOT NULL PRIMARY KEY,

   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))



CREATE SEQUENCE CHANGES_SEQNO



Example 28.2: 



CREATE PROCEDURE INSERT_CHANGES

   (IN CHA_PLAYERNO      INTEGER,

    IN CHA_TYPE          CHAR(1),

    IN CHA_LEAGUENO_NEW  CHAR(4),

    IN CHA_LEAGUENO_OLD  CHAR(4)) AS

BEGIN

   INSERT INTO CHANGES

      (CHA_SEQNO, CHA_USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE,

       CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)

   VALUES(NEXT VALUE FOR CHANGES_SEQNO, CURRENT_USER, CURRENT_TIMESTAMP,

       :CHA_PLAYERNO, :CHA_TYPE, :CHA_LEAGUENO_NEW, :CHA_LEAGUENO_OLD);

END



CREATE RULE INSERT_PLAYERS

   AFTER

   INSERT ON PLAYERS FOR EACH ROW

   EXECUTE PROCEDURE INSERT_CHANGES

      (CHA_PLAYERNO = NEW.PLAYERNO, CHA_TYPE = 'I', CHA_LEAGUENO_NEW = NEW.LEAGUENO, 

       CHA_LEAGUENO_OLD = NULL)



INSERT INTO PLAYERS VALUES

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

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



SELECT   CHA_SEQNO AS SEQNO, CHA_USER AS USER, 

         CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,

         CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD

FROM     CHANGES

WHERE    CHA_USER = CURRENT_USER

AND      CHA_PLAYERNO = 2000

AND      CHA_TIME = 

        (SELECT   MAX(CHA_TIME)

         FROM     CHANGES

         WHERE    CHA_USER = CURRENT_USER

         AND      CHA_PLAYERNO = 2000)



Example 28.3: 



CREATE RULE DELETE_PLAYERS

   AFTER DELETE ON PLAYERS FOR EACH ROW

   EXECUTE PROCEDURE INSERT_CHANGES

      (CHA_PLAYERNO = OLD.PLAYERNO, CHA_TYPE = 'D', CHA_LEAGUENO_NEW = NULL,

       CHA_LEAGUENO_OLD = OLD.LEAGUENO)



Example 28.4: 



DELETE

FROM     PLAYERS

WHERE    PLAYERNO = 2000



SELECT   CHA_SEQNO AS SEQNO, CHA_USER AS USER, CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,

         CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD

FROM     CHANGES

WHERE    CHA_USER = CURRENT_USER

AND      CHA_PLAYERNO = 2000

AND      CHA_TIME = 

        (SELECT   MAX(CHA_TIME)

         FROM     CHANGES

         WHERE    CHA_USER = CURRENT_USER

         AND      CHA_PLAYERNO = 2000)



Example 28.5: 



CREATE RULE UPDATE_PLAYERS

   AFTER UPDATE ON PLAYERS FOR EACH ROW

   EXECUTE PROCEDURE INSERT_CHANGES

      (CHA_PLAYERNO = OLD.PLAYERNO, CHA_TYPE= 'U', CHA_LEAGUENO_NEW = NEW.LEAGUENO,

       CHA_LEAGUENO_OLD = OLD.LEAGUENO)



Example 28.6: 



UPDATE   PLAYERS

SET      LEAGUENO = '4444'

WHERE    PLAYERNO = 6



SELECT   CHA_SEQNO AS SEQNO, CHA_USER AS USER, CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,

         CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD

FROM     CHANGES

WHERE    CHA_USER = CURRENT_USER

AND      CHA_PLAYERNO = 6

AND      CHA_TIME = (SELECT   MAX(CHA_TIME)

                     FROM     CHANGES

                     WHERE    CHA_USER = CURRENT_USER

                     AND      CHA_PLAYERNO = 6)



Example 28.7: 



CREATE RULE UPDATE_PLAYERS2

   AFTER UPDATE(LEAGUENO) ON PLAYERS FOR EACH ROW

   EXECUTE PROCEDURE INSERT_CHANGES

      (CHA_PLAYERNO = OLD.PLAYERNO, CHA_TYPE= 'U', CHA_LEAGUENO_NEW = NEW.LEAGUENO,

       CHA_LEAGUENO_OLD = OLD.LEAGUENO)



Example 28.8: 



CREATE RULE UPDATE_PLAYERS3

   AFTER UPDATE(LEAGUENO) ON PLAYERS 

   WHERE OLD.PLAYERNO > 100

   FOR EACH ROW

   EXECUTE PROCEDURE INSERT_CHANGES

      (CHA_PLAYERNO = OLD.PLAYERNO, CHA_TYPE= 'U', CHA_LEAGUENO_NEW = NEW.LEAGUENO,

       CHA_LEAGUENO_OLD = OLD.LEAGUENO)



Example 28.9: 



DELETE

FROM     PLAYERS

WHERE    PLAYERNO < 50



SELECT   FIRST 10

         CHA_SEQNO AS SEQNO, CHA_USER AS USER, CHA_PLAYERNO AS PLAYERNO,

         CHA_TYPE AS TYPE, CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD

FROM     CHANGES

WHERE    CHA_USER = CURRENT_USER

AND      CHA_PLAYERNO < 50

ORDER BY CHA_SEQNO DESC



Example 28.10: 



CREATE PROCEDURE INSERT_CHANGES2

   (INPUTTABLE = SET OF

      (IN CHA_PLAYERNO      INTEGER,

       IN CHA_TYPE          CHAR(1),

       IN CHA_LEAGUENO_NEW  CHAR(4),

       IN CHA_LEAGUENO_OLD  CHAR(4))) AS

BEGIN

   INSERT INTO CHANGES

      (CHA_SEQNO, CHA_USER, CHA_TIME, CHA_PLAYERNO,

       CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)

   SELECT NEXT VALUE FOR CHANGES_SEQNO, CURRENT_USER, CURRENT_TIMESTAMP,

          CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD

   FROM   INPUTTABLE;

END



CREATE RULE DELETE_PLAYERS2

   AFTER DELETE ON PLAYERS FOR EACH STATEMENT

   EXECUTE PROCEDURE INSERT_CHANGES2

      (CHA_PLAYERNO = OLD.PLAYERNO, CHA_TYPE = 'D',

       CHA_LEAGUENO_NEW = NULL, CHA_LEAGUENO_OLD = OLD.LEAGUENO)



DELETE

FROM     PLAYERS

WHERE    PLAYERNO < 50



SELECT   FIRST 10

         CHA_SEQNO AS SEQNO, CHA_USER AS USER, 

         CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,

         CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD

FROM     CHANGES

WHERE    CHA_USER = CURRENT_USER

AND      CHA_PLAYERNO < 50

ORDER BY CHA_SEQNO DESC



Example 28.11: 



INSERT INTO PLAYERS_MAT (PLAYERNO, NUMBER_OF_MATCHES)

SELECT   P.PLAYERNO, COUNT(DISTINCT MATCHNO)

FROM     PLAYERS AS P LEFT OUTER JOIN MATCHES AS M

         ON P.PLAYERNO = M.PLAYERNO

GROUP BY P.PLAYERNO



CREATE PROCEDURE INSERT_PLAYERS_MAT

   (IN PLAYERNO  INTEGER NOT NULL,

    IN NUMBER     INTEGER NOT NULL) AS

BEGIN

   INSERT INTO PLAYERS_MAT

   VALUES (:PLAYERNO, :NUMBER);

END



Example 28.12: 



CREATE RULE INSERT_PLAYERS_MAT

   AFTER INSERT ON PLAYERS FOR EACH ROW

   EXECUTE PROCEDURE INSERT_PLAYERS_MAT

      (PLAYERNO = NEW.PLAYERNO, NUMBER = 0)



Example 28.13: 



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 28.14: Create a rule on the PLAYERS table that ensures that if a new player is removed, he or she is also removed from the PLAYERS_MAT table.



CREATE PROCEDURE DELETE_PLAYERS_MAT

   (IN PLAYERNO  INTEGER NOT NULL) AS

BEGIN

   DELETE FROM PLAYERS_MAT

   WHERE PLAYERNO = :PLAYERNO;

END



CREATE RULE DELETE_PLAYERS_MAT

   AFTER DELETE ON PLAYERS FOR EACH ROW

   EXECUTE PROCEDURE DELETE_PLAYERS_MAT

      (PLAYERNO = OLD.PLAYERNO)



Example 28.15: 



CREATE PROCEDURE UPDATE_PLAYERS_MAT

   (IN PLAYERNO  INTEGER NOT NULL,

    IN NUMBER    INTEGER NOT NULL) AS

BEGIN

   UPDATE PLAYERS_MAT

   SET    NUMBER_OF_MATCHES = NUMBER_OF_MATCHES + :NUMBER

   WHERE  PLAYERNO = :PLAYERNO;

END



CREATE RULE INSERT_MATCHES

   AFTER INSERT ON MATCHES FOR EACH ROW

   EXECUTE PROCEDURE UPDATE_PLAYERS_MAT

      (PLAYERNO = NEW.PLAYERNO, NUMBER = 1)



Example 28.16: 



CREATE RULE DELETE_MATCHES

   AFTER DELETE ON MATCHES FOR EACH ROW

   EXECUTE PROCEDURE UPDATE_PLAYERS_MAT

      (PLAYERNO = OLD.PLAYERNO, NUMBER = -1)



Example 28.17: 



CREATE PROCEDURE UPDATE_SUM

   (IN PLAYERNO INTEGER NOT NULL) AS

DECLARE

   TOTAL DECIMAL(8,2);

BEGIN

   SELECT   SUM(AMOUNT)

   INTO     TOTAL

   FROM     PENALTIES

   WHERE    PLAYERNO = :PLAYERNO;

   UPDATE   PLAYERS

   SET      SUM_PENALTIES = TOTAL

   WHERE    PLAYERNO = :PLAYERNO

END



CREATE RULE SUM_PENALTIES_INSERT

   AFTER INSERT, UPDATE ON PENALTIES FOR EACH ROW

   EXECUTE PROCEDURE UPDATE_SUM

     (PLAYERNO = NEW.PLAYERNO)



CREATE RULE SUM_PENALTIES_DELETE

   AFTER DELETE, UPDATE ON PENALTIES FOR EACH ROW

   EXECUTE PROCEDURE UPDATE_SUM

     (PLAYERNO = OLD.PLAYERNO)



Example 28.18: 



DROP RULE BORN_VS_JOINED



Example 29.1: 



CREATE DBEVENT EVENT1



Example 29.2: 



REGISTER DBEVENT EVENT1



Example 29.3: 



RAISE DBEVENT EVENT1 'DBevent has been activated.'



Example 29.4: 



INQUIRE_SQL(:var1=dbeventtext, :var2=dbeventtime)



Example 29.5: 



REMOVE DBEVENT EVENT1



Example 29.6: 



DROP DBEVENT EVENT1



Example 31.1: 



DELETE

FROM     PENALTIES

WHERE    PLAYERNO = 44



SELECT   *

FROM     PENALTIES



Example 31.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 31.5: 



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + 25

WHERE    PAYMENTNO = 4



SELECT   *

FROM     PENALTIES

WHERE    PAYMENTNO = 4

	

Example 31.6: 



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 31.7: 



SELECT   PLAYERNO

FROM     PLAYERS

WHERE    TOWN = 'Stratford'



Example 31.8: 



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + 25

WHERE    PAYMENTNO = 4



UPDATE   PENALTIES

SET      AMOUNT = AMOUNT + 30

WHERE    PAYMENTNO = 4



Example 31.9: 



SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE



Example 31.10: 



SET CURRENT LOCK TIMEOUT TO WAIT 10