SQLize Online / PHPize Online  /  SQLtest Online

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) ); INSERT INTO SUPPLIER VALUES ( 762, 'South India Coffee', 'India', 'Andre Roussimoff', 'giant@sic.net' ), ( 223, 'Pocillo Inc.', 'Guatemala', 'George Gadaski', 'ggadask@pocillo.com' ), ( 556, 'Black Ivory Coffee LLC', 'Thailand', 'Terry Gibbs', 'terry.gibbs@blackivorycoffee.com' ); CREATE TABLE COFFEE_SHOP ( shop_id INT, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) ); INSERT INTO COFFEE_SHOP VALUES ( 2745, 'New World Coffee', 'Augusta', 'GA' ), (5839, 'Drippin', 'Memphis', 'TN'), ( 6017, 'Ultimate Coffee', 'Crawfordsville', 'IN' ); 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) ); INSERT INTO EMPLOYEE VALUES ( 001, 'Terry', 'Bollea', '1979-11-17', 'Hulk Hogan', 2745 ), ( 002, 'Richard', 'Fliehr', '1972-12-10', 'Ric Flair', 5839 ), ( 003, 'James', 'Hellwig', '1987-10-25', 'Ultimate Warrior', 6017 ); 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 VALUES ( 3829, 2745, 762, 'Full Metal Arabica', 7.99 ), ( 4157, 5839, 223, 'Sparkling Liberica', 19.99 ), ( 9837, 6017, 556, 'Robusta Blast', 5.99 ); CREATE INDEX COFFEE_INDEX ON COFFEE(coffee_name); CREATE VIEW EMPLOYEE_VIEW AS SELECT *, CONCAT_WS(' ', first_name, last_name) as employee_full_name FROM EMPLOYEE;