drop table master_order cascade;
drop table detail_order cascade;
drop table inventory cascade;
create table inventory (
	ino	SERIAL PRIMARY KEY,
	descr	text,
	price	numeric,
	units	text
);

create table master_order (
	ono	SERIAL PRIMARY KEY,
	cust	text,
	addr	text,
	odate	date
);
create table detail_order (
	ono	int8 references master_order (ono),
	dno	SERIAL,
	ino	integer references inventory (ino),
	qty	integer,
	tot_price	numeric,
	PRIMARY KEY (ono, ino)
);

insert into inventory values (default, 'Paper Napkins', 1.00, 'Bundle of 100');
insert into inventory values (default, 'Mustard', 8.00, '1 gallon');
insert into inventory values (default, 'Hot Cups', 5.00, 'Package of 50');
insert into inventory values (default, 'Sauerkraut', 7.00, '1 gallon');

insert into master_order values (default, 'Sam''s Snack Shack', '1123 W. Beach Ave, FL, USA',current_date);
insert into master_order values (default, 'Burger Heaven', '4331 Main Ave, Venice, CA, USA',current_date);
insert into master_order values (default, 'Dog y Dogs', '7882 California Ave, San Pedro, CA, USA',current_date);

insert into detail_order values (1, default, 1, 2, 2.00);
insert into detail_order values (1, default, 3, 1, 8.00);
insert into detail_order values (2, default, 1, 10, 10.00);
insert into detail_order values (2, default, 2, 2, 16.00);
insert into detail_order values (2, default, 4, 2, 14.00);
insert into detail_order values (3, default, 1, 3, 3.00);
insert into detail_order values (3, default, 2, 2, 16.00);
insert into detail_order values (3, default, 4, 2, 14.00);

select m.cust, m.odate, m.ono, d.dno, i.descr, i.units, d.qty, d.tot_price from master_order m JOIN detail_order d USING (ono) JOIN inventory i USING (ino);