SQLize | PHPize | SQLtest

CREATE TABLE coffee_shop ( shop_id INT, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) ); CREATE TABLE supplier ( supplier_id INT, company_name VARCHAR(50), country VARCHAR(30), sales_contact_name VARCHAR(60), email VARCHAR(50) NOT NULL, PRIMARY KEY (supplier_id) ); CREATE TABLE employee ( employee_id INT, first_name VARCHAR(30), last_name VARCHAR(30), hire_date DATE, job_title VARCHAR(30), shop_id INT, PRIMARY KEY (employee_id), FOREIGN KEY (shop_id) REFERENCES coffee_shop (shop_id) ); CREATE TABLE coffee ( coffee_id INT, shop_id INT, supplier_id INT, coffee_name VARCHAR(30), price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id), FOREIGN KEY (shop_id) REFERENCES coffee_shop(shop_id), FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ); INSERT INTO coffee_shop (shop_id, shop_name, city, state) VALUES ('101','WoodBee Coffee', 'Oak Harbor', 'WA'), ('201', 'Woolley Brew', 'Sedro Woolley', 'WA'), ('301', 'Cowboy Coffee', 'Burlington', 'WA'); INSERT INTO supplier (supplier_id, company_name, country, sales_contact_name, email) VALUES ('1', 'Crystal Lake Coffee', 'USA', 'Jason Voorhees', 'jvoorhees@gmail.com'), ('2', 'Elm Street Cafe', 'USA', 'Freddy Krueger', 'dontsleep@hotmail.com'), ('3', 'Haddonfield Brew', 'USA', 'Michael Myers', 'halloween@gmail.com'); INSERT INTO employee (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES ('1001', 'Jeremiah', 'Coleman', '20160229', 'Mess Maker', '101'), ('2001', 'Jennifer', 'Mott', '19850614', 'Mess Coordinator', '101'), ('3001', 'Jared', 'Lucian', '19790712', 'Mess Designer', '101'); INSERT INTO coffee (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES ('100', '101', '1', 'Killer Coffee', 10.99), ('200', '201', '2', 'Wide Awake Coffee', 10.99), ('300', '301', '3', 'Pumkin Spice Coffee', 10.99); CREATE VIEW employee_info_view AS SELECT employee_id, CONCAT(first_name,' ',last_name) AS employee_full_name, hire_date, job_title, shop_id FROM employee; SELECT * FROM employee_info_view; CREATE INDEX index_coffee_name ON coffee (coffee_name); SHOW INDEX FROM coffee;