-- Database Designed by Andrew Thorsen -- CS 325 - Fall 2017
-- Used by Drew Hengehold 2025
-- DLM: 11/26/2017

delete from sale_line_item;
delete from food;
delete from tea;
delete from sale_item;
delete from sale;
delete from reservation;
delete from member;
delete from guest;
delete from customer;
delete from empl_emails;
delete from employee;

prompt employee
insert into employee
values
('000000', 'miller', 'anna', 0.75, 100, 8675308);

insert into employee
values
('000001', 'higgins', 'mary', 0.75, 1000, 5555555555);

insert into employee
values
('000002', 'burger', 'ham', 0.80, 1100, 5554829483);

insert into employee
values
('000003', 'fry', 'french', 0.85, 1200, 5552017538);

insert into employee
values
('000004', 'elephante', 'tiny', 0.75, 1300, 555973142);
insert into employee
values
('000005', 'muell', 'cheyenne', 0.80, 1400, 5552890463);

insert into employee
values
('000006', 'fer', 'luci', 0.66, 1500, 5559475294);

insert into employee
values
('000007', 'thomas', 'will', 0.75, 1600, 5558429430);

insert into employee
values
('000008', 'thorleaf', 'bjorg', 0.75, 1700, 5555861482);

insert into employee
values
('000009', 'thompson', 'vivian', 0.70, 1800, 5554802893);


prompt empl_emails
insert into empl_emails
values
('000000', 'amiller@thismail.com');

insert into empl_emails
values
('000001', 'higgins@thismail.com');

insert into empl_emails
values
('000002', 'wherearemypickles@thismail.com');

insert into empl_emails
values
('000003', 'dontfrymebro@thismail.com');

insert into empl_emails
values
('000004', 'peanuts@thismail.com');

insert into empl_emails
values
('000005', 'muell@trash.com');

insert into empl_emails
values
('000006', 'dadyslilfallenangel@thismail.com');

insert into empl_emails
values
('000007', 'wiltom@thismail.com');

insert into empl_emails
values
('000008', 'bjorg@this,ail.com');

insert into empl_emails
values
('000009', 'vivson@thismail.com');

insert into empl_emails
values
('000002', 'burger@thismail.com');


prompt customer

drop sequence cust_num;
create sequence cust_num
increment by 1
start with 200000;

insert into customer
values
(cust_num.nextval, 'g', 'first');

insert into customer
values
(cust_num.nextval, 'g', 'coulsen');

insert into customer
values
(cust_num.nextval, 'g', 'lastname');

insert into customer
values
(cust_num.nextval, 'g', 'tex');

insert into customer
values
(cust_num.nextval, 'g', 'russ');

insert into customer
values
(cust_num.nextval, 'm', 'shield');

insert into customer
values
(cust_num.nextval, 'm', 'maive');

insert into customer
values
(cust_num.nextval, 'm', 'field');

insert into customer
values
(cust_num.nextval, 'm', 'kelly');

insert into customer
values
(cust_num.nextval, 'm', 'wu');

insert into customer
values
(cust_num.nextval, 'g', 'wally');

insert into customer
values
(cust_num.nextval, 'm', 'thomson');

insert into customer
values
(cust_num.nextval, 'g', 'holmes');

insert into customer
values
(cust_num.nextval, 'm', 'dezz');

insert into customer
values
(cust_num.nextval, 'g', 'moore');

insert into customer
values
(cust_num.nextval, 'm', 'austin');

insert into customer
values
(cust_num.nextval, 'm', 'dawn');

insert into customer
values
(cust_num.nextval, 'g', 'felprn');

insert into customer
values
(cust_num.nextval, 'm', 'mandrake');

insert into customer
values
(cust_num.nextval, 'g', 'fray');


prompt guest

insert into guest
values
('200000');

insert into guest
values
('200001');

insert into guest
values
('200002');

insert into guest
values
('200003');

insert into guest
values
('200004');

insert into guest
values
('200010');

insert into guest
values
('200012');

insert into guest
values
('200014');

insert into guest
values
('200015');

insert into guest
values
('200017');

prompt member

insert into member
values
('200005', 'linda', 'm', 'chei', 'snowball@thismail.com','345 gingertree ln. winzor CA', 0.20);

insert into member
values
('200006', 'Rob', 'm', 'peach', 'notroblow@thismail.com', '222 europe st. finland CA', 0.25);

insert into member
values
('200007', 'Zach', 'm', 'black', 'zman152@thismail.com','125 big blvd. whelp CA', 0.20);

insert into member
values
('200008', 'mary', 'm', 'herbal', 'hasalittlelamb@thismail.com','452 snowflake pl. winzor CA', 0.30);

insert into member
values
('200009', 'scar', 'm', 'himica', 'wanabeking@thismail.com','157 hyena st. badland CA', 0.15);

insert into member (cust_num, mem_fname, mem_reward, mem_fav_tea, mem_email,
			mem_address)
values
('200011', 'james', 'v', 'green', 'semajbackwords@thismail.com','3435 street ln. cambrian CA');

insert into member (cust_num, mem_fname, mem_reward, mem_fav_tea, mem_email,
			mem_address)
values
('200013', 'lindon', 'v', 'mint', 'forfitsake@thismail.com','3425 saber st. cambrian CA');

insert into member (cust_num, mem_fname, mem_reward, mem_fav_tea, mem_email,
			mem_address)
values
('200015', 'bob', 'm', 'fruity', 'whatsthis@thismail.com','2354 cleft st. campbrian CA');

insert into member (cust_num, mem_fname, mem_reward, mem_fav_tea, mem_email,
			mem_address)
values
('200017', 'tom', 'v', 'black', 'srtombomb@thismail.com','2193 pod pl. cambrian CA');

insert into member (cust_num, mem_fname, mem_reward, mem_fav_tea, mem_email,
			mem_address)
values
('200019', 'bart', 'm', 'shorts', 'donthaveacow@thismail.com',	'1234 spring fld. cambrian CA');

prompt reservation

drop sequence res_num;
create sequence res_num
increment by 1
start with 001;


insert into reservation
values
(res_num.nextval, '200002', '01-JUN-2050', 5);

insert into reservation
values
(res_num.nextval, '200004', '25-JUN-2050', 12);

insert into reservation
values
(res_num.nextval, '200005', '13-OCT-2050', 10);

insert into reservation
values
(res_num.nextval, '200015', '04-MAR-2050', 8);

insert into reservation
values
(res_num.nextval, '200012', '21-FEB-2050', 18);

insert into reservation
values
(res_num.nextval, '200018', '01-JUN-2050', 7);

insert into reservation
values
(res_num.nextval, '200017', '07-SEP-2050', 9);

insert into reservation
values
(res_num.nextval, '200016', '09-MAY-50', 7);

insert into reservation
values
(res_num.nextval, '200019', '10-JUN-50', 5);

insert into reservation
values
(res_num.nextval, '200001', '03-JUN-50', 9);

prompt sale

drop sequence sale_num;
create sequence sale_num
increment by 1
start with 001;

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 6.00, '200002', '000002');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200004', '000002');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200006', '000002');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200008', '000006');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200010', '000002');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200012', '000002');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200014', '000006');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200017', '000006');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200019', '000002');

insert into sale
values
(TO_CHAR(sale_num.NEXTVAL, 'FM000'), 2.00, '200015', '000006');

prompt sale_item

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('000', 't', 'honey lavender', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('001', 't', 'black', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('002', 't', 'earl grey', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('003', 't', 'green', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('004', 't', 'peppermint', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('005', 't', 'english breakfast', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('006', 't', 'cinnamon spice', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('007', 't', 'chamomile', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('008', 't', 'jasmine', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('009', 't', 'lemon bolm', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('010', 't', 'raspberry', 30, 2.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('011', 'f', 'bbq', 20, 3.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('012', 'f', 'salt and vinegar', 20, 3.00);

insert into sale_item (item_id, item_type,item_name, item_price)
values
('013', 'f', 'ham and cheese', 5.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('014', 'f', 'pretzels', 20, 2.00);

insert into sale_item (item_id, item_type,item_name, item_price)
values
('015', 'f', 'salad', 3.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('016', 'f', 'blueburry muffin', 10, 1.50);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('017', 'f', 'chocolate muffin', 10, 1.50);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('018', 'f', 'chocolate chip muffin', 10, 1.50);

insert into sale_item (item_id, item_type,item_name, item_price)
values
('019', 'f', 'chicken sandwhich', 5.00);

insert into sale_item(item_id, item_type, item_name, stock_quant, item_price)
values
('020', 'f', 'fries', 40, 2.50);

prompt tea

insert into tea
values
('000', 'infinatea', 'bagged', 'white');

insert into tea
values
('001', 'better than water', 'bagged', 'black');

insert into tea
values
('002', 'infinatea', 'bagged', 'black');

insert into tea
values
('003', 'witches brew', 'loose', 'green');

insert into tea
values
('004', 'infinatea', 'bagged', 'herbal');

insert into tea
values
('005', 'leaf me alone', 'bagged', 'black');

insert into tea
values
('006', 'leaf me alone', 'loose', 'herbal');

insert into tea
values
('007', 'witches brew', 'loose', 'herbal');

insert into tea
values
('008', 'teak tock', 'bagged', 'green');

insert into tea
values
('009', 'flirtea', 'bagged', 'herbal');

insert into tea
values
('010', 'flirtea', 'loose', 'fruit');


prompt food

insert into food
values
('011');

insert into food
values
('012');

insert into food
values
('013');

insert into food
values
('014');

insert into food
values
('015');

insert into food
values
('016');

insert into food
values
('017');

insert into food
values
('018');

insert into food
values
('019');

insert into food
values
('020');

prompt sale_line_item

insert into sale_line_item
values
('0000000001', '003', 2);

insert into sale_line_item
values
('0000000002', '006', 1);

insert into sale_line_item
values
('0000000003', '004', 1);

insert into sale_line_item
values
('0000000004', '008', 1);

insert into sale_line_item
values
('0000000005', '009', 1);

insert into sale_line_item
values
('0000000006', '002', 1);

insert into sale_line_item
values
('0000000007', '003', 1);

insert into sale_line_item
values
('0000000008', '005', 1);

insert into sale_line_item
values
('0000000009', '008', 1);

insert into sale_line_item
values
('0000000010', '007', 1);

insert into sale_line_item
values
('0000000001', '005', 1);


