Page 1 of 1

Example of SQL file that sets up a database

Posted: Sat Jul 27, 2019 10:31 am
by james
-- create and select the database
DROP DATABASE IF EXISTS my_guitar_shop1;
CREATE DATABASE my_guitar_shop1;
USE my_guitar_shop1; -- MySQL command

-- create the tables
CREATE TABLE categories (
categoryID INT(11) NOT NULL AUTO_INCREMENT,
categoryName VARCHAR(255) NOT NULL,
PRIMARY KEY (categoryID)
);

CREATE TABLE products (
productID INT(11) NOT NULL AUTO_INCREMENT,
categoryID INT(11) NOT NULL,
productCode VARCHAR(10) NOT NULL UNIQUE,
productName VARCHAR(255) NOT NULL,
listPrice DECIMAL(10,2) NOT NULL,
PRIMARY KEY (productID)
);

CREATE TABLE orders (
orderID INT(11) NOT NULL AUTO_INCREMENT,
customerID INT NOT NULL,
orderDate DATETIME NOT NULL,
PRIMARY KEY (orderID)
);

-- insert data into the database
INSERT INTO categories VALUES
(1, 'Guitars'),
(2, 'Basses'),
(3, 'Drums');

INSERT INTO products VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users and grant priveleges to those users
GRANT SELECT, INSERT, DELETE, UPDATE
ON my_guitar_shop1.*
TO mgs_user@localhost
IDENTIFIED BY 'pa55word';

GRANT SELECT
ON products
TO mgs_tester@localhost
IDENTIFIED BY 'pa55word';


:|