CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE EMPLOYEE(USERID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(127) DEFAULT NULL,LASTNAME VARCHAR(127) DEFAULT NULL,TITLE VARCHAR(127) DEFAULT NULL, PICURL VARCHAR(127) DEFAULT NULL,TENANTID INTEGER DEFAULT 1)
CREATE MEMORY TABLE CUSTOMER(CUSTID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1000) NOT NULL PRIMARY KEY,CUSTOMERNAME VARCHAR(50)  NOT NULL,LASTNAME VARCHAR(50)  NOT NULL,FIRSTNAME VARCHAR(50)  NOT NULL,PHONE VARCHAR(50)  NOT NULL,ADDRESSLINE1 VARCHAR(50),ADDRESSLINE2 VARCHAR(50) DEFAULT NULL,CITY VARCHAR(50),STATE VARCHAR(50) DEFAULT NULL,POSTALCODE VARCHAR(15) DEFAULT NULL,COUNTRY VARCHAR(50),SALESREPID INTEGER NOT NULL,CREDITLIMIT DOUBLE DEFAULT NULL,TENANTID INTEGER DEFAULT 1, CONSTRAINT SALESREPFKEY FOREIGN KEY(SALESREPID) REFERENCES EMPLOYEE(USERID))
CREATE MEMORY TABLE PURCHASE(PURCHASEID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 10100) NOT NULL PRIMARY KEY,PURCHASEDATE DATE NOT NULL,REQUIREDDATE DATE NOT NULL,SHIPPEDDATE DATE DEFAULT NULL,STATUS VARCHAR(15) NOT NULL,COMMENTS VARCHAR(255) DEFAULT NULL,CUSTOMERNUMBER INTEGER NOT NULL,TENANTID INTEGER DEFAULT 1, CONSTRAINT PURCHASEFKEY FOREIGN KEY(CUSTOMERNUMBER) REFERENCES CUSTOMER(CUSTID))
CREATE MEMORY TABLE USER(USERID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,USERNAME VARCHAR(20),PASSWORD VARCHAR(20),ROLE VARCHAR(20),TENANTID INTEGER DEFAULT 1)
ALTER TABLE EMPLOYEE ALTER COLUMN USERID RESTART WITH 7
ALTER TABLE CUSTOMER ALTER COLUMN CUSTID RESTART WITH 1001
ALTER TABLE PURCHASE ALTER COLUMN PURCHASEID RESTART WITH 10310
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO CUSTOMER VALUES(112,'Signal Gift Stores','King','Jean','7025551838','3900 Las Vegas Blvd S',NULL,'Las Vegas','NV','89119','USA',1,71800.0E0,1)
INSERT INTO CUSTOMER VALUES(124,'Palladin Gifts Distributors Ltd.','Nelson','Susan','4155551450','500 Guerrero St',NULL,'San Francisco','CA','94110','USA',1,210500.0E0,1)
INSERT INTO CUSTOMER VALUES(129,'Stonehenge Wheels Co.','Murphy','Julie','6505555787','5557 North Pendale Street',NULL,'San Francisco','CA','94217','USA',1,64600.0E0,1)
INSERT INTO CUSTOMER VALUES(131,'The Well of Toys Inc.','Lee','Kwai','2125557818','3139 16th St',NULL,'San Francisco','CA','94103','USA',1,114900.0E0,1)
INSERT INTO CUSTOMER VALUES(151,'Starbase One Machine Inc','Young','Jeff','2125557413','4092 Furth Circle','Suite 400','NYC','NY','10022','USA',2,138500.0E0,1)
INSERT INTO CUSTOMER VALUES(157,'Pandoras Box Inc.','Leong','Kelvin','2155551555','647 Valencia St',NULL,'San Francisco','CA','94110','USA',2,100600.0E0,1)
INSERT INTO CUSTOMER VALUES(161,'Digital Towers Stores Inc.','Hashimoto','Juri','6505556809','9408 Furth Circle',NULL,'Burlingame','CA','94217','USA',2,84600.0E0,1)
INSERT INTO CUSTOMER VALUES(168,'Marin Online Inc','Franco','Keith','2035557845','149 Spinnaker Dr.','Suite 101','New Haven','CT','97823','USA',2,0.0E0,1)
INSERT INTO CUSTOMER VALUES(173,'Stonehenge Collectables Co.','Tseng','Jerry','6175555555','4658 Baden Av.',NULL,'Cambridge','MA','51247','USA',2,43400.0E0,1)
INSERT INTO CUSTOMER VALUES(175,'Abacus Depot Inc.','King','Julie','2035552570','25593 South Bay Ln.',NULL,'Bridgewater','CT','97562','USA',2,84300.0E0,1)
INSERT INTO CUSTOMER VALUES(181,'Vitachrome Inc.','Frick','Michael','2125551500','2678 Kingston Rd.','Suite 101','NYC','NY','10022','USA',3,76400.0E0,1)
INSERT INTO CUSTOMER VALUES(198,'Auto-Moto Classics Inc.','Taylor','Leslie','6175558428','16780 Pompton St.',NULL,'Brickhaven','MA','58339','USA',3,23000.0E0,1)
INSERT INTO CUSTOMER VALUES(204,'Online Mini Collectables','Barajas','Miguel','6175557555','7635 Spinnaker Dr.',NULL,'Brickhaven','MA','58339','USA',3,68700.0E0,1)
INSERT INTO CUSTOMER VALUES(205,'Toys4GrownUps.com','Young','Julie','6265557265','78934 Hillside Dr.',NULL,'Pasadena','CA','90003','USA',3,90700.0E0,1)
INSERT INTO CUSTOMER VALUES(219,'Boards & Toys Co.','Young','Mary','3105552373','4097 Douglas Av.',NULL,'Glendale','CA','92561','USA',3,11000.0E0,1)
INSERT INTO CUSTOMER VALUES(239,'Collectable Mini Designs Co.','Thompson','Valarie','7605558146','361 Furth Circle',NULL,'San Diego','CA','91217','USA',3,105000.0E0,1)
INSERT INTO CUSTOMER VALUES(319,'Mini Classics','Frick','Steve','9145554562','3758 North Pendale Street',NULL,'White Plains','NY','24067','USA',3,102700.0E0,1)
INSERT INTO CUSTOMER VALUES(320,'Mini Creations Ltd.','Huang','Wing','5085559555','4575 Hillside Dr.',NULL,'New Bedford','MA','50553','USA',3,94500.0E0,1)
INSERT INTO CUSTOMER VALUES(321,'Corporate Gift Ideas Co.','Brown','Julie','6505551386','7734 Strong St.',NULL,'San Francisco','CA','94217','USA',4,105000.0E0,1)
INSERT INTO CUSTOMER VALUES(328,'Tekni Collectables Inc.','Brown','William','2015559350','7476 Moss Rd.',NULL,'Newark','NJ','94019','USA',4,43000.0E0,1)
INSERT INTO CUSTOMER VALUES(339,'Classic Gift Ideas, Inc','Cervantes','Francisca','2155554695','782 First Street',NULL,'Philadelphia','PA','71270','USA',4,81100.0E0,1)
INSERT INTO CUSTOMER VALUES(362,'Gifts4AllAges.com','Yoshido','Juri','6175559555','8616 Spinnaker Dr.',NULL,'Boston','MA','51003','USA',4,41900.0E0,1)
INSERT INTO CUSTOMER VALUES(363,'Online Diecast Creations Co.','Young','Dorothy','6035558647','2304 Long Airport Avenue',NULL,'Nashua','NH','62005','USA',1216,114200.0E0,1)
INSERT INTO CUSTOMER VALUES(379,'Collectables For Less Inc.','Nelson','Allen','6175558555','7825 Douglas Av.',NULL,'Brickhaven','MA','58339','USA',4,70700.0E0,1)
INSERT INTO CUSTOMER VALUES(424,'Classic Legends Inc.','Hernandez','Maria','2125558493','5905 Pompton St.','Suite 750','NYC','NY','10022','USA',5,67500.0E0,2)
INSERT INTO CUSTOMER VALUES(447,'Gift Ideas Corp.','Lewis','Dan','2035554407','2440 Pompton St.',NULL,'Glendale','CT','97561','USA',5,49700.0E0,2)
INSERT INTO CUSTOMER VALUES(450,'The Sharp Gifts Warehouse','Frick','Sue','4085553659','3086 Ingle Ln.',NULL,'San Jose','CA','94217','USA',5,77600.0E0,2)
INSERT INTO CUSTOMER VALUES(455,'Super Scale Inc.','Murphy','Leslie','2035559545','567 North Pendale Street',NULL,'New Haven','CT','97823','USA',5,95400.0E0,2)
INSERT INTO CUSTOMER VALUES(456,'Microscale Inc.','Choi','Yu','2125551957','5290 North Pendale Street','Suite 200','NYC','NY','10022','USA',5,39800.0E0,2)
INSERT INTO CUSTOMER VALUES(462,'FunGiftIdeas.com','Benitez','Violeta','5085552555','1785 First Street',NULL,'New Bedford','MA','50553','USA',5,85800.0E0,2)
INSERT INTO CUSTOMER VALUES(475,'West Coast Collectables Co.','Thompson','Steve','3105553722','3675 Furth Circle',NULL,'Burbank','CA','94019','USA',6,55400.0E0,2)
INSERT INTO CUSTOMER VALUES(486,'Motor Mint Distributors Inc.','Salazar','Rosa','2155559857','11328 Douglas Av.',NULL,'Philadelphia','PA','71270','USA',6,72600.0E0)
INSERT INTO CUSTOMER VALUES(487,'Signal Collectibles Ltd.','Taylor','Sue','4155554312','2793 Furth Circle',NULL,'Brisbane','CA','94217','USA',6,60300.0E0,2)
INSERT INTO CUSTOMER VALUES(495,'Diecast Collectables','Franco','Valarie','6175552555','6251 Ingle Ln.',NULL,'Boston','MA','51003','USA',6,85100.0E0,2)
INSERT INTO EMPLOYEE VALUES(1,'Fred','Berg','Western Region Manager','http://dev.wavemaker.com/example/comedians/harpomarx.jpg',1)
INSERT INTO EMPLOYEE VALUES(2,'Sandy','Connors','Eastern Region Manager','http://dev.wavemaker.com/example/comedians/georgecarlin.jpg',1)
INSERT INTO EMPLOYEE VALUES(3,'Alexander','Smithson','Central Region Manager','http://dev.wavemaker.com/example/comedians/shirleytemple.jpg',1)
INSERT INTO EMPLOYEE VALUES(4,'Nils','Martenson','Asian Region Manager','http://dev.wavemaker.com/example/comedians/danackroyd.jpg',1)
INSERT INTO EMPLOYEE VALUES(5,'Susan','Nelson','North American Manager','http://dev.wavemaker.com/example/comedians/carolburnett.jpg',2)
INSERT INTO EMPLOYEE VALUES(6,'Arthur','Keller','European Manager','http://dev.wavemaker.com/example/comedians/willrogers.jpg',2)
INSERT INTO PURCHASE VALUES(10100,'2003-01-06','2003-01-13','2003-01-10','Shipped','Wants us to ship him a new widget.',112,1)
INSERT INTO PURCHASE VALUES(10101,'2003-01-09','2003-01-18','2003-01-11','Shipped','Check on availability.',128,1)
INSERT INTO PURCHASE VALUES(10102,'2003-01-10','2003-01-18','2003-01-14','Shipped','Understanding and cheerful, pleasure to do business with!',112,1)
INSERT INTO PURCHASE VALUES(10103,'2003-01-29','2003-02-07','2003-02-02','Shipped','Tends to think all that glitters is gold.',124,1)
INSERT INTO PURCHASE VALUES(10104,'2003-01-31','2003-02-09','2003-02-01','Shipped','Moving to Boise soon, may ask him next time for update info.',129,1)
INSERT INTO PURCHASE VALUES(10105,'2003-02-11','2003-02-21','2003-02-12','Shipped','Try to route through Chicago for best experience.',131,1)
INSERT INTO PURCHASE VALUES(10106,'2003-02-17','2003-02-24','2003-02-21','Shipped','Need to see if there is room to ship amongst 3 different parcels',151,1)
INSERT INTO PURCHASE VALUES(10107,'2003-02-24','2003-03-03','2003-02-26','Shipped','Difficult to negotiate with customer. We need more marketing materials',157,1)
INSERT INTO PURCHASE VALUES(10108,'2003-03-03','2003-03-12','2003-03-08','Shipped','Never been to Spain by says the music is good.',161,1)
INSERT INTO PURCHASE VALUES(10109,'2003-03-10','2003-03-19','2003-03-11','Shipped','Customer requested that FedEx Ground is used for this shipping',168,1)
INSERT INTO PURCHASE VALUES(10110,'2003-03-18','2003-03-24','2003-03-20','Shipped','Is reccomending to friends.',173,1)
INSERT INTO PURCHASE VALUES(10111,'2003-03-25','2003-03-31','2003-03-30','Shipped','Check on availability.',175,1)
INSERT INTO PURCHASE VALUES(10112,'2003-03-24','2003-04-03','2003-03-29','Shipped','Customer requested that ad materials (such as posters, pamphlets) be included in the shippment',181,1)
INSERT INTO PURCHASE VALUES(10113,'2003-03-26','2003-04-02','2003-03-27','Shipped','Requests extra bubble wrap.',198,1)
INSERT INTO PURCHASE VALUES(10200,'2003-01-06','2003-01-13','2003-01-10','Shipped','Unhappy with warm climates, should investigate an AC unit.',204,1)
INSERT INTO PURCHASE VALUES(10201,'2003-01-09','2003-01-18','2003-01-11','Shipped','Check on availability.',205,1)
INSERT INTO PURCHASE VALUES(10202,'2003-01-10','2003-01-18','2003-01-14','Shipped','Last shipment arrived in seventeen pieces.',219,1)
INSERT INTO PURCHASE VALUES(10203,'2003-01-29','2003-02-07','2003-02-02','Shipped','Check on availability.',239,1)
INSERT INTO PURCHASE VALUES(10204,'2003-01-31','2003-02-09','2003-02-01','Shipped','Its been ten years or maybe more since he first became a customer.',319,1)
INSERT INTO PURCHASE VALUES(10205,'2003-02-11','2003-02-21','2003-02-12','Shipped','Unable to contact via telex.',320,1)
INSERT INTO PURCHASE VALUES(10206,'2003-02-17','2003-02-24','2003-02-21','Shipped','Wants more info on e-books.',321,1)
INSERT INTO PURCHASE VALUES(10207,'2003-02-24','2003-03-03','2003-02-26','Shipped','Couldnt be happier!',328,1)
INSERT INTO PURCHASE VALUES(10208,'2003-03-03','2003-03-12','2003-03-08','Shipped','What a gracious customer.',339,1)
INSERT INTO PURCHASE VALUES(10209,'2003-03-10','2003-03-19','2003-03-11','Shipped','Customer requested that FedEx Ground is used for this shipping',362,1)
INSERT INTO PURCHASE VALUES(10210,'2003-03-18','2003-03-24','2003-03-20','Shipped','Sent us fruitcakes last Xmas!',363,1)
INSERT INTO PURCHASE VALUES(10211,'2003-03-25','2003-03-31','2003-03-30','Shipped','Opening another branch within days.',379,1)
INSERT INTO PURCHASE VALUES(10300,'2003-01-06','2003-01-13','2003-01-10','Shipped','Wants us to let him know when we have at least 10K more in stock.',424,2)
INSERT INTO PURCHASE VALUES(10301,'2003-01-09','2003-01-18','2003-01-11','Shipped','Check on availability.',447,2)
INSERT INTO PURCHASE VALUES(10302,'2003-01-10','2003-01-18','2003-01-14','Shipped','Very excited about the product list.',450,2)
INSERT INTO PURCHASE VALUES(10303,'2003-01-29','2003-02-07','2003-02-02','Shipped','Tends to eat all the food then return the wrapper complaining.',455,2)
INSERT INTO PURCHASE VALUES(10304,'2003-01-31','2003-02-09','2003-02-01','Shipped','Will soon become a supplier.',456,2)
INSERT INTO PURCHASE VALUES(10305,'2003-02-11','2003-02-21','2003-02-12','Shipped','Unable to guess which method is quickest, so he wont choose at all.',462,2)
INSERT INTO PURCHASE VALUES(10306,'2003-02-17','2003-02-24','2003-02-21','Shipped','Check on availability.',475,2)
INSERT INTO PURCHASE VALUES(10307,'2003-02-24','2003-03-03','2003-02-26','Shipped','Difficult to negotiate with customer. We need more marketing materials',486,2)
INSERT INTO PURCHASE VALUES(10308,'2003-03-03','2003-03-12','2003-03-08','Shipped','Loves doing business with us.',487,2)
INSERT INTO PURCHASE VALUES(10309,'2003-03-10','2003-03-19','2003-03-11','Shipped','Customer requested that FedEx Ground is used for this shipping',495,2)
INSERT INTO USER VALUES(1,'admin','admin','adminrole',1)
INSERT INTO USER VALUES(2,'user','user','userrole',1)
INSERT INTO USER VALUES(3,'admin2','admin2','adminrole',2)
INSERT INTO USER VALUES(4,'user2','user2','userrole',2)