H3WCjD0e

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, 'Guitars'),
32(2, 'Basses'),
33(3, 'Drums');
34
35INSERT INTO products VALUES
36(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
37(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
38(3, 1, 'sg', 'Gibson SG', '2517.00'),
39(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
40(5, 1, 'washburn', 'Washburn D10S', '299.00'),
41(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
42(7, 2, 'precision', 'Fender Precision', '799.99'),
43(8, 2, 'hofner', 'Hofner Icon', '499.99'),
44(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
45(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.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';