HzYQECBL

1-- create and select the database
2DROP DATABASE IF EXISTS my_guitar_shop1;
3CREATE DATABASE my_guitar_shop1;
4USE my_guitar_shop1;  -- MySQL command
5
6-- create the tables
7CREATE TABLE categories (
8  categoryID       INT(11)        NOT NULL   AUTO_INCREMENT,
9  categoryName     VARCHAR(255)   NOT NULL,
10  PRIMARY KEY (categoryID)
11);
12
13CREATE TABLE products (
14  productID        INT(11)        NOT NULL   AUTO_INCREMENT,
15  categoryID       INT(11)        NOT NULL,
16  productCode      VARCHAR(10)    NOT NULL   UNIQUE,
17  productName      VARCHAR(255)   NOT NULL,
18  listPrice        DECIMAL(10,2)  NOT NULL,
19  PRIMARY KEY (productID)
20);
21
22CREATE TABLE orders (
23  orderID        INT(11)        NOT NULL   AUTO_INCREMENT,
24  customerID     INT            NOT NULL,
25  orderDate      DATETIME       NOT NULL,
26  PRIMARY KEY (orderID)
27);
28
29-- insert data into the database
30INSERT INTO categories VALUES
31(1, 'Sodas'),
32(2, 'Teas'),
33(3, 'Bobas');
34
35INSERT INTO products VALUES
36(1, 1, 'reg_coke', 'Coca Cola', '1.00'),
37(2, 1, 'reg_pepsi', 'Pepsi', '1.00'),
38(3, 1, 'reg_rootbeer', 'A&W Root Beer', '1.00'),
39(4, 1, 'reg_sprite', 'Sprite', '1.99'),
40(5, 1, 'coke_zero', 'Coke Zero', '1.00'),
41(6, 1, 'reg_7up', '7UP', '1.00'),
42(7, 2, 'green_tea', 'Green tea', '2.99'),
43(8, 2, 'black_tea', 'Black tea', '2.99'),
44(9, 3, 'milk_boba', 'Milk tea boba', '3.99'),
45(10, 3, 'wmelon_boba', 'Winter Melon boba', '3.99');
46
47-- create the users and grant priveleges to those users
48GRANT SELECT, INSERT, DELETE, UPDATE
49ON my_guitar_shop1.*
50TO mgs_user@localhost
51IDENTIFIED BY 'pa55word';
52
53GRANT SELECT
54ON products
55TO mgs_tester@localhost
56IDENTIFIED BY 'pa55word';