SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Employee ( employee_id INTEGER, first_name VARCHAR(30), last_name VARCHAR(30), hire_date DATE, job_title VARCHAR(30), shop_id INTEGER, PRIMARY KEY (employee_id) ); CREATE TABLE Coffee_Shop ( shop_id INTEGER, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) ); CREATE TABLE Coffee ( coffee_id INTEGER, shop_id INTEGER, supplier_id INTEGER, coffee_name VARCHAR(30), price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id) ); CREATE TABLE Supplier ( supplier_id INTEGER, company_name VARCHAR(50), country VARCHAR(30), sales_contact_name VARCHAR(60), email VARCHAR(50) NOT NULL, PRIMARY KEY (supplier_id) ); INSERT INTO Employee (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES (213, "Dave", "Goodell", "2008-12-02", "CLERK", "5003"), (615, "Linus", "Torvolds", "2001-06-15", "CLERK", "5004"), (912, "Phil", "Driscoll", "2006-09-30", "Manager", "6000"); INSERT INTO Coffee_Shop (shop_id, shop_name, city, state) VALUES (5003, "Jolt", "Amherst", "OH"), (5004, "Sacred Grounds", "Amherst", "OH"), (6000, "Jitterz", "Avon", "OH"); INSERT INTO Coffee (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES (36, 5003, 65, "Tar", "36.95"), (34, 5004, 66, "Asphalt Sealer", "45.99"), (55, 6000, 77, "Bleached Blonde", "67.98"); INSERT INTO Supplier (supplier_id, company_name, country, sales_contact_name, email) VALUES (65, "Beanery", "Honduras", "Juan Davis", "jdavis@bearnery.hn"), (66, "Café Bueno", "Brazil", "Victor Quesada", "quesadav@cafebueno.br"), (77, "Barista Nefario", "Venezuela", "Hector Reyna", "hector@nefarious.ve"); ALTER TABLE Employee ADD FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id); ALTER TABLE Coffee ADD FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id); ALTER TABLE Coffee ADD FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id);