SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE COFFEE_SHOP ( shop_id int PRIMARY KEY, shop_name varchar(50), city varchar(50), state varchar(2) ); INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state) VALUES (1234, 'Beans For U', 'Bean Town', 'TX'), (1235, 'Mo Coffee Plz', 'Athens', 'TX'), (1236, 'Hot N Cold', 'Killeen', 'TX'); CREATE TABLE EMPLOYEE ( employee_id int PRIMARY KEY, first_name varchar(30), last_name varchar(30), hire_date date, job_title varchar(30), shop_id int, foreign key (shop_id) references COFFEE_SHOP (shop_id) ); INSERT INTO EMPLOYEE ( employee_id, first_name, last_name, hire_date, job_title, shop_id ) VALUES (1001, 'Bruce', 'Wayne', '1999-09-28 00:00:00', 'manager', 1234), (1002, 'Peter', 'Parker', '2001-10-01 00:00:00', 'clerk', 1235), (1003, 'Bruce', 'Banner', '2010-11-15 00:00:00', 'cook', 1236); CREATE TABLE SUPPLIER ( supplier_id int PRIMARY KEY, company_name varchar(50), country varchar(30), sales_contact_name varchar(60), email varchar(50) ); INSERT INTO SUPPLIER ( supplier_id, company_name, country, sales_contact_name, email ) VALUES (1122, 'Big Cup Inc', 'USA', 'Dick Grayson', 'd-grayson@bci.com'), (1133, 'Hot Pot Corp', 'USA', 'Emma Stone', 'e-stone@bhpc.com'), (1144, 'Green Beans', 'Mexico', 'Betty Ross', 'b-ross@gbeans.com'); CREATE TABLE COFFEE ( coffee_id int PRIMARY KEY, shop_id int, supplier_id int, coffee_name varchar(30), price_per_pound numeric(5, 2), foreign key (shop_id) references COFFEE_SHOP (shop_id), foreign key (supplier_id) references SUPPLIER (supplier_id) ); INSERT INTO COFFEE ( coffee_id, shop_id, supplier_id, coffee_name, price_per_pound ) VALUES (101, 1234, 1122, 'darkbean', 1.20), (102, 1235, 1133, 'mediumbean', 2.49), (103, 1236, 1144, 'lightbean', 1.89); CREATE index CoffeeNameIndex on COFFEE (coffee_name); CREATE VIEW EmployeeView AS Select (first_name + ' ' + last_name) as 'employee_full_name', employee_id, hire_date, job_title, shop_id FROM EMPLOYEE;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear