SQLize Online / PHPize Online  /  SQLtest Online

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) ); CREATE TABLE COFFEE_SHOP ( shop_id INT, shop_name VARCHAR(50), city VARCHAR(50), state1 CHAR(2), PRIMARY KEY (shop_id) ); ALTER TABLE EMPLOYEE ADD 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) ); 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) ); ALTER TABLE COFFEE ADD FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id); INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state1) VALUES ('1','Co-Fee Coffee', 'Draper', 'UT'); INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state1) VALUES ('2', 'Dough Stop', 'Layton', 'OR'); INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state1) VALUES ('3', 'Biblio-Coffee', 'Denver', 'NV'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('1', 'JK Coffee', 'USA', 'Darnel D.', 'ddarnel@gmail.com'), ('2', 'Core Cups', 'USA', 'Johnny Rogo', 'rojo@outlook.com'), ('3', 'Billiam Coffee Filters', 'USA', 'Billiam Teagan', 'billiam@bcf.com'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES ('1', 'Davie', 'Bingham', '20220428', 'Manager', '1'), ('2', 'Jairen', 'Norwell', '20220518', 'Sales Clerk', '1'), ('3', 'Bowie', 'Lewis', '20221121', 'Stocker', '1'); INSERT INTO COFFEE (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES ('1', '1', '1', 'Caramel Coffee', 5.99), ('2', '2', '2', 'French Coffee', 5.99), ('3', '3', '3', 'Mint Coffee', 5.99); CREATE VIEW employee_info AS SELECT "employee_id", CONCAT(EMPLOYEE.first_name,' ',EMPLOYEE.last_name) employee_full_name, hire_date, job_title, shop_id FROM EMPLOYEE; CREATE INDEX coffee_name ON COFFEE (coffee_name);