mXPNrYZW

1-- -----------------------------------------------------
2-- Schema full-stack-ecommerce
3-- -----------------------------------------------------
4DROP SCHEMA IF EXISTS `full-stack-ecommerce`;
5
6CREATE SCHEMA `full-stack-ecommerce`;
7USE `full-stack-ecommerce` ;
8
9-- -----------------------------------------------------
10-- Table `full-stack-ecommerce`.`product_category`
11-- -----------------------------------------------------
12CREATE TABLE IF NOT EXISTS `full-stack-ecommerce`.`product_category` (
13  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
14  `category_name` VARCHAR(255) NULL DEFAULT NULL,
15  PRIMARY KEY (`id`))
16ENGINE=InnoDB
17AUTO_INCREMENT = 1;
18
19-- -----------------------------------------------------
20-- Table `full-stack-ecommerce`.`product`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `full-stack-ecommerce`.`product` (
23  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
24  `sku` VARCHAR(255) DEFAULT NULL,
25  `name` VARCHAR(255) DEFAULT NULL,
26  `description` VARCHAR(255) DEFAULT NULL,
27  `unit_price` DECIMAL(13,2) DEFAULT NULL,
28  `image_url` VARCHAR(255) DEFAULT NULL,
29  `active` BIT DEFAULT 1,
30  `units_in_stock` INT(11) DEFAULT NULL,
31   `date_created` DATETIME(6) DEFAULT NULL,
32  `last_updated` DATETIME(6) DEFAULT NULL,
33  `category_id` BIGINT(20) NOT NULL,
34  PRIMARY KEY (`id`),
35  KEY `fk_category` (`category_id`),
36  CONSTRAINT `fk_category` FOREIGN KEY (`category_id`) REFERENCES `product_category` (`id`)
37) 
38ENGINE=InnoDB
39AUTO_INCREMENT = 1;
40
41
42-- -----------------------------------------------------
43-- Add sample data
44-- -----------------------------------------------------
45
46INSERT INTO PRODUCT_CATEGORY(CATEGORY_NAME) VALUES ('BOOKS');
47
48INSERT INTO PRODUCT (SKU, NAME, DESCRIPTION, IMAGE_URL, ACTIVE, UNITS_IN_STOCK,
49UNIT_PRICE, CATEGORY_ID,DATE_CREATED)
50VALUES ('BOOK-TECH-1000', 'JavaScript - The Fun Parts', 'Learn JavaScript',
51'assets/images/products/placeholder.png'
52,1,100,19.99,1, NOW());
53
54INSERT INTO PRODUCT (SKU, NAME, DESCRIPTION, IMAGE_URL, ACTIVE, UNITS_IN_STOCK,
55UNIT_PRICE, CATEGORY_ID, DATE_CREATED)
56VALUES ('BOOK-TECH-1001', 'Spring Framework Tutorial', 'Learn Spring',
57'assets/images/products/placeholder.png'
58,1,100,29.99,1, NOW());
59
60INSERT INTO PRODUCT (SKU, NAME, DESCRIPTION, IMAGE_URL, ACTIVE, UNITS_IN_STOCK,
61UNIT_PRICE, CATEGORY_ID, DATE_CREATED)
62VALUES ('BOOK-TECH-1002', 'Kubernetes - Deploying Containers', 'Learn Kubernetes',
63'assets/images/products/placeholder.png'
64,1,100,24.99,1, NOW());
65
66INSERT INTO PRODUCT (SKU, NAME, DESCRIPTION, IMAGE_URL, ACTIVE, UNITS_IN_STOCK,
67UNIT_PRICE, CATEGORY_ID, DATE_CREATED)
68VALUES ('BOOK-TECH-1003', 'Internet of Things (IoT) - Getting Started', 'Learn IoT',
69'assets/images/products/placeholder.png'
70,1,100,29.99,1, NOW());
71
72INSERT INTO PRODUCT (SKU, NAME, DESCRIPTION, IMAGE_URL, ACTIVE, UNITS_IN_STOCK,
73UNIT_PRICE, CATEGORY_ID, DATE_CREATED)
74VALUES ('BOOK-TECH-1004', 'The Go Programming Language: A to Z', 'Learn Go',
75'assets/images/products/placeholder.png'
76,1,100,24.99,1, NOW());