home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 14 Text
/
14-Text.zip
/
sqlfun.zip
/
SQLFUN.SQL
< prev
Wrap
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: (201)-963-0071 ---
--- 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));