-- This script creates all tables referenced in The Free SQL Book. -- This script has been tested using SQL Server. -- SQL Rookies: Book-Appendix-I presents a short tutorial about SQL scripts. -- You can create these sample tables in any SQL Server database. [Book-Appendix-II assumes that you intend to create -- these tables in a SQL Server database called FREESQLDB.] -- This script does not contain any COMMIT statements because, unless otherwise specified, -- SQL Server automatically commits each statement. -- ************************************************************************* -- Note: This script file should ONLY be downloaded from: www.freesqlbook.com -- ************************************************************************* ------------------------------------------------------- ---------------------Chapters 1-5 -------------------------------------------------------- -- The following PRESERVE and EMPLOYEE tables are the only tables referenced -- in the sample queries and exercises for Chapters 1-5. DROP TABLE PRESERVE; DROP TABLE EMPLOYEE; CREATE TABLE PRESERVE (PNO INTEGER NOT NULL UNIQUE, PNAME VARCHAR (25) NOT NULL, STATE CHAR (2) NOT NULL, ACRES INTEGER NOT NULL, FEE DECIMAL (5,2) NOT NULL ); INSERT INTO PRESERVE VALUES (5, 'HASSAYAMPA RIVER', 'AZ', 660, 3.00); INSERT INTO PRESERVE VALUES (3, 'DANCING PRAIRIE', 'MT', 680, 0.00); INSERT INTO PRESERVE VALUES (7, 'MULESHOE RANCH', 'AZ', 49120, 0.00); INSERT INTO PRESERVE VALUES (40, 'SOUTH FORK MADISON', 'MT', 121, 0.00); INSERT INTO PRESERVE VALUES (14, 'MCELWAIN-OLSEN', 'MA', 66, 0.00); INSERT INTO PRESERVE VALUES (13, 'TATKON', 'MA', 40, 0.00); INSERT INTO PRESERVE VALUES (9, 'DAVID H. SMITH', 'MA', 830, 0.00); INSERT INTO PRESERVE VALUES (11, 'MIACOMET MOORS', 'MA', 4, 0.00); INSERT INTO PRESERVE VALUES (12, 'MOUNT PLANTAIN', 'MA', 730, 0.00); INSERT INTO PRESERVE VALUES (1, 'COMERTOWN PRAIRIE', 'MT', 1130, 0.00); INSERT INTO PRESERVE VALUES (2, 'PINE BUTTE SWAMP', 'MT', 15000, 0.00); INSERT INTO PRESERVE VALUES (80, 'RAMSEY CANYON', 'AZ', 380, 3.00); INSERT INTO PRESERVE VALUES (10, 'HOFT FARM', 'MA', 90, 0.00); INSERT INTO PRESERVE VALUES (6, 'PAPAGONIA-SONOITA CREEK', 'AZ', 1200, 3.00); CREATE TABLE EMPLOYEE (ENO CHAR (4) NOT NULL UNIQUE, ENAME VARCHAR(25) NOT NULL, SALARY DECIMAL(7,2) NOT NULL, DNO INTEGER NOT NULL ); INSERT INTO EMPLOYEE VALUES ('1000', 'MOE', 2000.00, 20); INSERT INTO EMPLOYEE VALUES ('2000', 'LARRY', 2000.00, 10); INSERT INTO EMPLOYEE VALUES ('3000', 'CURLY', 3000.00, 20); INSERT INTO EMPLOYEE VALUES ('4000', 'SHEMP', 500.00, 40); INSERT INTO EMPLOYEE VALUES ('5000', 'JOE', 400.00, 10); INSERT INTO EMPLOYEE VALUES ('6000', 'GEORGE', 9000.00, 20); -- The preceding EMPLOYEE table will be referenced in Chapters 1-15. -- This table will be dropped and re-created (with some changes) -- in the CREATE TABLE statements for Part IV (Chapter 16). ------------------------------------------------------- ---------------------Chapter 6: LIKE -------------------------------------------------------- DROP TABLE DEMO1; DROP TABLE DEMO1A; CREATE TABLE DEMO1 (CHARNAME CHAR(10), VCHARNAME VARCHAR (10)); INSERT INTO DEMO1 VALUES ('DAVID', 'DAVID'); INSERT INTO DEMO1 VALUES ('SOLOMON', 'SOLOMON'); INSERT INTO DEMO1 VALUES ('MATTHEW', 'MATTHEW'); INSERT INTO DEMO1 VALUES ('MARK', 'MARK'); INSERT INTO DEMO1 VALUES ('LUKE', 'LUKE'); INSERT INTO DEMO1 VALUES ('JOHN', 'JOHN'); INSERT INTO DEMO1 VALUES ('EUCLID', 'EUCLID'); INSERT INTO DEMO1 VALUES ('WASHINGTON', 'WASHINGTON'); INSERT INTO DEMO1 VALUES ('ADAMS', 'ADAMS'); INSERT INTO DEMO1 VALUES ('JEFFERSON', 'JEFFERSON'); INSERT INTO DEMO1 VALUES ('MADISON', 'MADISON'); CREATE TABLE DEMO1A (CHARNAME CHAR (10), VCHARNAME VARCHAR (10), UGLY VARCHAR (10)); INSERT INTO DEMO1A VALUES ('SOLOMON', 'SOLOMON', 'SOLOMON '); INSERT INTO DEMO1A VALUES ('WASHINGTON', 'WASHINGTON', 'WASHINGTON'); INSERT INTO DEMO1A VALUES ('MADISONXX', 'MADISONXX', 'MADISONXX'); INSERT INTO DEMO1A VALUES ('MADISON', 'MADISON', 'MADISON '); -------------------------------------------------------- -----------Part II -- Chapter 8: Built-In Functions -------------------------------------------------------- DROP TABLE DEMO2; CREATE TABLE DEMO2 (I1 INTEGER, D1 DECIMAL (5,2), V1 VARCHAR (15), F1 CHAR(5) ); INSERT INTO DEMO2 VALUES (-10, -8.82, 'Julie Martyn', 'Hello'); INSERT INTO DEMO2 VALUES (-5, -5.28, 'JESSIE MARTYN', 'GOOD'); INSERT INTO DEMO2 VALUES (0, 0.00, 'Janet Martyn', 'By'); INSERT INTO DEMO2 VALUES (2, 6.42, 'Frank', 'BYE'); INSERT INTO DEMO2 VALUES (9, 9.98, 'Wally', 'HYY'); -------------------------------------------------------------------------- ----------------------- Chapter 9.5 (GROUP BY) -- This is an optional chapter that references the following PURCHASE table -- illustrate grouping by multiple columns. -------------------------------------------------------------------------- DROP TABLE PURCHASE; CREATE TABLE PURCHASE ( PNO CHAR(2) NOT NULL, SNO CHAR(2) NOT NULL, ENO CHAR(2) NOT NULL, PJNO CHAR(3) NOT NULL, PURDAY INTEGER NOT NULL, COST INTEGER, DISCOUNT INTEGER, PRIMARY KEY (PNO, SNO, ENO, PJNO, PURDAY) ); INSERT INTO PURCHASE VALUES ('P1', 'S1', 'E1', 'PJ1', 1, 200, 0); INSERT INTO PURCHASE VALUES ('P1', 'S1', 'E1', 'PJ1', 17, 250, 50); INSERT INTO PURCHASE VALUES ('P1', 'S1', 'E1', 'PJ1', 80, 300, 0); INSERT INTO PURCHASE VALUES ('P1', 'S1', 'E1', 'PJ3', 5, 200, 0); INSERT INTO PURCHASE VALUES ('P1', 'S1', 'E1', 'PJ3', 6, 200, 0); INSERT INTO PURCHASE VALUES ('P1', 'S2', 'E2', 'PJ3', 85, 300, 100); INSERT INTO PURCHASE VALUES ('P2', 'S1', 'E1', 'PJ2', 1, 1000, 0); INSERT INTO PURCHASE VALUES ('P2', 'S1', 'E2', 'PJ2', 2, 1000, 0); INSERT INTO PURCHASE VALUES ('P2', 'S1', 'E2', 'PJ2', 85, 1200, 0); INSERT INTO PURCHASE VALUES ('P3', 'S1', 'E1', 'PJ1', 10, 500, 100); INSERT INTO PURCHASE VALUES ('P3', 'S1', 'E1', 'PJ1', 11, 500, 100); INSERT INTO PURCHASE VALUES ('P3', 'S1', 'E1', 'PJ1', 70, 550, 200); INSERT INTO PURCHASE VALUES ('P4', 'S2', 'E2', 'PJ2', 10, 300, 0); INSERT INTO PURCHASE VALUES ('P4', 'S1', 'E2', 'PJ2', 11, 300, 0); INSERT INTO PURCHASE VALUES ('P4', 'S2', 'E2', 'PJ2', 70, 400, 50); INSERT INTO PURCHASE VALUES ('P5', 'S1', 'E1', 'PJ1', 11, 1200, 0); INSERT INTO PURCHASE VALUES ('P5', 'S1', 'E1', 'PJ1', 22, 1200, 0); INSERT INTO PURCHASE VALUES ('P5', 'S1', 'E1', 'PJ1', 33, 1500, 25); INSERT INTO PURCHASE VALUES ('P6', 'S2', 'E2', 'PJ2', 11, 200, 0); INSERT INTO PURCHASE VALUES ('P6', 'S2', 'E2', 'PJ2', 33, 100, 0); INSERT INTO PURCHASE VALUES ('P7', 'S3', 'E1', 'PJ1', 11, 1000, 0); INSERT INTO PURCHASE VALUES ('P7', 'S4', 'E1', 'PJ1', 22, 1000, 0); INSERT INTO PURCHASE VALUES ('P7', 'S3', 'E1', 'PJ1', 33, 1000, 0); INSERT INTO PURCHASE VALUES ('P7', 'S4', 'E2', 'PJ2', 61, 1200, 0); INSERT INTO PURCHASE VALUES ('P7', 'S4', 'E2', 'PJ2', 72, 1200, 200); INSERT INTO PURCHASE VALUES ('P8', 'S1', 'E1', 'PJ1', 61, 100, 0); INSERT INTO PURCHASE VALUES ('P8', 'S1', 'E1', 'PJ1', 72, 100, 0); INSERT INTO PURCHASE VALUES ('P8', 'S1', 'E1', 'PJ1', 73, 100, 0); INSERT INTO PURCHASE VALUES ('P8', 'S1', 'E2', 'PJ2', 41, 100, 50); INSERT INTO PURCHASE VALUES ('P8', 'S2', 'E1', 'PJ2', 42, 100, 75); INSERT INTO PURCHASE VALUES ('P8', 'S1', 'E1', 'PJ2', 43, 100, 100); INSERT INTO PURCHASE VALUES ('P4', 'S1', 'E1', 'PJ3', 11, 300, 75); INSERT INTO PURCHASE VALUES ('P4', 'S1', 'E1', 'PJ3', 22, 300, 75); INSERT INTO PURCHASE VALUES ('P4', 'S2', 'E2', 'PJ3', 33, 300, 0); INSERT INTO PURCHASE VALUES ('P3', 'S3', 'E3', 'PJ1', 7, 1200, 1100); INSERT INTO PURCHASE VALUES ('P4', 'S3', 'E3', 'PJ1', 8, 1200, 900); INSERT INTO PURCHASE VALUES ('P5', 'S3', 'E3', 'PJ1', 9, 1500, 1400); INSERT INTO PURCHASE VALUES ('P3', 'S3', 'E3', 'PJ2', 14, 1200, 1000); INSERT INTO PURCHASE VALUES ('P4', 'S3', 'E3', 'PJ2', 15, 1200, 1000); INSERT INTO PURCHASE VALUES ('P5', 'S3', 'E3', 'PJ2', 16, 1500, 1300); INSERT INTO PURCHASE VALUES ('P3', 'S4', 'E4', 'PJ1', 10, 1100, 0); INSERT INTO PURCHASE VALUES ('P4', 'S4', 'E4', 'PJ1', 15, 1200, 0); INSERT INTO PURCHASE VALUES ('P5', 'S4', 'E4', 'PJ2', 20, 1500, 50); INSERT INTO PURCHASE VALUES ('P3', 'S4', 'E4', 'PJ2', 21, 1100, 50); INSERT INTO PURCHASE VALUES ('P4', 'S4', 'E4', 'PJ1', 70, 1200, 0); INSERT INTO PURCHASE VALUES ('P6', 'S4', 'E4', 'PJ2', 71, 1500, 0); -------------------------------------------------------- -----------Chapter 10.5: Processing DATE Values -------------------------------------------------------- -- Among different relational database systems, there is considerable variation -- in SQL code for processing date-time data. The following code works in SQL Server. -- If you are using some other database system, you may have to modify the following -- CREATE TABLE and/or INSERT statements. DROP TABLE DEMO3; CREATE TABLE DEMO3 (MNAME CHAR (10) NOT NULL UNIQUE, BDDATE DATE NOT NULL, BDCHAR1 CHAR (10) NOT NULL, BDCHAR2 CHAR (10) NOT NULL, BDCHAR3 CHAR (20) NOT NULL); INSERT INTO DEMO3 VALUES ('EVAN', '2017-06-05','2017-06-05','06/05/2017', 'June 5, 2017' ); INSERT INTO DEMO3 VALUES ('HANNAH', '2014-11-25','2014-11-25','11/25/2014', 'November 25, 2014' ); INSERT INTO DEMO3 VALUES ('JACQUELINE', '2019-01-10','2019-01-10','01/10/2019', 'January 10, 2019' ); INSERT INTO DEMO3 VALUES ('JESSIE', '1982-03-07','1982-03-07','03/07/1982', 'March 7, 1982' ); INSERT INTO DEMO3 VALUES ('JONHHY', '2015-05-10','2015-05-10','05/10/2015', 'May 10, 2015' ); INSERT INTO DEMO3 VALUES ('JOSEPHINE', '2017-06-13','2017-06-13','06/13/2017', 'June 13, 2017' ); INSERT INTO DEMO3 VALUES ('JULIE', '1978-05-17','1978-05-17','05/17/1978', 'May 17, 1978' ); ----------------------------------------------------------------- ------------Chapter 11: Null Values ----------------------------------------------------------------- DROP TABLE NTAB; DROP TABLE NTAB2; DROP TABLE NTAB3; CREATE TABLE NTAB (A INTEGER, B INTEGER); INSERT INTO NTAB VALUES ( 5, 5); INSERT INTO NTAB VALUES ( 5, 10); INSERT INTO NTAB VALUES ( 5, NULL); INSERT INTO NTAB VALUES (NULL, 10); INSERT INTO NTAB VALUES (NULL, NULL); CREATE TABLE NTAB2 (A INTEGER, B INTEGER); INSERT INTO NTAB2 VALUES (10, NULL); INSERT INTO NTAB2 VALUES (15, 10); INSERT INTO NTAB2 VALUES (NULL, 30); INSERT INTO NTAB2 VALUES (NULL, 10); INSERT INTO NTAB2 VALUES (40, 40); INSERT INTO NTAB2 VALUES (NULL, NULL); CREATE TABLE NTAB3 (A INTEGER, B INTEGER); INSERT INTO NTAB3 VALUES (20, 20); INSERT INTO NTAB3 VALUES (50, NULL); INSERT INTO NTAB3 VALUES (NULL, NULL); INSERT INTO NTAB3 VALUES (NULL, 30); INSERT INTO NTAB3 VALUES (10, 50); INSERT INTO NTAB3 VALUES (10, 10); INSERT INTO NTAB3 VALUES (40, 50); -- Part III doe not require the creation of any new tables. ----------------------------------------- ----------Part IV - Chapter 16: Join Operations ------------------------------------------ DROP TABLE EMPLOYEE; DROP TABLE EMPLOYEE2; DROP TABLE EMPLOYEE3; DROP TABLE DEPARTMENT; CREATE TABLE DEPARTMENT (DNO INTEGER NOT NULL PRIMARY KEY, DNAME VARCHAR(20) NOT NULL UNIQUE, BUDGET DECIMAL(9,2) NOT NULL); INSERT INTO DEPARTMENT VALUES (10, 'ACCOUNTING', 75000.00); INSERT INTO DEPARTMENT VALUES (20, 'INFO. SYS.', 20000.00); INSERT INTO DEPARTMENT VALUES (30, 'PRODUCTION', 7000.00); INSERT INTO DEPARTMENT VALUES (40, 'ENGINEERING', 25000.00); -- The EMPLOYEE table was previously created for excerises in Chapter 1. -- The following code drops and re-creates this table with two changes. -- The CREATE TABLE specifies a PRIMARY KEY clause (versus UNIQUE), -- and it specifies a FOREIGN KEY clause. CREATE TABLE EMPLOYEE (ENO CHAR (4) NOT NULL, ENAME VARCHAR(25) NOT NULL, SALARY DECIMAL(7,2) NOT NULL, DNO INTEGER NOT NULL, PRIMARY KEY (ENO), FOREIGN KEY (DNO) REFERENCES DEPARTMENT); INSERT INTO EMPLOYEE VALUES ('1000', 'MOE', 2000.00, 20); INSERT INTO EMPLOYEE VALUES ('2000', 'LARRY', 2000.00, 10); INSERT INTO EMPLOYEE VALUES ('3000', 'CURLY', 3000.00, 20); INSERT INTO EMPLOYEE VALUES ('4000', 'SHEMP', 500.00, 40); INSERT INTO EMPLOYEE VALUES ('5000', 'JOE', 400.00, 10); INSERT INTO EMPLOYEE VALUES ('6000', 'GEORGE', 9000.00, 20); CREATE TABLE EMPLOYEE2 (ENO CHAR (4) NOT NULL, ENAME VARCHAR(25) NOT NULL, SALARY DECIMAL(7,2) NOT NULL, DNO INTEGER, PRIMARY KEY (ENO), FOREIGN KEY (DNO) REFERENCES DEPARTMENT); INSERT INTO EMPLOYEE2 VALUES ('1000', 'MOE', 2000.00, 20); INSERT INTO EMPLOYEE2 VALUES ('2000', 'LARRY', 2000.00, 10); INSERT INTO EMPLOYEE2 VALUES ('3000', 'CURLY', 3000.00, 20); INSERT INTO EMPLOYEE2 VALUES ('4000', 'SHEMP', 500.00, 40); INSERT INTO EMPLOYEE2 VALUES ('5000', 'JOE', 400.00, 10); INSERT INTO EMPLOYEE2 VALUES ('6000', 'GEORGE', 9000.00, NULL); CREATE TABLE EMPLOYEE3 (ENO CHAR (4) NOT NULL, ENAME VARCHAR(25) NOT NULL, SALARY DECIMAL(7,2) NOT NULL, DNO INTEGER, PRIMARY KEY (ENO) ); INSERT INTO EMPLOYEE3 VALUES ('1000', 'MOE', 2000.00, 99); INSERT INTO EMPLOYEE3 VALUES ('2000', 'LARRY', 2000.00, 10); INSERT INTO EMPLOYEE3 VALUES ('3000', 'CURLY', 3000.00, 20); INSERT INTO EMPLOYEE3 VALUES ('4000', 'SHEMP', 500.00, 40); INSERT INTO EMPLOYEE3 VALUES ('5000', 'JOE', 400.00, 10); INSERT INTO EMPLOYEE3 VALUES ('6000', 'GEORGE', 9000.00, NULL); ------------------------------------------------------------ ------------ Chapter 18: Modified TPCH Design (MTPCH) ----- ------------------------------------------------------------ DROP TABLE LINEITEM; DROP TABLE PARTSUPP; DROP TABLE PART; DROP TABLE SUPPLIER; DROP TABLE PUR_ORDER; DROP TABLE CUSTOMER; DROP TABLE STATE; DROP TABLE REGION; CREATE TABLE REGION (RNO INTEGER NOT NULL, RNAME CHAR (10) NOT NULL UNIQUE, CLIMATE CHAR (5), PRIMARY KEY (RNO) ); INSERT INTO REGION VALUES (1,'NORTHEAST', 'Cold'); INSERT INTO REGION VALUES (2,'NORTHWEST', 'Cold'); INSERT INTO REGION VALUES (3,'SOUTHEAST', 'Hot'); INSERT INTO REGION VALUES (4,'SOUTHWEST', 'Hot'); INSERT INTO REGION VALUES (5,'MIDWEST', 'Empty'); -- Observation: Region 5 is the only region that does not have any states. -- If you look ahead to the INSERT statements for the STATE table, -- observe that no INSERT statement has an RNO value of 5. CREATE TABLE STATE (STCODE CHAR (2) NOT NULL, STNAME CHAR (15) NOT NULL UNIQUE, POPULATION INTEGER, RNO INTEGER NOT NULL, PRIMARY KEY (STCODE), FOREIGN KEY (RNO) REFERENCES REGION ); INSERT INTO STATE VALUES ('CT', 'CONNECTICUT', 3502000, 1); INSERT INTO STATE VALUES ('MA', 'MASSACHUSETTS', 6450000, 1); INSERT INTO STATE VALUES ('OR', 'OREGON', 3747000, 2); INSERT INTO STATE VALUES ('WA', 'WASHINGTON', 6468000, 2); INSERT INTO STATE VALUES ('FL', 'FLORIDA', 18251000, 3); INSERT INTO STATE VALUES ('GE', 'GEORGIA', 9545000, 3); INSERT INTO STATE VALUES ('NM', 'NEW MEXICO', 1970000, 4); INSERT INTO STATE VALUES ('AZ', 'ARIZONA', 6339000, 4); -- Observation-1: CONNECTICUT (CT) does not have any customers. -- If you look ahead to the INSERT statements for the CUSTOMER table, -- observe that no INSERT statement has an STCODE value of CT. -- Observation-2: NEW MEXICO (NM) and ARIZONA (AZ) do not have any suppliers. -- If you look ahead to the INSERT statements for the SUPPLIER table, -- observe that no INSERT statement has an STCODE value of NM or AZ. CREATE TABLE CUSTOMER (CNO CHAR (3) NOT NULL, CNAME CHAR (15) NOT NULL, CPHONE CHAR (12), STCODE CHAR (2) NOT NULL, PRIMARY KEY (CNO), FOREIGN KEY (STCODE) REFERENCES STATE); INSERT INTO CUSTOMER VALUES ('100', 'PYTHAGORAS', '800-999-9999', 'MA'); INSERT INTO CUSTOMER VALUES ('110', 'EUCLID', '800-999-8888', 'MA'); INSERT INTO CUSTOMER VALUES ('200', 'HYPATIA', '800-888-9999', 'MA'); INSERT INTO CUSTOMER VALUES ('220', 'ZENO', '800-888-8888', 'MA'); INSERT INTO CUSTOMER VALUES ('230', 'BOLYAI', '800-888-7777', 'MA'); INSERT INTO CUSTOMER VALUES ('300', 'NEWTON', '800-777-9999', 'OR'); INSERT INTO CUSTOMER VALUES ('330', 'LEIBNIZ', '800-777-8888', 'OR'); INSERT INTO CUSTOMER VALUES ('400', 'DECARTES', '800-666-9999', 'WA'); INSERT INTO CUSTOMER VALUES ('440', 'PASCAL', '800-666-8888', 'WA'); INSERT INTO CUSTOMER VALUES ('500', 'HILBERT', '877-999-1234', 'MA'); INSERT INTO CUSTOMER VALUES ('600', 'BOOLE', '877-888-4321', 'FL'); INSERT INTO CUSTOMER VALUES ('660', 'CANTOR', '877-888-8765', 'FL'); INSERT INTO CUSTOMER VALUES ('700', 'RUSSELL', '877-777-1235', 'GE'); INSERT INTO CUSTOMER VALUES ('770', 'GODEL', '877-777-5321', 'GE'); INSERT INTO CUSTOMER VALUES ('780', 'CHURCH', '877-777-6321', 'NM'); INSERT INTO CUSTOMER VALUES ('800', 'VON NEUMANN', '877-666-9123', 'NM'); INSERT INTO CUSTOMER VALUES ('880', 'TURING', '877-666-3219', 'AZ'); INSERT INTO CUSTOMER VALUES ('890', 'MANDELBROT', '877-666-5219', 'AZ'); -- Observation: Customers 780 and 890 do not have any purchase-orders. -- If you look ahead to the INSERT statements for the PUR_ORDER table, -- observe that no INSERT statement has a CNO value of 780 or 890. CREATE TABLE SUPPLIER (SNO CHAR (2) NOT NULL, SNAME CHAR (10) NOT NULL, SPHONE CHAR (12), SSTATUS CHAR (1), STCODE CHAR (2) NOT NULL, PRIMARY KEY (SNO), FOREIGN KEY (STCODE) REFERENCES STATE); INSERT INTO SUPPLIER VALUES ('S1', 'SUPPLIER1', '888-999-9999', 'S', 'MA'); INSERT INTO SUPPLIER VALUES ('S2', 'SUPPLIER2', '888-999-1111', 'P', 'MA'); INSERT INTO SUPPLIER VALUES ('S3', 'SUPPLIER3', '888-999-3333', 'S', 'CT'); INSERT INTO SUPPLIER VALUES ('S4', 'SUPPLIER4', '888-999-4444', 'S', 'FL'); INSERT INTO SUPPLIER VALUES ('S5', 'SUPPLIER5', '888-999-6666', 'S', 'GE'); INSERT INTO SUPPLIER VALUES ('S6', 'SUPPLIER6', '889-888-9999', 'S', 'WA'); INSERT INTO SUPPLIER VALUES ('S7', 'SUPPLIER7', '889-888-3333', 'P', 'OR'); INSERT INTO SUPPLIER VALUES ('S8', 'SUPPLIER8', '889-888-2222', 'S', 'OR'); -- Observation: Supplier S7 is not authorized to sell any parts. -- If you look ahead to the INSERT statements for the PARTSUPP table, -- observe that no INSERT statement has a SNO value of S7. CREATE TABLE PART (PNO CHAR (2) NOT NULL, PNAME CHAR (6) NOT NULL, PCOLOR CHAR (6), PWT INTEGER NOT NULL, PRIMARY KEY (PNO) ); INSERT INTO PART VALUES ('P1', 'PART1', 'RED', 20); INSERT INTO PART VALUES ('P2', 'PART2', 'BLUE', 10); INSERT INTO PART VALUES ('P3', 'PART3', 'PINK', 20); INSERT INTO PART VALUES ('P4', 'PART4', 'YELLOW', 10); INSERT INTO PART VALUES ('P5', 'PART5', 'RED', 20); INSERT INTO PART VALUES ('P6', 'PART6', 'BLUE', 12); INSERT INTO PART VALUES ('P7', 'PART7', 'PINK', 20); INSERT INTO PART VALUES ('P8', 'PART8', 'PINK', 15); -- Observation: Part P2 is not sold by any supplier. -- If you look ahead to the INSERT statements for the PARTSUPP table, -- observe that no INSERT statement has a PNO value of P2. CREATE TABLE PARTSUPP (PNO CHAR (2) NOT NULL, SNO CHAR (2) NOT NULL, PSPRICE DECIMAL (7,2), PRIMARY KEY (PNO, SNO), FOREIGN KEY (PNO) REFERENCES PART, FOREIGN KEY (SNO) REFERENCES SUPPLIER); INSERT INTO PARTSUPP VALUES ('P1', 'S2', 10.50); INSERT INTO PARTSUPP VALUES ('P1', 'S4', 11.00); INSERT INTO PARTSUPP VALUES ('P3', 'S3', 12.00); INSERT INTO PARTSUPP VALUES ('P3', 'S4', 12.50); INSERT INTO PARTSUPP VALUES ('P4', 'S4', 12.00); INSERT INTO PARTSUPP VALUES ('P5', 'S1', 10.00); INSERT INTO PARTSUPP VALUES ('P5', 'S2', 10.00); INSERT INTO PARTSUPP VALUES ('P5', 'S4', 11.00); INSERT INTO PARTSUPP VALUES ('P6', 'S4', 4.00); INSERT INTO PARTSUPP VALUES ('P6', 'S6', 4.00); INSERT INTO PARTSUPP VALUES ('P6', 'S8', 4.00); INSERT INTO PARTSUPP VALUES ('P7', 'S2', 2.00); INSERT INTO PARTSUPP VALUES ('P7', 'S4', 3.00); INSERT INTO PARTSUPP VALUES ('P7', 'S5', 3.50); INSERT INTO PARTSUPP VALUES ('P7', 'S6', 3.50); INSERT INTO PARTSUPP VALUES ('P8', 'S4', 5.00); INSERT INTO PARTSUPP VALUES ('P8', 'S6', 4.00); INSERT INTO PARTSUPP VALUES ('P8', 'S8', 3.00); -- Observation-1: Supplier S6 is authorized to sell P7. -- If you look ahead to the INSERT statements for the LINEITEM table, -- observe that no INSERT statement inserts a row with -- an SNO value of S6 and PNO value of P7. -- Observation-2: Supplier S4 sells all parts except P2. -- The PART table shows eight rows with PNO values of P1-P8. -- The above INSERT statements for PARTSUPP show seven rows that reference -- seven different PNO values, excluding the P2 value. CREATE TABLE PUR_ORDER (PONO CHAR (5) NOT NULL, PODATE INTEGER NOT NULL, POSTATUS CHAR (1) NOT NULL, CNO CHAR (3) NOT NULL, PRIMARY KEY (PONO), FOREIGN KEY (CNO) REFERENCES CUSTOMER); INSERT INTO PUR_ORDER VALUES ('11101', 1, 'C', '100'); INSERT INTO PUR_ORDER VALUES ('11102', 3, 'P', '100'); INSERT INTO PUR_ORDER VALUES ('11108', 47, 'C', '110'); INSERT INTO PUR_ORDER VALUES ('11109', 49, 'P', '110'); INSERT INTO PUR_ORDER VALUES ('11110', 20, 'C', '200'); INSERT INTO PUR_ORDER VALUES ('11111', 21, 'P', '200'); INSERT INTO PUR_ORDER VALUES ('11120', 22, 'C', '220'); INSERT INTO PUR_ORDER VALUES ('11121', 23, 'C', '220'); INSERT INTO PUR_ORDER VALUES ('11122', 5, 'P', '220'); INSERT INTO PUR_ORDER VALUES ('11124', 6, 'P', '230'); INSERT INTO PUR_ORDER VALUES ('11130', 7, 'C', '300'); INSERT INTO PUR_ORDER VALUES ('11133', 8, 'P', '300'); INSERT INTO PUR_ORDER VALUES ('11139', 9, 'C', '330'); INSERT INTO PUR_ORDER VALUES ('11141', 61, 'P', '330'); INSERT INTO PUR_ORDER VALUES ('11142', 62, 'C', '400'); INSERT INTO PUR_ORDER VALUES ('11144', 63, 'P', '400'); INSERT INTO PUR_ORDER VALUES ('11146', 64, 'C', '440'); INSERT INTO PUR_ORDER VALUES ('11148', 65, 'C', '440'); INSERT INTO PUR_ORDER VALUES ('11149', 71, 'P', '440'); INSERT INTO PUR_ORDER VALUES ('11150', 72, 'P', '500'); INSERT INTO PUR_ORDER VALUES ('11152', 73, 'C', '600'); INSERT INTO PUR_ORDER VALUES ('11153', 74, 'P', '600'); INSERT INTO PUR_ORDER VALUES ('11154', 75, 'C', '660'); INSERT INTO PUR_ORDER VALUES ('11155', 1, 'P', '660'); INSERT INTO PUR_ORDER VALUES ('11156', 1, 'C', '700'); INSERT INTO PUR_ORDER VALUES ('11157', 3, 'P', '770'); INSERT INTO PUR_ORDER VALUES ('11158', 3, 'C', '800'); INSERT INTO PUR_ORDER VALUES ('11159', 3, 'C', '880'); INSERT INTO PUR_ORDER VALUES ('11160', 4, 'P', '880'); INSERT INTO PUR_ORDER VALUES ('11170', 10, 'P', '880'); INSERT INTO PUR_ORDER VALUES ('11198', 10, 'P', '880'); -- Observation: Purchase Order 11198 is the only purchase-order that -- does not have any line-items. -- If you look ahead to the INSERT statements for the LINEITEM table, -- observe that no INSERT statement has a PONO value of 11198. CREATE TABLE LINEITEM (PONO CHAR (5) NOT NULL, LINE INTEGER NOT NULL, PNO CHAR (2) NOT NULL, SNO CHAR (2) NOT NULL, QTY INTEGER NOT NULL, LIPRICE DECIMAL (7, 2) NOT NULL, PRIMARY KEY (PONO, LINE), FOREIGN KEY (PONO) REFERENCES PUR_ORDER, FOREIGN KEY (PNO,SNO) REFERENCES PARTSUPP); INSERT INTO LINEITEM VALUES ('11101', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11101', 2, 'P3', 'S3', 10, 12.00); INSERT INTO LINEITEM VALUES ('11102', 1, 'P3', 'S3', 20, 13.00); INSERT INTO LINEITEM VALUES ('11102', 2, 'P4', 'S4', 20, 13.00); INSERT INTO LINEITEM VALUES ('11108', 1, 'P5', 'S1', 10, 11.00); INSERT INTO LINEITEM VALUES ('11108', 2, 'P6', 'S4', 10, 5.00); INSERT INTO LINEITEM VALUES ('11109', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11109', 2, 'P7', 'S2', 20, 3.00); INSERT INTO LINEITEM VALUES ('11109', 3, 'P8', 'S4', 20, 6.00); INSERT INTO LINEITEM VALUES ('11110', 1, 'P8', 'S4', 30, 6.00); INSERT INTO LINEITEM VALUES ('11111', 1, 'P1', 'S4', 10, 12.00); INSERT INTO LINEITEM VALUES ('11111', 2, 'P3', 'S4', 10, 13.50); INSERT INTO LINEITEM VALUES ('11120', 1, 'P4', 'S4', 20, 13.00); INSERT INTO LINEITEM VALUES ('11120', 2, 'P5', 'S2', 20, 11.00); INSERT INTO LINEITEM VALUES ('11121', 1, 'P6', 'S6', 20, 5.00); INSERT INTO LINEITEM VALUES ('11121', 2, 'P7', 'S4', 20, 4.00); INSERT INTO LINEITEM VALUES ('11122', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11122', 2, 'P3', 'S3', 10, 13.00); INSERT INTO LINEITEM VALUES ('11124', 3, 'P4', 'S4', 10, 13.00); INSERT INTO LINEITEM VALUES ('11124', 4, 'P5', 'S1', 10, 11.00); INSERT INTO LINEITEM VALUES ('11130', 1, 'P6', 'S4', 5, 5.00); INSERT INTO LINEITEM VALUES ('11130', 2, 'P7', 'S2', 5, 3.00); INSERT INTO LINEITEM VALUES ('11130', 3, 'P5', 'S4', 10, 12.00); INSERT INTO LINEITEM VALUES ('11133', 1, 'P1', 'S4', 10, 12.00); INSERT INTO LINEITEM VALUES ('11139', 1, 'P3', 'S4', 20, 13.50); INSERT INTO LINEITEM VALUES ('11139', 2, 'P5', 'S2', 20, 11.00); INSERT INTO LINEITEM VALUES ('11141', 1, 'P5', 'S4', 10, 12.00); INSERT INTO LINEITEM VALUES ('11141', 2, 'P6', 'S4', 10, 5.00); INSERT INTO LINEITEM VALUES ('11142', 1, 'P6', 'S8', 10, 5.00); INSERT INTO LINEITEM VALUES ('11142', 2, 'P7', 'S2', 20, 3.00); INSERT INTO LINEITEM VALUES ('11144', 1, 'P7', 'S4', 20, 4.00); INSERT INTO LINEITEM VALUES ('11144', 2, 'P8', 'S6', 10, 5.00); INSERT INTO LINEITEM VALUES ('11146', 1, 'P7', 'S5', 10, 4.50); INSERT INTO LINEITEM VALUES ('11146', 2, 'P8', 'S6', 10, 5.00); INSERT INTO LINEITEM VALUES ('11148', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11148', 2, 'P8', 'S4', 10, 6.00); INSERT INTO LINEITEM VALUES ('11149', 1, 'P7', 'S5', 40, 4.50); INSERT INTO LINEITEM VALUES ('11149', 2, 'P8', 'S8', 40, 4.00); INSERT INTO LINEITEM VALUES ('11150', 1, 'P3', 'S4', 20, 13.50); INSERT INTO LINEITEM VALUES ('11150', 2, 'P6', 'S4', 10, 5.00); INSERT INTO LINEITEM VALUES ('11152', 1, 'P5', 'S4', 10, 12.00); INSERT INTO LINEITEM VALUES ('11152', 2, 'P7', 'S2', 5, 3.00); INSERT INTO LINEITEM VALUES ('11152', 3, 'P8', 'S8', 40, 4.00); INSERT INTO LINEITEM VALUES ('11153', 1, 'P8', 'S8', 40, 4.00); INSERT INTO LINEITEM VALUES ('11154', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11154', 2, 'P3', 'S4', 20, 14.50); INSERT INTO LINEITEM VALUES ('11154', 3, 'P4', 'S4', 10, 13.00); INSERT INTO LINEITEM VALUES ('11154', 4, 'P5', 'S1', 10, 11.00); INSERT INTO LINEITEM VALUES ('11155', 1, 'P8', 'S8', 40, 4.50); INSERT INTO LINEITEM VALUES ('11156', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11156', 2, 'P3', 'S4', 20, 13.50); INSERT INTO LINEITEM VALUES ('11156', 3, 'P5', 'S1', 10, 11.00); INSERT INTO LINEITEM VALUES ('11157', 1, 'P3', 'S4', 20, 13.50); INSERT INTO LINEITEM VALUES ('11157', 2, 'P5', 'S1', 10, 11.00); INSERT INTO LINEITEM VALUES ('11158', 1, 'P1', 'S2', 10, 11.50); INSERT INTO LINEITEM VALUES ('11158', 2, 'P3', 'S4', 20, 13.50); INSERT INTO LINEITEM VALUES ('11159', 1, 'P6', 'S4', 10, 5.00); INSERT INTO LINEITEM VALUES ('11159', 2, 'P7', 'S2', 5, 3.00); INSERT INTO LINEITEM VALUES ('11160', 1, 'P1', 'S2', 10, 12.50); INSERT INTO LINEITEM VALUES ('11160', 2, 'P7', 'S2', 5, 3.00); INSERT INTO LINEITEM VALUES ('11170', 1, 'P3', 'S4', 20, 12.50); INSERT INTO LINEITEM VALUES ('11170', 2, 'P4', 'S4', 10, 13.00); -------------------------------------------------------------------- ---- Part V - Chapter 21: Set Operations (UNION - INTERSECT - EXCEPT) -------------------------------------------------------------------- DROP TABLE PROJMGR; DROP TABLE PROJMGR2; DROP TABLE PROJ1PARTS; DROP TABLE PROJ2PARTS; DROP TABLE PROJ3PARTS; DROP TABLE JUNK1; DROP TABLE JUNK2; CREATE TABLE PROJMGR (ENO CHAR (4) NOT NULL PRIMARY KEY, PMNAME VARCHAR (25) NOT NULL, MBA CHAR(1) NOT NULL, RATE DECIMAL (7,2) NOT NULL, DNO INTEGER NOT NULL); INSERT INTO PROJMGR VALUES ('1000', 'MOE', 'N', 500.00, 20); INSERT INTO PROJMGR VALUES ('2500', 'DICK', 'N', 100.00, 40); INSERT INTO PROJMGR VALUES ('6000', 'GEORGE','Y', 10.00, 20); INSERT INTO PROJMGR VALUES ('4500', 'DON', 'N', 70.00, 40); CREATE TABLE PROJMGR2 (PMNO CHAR (4) NOT NULL PRIMARY KEY, PMNAME VARCHAR (25) NOT NULL, MBA CHAR(1) NOT NULL, RATE DECIMAL (7,2) NOT NULL, DNO INTEGER NOT NULL); INSERT INTO PROJMGR2 VALUES ('1500', 'MOE', 'N', 500.00, 20); INSERT INTO PROJMGR2 VALUES ('2500', 'DICK', 'N', 100.00, 40); INSERT INTO PROJMGR2 VALUES ('6500', 'GEORGE','Y', 10.00, 20); INSERT INTO PROJMGR2 VALUES ('4500', 'DON', 'N', 70.00, 40); CREATE TABLE PROJ1PARTS (PNO CHAR (2) NOT NULL, PNAME CHAR (5) NOT NULL, PCOLOR CHAR (6) NOT NULL, QTY INTEGER NOT NULL, PRIMARY KEY (PNO) ); INSERT INTO PROJ1PARTS VALUES ('P1', 'PART1', 'RED', 16); INSERT INTO PROJ1PARTS VALUES ('P2', 'PART2', 'BLUE', 16); INSERT INTO PROJ1PARTS VALUES ('P4', 'PART4', 'YELLOW', 17); INSERT INTO PROJ1PARTS VALUES ('P5', 'PART5', 'RED', 15); CREATE TABLE PROJ2PARTS (PNO CHAR (2) NOT NULL, PNAME CHAR (5) NOT NULL, PWT INTEGER NOT NULL, PRIMARY KEY (PNO) ); INSERT INTO PROJ2PARTS VALUES ('P3', 'PART3', 20); INSERT INTO PROJ2PARTS VALUES ('P4', 'PART4', 10); INSERT INTO PROJ2PARTS VALUES ('P5', 'PART5', 20); INSERT INTO PROJ2PARTS VALUES ('P6', 'PART6', 12); CREATE TABLE PROJ3PARTS (PNO CHAR (2) NOT NULL, PNAME CHAR (5) NOT NULL, PCOLOR CHAR (6) NOT NULL, QTY INTEGER NOT NULL, PRIMARY KEY (PNO) ); INSERT INTO PROJ3PARTS VALUES ('P3', 'PART3', 'PINK', 98); INSERT INTO PROJ3PARTS VALUES ('P6', 'PART6', 'BLUE', 97); INSERT INTO PROJ3PARTS VALUES ('P7', 'PART7', 'PINK', 95); INSERT INTO PROJ3PARTS VALUES ('P8', 'PART8', 'PINK', 99); CREATE TABLE JUNK1 (JNO INTEGER); INSERT INTO JUNK1 VALUES (20); INSERT INTO JUNK1 VALUES (10); INSERT INTO JUNK1 VALUES (20); INSERT INTO JUNK1 VALUES (40); INSERT INTO JUNK1 VALUES (10); INSERT INTO JUNK1 VALUES (20); INSERT INTO JUNK1 VALUES (NULL); INSERT INTO JUNK1 VALUES (NULL); INSERT INTO JUNK1 VALUES (NULL); CREATE TABLE JUNK2 (JNO INTEGER); INSERT INTO JUNK2 VALUES (20); INSERT INTO JUNK2 VALUES (40); INSERT INTO JUNK2 VALUES (20); INSERT INTO JUNK2 VALUES (40); INSERT INTO JUNK2 VALUES (NULL); INSERT INTO JUNK2 VALUES (30); -- The following tables (SETA, SETB, and SETC) are used to illustrate the -- execution hierarchy for the set operations. These tables are not referenced -- in any sample queries or exercises. DROP TABLE SETA; DROP TABLE SETB; DROP TABLE SETC; CREATE TABLE SETA (XNO INTEGER); INSERT INTO SETA VALUES (10); INSERT INTO SETA VALUES (20); INSERT INTO SETA VALUES (30); INSERT INTO SETA VALUES (50); CREATE TABLE SETB (XNO INTEGER); INSERT INTO SETB VALUES (10); INSERT INTO SETB VALUES (30); INSERT INTO SETB VALUES (40); INSERT INTO SETB VALUES (60); CREATE TABLE SETC (XNO INTEGER); INSERT INTO SETC VALUES (10); INSERT INTO SETC VALUES (20); INSERT INTO SETC VALUES (40); INSERT INTO SETC VALUES (70); GO ------------------------------------------------------------ -----------------------Chapter 28: CREATE VIEW ------------ ------------------------------------------------------------ DROP VIEW DEPT20; DROP VIEW DEPTSTATSV; DROP VIEW EMPDEPTV; -- Preliminary observation re. following CREATE VIEW statements: -- In SQL Server, GO is specified before each CREATE VIEW statement. -- Why? -- SQL Server allows you to bundle a "batch" of statments to be executed as a unit. -- The word GO tells the system to execute preceding statements as a batch. -- Special Rule: When executing a CREATE VIEW statement, -- it must be the only statement in the batch. -- (This rule applies to some other DDL statements.) GO CREATE VIEW DEPT20 AS SELECT ENO, ENAME, SALARY FROM EMPLOYEE WHERE DNO = 20; GO CREATE VIEW DEPTSTATSV (DNO, MAXSAL, MINSAL, TOTALSAL) AS SELECT DNO, MAX(SALARY), MIN(SALARY), SUM(SALARY) FROM EMPLOYEE GROUP BY DNO; GO CREATE VIEW EMPDEPTV AS SELECT E.ENAME, E.ENO, E.SALARY, E.DNO, D.DNAME, D.BUDGET FROM EMPLOYEE E, DEPARTMENT D WHERE E.DNO = D.DNO; GO ---xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ---xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -------------------------------------------------------------------- ---------------------- Chapter 30: Recursive Queries ------------- -------------------------------------------------------------------- ---xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ---xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx DROP TABLE SUPERVISES; DROP TABLE REPORTS_TO; DROP TABLE REPORTS_TO2; DROP TABLE RDEMO2MM; DROP TABLE REMPLOYEE; DROP TABLE REMPLOYEE2; DROP TABLE REMPLOYEE3; DROP TABLE RDEMO1; DROP TABLE RDEMO2; -- REMPLOYEE creates recursive one-to-many relationship CREATE TABLE REMPLOYEE (ENO CHAR (4) NOT NULL PRIMARY KEY, ENAME VARCHAR(25) NOT NULL, SALARY DECIMAL(7,2), SENO CHAR (4), FOREIGN KEY (SENO) REFERENCES REMPLOYEE); INSERT INTO REMPLOYEE VALUES ('1000', 'MOE', 2000.00, NULL); INSERT INTO REMPLOYEE VALUES ('2000', 'JANET', 2000.00,'1000'); INSERT INTO REMPLOYEE VALUES ('3000', 'LARRY', 3000.00,'1000'); INSERT INTO REMPLOYEE VALUES ('8000', 'JOE', 8000.00,'1000'); INSERT INTO REMPLOYEE VALUES ('4000', 'JULIE', 500.00,'2000'); INSERT INTO REMPLOYEE VALUES ('5000', 'JESSIE', 400.00,'2000'); INSERT INTO REMPLOYEE VALUES ('6000', 'FRANK', 9000.00,'2000'); INSERT INTO REMPLOYEE VALUES ('6500', 'CURLY', 8000.00,'3000'); INSERT INTO REMPLOYEE VALUES ('8500', 'GEORGE', 7000.00,'8000'); INSERT INTO REMPLOYEE VALUES ('4500', 'JOHNNY', 2000.00,'4000'); INSERT INTO REMPLOYEE VALUES ('4600', 'ELEANOR', 3000.00,'4000'); INSERT INTO REMPLOYEE VALUES ('5500', 'HANNAH', 4000.00,'5000'); INSERT INTO REMPLOYEE VALUES ('7500', 'SHEMP', 9000.00,'6500'); INSERT INTO REMPLOYEE VALUES ('8600', 'DICK', 6000.00,'8500'); INSERT INTO REMPLOYEE VALUES ('8700', 'HANK', 6000.00,'8500'); INSERT INTO REMPLOYEE VALUES ('4700', 'ANDY', 2000.00,'4600'); INSERT INTO REMPLOYEE VALUES ('4800', 'MATT', 3000.00,'4600'); -- RDEMO1 creates recursive one-to-many relationship CREATE TABLE RDEMO1 (PKEY INTEGER NOT NULL PRIMARY KEY, CODE INTEGER NOT NULL, FKEY INTEGER, FOREIGN KEY (FKEY) REFERENCES RDEMO1); INSERT INTO RDEMO1 VALUES (10, 0, NULL); INSERT INTO RDEMO1 VALUES (20, 0, 10); INSERT INTO RDEMO1 VALUES (25, 0, 20); INSERT INTO RDEMO1 VALUES (15, 1000, 25); INSERT INTO RDEMO1 VALUES (30, 0, 15); INSERT INTO RDEMO1 VALUES (35, 0, 30); INSERT INTO RDEMO1 VALUES (40, 0, 25); INSERT INTO RDEMO1 VALUES (50, 0, 40); -- Two following tables, REMPLOYEE2 and REPORTS_TO, -- define a recursive many-to-many relationship. CREATE TABLE REMPLOYEE2 (ENO CHAR (4) NOT NULL PRIMARY KEY, ENAME VARCHAR(25) NOT NULL, SALARY DECIMAL(7,2) ); INSERT INTO REMPLOYEE2 VALUES ('0000', 'DUMMY', 0000.00); INSERT INTO REMPLOYEE2 VALUES ('1000', 'MOE', 2000.00); INSERT INTO REMPLOYEE2 VALUES ('2000', 'JANET', 2000.00); INSERT INTO REMPLOYEE2 VALUES ('3000', 'LARRY', 3000.00); INSERT INTO REMPLOYEE2 VALUES ('8000', 'JOE', 8000.00); INSERT INTO REMPLOYEE2 VALUES ('4000', 'JULIE', 500.00); INSERT INTO REMPLOYEE2 VALUES ('5000', 'JESSIE', 400.00); INSERT INTO REMPLOYEE2 VALUES ('6000', 'FRANK', 9000.00); INSERT INTO REMPLOYEE2 VALUES ('6500', 'CURLY', 8000.00); INSERT INTO REMPLOYEE2 VALUES ('8500', 'GEORGE', 7000.00); INSERT INTO REMPLOYEE2 VALUES ('4500', 'JOHNNY', 2000.00); INSERT INTO REMPLOYEE2 VALUES ('4600', 'ELEANOR', 3000.00); INSERT INTO REMPLOYEE2 VALUES ('5500', 'HANNAH', 4000.00); INSERT INTO REMPLOYEE2 VALUES ('7500', 'SHEMP', 9000.00); INSERT INTO REMPLOYEE2 VALUES ('8600', 'DICK', 6000.00); INSERT INTO REMPLOYEE2 VALUES ('8700', 'HANK', 6000.00); INSERT INTO REMPLOYEE2 VALUES ('4700', 'ANDY', 2000.00); INSERT INTO REMPLOYEE2 VALUES ('4800', 'MATT', 3000.00); CREATE TABLE REPORTS_TO (ENO CHAR (4) NOT NULL, SENO CHAR (4) NOT NULL, PRIMARY KEY (ENO, SENO), FOREIGN KEY (ENO) REFERENCES REMPLOYEE2, FOREIGN KEY (SENO) REFERENCES REMPLOYEE2); INSERT INTO REPORTS_TO VALUES ('1000', '0000'); INSERT INTO REPORTS_TO VALUES ('2000', '1000'); INSERT INTO REPORTS_TO VALUES ('3000', '1000'); INSERT INTO REPORTS_TO VALUES ('8000', '1000'); INSERT INTO REPORTS_TO VALUES ('4000', '2000'); INSERT INTO REPORTS_TO VALUES ('5000', '2000'); INSERT INTO REPORTS_TO VALUES ('6000', '2000'); INSERT INTO REPORTS_TO VALUES ('6500', '3000'); INSERT INTO REPORTS_TO VALUES ('8500', '8000'); INSERT INTO REPORTS_TO VALUES ('4500', '4000'); INSERT INTO REPORTS_TO VALUES ('4600', '4000'); INSERT INTO REPORTS_TO VALUES ('5500', '5000'); INSERT INTO REPORTS_TO VALUES ('7500', '6500'); INSERT INTO REPORTS_TO VALUES ('8600', '8500'); INSERT INTO REPORTS_TO VALUES ('8700', '8500'); INSERT INTO REPORTS_TO VALUES ('4700', '4600'); INSERT INTO REPORTS_TO VALUES ('4800', '4600'); INSERT INTO REPORTS_TO VALUES ('4600', '5000'); INSERT INTO REPORTS_TO VALUES ('4800', '5500'); INSERT INTO REPORTS_TO VALUES ('4800', '6000'); -- All rows, except the last three rows, show the same parent-child relationships -- as in REMPLOYEE. These three rows add parent-child relationships that establish a -- Many-to-Many Network structure. -- Two following tables, RDEMO2 and RDEMO2MM, -- define a recursive many-to-many relationship CREATE TABLE RDEMO2 (XKEY INTEGER NOT NULL PRIMARY KEY, AMT INTEGER); INSERT INTO RDEMO2 VALUES (00, NULL); INSERT INTO RDEMO2 VALUES (10, 200); INSERT INTO RDEMO2 VALUES (20, 700); INSERT INTO RDEMO2 VALUES (30, 500); INSERT INTO RDEMO2 VALUES (40, 100); INSERT INTO RDEMO2 VALUES (50, 200); INSERT INTO RDEMO2 VALUES (60, 500); INSERT INTO RDEMO2 VALUES (70, 600); CREATE TABLE RDEMO2MM (CHILDKEY INTEGER NOT NULL, PARENTKEY INTEGER NOT NULL, PRIMARY KEY (CHILDKEY, PARENTKEY), FOREIGN KEY (CHILDKEY) REFERENCES RDEMO2, FOREIGN KEY (PARENTKEY) REFERENCES RDEMO2); INSERT INTO RDEMO2MM VALUES (40, 00); INSERT INTO RDEMO2MM VALUES (30, 40); INSERT INTO RDEMO2MM VALUES (10, 40); INSERT INTO RDEMO2MM VALUES (20, 40); INSERT INTO RDEMO2MM VALUES (50, 10); INSERT INTO RDEMO2MM VALUES (50, 30); INSERT INTO RDEMO2MM VALUES (50, 20); INSERT INTO RDEMO2MM VALUES (70, 60); INSERT INTO RDEMO2MM VALUES (60, 50); -- The following two tables, REMPLOYEE3 and REPORTS_TO2, -- define an alternative recursive many-to-many relationship. -- All rows in REPORTS_TO2 have same data as REPORTS_TO, -- plus two additional columns as intersection data. CREATE TABLE REMPLOYEE3 (ENO CHAR (4) NOT NULL PRIMARY KEY, ENAME VARCHAR(25) NOT NULL); INSERT INTO REMPLOYEE3 VALUES ('0000', 'DUMMY'); INSERT INTO REMPLOYEE3 VALUES ('1000', 'MOE'); INSERT INTO REMPLOYEE3 VALUES ('2000', 'JANET'); INSERT INTO REMPLOYEE3 VALUES ('3000', 'LARRY'); INSERT INTO REMPLOYEE3 VALUES ('8000', 'JOE'); INSERT INTO REMPLOYEE3 VALUES ('4000', 'JULIE'); INSERT INTO REMPLOYEE3 VALUES ('5000', 'JESSIE'); INSERT INTO REMPLOYEE3 VALUES ('6000', 'FRANK'); INSERT INTO REMPLOYEE3 VALUES ('6500', 'CURLY'); INSERT INTO REMPLOYEE3 VALUES ('8500', 'GEORGE'); INSERT INTO REMPLOYEE3 VALUES ('4500', 'JOHNNY'); INSERT INTO REMPLOYEE3 VALUES ('4600', 'ELEANOR'); INSERT INTO REMPLOYEE3 VALUES ('5500', 'HANNAH'); INSERT INTO REMPLOYEE3 VALUES ('7500', 'SHEMP'); INSERT INTO REMPLOYEE3 VALUES ('8600', 'DICK'); INSERT INTO REMPLOYEE3 VALUES ('8700', 'HANK'); INSERT INTO REMPLOYEE3 VALUES ('4700', 'ANDY'); INSERT INTO REMPLOYEE3 VALUES ('4800', 'MATT'); CREATE TABLE REPORTS_TO2 (ENO CHAR (4) NOT NULL, SENO CHAR (4) NOT NULL, HOURS INTEGER, PAYRATE INTEGER, PRIMARY KEY (ENO, SENO), FOREIGN KEY (ENO) REFERENCES REMPLOYEE3, FOREIGN KEY (SENO) REFERENCES REMPLOYEE3); INSERT INTO REPORTS_TO2 VALUES ('1000', '0000', 30, 100); INSERT INTO REPORTS_TO2 VALUES ('2000', '1000', 40, 80); INSERT INTO REPORTS_TO2 VALUES ('3000', '1000', 40, 70); INSERT INTO REPORTS_TO2 VALUES ('8000', '1000', 40, 80); INSERT INTO REPORTS_TO2 VALUES ('4000', '2000', 40, 60); INSERT INTO REPORTS_TO2 VALUES ('5000', '2000', 40, 60); INSERT INTO REPORTS_TO2 VALUES ('6000', '2000', 40, 70); INSERT INTO REPORTS_TO2 VALUES ('6500', '3000', 40, 60); INSERT INTO REPORTS_TO2 VALUES ('8500', '8000', 20, 90); INSERT INTO REPORTS_TO2 VALUES ('4500', '4000', 40, 60); INSERT INTO REPORTS_TO2 VALUES ('4600', '4000', 20, 50); INSERT INTO REPORTS_TO2 VALUES ('5500', '5000', 40, 50); INSERT INTO REPORTS_TO2 VALUES ('7500', '6500', 30, 50); INSERT INTO REPORTS_TO2 VALUES ('8600', '8500', 40, 40); INSERT INTO REPORTS_TO2 VALUES ('8700', '8500', 40, 50); INSERT INTO REPORTS_TO2 VALUES ('4700', '4600', 20, 40); INSERT INTO REPORTS_TO2 VALUES ('4800', '4600', 10, 50); INSERT INTO REPORTS_TO2 VALUES ('4600', '5000', 20, 50); INSERT INTO REPORTS_TO2 VALUES ('4800', '5500', 20, 20); INSERT INTO REPORTS_TO2 VALUES ('4800', '6000', 10, 60); -- The following SUPERVISES table is a "good" parent-oriented table -- that contains the same data as the REPORTS_TO table. CREATE TABLE SUPERVISES (SENO CHAR (4) NOT NULL, ENO CHAR (4) NOT NULL, PRIMARY KEY (SENO, ENO), FOREIGN KEY (SENO) REFERENCES REMPLOYEE2, FOREIGN KEY (ENO) REFERENCES REMPLOYEE2); INSERT INTO SUPERVISES VALUES ('0000', '1000'); INSERT INTO SUPERVISES VALUES ('1000', '2000'); INSERT INTO SUPERVISES VALUES ('1000', '3000'); INSERT INTO SUPERVISES VALUES ('1000', '8000'); INSERT INTO SUPERVISES VALUES ('2000', '4000'); INSERT INTO SUPERVISES VALUES ('2000', '5000'); INSERT INTO SUPERVISES VALUES ('2000', '6000'); INSERT INTO SUPERVISES VALUES ('3000', '6500'); INSERT INTO SUPERVISES VALUES ('8000', '8500'); INSERT INTO SUPERVISES VALUES ('4000', '4500'); INSERT INTO SUPERVISES VALUES ('4000', '4600'); INSERT INTO SUPERVISES VALUES ('5000', '5500'); INSERT INTO SUPERVISES VALUES ('6500', '7500'); INSERT INTO SUPERVISES VALUES ('8500', '8600'); INSERT INTO SUPERVISES VALUES ('8500', '8700'); INSERT INTO SUPERVISES VALUES ('4600', '4700'); INSERT INTO SUPERVISES VALUES ('4600', '4800'); INSERT INTO SUPERVISES VALUES ('5000', '4600'); INSERT INTO SUPERVISES VALUES ('5500', '4800'); INSERT INTO SUPERVISES VALUES ('6000', '4800');