./ $SETHWDC 08.05 11/10/25 11/10/25 09:00 75 75 21 JCL //HWINS01D JOB (HW213AB99T,PGRN),'SQLB TRAINING',NOTIFY=&SYSUID, 00010003 // TIME=1,MSGCLASS=Y 00020001 /*WHILE HWTDMSTR 00030000 //* 00040000 //JOBLIB DD DSN=HWTD.SDSNLOAD,DISP=SHR 00050000 //* 00060001 //SETVARS SET SRCLIB=&SYSUID..SQLB.LIB 00070001 //* 00071002 //CDBTRAIN EXEC PGM=IKJEFT01,REGION=2048K 00072002 //SYSTSPRT DD SYSOUT=* 00073002 //SYSTSIN DD * 00074002 DSN SYSTEM(HWTD) 00075002 RUN PROGRAM(DSNTIAD) 00076002 //SYSPRINT DD SYSOUT=* 00077002 //SYSUDUMP DD SYSOUT=* 00078002 //SYSIN DD DSN=&SRCLIB(CDBTRAIN),DISP=SHR 00079002 //* 00080000 //CSUPP EXEC PGM=IKJEFT01,REGION=2048K 00090000 //SYSTSPRT DD SYSOUT=* 00100000 //SYSTSIN DD * 00110000 DSN SYSTEM(HWTD) 00120000 RUN PROGRAM(DSNTIAD) 00130000 //SYSPRINT DD SYSOUT=* 00140000 //SYSUDUMP DD SYSOUT=* 00150000 //SYSIN DD DSN=&SRCLIB(CSUPP),DISP=SHR 00160001 //* 00170000 //LSUPP EXEC DSNUPROC,SYSTEM=HWTD,UID=&SYSUID.A,UTPROC='' 00180005 //DSNUPROC.SORTWK01 DD DSN=&&TEMP1, 00190000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00200000 //DSNUPROC.SORTWK02 DD DSN=&&TEMP2, 00210000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00220000 //DSNUPROC.SORTWK03 DD DSN=&&TEMP3, 00230000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00240000 //DSNUPROC.SORTWK04 DD DSN=&&TEMP4, 00250000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00260000 //DSNUPROC.SYSREC DD DSN=&SRCLIB(DATASUPP),DISP=SHR 00270004 //DSNUPROC.SYSUT1 DD DSN=&&TEMP5, 00280000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00290000 //DSNUPROC.SORTOUT DD DSN=&&TEMP6, 00300000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00310000 //DSNUPROC.SYSERR DD DSN=&&TEMP7, 00320000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00330000 //DSNUPROC.SYSMAP DD DSN=&&TEMP8, 00340000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00350000 //DSNUPROC.SYSIN DD DSN=&SRCLIB(ULODSUPP),DISP=SHR 00360001 //* 00370000 //CPART EXEC PGM=IKJEFT01,REGION=2048K 00380000 //SYSTSPRT DD SYSOUT=* 00390000 //SYSTSIN DD * 00400000 DSN SYSTEM(HWTD) 00410000 RUN PROGRAM(DSNTIAD) 00420000 //SYSPRINT DD SYSOUT=* 00430000 //SYSUDUMP DD SYSOUT=* 00440000 //SYSIN DD DSN=&SRCLIB(CPART),DISP=SHR 00450001 //* 00460000 //LPART EXEC DSNUPROC,SYSTEM=HWTD,UID=&SYSUID.A,UTPROC='' 00470005 //DSNUPROC.SORTWK01 DD DSN=&&TEMP1, 00480000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00490000 //DSNUPROC.SORTWK02 DD DSN=&&TEMP2, 00500000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00510000 //DSNUPROC.SORTWK03 DD DSN=&&TEMP3, 00520000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00530000 //DSNUPROC.SORTWK04 DD DSN=&&TEMP4, 00540000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00550000 //DSNUPROC.SYSREC DD DSN=&SRCLIB(DATAPART),DISP=SHR 00560004 //DSNUPROC.SYSUT1 DD DSN=&&TEMP5, 00570000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00580000 //DSNUPROC.SORTOUT DD DSN=&&TEMP6, 00590000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00600000 //DSNUPROC.SYSERR DD DSN=&&TEMP7, 00610000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00620000 //DSNUPROC.SYSMAP DD DSN=&&TEMP8, 00630000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00640000 //DSNUPROC.SYSIN DD DSN=&SRCLIB(ULODPART),DISP=SHR 00650001 // 00860000 ./ $SETIBM 01.00 11/11/30 11/11/30 12:23 76 76 0 JCL //IBMUSERD JOB 'TRAIN-RIGHT',MSGCLASS=H,CLASS=S, 00010000 // NOTIFY=&SYSUID 00020000 //* 00030000 //PRIVPROC JCLLIB ORDER=(DSN910.PROCLIB) 00040000 //JOBLIB DD DSN=DSN910.DB9G.RUNLIB.LOAD,DISP=SHR 00050000 // DD DSN=DSN910.SDSNLOAD,DISP=SHR 00060000 //* 00070000 //SETVARS SET SRCLIB=&SYSUID..SQLB.LIB 00080000 //* 00090000 //CDBTRAIN EXEC PGM=IKJEFT01,REGION=2048K 00100000 //SYSTSPRT DD SYSOUT=* 00110000 //SYSTSIN DD * 00120000 DSN SYSTEM(DB9G) 00130000 RUN PROGRAM(DSNTIAD) PLAN(DSNTIA91) 00140000 //SYSPRINT DD SYSOUT=* 00150000 //SYSUDUMP DD SYSOUT=* 00160000 //SYSIN DD DSN=&SRCLIB(CDBTRAIN),DISP=SHR 00170000 //* 00180000 //CSUPP EXEC PGM=IKJEFT01,REGION=2048K 00190000 //SYSTSPRT DD SYSOUT=* 00200000 //SYSTSIN DD * 00210000 DSN SYSTEM(DB9G) 00220000 RUN PROGRAM(DSNTIAD) PLAN(DSNTIA91) 00230000 //SYSPRINT DD SYSOUT=* 00240000 //SYSUDUMP DD SYSOUT=* 00250000 //SYSIN DD DSN=&SRCLIB(CSUPP),DISP=SHR 00260000 //* 00270000 //LSUPP EXEC DSNUPROC,SYSTEM=DB9G,UID=&SYSUID.A,UTPROC='' 00280000 //DSNUPROC.SORTWK01 DD DSN=&&TEMP1, 00290000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00300000 //DSNUPROC.SORTWK02 DD DSN=&&TEMP2, 00310000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00320000 //DSNUPROC.SORTWK03 DD DSN=&&TEMP3, 00330000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00340000 //DSNUPROC.SORTWK04 DD DSN=&&TEMP4, 00350000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00360000 //DSNUPROC.SYSREC DD DSN=&SRCLIB(DATASUPP),DISP=SHR 00370000 //DSNUPROC.SYSUT1 DD DSN=&&TEMP5, 00380000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00390000 //DSNUPROC.SORTOUT DD DSN=&&TEMP6, 00400000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00410000 //DSNUPROC.SYSERR DD DSN=&&TEMP7, 00420000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00430000 //DSNUPROC.SYSMAP DD DSN=&&TEMP8, 00440000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00450000 //DSNUPROC.SYSIN DD DSN=&SRCLIB(ULODSUPP),DISP=SHR 00460000 //* 00470000 //CPART EXEC PGM=IKJEFT01,REGION=2048K 00480000 //SYSTSPRT DD SYSOUT=* 00490000 //SYSTSIN DD * 00500000 DSN SYSTEM(DB9G) 00510000 RUN PROGRAM(DSNTIAD) PLAN(DSNTIA91) 00520000 //SYSPRINT DD SYSOUT=* 00530000 //SYSUDUMP DD SYSOUT=* 00540000 //SYSIN DD DSN=&SRCLIB(CPART),DISP=SHR 00550000 //* 00560000 //LPART EXEC DSNUPROC,SYSTEM=DB9G,UID=&SYSUID.A,UTPROC='' 00570000 //DSNUPROC.SORTWK01 DD DSN=&&TEMP1, 00580000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00590000 //DSNUPROC.SORTWK02 DD DSN=&&TEMP2, 00600000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00610000 //DSNUPROC.SORTWK03 DD DSN=&&TEMP3, 00620000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00630000 //DSNUPROC.SORTWK04 DD DSN=&&TEMP4, 00640000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00650000 //DSNUPROC.SYSREC DD DSN=&SRCLIB(DATAPART),DISP=SHR 00660000 //DSNUPROC.SYSUT1 DD DSN=&&TEMP5, 00670000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00680000 //DSNUPROC.SORTOUT DD DSN=&&TEMP6, 00690000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00700000 //DSNUPROC.SYSERR DD DSN=&&TEMP7, 00710000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00720000 //DSNUPROC.SYSMAP DD DSN=&&TEMP8, 00730000 // UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND) 00740000 //DSNUPROC.SYSIN DD DSN=&SRCLIB(ULODPART),DISP=SHR 00750000 // 00760000 ./ #DATE 08.00 06/10/26 06/10/26 16:44 23 23 0 SQL SELECT CURRENT DATE, CURRENT TIME FROM SYSIBM.SYSDUMMY1; -- SELECT CHAR(CURRENT_DATE,EUR) FROM SYSIBM.SYSDUMMY1; -- SELECT PARTNAME, PARTDATE FROM PART WHERE YEAR(PARTDATE) >= 2000; -- SELECT PARTNAME, PARTDATE FROM PART WHERE YEAR(CURRENT DATE - PARTDATE) >= 10; -- SELECT PARTNO, PARTDATE + 30 DAYS FROM PART; -- SELECT DAYS(CURRENT DATE) - DAYS(PARTDATE) FROM PART; ./ #DELETE 08.00 06/10/26 06/10/26 16:44 10 10 0 SQL DELETE FROM PART WHERE PARTNO = 'P91' OR PARTNAME LIKE 'GIZMO%';(MS ACCESS: 'GIZMO*') -- DELETE FROM SUPPLIER WHERE SUPPLIERNO BETWEEN 'S90' AND 'S99'; -- DELETE FROM GIZMOS; -- DROP TABLE GIZMOS; ./ #EXPRS 08.00 06/10/26 06/10/26 16:44 30 30 0 SQL SELECT 'PART NAME:', PARTNAME FROM PART; -- SELECT PARTNO AS "PART NUMBER", PARTNAME AS "PART NAME", PARTQTY * PARTCOST AS "INVENTORY COST" FROM PART; -- SELECT SUPPLIERNAME CONCAT ', ' CONCAT SUPPLIERLOC CONCAT '.' AS "SUPPLIERS" FROM SUPPLIER ORDER BY 1; -- SELECT PARTNO, PARTCOST, (PARTCOST + 1) * .25 AS "QTY DISCOUNT" FROM PART ORDER BY "QTY DISCOUNT"; -- SELECT PARTNO, PARTQTY / 2 AS "RE-ORDER QTY" FROM PART ORDER BY "RE-ORDER QTY"; ./ #FUNCCOL 08.00 06/10/26 06/10/26 16:44 20 20 0 SQL SELECT AVG(PARTCOST), MAX(PARTCOST), MIN(PARTCOST) FROM PART; -- SELECT AVG(PARTCOST), MAX(PARTCOST), MIN(PARTCOST) FROM PART WHERE PARTSUPPLIER IS NOT NULL; -- SELECT MAX(PARTQTY) FROM PART; -- SELECT COUNT(*) FROM SUPPLIER WHERE TIMESUSED > 0; -- SELECT SUM(PARTCOST) FROM PART; -- SELECT MIN(PARTCOST) FROM PART WHERE PARTNAME LIKE 'WIDGET%'; ./ #INSERT 08.00 06/10/26 06/10/26 16:44 26 26 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- INSERT INTO PART (PARTNO,PARTNAME,PARTQTY) VALUES ('P90','WIDGETS',0); -- INSERT INTO SUPPLIER VALUES ('S90','MR! GIZMOS!','SACRAMENTO',1); -- INSERT INTO PART (PARTNO,PARTNAME,PARTQTY, PARTCOST,PARTSUPPLIER,PARTDATE) VALUES ('P91','GIZMOS',10, 29.95,'S90',CURRENT DATE); -- INSERT INTO SUPPLIER VALUES ('S91','GIZMOS! NOW!','SACRAMENTO',0); -- CREATE TABLE GIZMOS LIKE SUPPLIER IN TRAINING.KWCEMP; -- INSERT INTO GIZMOS SELECT * FROM SUPPLIER WHERE SUPPLIERNO IN ('S90','S91'); ./ #JOINI 08.00 06/10/26 06/10/26 16:44 23 23 0 SQL SELECT PARTNO, PARTNAME, SUPPLIERNO, SUPPLIERNAME FROM PART, SUPPLIER WHERE PARTSUPPLIER = SUPPLIERNO ORDER BY PARTNO; -- SELECT PARTNAME, PARTQTY, SUPPLIERNO, SUPPLIERLOC FROM PART INNER JOIN SUPPLIER ON PARTSUPPLIER = SUPPLIERNO WHERE PARTQTY < 100 ORDER BY PARTQTY ASC; -- SELECT SUPPLIER.SUPPLIERNAME, SUPPLIER.TIMESUSED, PART.PARTNAME FROM PART, SUPPLIER WHERE PART.PARTSUPPLIER = SUPPLIER.SUPPLIERNO AND SUPPLIER.SUPPLIERLOC = 'SACRAMENTO' ORDER BY SUPPLIER.TIMESUSED DESC; -- SELECT S.SUPPLIERNAME, S.TIMESUSED, P.PARTNAME FROM PART P INNER JOIN SUPPLIER S ON P.PARTSUPPLIER = S.SUPPLIERNO WHERE S.SUPPLIERLOC = 'SACRAMENTO' ORDER BY S.TIMESUSED DESC; ./ #SELECT 08.00 06/10/26 06/10/26 16:44 23 23 0 SQL SELECT PARTNO, PARTNAME, PARTQTY FROM PART; -- SELECT SUPPLIERLOC, SUPPLIERNAME FROM SUPPLIER ORDER BY SUPPLIERLOC ASC; -- SELECT * FROM SUPPLIER ORDER BY SUPPLIERNO; -- SELECT * FROM PART ORDER BY PARTSUPPLIER ASC, PARTCOST DESC; -- SELECT SUPPLIERNAME, TIMESUSED FROM SUPPLIER ORDER BY TIMESUSED DESC; -- SELECT SUPPLIERNAME, TIMESUSED FROM SUPPLIER ORDER BY 2 DESC; ./ #UPDATE 08.00 06/10/26 06/10/26 16:44 18 18 0 SQL UPDATE PART SET PARTNAME = 'GIZMOS PLUS' WHERE PARTNAME = 'GIZMOS'; -- UPDATE PART SET PARTQTY = PARTQTY + 50 WHERE PARTNO = 'P91'; -- UPDATE SUPPLIER SET SUPPLIERNAME = RTRIM(SUPPLIERNAME) CONCAT '& SON', TIMESUSED = TIMESUSED + 1 WHERE SUPPLIERNO = 'S90'; -- UPDATE SUPPLIER SET SUPPLIERNAME = 'WHO KNOWS?', SUPPLIERLOC = 'SOMEWHERE', TIMESUSED = 0 WHERE SUPPLIERNO = 'S91'; ./ #WHERE 08.00 06/10/26 06/10/26 16:44 26 26 0 SQL SELECT PARTNO, PARTNAME, PARTQTY FROM PART WHERE PARTNO = 'P01'; -- SELECT SUPPLIERLOC, SUPPLIERNAME FROM SUPPLIER WHERE SUPPLIERLOC IN ('SACRAMENTO', 'PHOENIX'); -- SELECT * FROM SUPPLIER WHERE TIMESUSED > 1 ORDER BY SUPPLIERNO; -- SELECT * FROM PART WHERE PARTCOST BETWEEN 5 AND 10 AND PARTSUPPLIER IS NOT NULL; -- SELECT SUPPLIERNAME FROM SUPPLIER WHERE TIMESUSED = 0; -- SELECT * FROM PART WHERE PARTNAME LIKE 'WIDGET%' OR PARTNAME LIKE 'T___G%'; ./ AGE 08.00 06/10/26 06/10/26 15:37 5 5 0 SQL SELECT SUBSTR(DIGITS(CURRENT DATE - '10/23/1957'),1,4) AS "Years Alive", SUBSTR(DIGITS(CURRENT DATE - '10/23/1957'),5,2) AS "Months Alive", SUBSTR(DIGITS(CURRENT DATE - '10/23/1957'),7,2) AS "Days Alive" FROM SYSIBM.SYSDUMMY1; ./ CDBTRAIN 01.00 11/05/16 11/05/16 16:06 6 6 0 SQL DROP DATABASE TRAINING; 00160000 COMMIT; 00170000 CREATE DATABASE TRAINING; 00180000 COMMIT; 00190000 GRANT DBADM ON DATABASE TRAINING TO PUBLIC; 00200000 COMMIT; 00210000 ./ CDEPTLIK 08.03 12/10/04 12/10/04 08:26 10 10 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- CREATE TABLESPACE KWCDEPT IN TRAINING; -- CREATE TABLE DEPT LIKE DSN8910.DEPT IN TRAINING.KWCDEPT; -- INSERT INTO DEPT SELECT * FROM DSN8910.DEPT; ./ CEMPLIK 08.03 12/10/04 12/10/04 08:26 10 10 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- CREATE TABLESPACE KWCEMP IN TRAINING; -- CREATE TABLE EMP LIKE DSN8910.EMP IN TRAINING.KWCEMP; -- INSERT INTO EMP SELECT * FROM DSN8910.EMP; ./ CPART 08.00 06/10/26 06/10/26 15:37 17 17 0 SQL CREATE TABLESPACE KWCPART IN TRAINING; COMMIT; CREATE TABLE PART (PARTNO CHAR(3) NOT NULL, PRIMARY KEY(PARTNO), PARTNAME CHAR(36) NOT NULL, PARTQTY SMALLINT NOT NULL, PARTCOST DECIMAL(9,2), PARTSUPPLIER CHAR(3), FOREIGN KEY FK1 (PARTSUPPLIER) REFERENCES SUPPLIER (SUPPLIERNO) ON DELETE SET NULL, PARTDATE DATE) IN TRAINING.KWCPART; COMMIT; CREATE UNIQUE INDEX PARTPK ON PART(PARTNO); COMMIT; ./ CPARTLIK 08.02 12/10/04 12/10/04 08:26 23 23 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- CREATE TABLESPACE KWCPART IN TRAINING; -- CREATE TABLE PART (PARTNO CHAR(3) NOT NULL, PRIMARY KEY(PARTNO), PARTNAME CHAR(36) NOT NULL, PARTQTY SMALLINT NOT NULL, PARTCOST DECIMAL(9,2), PARTSUPPLIER CHAR(3), FOREIGN KEY FK1 (PARTSUPPLIER) REFERENCES SUPPLIER (SUPPLIERNO) ON DELETE SET NULL, PARTDATE DATE) IN TRAINING.KWCPART; -- CREATE UNIQUE INDEX PARTPK ON PART(PARTNO); -- INSERT INTO PART SELECT * FROM HWPFEND.PART; ./ CSUPP 08.00 06/10/26 06/10/26 15:37 12 12 0 SQL CREATE TABLESPACE KWCSUPP IN TRAINING; COMMIT; CREATE TABLE SUPPLIER (SUPPLIERNO CHAR(3) NOT NULL, PRIMARY KEY(SUPPLIERNO), SUPPLIERNAME CHAR(36) NOT NULL, SUPPLIERLOC CHAR(36) NOT NULL, TIMESUSED SMALLINT NOT NULL WITH DEFAULT) IN TRAINING.KWCSUPP; COMMIT; CREATE UNIQUE INDEX SUPPLIERPK ON SUPPLIER(SUPPLIERNO); COMMIT; ./ CSUPPLIK 08.02 12/10/04 12/10/04 08:26 18 18 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- CREATE TABLESPACE KWCSUPP IN TRAINING; -- CREATE TABLE SUPPLIER (SUPPLIERNO CHAR(3) NOT NULL, PRIMARY KEY(SUPPLIERNO), SUPPLIERNAME CHAR(36) NOT NULL, SUPPLIERLOC CHAR(36) NOT NULL, TIMESUSED SMALLINT NOT NULL WITH DEFAULT) IN TRAINING.KWCSUPP; -- CREATE UNIQUE INDEX SUPPLIERPK ON SUPPLIER(SUPPLIERNO); -- INSERT INTO SUPPLIER SELECT * FROM HWPFEND.SUPPLIER; ./ DATAPART 08.00 06/10/26 06/10/26 15:38 21 21 0 TXT P01WIDGETS 10000$16.00 P10WIDGET 0 P11THINGS 100 $0.55 P19THINGY 90 $0.65 P20WIDGET PLUS 1200 $9.95 P21HIPER-WIDGET 20 $39.95 P30BIG THING 2 $1400.95 P40STUFF 5000 $12.95 S3110/23/1957 0:00:00 P41JUNK 32500$1.00 S3312/31/1984 0:00:00 P42GARBAGE 4000 $0.99 P50MASK 40 $39.95 S2002/01/2001 0:00:00 P51FINS 40 $50.00 S2002/01/2001 0:00:00 P52SNORKEL 40 $15.95 S2002/01/2001 0:00:00 P53WET SUIT 120 $80.00 S2002/01/2001 0:00:00 P54BOOTIES 40 $25.95 S2002/01/2001 0:00:00 P55GLOVES 120 $12.95 S2002/01/2001 0:00:00 P56DEPTH GAUGE 50 $19.95 S2002/01/2001 0:00:00 P57WATCH 40 $39.95 S2002/01/2001 0:00:00 P80MORE STUFF 5 $6.95 S3111/15/1988 0:00:00 P81MORE JUNK 5 $4.95 S3212/31/1988 0:00:00 P82MORE GARBAGE 5 $9.95 S3001/01/2000 0:00:00 ./ DATASUPP 08.00 06/10/26 06/10/26 15:38 8 8 0 TXT S01MR. PARTS SACRAMENTO 0 S02MS. PARTS PHOENIX 0 S11Who knows? Somewhere 0 S20UNDERWATER WORLD GRAND CAYMAN 32 S30HOUSE OF GARBAGE SOMEWHERE ELSE 1 S31STUFF UNLIMITED EVERYWHERE 2 S32JUNK TO YOU SACRAMENTO 2 S33SAMS JUNK SAMS TOWN 1 ./ DDEPTCLO 08.00 06/10/26 06/10/26 15:37 1 1 0 SQL DELETE FROM DEPTDXX; ./ DDEPTMGR 08.00 06/10/26 06/10/26 15:37 2 2 0 SQL DELETE FROM DEPT WHERE MGRNO = '900000'; ./ DDEPTONE 08.00 06/10/26 06/10/26 15:37 2 2 0 SQL DELETE FROM DEPT WHERE DEPTNO = 'E11'; ./ DEMPCLON 08.00 06/10/26 06/10/26 15:37 1 1 0 SQL DELETE FROM EMPJONES; ./ DEMPDEPT 08.00 06/10/26 06/10/26 15:37 2 2 0 SQL DELETE FROM EMP WHERE WORKDEPT = 'E11'; ./ DEMPONE 08.00 06/10/26 06/10/26 15:37 2 2 0 SQL DELETE FROM EMP WHERE EMPNO = '900000'; ./ DROPDEPT 08.00 06/10/26 06/10/26 15:37 5 5 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- DROP TABLESPACE TRAINING.KWCDEPT; ./ DROPEMP 08.00 06/10/26 06/10/26 15:37 5 5 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- DROP TABLESPACE TRAINING.KWCEMP; ./ DROPPART 08.00 06/10/26 06/10/26 15:37 5 5 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- DROP TABLESPACE TRAINING.KWCPART; ./ DROPSUPP 08.00 06/10/26 06/10/26 15:37 5 5 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- DROP TABLESPACE TRAINING.KWCSUPP; ./ IDEPTALL 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL INSERT INTO DEPT VALUES ('X99', 'THE X DEPT', '000100', 'A00', 'SACRAMENTO'); ./ IDEPTCLO 08.00 06/10/26 06/10/26 15:38 11 11 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- CREATE TABLESPACE KWCDEPTD IN TRAINING; CREATE TABLE DEPTDXX LIKE DEPT IN TRAINING.KWCDEPTD; -- INSERT INTO DEPTDXX SELECT * FROM DEPT WHERE DEPTNO LIKE 'D%'; ./ IDEPTMIN 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL INSERT INTO DEPT (DEPTNO, DEPTNAME, ADMRDEPT) VALUES ('X98', 'THE X DEPT', 'A00'); ./ IEMPALL 08.00 06/10/26 06/10/26 15:38 5 5 0 SQL INSERT INTO EMP VALUES ('900001', 'KEN', 'W', 'JONES', 'A00', '1234', '01/27/2001', 'CEO', 99, 'M', '10/23/1957', 99.99, 99.99, 99.99); ./ IEMPCLON 08.00 06/10/26 06/10/26 15:38 11 11 0 SQL -- -- NOTE: BEFORE YOU EXECUTE THIS SQL, YOU MUST CHANGE THE CHARACTERS -- KWC TO YOUR INITIALS. -- CREATE TABLESPACE KWCEMPJ IN TRAINING; CREATE TABLE EMPJONES LIKE EMP IN TRAINING.KWCEMPJ; -- INSERT INTO EMPJONES SELECT * FROM EMP WHERE LASTNAME = 'JONES'; ./ IEMPMIN 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL INSERT INTO EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME) VALUES ('900002', 'KEN', 'W', 'JONES'); ./ IME 08.00 06/10/26 06/10/26 15:38 7 7 0 SQL INSERT INTO EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('900000', 'KEN', 'W', 'CALDWELL', 'X99', '1234', '01/27/2001', 'CEO', 99, 'M', '10/23/1957', 99.99, 99.99, 99.99); ./ IMECLONE 08.00 06/10/26 06/10/26 15:38 5 5 0 SQL INSERT INTO EMPJONES VALUES ('900003', 'KEN', 'J', 'JONES', 'A00', '1234', '01/27/2001', 'CEO', 99, 'M', '10/23/1957', 99.99, 99.99, 99.99); ./ JDEPTADM 08.00 06/10/26 06/10/26 15:38 4 4 0 SQL SELECT D1.DEPTNAME AS "Department Name", D2.DEPTNAME AS "Admin Department" FROM DEPT D1 INNER JOIN DEPT D2 ON D1.ADMRDEPT = D2.DEPTNO; ./ JDEPTMGR 08.00 06/10/26 06/10/26 15:38 4 4 0 SQL SELECT DEPTNO AS "Department Number", LASTNAME AS "Manager" FROM DEPT INNER JOIN EMP ON MGRNO = EMPNO; ./ JEMPDEPT 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, DEPTNAME FROM EMP INNER JOIN DEPT ON WORKDEPT = DEPTNO; ./ JEMPMGR 08.00 06/10/26 06/10/26 15:38 5 5 0 SQL SELECT E1.LASTNAME AS "Last Name", E2.LASTNAME AS "Manager" FROM EMP E1, DEPT D1, EMP E2 WHERE E1.WORKDEPT = D1.DEPTNO AND D1.MGRNO = E2.EMPNO; ./ JEMPMGR2 08.00 06/10/26 06/10/26 15:38 6 6 0 SQL SELECT E1.LASTNAME AS "Last Name", E2.LASTNAME AS "Manager" FROM EMP E1 INNER JOIN (DEPT D1 INNER JOIN EMP E2 ON D1.MGRNO = E2.EMPNO) ON E1.WORKDEPT = D1.DEPTNO; ./ SDEPTALL 08.00 06/10/26 06/10/26 15:38 1 1 0 SQL SELECT * FROM DEPT ORDER BY DEPTNO; ./ SDEPTCNT 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT COUNT(*) AS "Total Departments" FROM DEPT; ./ SDEPTC2 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT COUNT(*) AS "Depts no Managers" FROM DEPT WHERE MGRNO IS NULL; ./ SDEPTDXX 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT DEPTNO FROM DEPT WHERE DEPTNO LIKE 'D%'; ./ SDEPTLOC 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT DEPTNO, LOCATION FROM DEPT ORDER BY DEPTNO; ./ SDEPTMGR 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPT ORDER BY MGRNO, DEPTNO; ./ SDEPTNAM 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT DEPTNAME FROM DEPT; ./ SDEPTNUL 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT * FROM DEPT WHERE MGRNO IS NOT NULL; ./ SEMPAGE 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT * FROM EMP WHERE YEAR(BIRTHDATE) > 1950; ./ SEMPALL 08.00 06/10/26 06/10/26 15:38 1 1 0 SQL SELECT * FROM EMP ORDER BY EMPNO; ./ SEMPAVG 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT AVG(SALARY) AS "Average Salary" FROM EMP; ./ SEMPAVG2 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT AVG(SALARY) AS "Avg Sal Dept D11" FROM EMP WHERE WORKDEPT = 'D11'; ./ SEMPBIG 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT MAX(SALARY) AS "Largest Salary" FROM EMP; ./ SEMPCNT 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT COUNT(*) AS "Total Employees" FROM EMP; ./ SEMPDEPT 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT * FROM EMP WHERE WORKDEPT IN ('A00', 'B01', 'C01'); ./ SEMPEDU 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT LASTNAME, EDLEVEL FROM EMP; ./ SEMPEDUH 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT EMPNO, EDLEVEL FROM EMP WHERE EDLEVEL > 15; ./ SEMPEDUL 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT EMPNO, SALARY FROM EMP WHERE EDLEVEL <= 15; ./ SEMPFULL 08.00 06/10/26 06/10/26 15:38 4 4 0 SQL SELECT LASTNAME CONCAT ', ' CONCAT FIRSTNME CONCAT ' ' CONCAT MIDINIT CONCAT '.' AS "Full Name" FROM EMP; ./ SEMPJOB 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT AVG(EDLEVEL) AS "Avg Edlvl Managers" FROM EMP WHERE JOB = 'MANAGER'; ./ SEMPJONE 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT * FROM EMP WHERE LASTNAME = 'JONES'; ./ SEMPMTH 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT SALARY / 12 AS "Monthly Salary" FROM EMP; ./ SEMPNAM 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT LASTNAME, FIRSTNME, MIDINIT FROM EMP ORDER BY LASTNAME, FIRSTNME, MIDINIT; ./ SEMPPHON 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT '442-' CONCAT PHONENO AS "Phone Number" FROM EMP; ./ SEMPSAL 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT EMPNO, SALARY FROM EMP ORDER BY SALARY DESC; ./ SEMPSAL2 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY FROM EMP WHERE SALARY BETWEEN 30000 AND 40000; ./ SEMPTOT 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT SALARY + BONUS + COMM AS "Total Payroll" FROM EMP; ./ SEMPTOT2 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT SUM(SALARY + BONUS + COMM) AS "Total Payroll" FROM EMP; ./ SEMPYEAR 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT FIRSTNME, MIDINIT, LASTNAME, YEAR(HIREDATE) AS "Year Hired" FROM EMP; ./ SEMP10P 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT SALARY, SALARY * 1.1 AS "10% Increase" FROM EMP; ./ SEMP30K 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME FROM EMP WHERE SALARY > 30000; ./ SEMP30YR 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT FIRSTNME, MIDINIT, LASTNAME, HIREDATE FROM EMP WHERE YEAR(CURRENT DATE - HIREDATE) > 30; ./ SPARTALL 08.00 06/10/26 06/10/26 15:38 1 1 0 SQL SELECT * FROM PART ORDER BY PARTNO; ./ SSUPPALL 08.00 06/10/26 06/10/26 15:38 1 1 0 SQL SELECT * FROM SUPPLIER ORDER BY SUPPLIERNO; ./ SYSCOLS 08.01 09/02/14 09/02/14 13:13 21 21 0 SQL -- -- V8 EXPANDED TABLE, COLUMN AND USER NAMES TO 256 CHARACTERS. -- SUBSTR REDUCES TO ORIGINAL 8. -- SELECT SUBSTR(TBNAME,1,8) AS TBNAME, COLNO, SUBSTR(NAME,1,8) AS NAME, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = USER ORDER BY TBNAME, COLNO; -- SELECT SUBSTR(TBCREATOR,1,8) AS TBCREATOR, SUBSTR(TBNAME,1,8) AS TBNAME, COLNO, SUBSTR(NAME,1,8) AS NAME, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR LIKE 'HWTRN__' OR TBCREATOR LIKE 'HWINS__' ORDER BY TBCREATOR, TBNAME, COLNO; ./ SYSDB 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL SELECT * FROM SYSIBM.SYSDATABASE WHERE NAME = 'TRAINING'; ./ SYSTABLE 08.01 09/02/14 09/02/14 13:12 7 7 0 SQL SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR = USER; -- SELECT * FROM SYSIBM.SYSTABLES WHERE DBNAME = 'TRAINING'; ./ SYSTBLSP 08.02 11/11/30 11/11/30 11:13 7 7 0 SQL SELECT * FROM SYSIBM.SYSTABLESPACE WHERE CREATOR = USER; -- SELECT * FROM SYSIBM.SYSTABLESPACE WHERE DBNAME = 'TRAINING'; ./ TODAY 08.00 06/10/26 06/10/26 15:38 5 5 0 SQL SELECT CHAR(CURRENT DATE,USA) AS "USA Date", CHAR(CURRENT DATE,EUR) AS "EUR Date", CHAR(CURRENT DATE,ISO) AS "ISO Date", CHAR(CURRENT DATE,JIS) AS "JIS Date" FROM SYSIBM.SYSDUMMY1; ./ TOMORROW 08.00 06/10/26 06/10/26 15:38 2 2 0 SQL SELECT CURRENT DATE + 1 DAY AS "Tomorrows Date" FROM SYSIBM.SYSDUMMY1; ./ UDEPTMGR 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL UPDATE DEPT SET MGRNO = '900000' WHERE MGRNO IS NULL; ./ UDEPTNAM 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL UPDATE DEPT SET DEPTNAME = 'PLUMBING' WHERE DEPTNO = 'I22'; ./ UDEPTNUL 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL UPDATE DEPT SET MGRNO = NULL WHERE DEPTNO = 'E11'; ./ UEMPDEPT 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL UPDATE EMP SET WORKDEPT = 'A00' WHERE EMPNO = '000090'; ./ UEMPME 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL UPDATE EMP SET SALARY = 9999999.99 WHERE EMPNO = '900000'; ./ UEMPSAL 08.00 06/10/26 06/10/26 15:38 3 3 0 SQL UPDATE EMP SET SALARY = SALARY + 1 WHERE EMPNO <> '900000'; ./ ULODPART 08.00 12/09/26 12/09/26 08:17 10 10 0 DB2UTIL LOAD DATA INTO TABLE PART (PARTNO POSITION(1) CHAR(3), PARTNAME POSITION(4) CHAR(36), PARTQTY POSITION(40) INTEGER EXTERNAL(5), PARTCOST POSITION(46) DECIMAL EXTERNAL(9,2) NULLIF(46:46) = ' ', PARTSUPPLIER POSITION(56) CHAR(3) NULLIF(56:56) = ' ', PARTDATE POSITION(59) DATE EXTERNAL NULLIF(59:59) = ' ') ./ ULODSUPP 08.00 12/09/26 12/09/26 08:17 5 5 0 DB2UTIL LOAD DATA INTO TABLE SUPPLIER (SUPPLIERNO POSITION(1) CHAR(3), SUPPLIERNAME POSITION(4) CHAR(36), SUPPLIERLOC POSITION(40) CHAR(36), TIMESUSED POSITION(76) INTEGER EXTERNAL(5))