SQLize | PHPize | SQLtest

create table COFFEE_SHOP ( shop_id INTEGER, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY(shop_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) ); 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), FOREIGN KEY(shop_id) REFERENCES COFFEE_SHOP(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), 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 ('27','Springfield Uptown','Springfield','MO'), ('21','Cowboy Cabana','Evanston','WY'), ('15','Casablancas','Augusta','MA'), ('20','The Fightin Aardvark','San Antonio','TX'), ('8','Smoking Beans','Burlington','CT'); INSERT INTO SUPPLIER(supplier_id, company_name, country, sales_contact_name, email) VALUES ('645','AGT Wholesalers','Argentina','Barry White','bigbarry@agt.com'), ('118','YYY National','Mexico','Cyril Arturios','cart@yyy.com'), ('262','Valtis','Venezuela','Yun-Hua Kuo','ykuo@valtis.com'), ('74',' Pico Columbia','Columbia','Idris Hassan','idrishassan12@picoc.com'), ('65','Central Distribution','Belgium','Magnus Lawless','maglaw@cdist.com'); INSERT INTO COFFEE(coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES ('22','27','645','Mellow Brown','13.05'), ('81','21','118','Nitro Express','15.34'), ('19','15','262','Bombay Bomb','11.99'), ('66','20','74','Mustang Madness','19.47'), ('54','8','65','Momma Claras','14.56'); INSERT INTO EMPLOYEE(employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES ('1','Allen','Stevenson','1999-01-11','Sales','27'), ('2','Wei','Hsueh-Kang','2013-08-17','Marketing Rep','21'), ('3','Larry','Roberts','2018-12-21','IT Technician','15'), ('4','Ihsan','Yousufzai','2020-06-11','Branch Manager','20'), ('5','Dean','Martin','1999-04-15','Regional Manager','8'); CREATE VIEW Employee_View AS SELECT employee_id, concat(first_name,' ',last_name) AS employee_full_name, hire_date, job_title, shop_id FROM EMPLOYEE; CREATE INDEX Coffee_Index ON COFFEE (coffee_name); SELECT COFFEE_SHOP.shop_name, COFFEE_SHOP.shop_id, COFFEE.coffee_id, COFFEE.supplier_id, SUPPLIER.supplier_id, SUPPLIER.company_name FROM COFFEE_SHOP LEFT JOIN SUPPLIER USING (supplier_id);