OS/2 Shareware BBS: 8 Other/08-Other.zip/sqlfun.zip/COURSE.SQL

Text File | 1993-10-25 | 16KB | 597 lines

   ---------------------------------------------------
   ---  Date:        Oct-93                        ---
   ---  Author:      Graeme Birchall               ---
   ---  Address:     35 River Drive South #1214    ---
   ---               Jersey City NJ 07310          ---
   ---  Ph/Fax:      [phone]                ---
   ---  Compuserve   73540,1566                    ---
   ---  Purpose:     DB2/2 Course - Sample DDL     ---
   ---                            - Exercise SQL   ---
   ---------------------------------------------------

   CREATE TABLE BASE_TABLE
     (U1     SMALLINT  NOT NULL,
      U2     CHAR(02)  NOT NULL,
      U3     CHAR(02)  NOT NULL,
      D1     SMALLINT  NOT NULL,
      D2     CHAR(01)  NOT NULL,
      DATE1  DATE      NOT NULL);
   COMMIT;

   INSERT INTO BASE_TABLE VALUES (0,'C0','W0',9,'J','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (1,'C1','W1',8,'I','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (2,'C2','W2',7,'H','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (3,'C3','W3',6,'G','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (4,'C4','W4',5,'F','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (5,'C5','W5',4,'E','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (6,'C6','W6',3,'D','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (7,'C7','W7',2,'C','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (8,'C8','W8',1,'B','0001-01-01');
   INSERT INTO BASE_TABLE VALUES (9,'C9','W9',0,'A','0001-01-01');
   COMMIT;

   CREATE TABLE CITY_TABLE
     (CITY_NUM            INTEGER   NOT NULL,
      CITY_RGN            CHAR(04)  NOT NULL,
      CITY_DATA           CHAR(04)  NOT NULL,
      CITY_PADDING        CHAR(80)  NOT NULL);
   COMMIT;

   CREATE UNIQUE INDEX ICITYNUM  ON CITY_TABLE
      (CITY_NUM);
   COMMIT;

   CREATE INDEX ICITYNME  ON CITY_TABLE
      (CITY_RGN);
   COMMIT;

   INSERT INTO CITY_TABLE
   SELECT U1
         ,' '
         ,D2
         ,'CITY'
   FROM BASE_TABLE;
   COMMIT;

   UPDATE CITY_TABLE
   SET    CITY_RGN = 'C0'
   WHERE  CITY_NUM = CITY_NUM/2 * 2;

   UPDATE CITY_TABLE
   SET    CITY_RGN = 'C1'
   WHERE  CITY_NUM <> CITY_NUM/2 * 2;
   COMMIT;

   CREATE TABLE WHSE_TABLE
     (CITY_NUM            INTEGER   NOT NULL,
      WHSE_NUM            INTEGER   NOT NULL,
      WHSE_NAME           CHAR(04)  NOT NULL,
      WHSE_SIZE           INTEGER   NOT NULL,
      WHSE_PADDING        CHAR(76)  NOT NULL);
   COMMIT;

   CREATE UNIQUE INDEX IWHSENUM  ON WHSE_TABLE
      (CITY_NUM, WHSE_NUM);
   COMMIT;

   CREATE UNIQUE INDEX IWHSENME  ON WHSE_TABLE
      (WHSE_NAME);
   COMMIT;

   INSERT INTO WHSE_TABLE
   SELECT A.U1
         ,100 + B.U1
         ,SUBSTR(CHAR(A.DATE1 + (A.U1*10 + B.U1) YEARS,ISO),1,4)
         ,100 + A.U1
         ,'WHSE'
   FROM  BASE_TABLE A,
         BASE_TABLE B
   WHERE B.U1 < 4;
   COMMIT;

   CREATE TABLE PROD_TABLE
     (PROD_NUM            INTEGER   NOT NULL,
      PROD_NAME           CHAR(04)  NOT NULL,
      PROD_TYPE           CHAR(04)  NOT NULL,
      PROD_COST           INTEGER   NOT NULL,
      PROD_PADDING        CHAR(76)  NOT NULL);
   COMMIT;

   CREATE UNIQUE INDEX IPRODNUM  ON PROD_TABLE
      (PROD_NUM);
   COMMIT;

   CREATE UNIQUE INDEX IPRODNME  ON PROD_TABLE
      (PROD_NAME);
   COMMIT;

   CREATE INDEX IPRODTYP  ON PROD_TABLE
      (PROD_TYPE);
   COMMIT;

   INSERT INTO PROD_TABLE
   SELECT  2000 + A.U1*100 + B.U1*10 + C.U1
          ,SUBSTR(CHAR(A.DATE1 + (A.U1*100 + B.U1*10 + C.U1) YEARS,ISO),1,4)
          ,A.D2
          ,2000 + A.U1*10+B.U1
          ,'PROD'
   FROM  BASE_TABLE A,
         BASE_TABLE B,
         BASE_TABLE C
   WHERE B.U1 < 3;
   COMMIT;

   UPDATE PROD_TABLE
   SET    PROD_PADDING = 'ABC%DEF'
   WHERE  PROD_NUM     =  2000;

   CREATE VIEW PROD_VIEW  (PROD_COST)
       AS   SELECT  AVG(PROD_COST)
       FROM PROD_TABLE;

   CREATE TABLE WP_TABLE
     (CITY_NUM            INTEGER   NOT NULL,
      WHSE_NUM            INTEGER   NOT NULL,
      PROD_NUM            INTEGER   NOT NULL,
      QTY                 INTEGER   NOT NULL,
      WP_PADDING          CHAR(76)  NOT NULL);
   COMMIT;

   CREATE VIEW WP_VIEW
    (CITY_NUM
    ,NMBR_PROD
    ,SUM_QTY
    ,NMBR_ROWS)
   AS
   SELECT   CITY_NUM
           ,COUNT(DISTINCT PROD_NUM)
           ,SUM(QTY)
           ,COUNT(*)
   FROM     WP_TABLE
   GROUP BY CITY_NUM;
   COMMIT;

   INSERT INTO WP_TABLE
   SELECT CITY_NUM
         ,WHSE_NUM
         ,PROD_NUM
         ,WHSE_SIZE + PROD_COST
         ,'WP'
   FROM   WHSE_TABLE,
          PROD_TABLE
   WHERE CITY_NUM IN (0,1);
   COMMIT;

   INSERT INTO WP_TABLE
   SELECT CITY_NUM
         ,WHSE_NUM
         ,PROD_NUM
         ,WHSE_SIZE + PROD_COST
         ,'WP'
   FROM   WHSE_TABLE,
          PROD_TABLE
   WHERE CITY_NUM IN (2,3);
   COMMIT;

   INSERT INTO WP_TABLE
   SELECT CITY_NUM
         ,WHSE_NUM
         ,PROD_NUM
         ,WHSE_SIZE + PROD_COST
         ,'WP'
   FROM   WHSE_TABLE,
          PROD_TABLE
   WHERE CITY_NUM IN (4,5);
   COMMIT;

   INSERT INTO WP_TABLE
   SELECT CITY_NUM
         ,WHSE_NUM
         ,PROD_NUM
         ,WHSE_SIZE + PROD_COST
         ,'WP'
   FROM   WHSE_TABLE,
          PROD_TABLE
   WHERE CITY_NUM IN (6,7);
   COMMIT;

   INSERT INTO WP_TABLE
   SELECT CITY_NUM
         ,WHSE_NUM
         ,PROD_NUM
         ,WHSE_SIZE + PROD_COST
         ,'WP'
   FROM   WHSE_TABLE,
          PROD_TABLE
   WHERE CITY_NUM IN (8,9);
   COMMIT;

   CREATE UNIQUE INDEX IWPWHSE ON WP_TABLE
      (CITY_NUM, WHSE_NUM, PROD_NUM);
   COMMIT;

   CREATE UNIQUE INDEX IWPPROD ON WP_TABLE
      (PROD_NUM, CITY_NUM, WHSE_NUM);
   COMMIT;

   CREATE TABLE SALE_TABLE
     (PROD_NUM        INTEGER       NOT NULL,
      SALE_DATE       DATE                  ,
      SALE_TIME       TIME                  ,
      NUM_SOLD        INTEGER               ,
      ITEM_COST       DECIMAL(7,2)          ,
      CUST_NUM        INTEGER               ,
      SALE_PADDING    CHAR(63)              );
   COMMIT;

   CREATE VIEW SALE_WEEK
     (PROD_NUM       ,SALE_DATE   ,SALE_TIME
     ,NUM_SOLD       ,ITEM_COST   ,CUST_NUM
     ,SALE_PADDING   ,WEEK_DAY)
   AS SELECT
      PROD_NUM       ,SALE_DATE   ,SALE_TIME
     ,NUM_SOLD       ,ITEM_COST   ,CUST_NUM
     ,SALE_PADDING
     ,((DAYS(SALE_DATE) - DAYS('1993-01-17')) -
     (((DAYS(SALE_DATE) - DAYS('1993-01-17'))/7) * 7)) + 1
   FROM SALE_TABLE;
   COMMIT;

   CREATE UNIQUE INDEX ISALENUM  ON SALE_TABLE
      (PROD_NUM
      ,SALE_DATE
      ,SALE_TIME);
   COMMIT;

   CREATE INDEX ISALCUST  ON SALE_TABLE
      (CUST_NUM);
   COMMIT;

   INSERT INTO SALE_TABLE
   SELECT 2000 + A.U1*100 + B.U1*10 + C.U1
         ,DATE('1994-01-01') + B.U1 MONTHS - A.U1 DAYS
         ,TIME('12.00.00')   - D.U1 HOURS  + C.U1 MINUTES
         ,D.U1*10 + A.U1
         ,A.U1*100 + B.U1*10 + D.U1/100
         ,D.U1*1000 + C.U1*100
         ,'SALE'
   FROM  BASE_TABLE A
        ,BASE_TABLE B
        ,BASE_TABLE C
        ,BASE_TABLE D
   WHERE B.U1 = 0
     AND C.U1 BETWEEN 1 AND 8;
   COMMIT;

   INSERT INTO SALE_TABLE
   SELECT 2000 + A.U1*100 + B.U1*10 + C.U1
         ,DATE('1994-01-01') + B.U1 MONTHS - A.U1 DAYS
         ,TIME('12.00.00')   - D.U1 HOURS  + C.U1 MINUTES
         ,D.U1*10 + A.U1
         ,A.U1*100 + B.U1*10 + D.U1/100
         ,D.U1*1000 + C.U1*100
         ,'SALE'
   FROM  BASE_TABLE A
        ,BASE_TABLE B
        ,BASE_TABLE C
        ,BASE_TABLE D
   WHERE B.U1 = 1
     AND C.U1 BETWEEN 1 AND 8;
   COMMIT;

   INSERT INTO SALE_TABLE
   SELECT 2000 + A.U1*100 + B.U1*10 + C.U1
         ,DATE('1994-01-01') + B.U1 MONTHS - A.U1 DAYS
         ,TIME('12.00.00')   - D.U1 HOURS  + C.U1 MINUTES
         ,D.U1*10 + A.U1
         ,A.U1*100 + B.U1*10 + D.U1/100
         ,D.U1*1000 + C.U1*100
         ,'SALE'
   FROM  BASE_TABLE A
        ,BASE_TABLE B
        ,BASE_TABLE C
        ,BASE_TABLE D
   WHERE B.U1 = 2
     AND C.U1 BETWEEN 1 AND 8;
   COMMIT;

   UPDATE SALE_TABLE
   SET    CUST_NUM = NULL
   WHERE  PROD_NUM - (PROD_NUM/10*10) BETWEEN 3 AND 4;
   COMMIT;

   UPDATE SALE_TABLE
   SET    CUST_NUM  = NULL
         ,NUM_SOLD  = NULL
   WHERE  PROD_NUM - (PROD_NUM/10*10) BETWEEN 5 AND 6;
   COMMIT;

   UPDATE SALE_TABLE
   SET    CUST_NUM  = NULL
         ,NUM_SOLD  = NULL
         ,ITEM_COST = NULL
   WHERE  PROD_NUM - (PROD_NUM/10*10) BETWEEN 7 AND 8;
   COMMIT;

   ---------------------------------------------------
   ---    CHAPTER 2, EXERCISE SQL SAMPLES          ---
   ---------------------------------------------------

   SELECT COUNT(*)
   FROM   SALE_TABLE
   WHERE  CUST_NUM IS NULL;

   SELECT COUNT(*)
   FROM   SALE_TABLE
   WHERE  DAY(SALE_DATE) IN (1,11,21,31);

   SELECT COUNT(DISTINCT WHSE_NUM)
   FROM   WP_TABLE
   WHERE  CITY_NUM < 2;

   SELECT AVG(PROD_NUM)
         ,AVG(DISTINCT PROD_NUM)
   FROM   SALE_TABLE;

   SELECT INTEGER(SUM(NUM_SOLD))
         ,DECIMAL(SUM(NUM_SOLD))
         ,FLOAT(SUM(NUM_SOLD))
   FROM   SALE_TABLE;

   SELECT COUNT(*)
   FROM   SALE_TABLE
   WHERE  MONTH(SALE_DATE) = 12
     AND  HOUR(SALE_TIME)  =  9
     AND  NUM_SOLD IS NOT NULL;

   SELECT COUNT(*)
   FROM   PROD_TABLE
   WHERE  PROD_TYPE LIKE '%J%'
     AND  PROD_PADDING LIKE 'P%'
     AND  LENGTH(PROD_PADDING)  >  3
     AND  SUBSTR(PROD_NAME,2,1) < 'A';

   SELECT AVG(CITY_NUM)
         ,AVG(CITY_NUM * 1.0)
   FROM   CITY_TABLE;

   SELECT CHAR(SALE_DATE,USA)
         ,CHAR(SALE_DATE,EUR)
         ,DAY(SALE_DATE)
         ,DECIMAL(DAY(SALE_DATE),2,0)
         ,TIMESTAMP(SALE_DATE,SALE_TIME)
   FROM   SALE_TABLE
   WHERE  PROD_NUM = 2001
     AND  HOUR(SALE_TIME) = 3;

   SELECT SALE_DATE
         ,SALE_DATE + 10 YEARS + 2 MONTHS + 5 DAYS
         ,SALE_DATE + DECIMAL(00100205,8,0)
         ,DAYS('1995-12-31') - DAYS(SALE_DATE)
         ,DAY(SALE_DATE) * HOUR(SALE_TIME)
   FROM   SALE_TABLE
   WHERE  PROD_NUM = 2001
     AND  HOUR(SALE_TIME) = 3;

   SELECT SALE_DATE
         ,CHAR(SALE_DATE,ISO)
         ,SUBSTR(CHAR(SALE_DATE,ISO),1,7)
         ,FLOAT(CUST_NUM)
   FROM   SALE_TABLE
   WHERE  PROD_NUM = 2001
     AND  HOUR(SALE_TIME) = 3;

   SELECT COUNT(*)
   FROM   SALE_TABLE
   WHERE  PROD_NUM  BETWEEN 2000 AND 3000
     AND  SALE_DATE > '1991-12-31'
     AND  SALE_TIME BETWEEN '03.00.00' AND '06.00.00'
     AND  SALE_PADDING LIKE '_A%';

   SELECT PROD_NUM
         ,SUBSTR(PROD_PADDING,1,4)
         ,TRANSLATE(SUBSTR(PROD_PADDING,1,4),'D','P')
         ,TRANSLATE(SUBSTR(PROD_PADDING,1,4),'DQ','PR')
         ,TRANSLATE(SUBSTR(PROD_PADDING,1,4),'DP','PD')
         ,TRANSLATE(SUBSTR(PROD_PADDING,1,4),'D','P','Z')
   FROM   PROD_TABLE
   WHERE  PROD_NUM  < 2009
     AND  PROD_TYPE IN ('J','K','L','M')
     AND  PROD_NAME <> ''''
     AND  PROD_PADDING LIKE '% %'
     AND  TRANSLATE(PROD_PADDING,'$@','%$') NOT LIKE '%$%';

   ---------------------------------------------------
   ---    CHAPTER 3, EXERCISE SQL SAMPLES          ---
   ---------------------------------------------------

   SELECT   PROD_NUM
           ,COUNT(*)
   FROM     SALE_TABLE
   WHERE    PROD_NUM < 2010
     AND    CUST_NUM IS NOT NULL
   GROUP BY PROD_NUM
   HAVING   COUNT(DISTINCT SALE_DATE) = 1;

   SELECT   PROD_NUM
           ,COUNT(*)
   FROM     SALE_TABLE
   WHERE    PROD_NUM < 2010
     AND    CUST_NUM IS NULL
   GROUP BY PROD_NUM
   HAVING   COUNT(*) > 5
      AND   COUNT(DISTINCT SALE_DATE) = 1
      AND   AVG(HOUR(SALE_TIME)) > 4;

   SELECT   *
   FROM     SALE_TABLE A
   WHERE    PROD_NUM  < 2005
     AND    ITEM_COST < 10
     AND    SALE_TIME < '05.00.00'
     AND    1 <
           (SELECT COUNT(DISTINCT CUST_NUM)
            FROM   SALE_TABLE B
            WHERE  A.PROD_NUM = B.PROD_NUM
              AND  B.CUST_NUM IS NOT NULL);

   SELECT   COUNT(*)
           ,COUNT(DISTINCT A.PROD_TYPE)
   FROM     PROD_TABLE A
           ,SALE_TABLE B
   WHERE    A.PROD_NUM = B.PROD_NUM
     AND    A.PROD_TYPE LIKE 'J%'
     AND    B.SALE_TIME < '11.00.00';

   SELECT   COUNT(*)
   FROM     PROD_TABLE A
   WHERE    PROD_NUM BETWEEN 2000 AND 2100
     AND    5 <
           (SELECT COUNT(*)
            FROM   SALE_TABLE B
            WHERE  A.PROD_NUM = B.PROD_NUM)
     AND    10 <
           (SELECT COUNT(*)
            FROM   WP_TABLE C
            WHERE  A.PROD_NUM = C.PROD_NUM);

   SELECT   COUNT(*)
   FROM     PROD_TABLE
   WHERE    PROD_NUM NOT IN
           (SELECT PROD_NUM
            FROM   SALE_TABLE);

   SELECT   COUNT(*)
   FROM     PROD_TABLE A
   WHERE    PROD_NUM NOT IN
           (SELECT B.PROD_NUM
            FROM   SALE_TABLE B
            WHERE  A.PROD_NUM = B.PROD_NUM);

   SELECT  'NULL'
           ,COUNT(*)
   FROM     SALE_TABLE A
   WHERE    CUST_NUM IS NULL
   UNION
   SELECT  'NOT NULL'
           ,COUNT(*)
   FROM     SALE_TABLE A
   WHERE    CUST_NUM IS NOT NULL;

   SELECT   PROD_NUM
   FROM     PROD_TABLE
   WHERE    PROD_TYPE = 'J'
   EXCEPT
   SELECT   PROD_NUM
   FROM     SALE_TABLE;

   SELECT CITY_NUM
   FROM   CITY_TABLE
   INTERSECT
   SELECT CITY_NUM
   FROM   WHSE_TABLE
   WHERE  WHSE_SIZE < 104
   EXCEPT
   SELECT CITY_NUM
   FROM   WHSE_TABLE
   WHERE  WHSE_NAME > '0033'
   UNION ALL
   SELECT CITY_NUM
   FROM   WHSE_TABLE
   WHERE  WHSE_SIZE > 108
   ORDER BY 1 DESC;

   ---------------------------------------------------
   ---    CHAPTER 4, EXERCISE SQL SAMPLES          ---
   ---------------------------------------------------

   SELECT   PROD_NUM
   FROM     SALE_TABLE
   GROUP BY PROD_NUM
   HAVING   COUNT(*) > 8
      AND   COUNT(DISTINCT CUST_NUM) > 3
      AND  (AVG(NUM_SOLD)  > 40
       OR   MAX(CUST_NUM)  < 9999)
      AND   MIN(SALE_TIME) < '23.00.00'
      AND   AVG(NUM_SOLD)  < 47
   ORDER BY PROD_NUM DESC;

   SELECT  *
   FROM    PROD_TABLE A
   WHERE   PROD_NUM < 2100
     AND ( SUBSTR(PROD_TYPE,4,1) = '0'
      OR   EXISTS
          (SELECT *
           FROM   SALE_TABLE B
           WHERE  A.PROD_NUM = B.PROD_NUM
             AND  B.CUST_NUM IS NOT NULL) );

   SELECT   D.PROD_TYPE
           ,A.CITY_RGN
           ,B.*
   FROM     CITY_TABLE A
           ,WHSE_TABLE B
           ,WP_TABLE   C
           ,PROD_TABLE D
   WHERE    A.CITY_DATA = 'A'
     AND    A.CITY_NUM  = B.CITY_NUM
     AND  ( B.WHSE_NAME = '0093'
      OR    B.WHSE_SIZE < 22 )
     AND    B.CITY_NUM  = C.CITY_NUM
     AND    B.WHSE_NUM  = C.WHSE_NUM
     AND    C.QTY       < 2222
     AND    C.PROD_NUM  = D.PROD_NUM
     AND    D.PROD_TYPE = 'J'
     AND    EXISTS
           (SELECT *
            FROM   SALE_TABLE E
            WHERE  D.PROD_NUM  = E.PROD_NUM
              AND  E.SALE_DATE > DATE('1992246')
              AND  E.CUST_NUM IS NOT NULL);

   SELECT   *
   FROM     SALE_TABLE A
   WHERE    PROD_NUM < 2020
     AND (  CUST_NUM IN
           (SELECT MAX(CUST_NUM)
            FROM   SALE_TABLE B
            WHERE  A.PROD_NUM = B.PROD_NUM)
     OR     CUST_NUM IN
           (SELECT MIN(CUST_NUM)
            FROM   SALE_TABLE B
            WHERE  A.PROD_NUM = B.PROD_NUM) )
   ORDER BY PROD_NUM
           ,SALE_TIME;

   SELECT   CITY_NUM
           ,CITY_RGN
           ,SUBSTR(CITY_RGN,LENGTH(CITY_RGN)-2,1)
   FROM     CITY_TABLE;

   SELECT   DECIMAL(PROD_NUM,8,3)
           ,CHAR(SALE_DATE,USA)
           ,DECIMAL(MINUTE(SALE_TIME),2,0)
           ,SUBSTR(SALE_PADDING,1,4)
           ,NUM_SOLD * 16
   FROM     SALE_TABLE
   WHERE    PROD_NUM   < 2010
     AND    PROD_NUM   < YEAR(SALE_DATE + 100 YEARS)
     AND    HOUR(SALE_TIME)           > MINUTE(SALE_TIME)
     AND    YEAR(CURRENT DATE)        < 1997
     AND    CURRENT SERVER           <> 'FRED';

   SELECT AVG(WHSE_SIZE)
   FROM   WHSE_TABLE;

   SELECT AVG(WHSE_SIZE * 1.0)
   FROM   WHSE_TABLE;

   SELECT *
   FROM   WHSE_TABLE
   WHERE  CITY_NUM =
         (SELECT MAX(CITY_NUM)
          FROM   WHSE_TABLE
          WHERE  CITY_NUM <
                (SELECT MAX(CITY_NUM)
                 FROM   WHSE_TABLE));