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); SELECT * FROM COFFEE a LEFT JOIN COFFEE_SHOP b ON a.shop_id = b.shop_id LEFT JOIN EMPLOYEE c ON c.shop_id = a.shop_id LEFT JOIN SUPPLIER d ON d.supplier_id = a.supplier_id;