CREATE DATABASE IF NOT EXISTS hwdb; USE hwdb; DROP TABLE IF EXISTS LINE; DROP TABLE IF EXISTS INVOICE; DROP TABLE IF EXISTS CUSTOMER; DROP TABLE IF EXISTS PRODUCT; DROP TABLE IF EXISTS VENDOR; DROP TABLE IF EXISTS EMPLOYEE; DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS P; DROP TABLE IF EXISTS V; DROP TABLE IF EXISTS CUSTOMER_2; CREATE TABLE V ( V_CODE int(11) NOT NULL, V_NAME varchar(35) NOT NULL, V_CONTACT varchar(15) NOT NULL, V_AREACODE char(3) NOT NULL, V_PHONE char(8) NOT NULL, V_STATE char(2) NOT NULL, V_ORDER char(1) NOT NULL, PRIMARY KEY (V_CODE) ); CREATE TABLE P ( P_CODE varchar(10) NOT NULL, P_DESCRIPT varchar(35) NOT NULL, P_INDATE datetime NOT NULL, P_QOH decimal(18,0) NOT NULL, P_MIN decimal(18,0) NOT NULL, P_PRICE decimal(8,2) NOT NULL, P_DISCOUNT decimal(4,2) NOT NULL, V_CODE decimal(18,0) DEFAULT NULL, P_MIN_ORDER decimal(18,0) DEFAULT NULL, P_REORDER decimal(18,0) DEFAULT NULL, PRIMARY KEY (P_CODE) ); CREATE TABLE VENDOR ( V_CODE decimal(18,0) NOT NULL, V_NAME varchar(35) NOT NULL, V_CONTACT varchar(15) NOT NULL, V_AREACODE char(3) NOT NULL, V_PHONE char(8) NOT NULL, V_STATE char(2) NOT NULL, V_ORDER char(1) NOT NULL, PRIMARY KEY (V_CODE) ); CREATE TABLE PRODUCT ( P_CODE varchar(10) NOT NULL, P_DESCRIPT varchar(35) NOT NULL, P_INDATE datetime NOT NULL, P_QOH decimal(18,0) NOT NULL, P_MIN decimal(18,0) NOT NULL, P_PRICE decimal(8,2) NOT NULL, P_DISCOUNT decimal(4,2) NOT NULL, V_CODE decimal(18,0) DEFAULT NULL, P_MIN_ORDER decimal(18,0) DEFAULT NULL, P_REORDER decimal(18,0) DEFAULT NULL, PRIMARY KEY (P_CODE), KEY PRODUCT_V_CODE_FK (V_CODE), CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE) ON DELETE NO ACTION ON UPDATE NO ACTION ); -- -- Table structure for table CUSTOMER -- CREATE TABLE CUSTOMER ( CUS_CODE decimal(18,0) NOT NULL, CUS_LNAME varchar(15) NOT NULL, CUS_FNAME varchar(15) NOT NULL, CUS_INITIAL char(1) DEFAULT NULL, CUS_AREACODE char(3) NOT NULL DEFAULT '615', CUS_PHONE char(8) NOT NULL, CUS_BALANCE decimal(9,2) DEFAULT '0.00', PRIMARY KEY (CUS_CODE), UNIQUE KEY CUS_UI1 (CUS_LNAME,CUS_FNAME) ); CREATE TABLE INVOICE ( INV_NUMBER decimal(18,0) NOT NULL, CUS_CODE decimal(18,0) NOT NULL, INV_DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (INV_NUMBER), KEY FK__INVOICE__CUS_COD__20C1E124 (CUS_CODE), CONSTRAINT FK__INVOICE__CUS_COD__20C1E124 FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER (CUS_CODE) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE LINE ( INV_NUMBER decimal(18,0) NOT NULL, LINE_NUMBER decimal(2,0) NOT NULL, P_CODE varchar(10) NOT NULL, LINE_UNITS decimal(9,2) NOT NULL DEFAULT '0.00', LINE_PRICE decimal(9,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (INV_NUMBER,LINE_NUMBER), UNIQUE KEY LINE_UI1 (INV_NUMBER,P_CODE), KEY FK__LINE__P_CODE__29572725 (P_CODE), CONSTRAINT FK__LINE__INV_NUMBER__286302EC FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE (INV_NUMBER) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT FK__LINE__P_CODE__29572725 FOREIGN KEY (P_CODE) REFERENCES PRODUCT (P_CODE) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE EMPLOYEE ( EMP_NUM decimal(18,0) NOT NULL, EMP_TITLE char(10) DEFAULT NULL, EMP_LNAME varchar(15) NOT NULL, EMP_FNAME varchar(15) NOT NULL, EMP_INITIAL char(1) DEFAULT NULL, EMP_DOB datetime DEFAULT NULL, EMP_HIRE_DATE datetime DEFAULT NULL, EMP_YEARS decimal(18,0) DEFAULT NULL, EMP_AREACODE char(3) DEFAULT NULL, EMP_PHONE char(8) DEFAULT NULL, PRIMARY KEY (EMP_NUM) ); CREATE TABLE EMP ( EMP_NUM decimal(18,0) NOT NULL, EMP_TITLE char(10) DEFAULT NULL, EMP_LNAME varchar(15) NOT NULL, EMP_FNAME varchar(15) NOT NULL, EMP_INITIAL char(1) DEFAULT NULL, EMP_DOB datetime DEFAULT NULL, EMP_HIRE_DATE datetime DEFAULT NULL, EMP_AREACODE char(3) DEFAULT NULL, EMP_PHONE char(8) DEFAULT NULL, EMP_MGR decimal(18,0) DEFAULT NULL, PRIMARY KEY (EMP_NUM) ); CREATE TABLE CUSTOMER_2 ( CUS_CODE decimal(18,0) NOT NULL, CUS_LNAME varchar(15) NOT NULL, CUS_FNAME varchar(15) NOT NULL, CUS_INITIAL char(1) DEFAULT NULL, CUS_AREACODE char(3) DEFAULT NULL, CUS_PHONE char(8) DEFAULT NULL, PRIMARY KEY (CUS_CODE) ); -- -- Dumping data for table CUSTOMER -- INSERT INTO V VALUES (21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y'),(21226,'SuperLoo, Inc.','Flushing','904','215-8995','FL','N'),(21231,'D\\&E Supply','Singh','615','228-3245','TN','Y'),(21344,'Gomez Bros.','Ortega','615','889-2546','KY','N'),(22567,'Dome Supply','Smith','901','678-1419','GA','N'),(23119,'Randsets Ltd.','Anderson','901','678-3998','GA','Y'),(24004,'Brackman Bros.','Browning','615','228-1410','TN','N'),(24288,'ORDVA, Inc.','Hakford','615','898-1234','TN','Y'),(25443,'B\\&K, Inc.','Smith','904','227-0093','FL','N'),(25501,'Damal Supplies','Smythe','615','890-3529','TN','N'),(25595,'Rubicon Systems','Orton','904','456-0092','FL','Y'); INSERT INTO P VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','2011-11-03 00:00:00',8,5,109.99,0.00,25595,25,0),('13-Q2/P2','7.25-in. pwr. saw blade','2011-12-13 00:00:00',32,15,14.99,0.05,21344,50,0),('14-Q1/L3','9.00-in. pwr. saw blade','2011-11-13 00:00:00',18,12,17.49,0.00,21344,50,0),('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2012-01-15 00:00:00',15,8,39.95,0.00,23119,35,0),('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2012-01-15 00:00:00',23,5,43.99,0.00,23119,25,0),('2232/QTY','B\\&D jigsaw, 12-in. blade','2011-12-30 00:00:00',8,5,109.92,0.05,24288,15,0),('2232/QWE','B\\&D jigsaw, 8-in. blade','2011-12-24 00:00:00',6,5,99.87,0.05,24288,15,0),('2238/QPD','B\\&D cordless drill, 1/2-in.','2012-01-20 00:00:00',12,5,38.95,0.05,25595,12,0),('23109-HB','Claw hammer','2012-01-20 00:00:00',23,10,9.95,0.10,21225,25,0),('23114-AA','Sledge hammer, 12 lb.','2012-01-02 00:00:00',8,5,14.40,0.05,NULL,12,0),('54778-2T','Rat-tail file, 1/8-in. fine','2011-12-15 00:00:00',43,20,4.99,0.00,21344,25,0),('89-WRE-Q','Hicut chain saw, 16 in.','2012-02-07 00:00:00',11,5,256.99,0.05,24288,10,0),('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2012-02-20 00:00:00',188,75,5.87,0.00,NULL,50,0),('SM-18277','1.25-in. metal screw, 25','2012-03-01 00:00:00',172,75,6.99,0.00,21225,50,0),('SW-23116','2.5-in. wd. screw, 50','2012-02-24 00:00:00',237,100,8.45,0.00,21231,100,0),('WR3/TT3','Steel matting, 4\'x8\'x1/6\", .5\" mesh','2012-01-17 00:00:00',18,5,119.95,0.10,25595,10,0); /* VENDOR rows */ INSERT INTO VENDOR (SELECT * FROM V); /* PRODUCT rows */ INSERT INTO PRODUCT (SELECT * FROM P); INSERT INTO CUSTOMER VALUES (10010,'Ramas','Alfred','A','615','844-2573',0.00),(10011,'Dunne','Leona','K','713','894-1238',0.00),(10012,'Smith','Kathy','W','615','894-2285',345.86),(10013,'Olowski','Paul','F','615','894-2180',536.75),(10014,'Orlando','Myron',NULL,'615','222-1672',0.00), (10015,'O\'Brian','Amy','B','713','442-3381',0.00),(10016,'Brown','James','G','615','297-1228',221.19),(10017,'Williams','George',NULL,'615','290-2556',768.93),(10018,'Farriss','Anne','G','713','382-7185',216.55),(10019,'Smith','Olette','K','615','297-3809',0.00); INSERT INTO INVOICE VALUES (1001,10014,'2012-01-16 06:00:00'), (1002,10011,'2012-01-16 06:00:00'), (1003,10012,'2012-01-16 06:00:00'), (1004,10011,'2012-01-17 06:00:00'), (1005,10018,'2012-01-17 06:00:00'), (1006,10014,'2012-01-17 06:00:00'), (1007,10015,'2012-01-17 06:00:00'), (1008,10011,'2012-01-17 06:00:00'); INSERT INTO LINE VALUES (1001,1,'13-Q2/P2',1.00,14.99), (1001,2,'23109-HB',1.00,9.95), (1002,1,'54778-2T',2.00,4.99), (1003,1,'2238/QPD',1.00,38.95), (1003,2,'1546-QQ2',1.00,39.95), (1003,3,'13-Q2/P2',5.00,14.99), (1004,1,'54778-2T',3.00,4.99), (1004,2,'23109-HB',2.00,9.95), (1005,1,'PVC23DRT',12.00,5.87), (1006,1,'SM-18277',3.00,6.99), (1006,2,'2232/QTY',1.00,109.92), (1006,3,'23109-HB',1.00,9.95), (1006,4,'89-WRE-Q',1.00,256.99), (1007,1,'13-Q2/P2',2.00,14.99), (1007,2,'54778-2T',1.00,4.99), (1008,1,'PVC23DRT',5.00,5.87), (1008,2,'WR3/TT3',3.00,119.95), (1008,3,'23109-HB',1.00,9.95); INSERT INTO CUSTOMER_2 VALUES (345,'Terrell','Justine','H','615','322-9870'),(347,'Olowski','Paul','F','615','894-2180'),(351,'Hernandez','Carlos','J','723','123-7654'),(352,'McDowell','George',NULL,'723','123-7768'),(365,'Tirpin','Khaleed','G','723','123-9876'),(368,'Lewis','Marie','J','734','332-1789'),(369,'Dunne','Leona','K','713','894-1238'); INSERT INTO EMPLOYEE VALUES (100,'Mr.','Kolmycz','George','D','1942-06-15 00:00:00','1985-03-15 00:00:00',18,'615','324-5456'),(101,'Ms.','Lewis','Rhonda','G','1965-03-19 00:00:00','1986-04-25 00:00:00',16,'615','324-4472'),(102,'Mr.','Vandam','Rhett',NULL,'1958-11-14 00:00:00','1990-12-20 00:00:00',12,'901','675-8993'),(103,'Ms.','Jones','Anne','M','1974-10-16 00:00:00','1994-08-28 00:00:00',8,'615','898-3456'),(104,'Mr.','Lange','John','P','1971-11-08 00:00:00','1994-10-20 00:00:00',8,'901','504-4430'),(105,'Mr.','Williams','Robert','D','1975-03-14 00:00:00','1998-11-08 00:00:00',4,'615','890-3220'),(106,'Mrs.','Smith','Jeanine','K','1968-02-12 00:00:00','1989-01-05 00:00:00',14,'615','324-7883'),(107,'Mr.','Diante','Jorge','D','1974-08-21 00:00:00','1994-07-02 00:00:00',8,'615','890-4567'),(108,'Mr.','Wiesenbach','Paul','R','1966-02-14 00:00:00','1992-11-18 00:00:00',10,'615','897-4358'),(109,'Mr.','Smith','George','K','1961-06-18 00:00:00','1989-04-14 00:00:00',13,'901','504-3339'),(110,'Mrs.','Genkazi','Leighla','W','1970-05-19 00:00:00','1990-12-01 00:00:00',12,'901','569-0093'),(111,'Mr.','Washington','Rupert','E','1966-01-03 00:00:00','1993-06-21 00:00:00',9,'615','890-4925'),(112,'Mr.','Johnson','Edward','E','1961-05-14 00:00:00','1983-12-01 00:00:00',19,'615','898-4387'),(113,'Ms.','Smythe','Melanie','P','1970-09-15 00:00:00','1999-05-11 00:00:00',3,'615','324-9006'),(114,'Ms.','Brandon','Marie','G','1956-11-02 00:00:00','1979-11-15 00:00:00',23,'901','882-0845'),(115,'Mrs.','Saranda','Hermine','R','1972-07-25 00:00:00','1993-04-23 00:00:00',9,'615','324-5505'),(116,'Mr.','Smith','George','A','1965-11-08 00:00:00','1988-12-10 00:00:00',14,'615','890-2984'); INSERT INTO EMP VALUES (100,'Mr.','Kolmycz','George','D','1942-06-15 00:00:00','1985-03-15 00:00:00','615','324-5456',NULL), (101,'Ms.','Lewis','Rhonda','G','1965-03-19 00:00:00','1986-04-25 00:00:00','615','324-4472',100), (102,'Mr.','Vandam','Rhett',NULL,'1958-11-14 00:00:00','1990-12-20 00:00:00','901','675-8993',100), (103,'Ms.','Jones','Anne','M','1974-10-16 00:00:00','1994-08-28 00:00:00','615','898-3456',100), (104,'Mr.','Lange','John','P','1971-11-08 00:00:00','1994-10-20 00:00:00','901','504-4430',105), (105,'Mr.','Williams','Robert','D','1975-03-14 00:00:00','1998-11-08 00:00:00','615','890-3220',NULL), (106,'Mrs.','Smith','Jeanine','K','1968-02-12 00:00:00','1989-01-05 00:00:00','615','324-7883',105), (107,'Mr.','Diante','Jorge','D','1974-08-21 00:00:00','1994-07-02 00:00:00','615','890-4567',105), (108,'Mr.','Wiesenbach','Paul','R','1966-02-14 00:00:00','1992-11-18 00:00:00','615','897-4358',NULL), (109,'Mr.','Smith','George','K','1961-06-18 00:00:00','1989-04-14 00:00:00','901','504-3339',108), (110,'Mrs.','Genkazi','Leighla','W','1970-05-19 00:00:00','1990-12-01 00:00:00','901','569-0093',108), (111,'Mr.','Washington','Rupert','E','1966-01-03 00:00:00','1993-06-21 00:00:00','615','890-4925',105), (112,'Mr.','Johnson','Edward','E','1961-05-14 00:00:00','1983-12-01 00:00:00','615','898-4387',100), (113,'Ms.','Smythe','Melanie','P','1970-09-15 00:00:00','1999-05-11 00:00:00','615','324-9006',105), (114,'Ms.','Brandon','Marie','G','1956-11-02 00:00:00','1979-11-15 00:00:00','901','882-0845',108), (115,'Mrs.','Saranda','Hermine','R','1972-07-25 00:00:00','1993-04-23 00:00:00','615','324-5505',105), (116,'Mr.','Smith','George','A','1965-11-08 00:00:00','1988-12-10 00:00:00','615','890-2984',108);