· 5 years ago · Feb 13, 2020, 03:06 AM
1START TRANSACTION;
2
3CREATE DATABASE IF NOT EXISTS `salesdb`;
4USE `salesdb`;
5
6DROP TABLE IF EXISTS `line`;
7DROP TABLE IF EXISTS `product`;
8DROP TABLE IF EXISTS `invoice`;
9DROP TABLE IF EXISTS `vendor`;
10DROP TABLE IF EXISTS `customer`;
11
12CREATE TABLE `vendor` (
13 `V_CODE` int(11) NOT NULL,
14 `V_NAME` varchar(35) NOT NULL,
15 `V_CONTACT` varchar(15) NOT NULL,
16 `V_AREACODE` char(3) NOT NULL,
17 `V_PHONE` char(8) NOT NULL,
18 `V_STATE` char(2) NOT NULL,
19 `V_ORDER` char(1) NOT NULL,
20 PRIMARY KEY (`V_CODE`)
21);
22
23CREATE TABLE `customer` (
24 `CUS_CODE` int(11) NOT NULL,
25 `CUS_LNAME` varchar(15) NOT NULL,
26 `CUS_FNAME` varchar(15) NOT NULL,
27 `CUS_INITIAL` char(1) DEFAULT NULL,
28 `CUS_AREACODE` char(3) NOT NULL DEFAULT '615',
29 `CUS_PHONE` char(8) NOT NULL,
30 `CUS_BALANCE` decimal(9,2) DEFAULT '0.00',
31 `CUS_PPBAL` decimal(9,2) DEFAULT NULL,
32 PRIMARY KEY (`CUS_CODE`)
33);
34
35CREATE TABLE `product` (
36 `P_CODE` varchar(10) NOT NULL,
37 `P_DESCRIPT` varchar(35) NOT NULL,
38 `P_INDATE` datetime NOT NULL,
39 `P_QOH` int(11) NOT NULL,
40 `P_MIN` int(11) NOT NULL,
41 `P_PRICE` decimal(8,2) NOT NULL,
42 `P_DISCOUNT` decimal(4,2) NOT NULL,
43 `V_CODE` int(11) DEFAULT NULL,
44 `P_REORDER` int(11) DEFAULT '0',
45 PRIMARY KEY (`P_CODE`),
46 CONSTRAINT `PRODUCT_V_CODE_FK` FOREIGN KEY (`V_CODE`) REFERENCES `vendor` (`V_CODE`)
47);
48
49CREATE TABLE `invoice` (
50 `INV_NUMBER` int(11) NOT NULL,
51 `CUS_CODE` int(11) NOT NULL,
52 `INV_DATE` datetime NOT NULL,
53 PRIMARY KEY (`INV_NUMBER`),
54 CONSTRAINT `INVOICE_CUS_CODE_FK` FOREIGN KEY (`CUS_CODE`) REFERENCES `customer` (`CUS_CODE`)
55);
56
57CREATE TABLE `line` (
58 `INV_NUMBER` int(11) NOT NULL,
59 `LINE_NUMBER` decimal(2,0) NOT NULL,
60 `P_CODE` varchar(10) NOT NULL,
61 `LINE_UNITS` decimal(9,2) NOT NULL DEFAULT '0.00',
62 `LINE_PRICE` decimal(9,2) NOT NULL DEFAULT '0.00',
63 PRIMARY KEY (`INV_NUMBER`,`LINE_NUMBER`),
64 UNIQUE KEY `LINE_UI1` (`INV_NUMBER`,`P_CODE`),
65 CONSTRAINT `line_ibfk_1` FOREIGN KEY (`INV_NUMBER`) REFERENCES `invoice` (`INV_NUMBER`) ON DELETE CASCADE,
66 CONSTRAINT `line_ibfk_2` FOREIGN KEY (`P_CODE`) REFERENCES `product` (`P_CODE`)
67);
68
69
70INSERT INTO `vendor` 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'),(26000,'PowerToolsInc.','DalhousieFCS','902','999-9999','NS','Y');
71INSERT INTO `customer` VALUES (10010,'Ramas','Alfred','A','615','844-2573',0.00,1000.00),(10011,'Dunne','Leona','K','713','894-1238',44.97,1000.00),(10012,'Smith','Kathy','W','615','894-2285',345.86,1000.00),(10013,'Olowski','Paul','F','615','894-2180',536.75,1000.00),(10014,'Orlando','Myron',NULL,'615','222-1672',0.00,1000.00),(10015,'O\'Brian','Amy','B','713','442-3381',0.00,1000.00),(10016,'Brown','James','G','615','297-1228',221.19,1000.00),(10017,'Williams','George',NULL,'615','290-2556',768.93,1000.00),(10018,'Farriss','Anne','G','713','382-7185',216.55,1000.00),(10019,'Smith','Olette','K','615','297-3809',0.00,1000.00),(10020,'Aziz','Khurram',NULL,'902','111-1111',-999.80,0.00);
72INSERT INTO `product` VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','2015-11-03 00:00:00',8,5,109.99,0.00,25595,0),('13-Q2/P2','7.25-in. pwr. saw blade','2015-12-13 00:00:00',32,15,14.99,0.05,21344,0),('14-Q1/L3','9.00-in. pwr. saw blade','2015-11-13 00:00:00',18,12,17.49,0.00,21344,0),('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01-15 00:00:00',15,8,39.95,0.00,23119,0),('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01-15 00:00:00',23,5,43.99,0.00,23119,0),('2232/QTY','B&D jigsaw, 12-in. blade','2015-12-30 00:00:00',8,5,109.92,0.05,24288,0),('2232/QWE','B&D jigsaw, 8-in. blade','2015-12-24 00:00:00',6,5,99.87,0.05,24288,0),('2238/QPD','B&D cordless drill, 1/2-in.','2016-01-20 00:00:00',12,5,38.95,0.05,25595,0),('23109-HB','Claw hammer','2016-01-20 00:00:00',23,10,9.95,0.10,21225,0),('23114-AA','Sledge hammer, 12 lb.','2016-01-02 00:00:00',8,5,14.40,0.05,NULL,0),('54778-2T','Rat-tail file, 1/8-in. fine','2015-12-15 00:00:00',43,20,4.99,0.00,21344,0),('89-WRE-Q','Hicut chain saw, 16 in.','2016-02-07 00:00:00',11,5,256.99,0.05,24288,0),('PD-101-Q','1000W Power Drill','2017-02-02 00:00:00',80,50,99.99,0.00,26000,0),('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02-20 00:00:00',188,75,5.87,0.00,NULL,0),('SM-18277','1.25-in. metal screw, 25','2016-03-01 00:00:00',172,75,6.99,0.00,21225,0),('SW-23116','2.5-in. wd. screw, 50','2016-02-24 00:00:00',237,100,8.45,0.00,21231,0),('WR3/TT3','Steel matting, 4\'x8\'x1/6\", .5\" mesh','2016-01-17 00:00:00',18,5,119.95,0.10,25595,0);
73INSERT INTO `invoice` VALUES (1001,10014,'2016-01-16 00:00:00'),(1002,10011,'2016-01-16 00:00:00'),(1003,10012,'2016-01-16 00:00:00'),(1004,10011,'2016-01-17 00:00:00'),(1005,10018,'2016-01-17 00:00:00'),(1006,10014,'2016-01-17 00:00:00'),(1007,10015,'2016-01-17 00:00:00'),(1008,10011,'2016-01-17 00:00:00'),(1010,10020,'2017-02-02 00:00:00');
74INSERT 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),(1002,2,'13-Q2/P2',3.00,14.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),(1010,1,'PD-101-Q',20.00,99.99);
75
76COMMIT;