create table coffees(
COF_NAME varchar2(32) primary key,
SUP_ID int not null,
PRICE real not null,
SALES int  default 0 not null,
TOTAL int default 0 not null);

create table SUPPLIERS(
SUP_ID int not null primary key,
SUP_NAME varchar2(40) not null,
STREET varchar2(40) not null,
CITY varchar2(20) not null,
STATE char(2) not null,
ZIP char(5) not null);

Insert into suppliers values(49,'Superior Coffee','1 Party Place','Mendocino','CA','95460');
Insert into suppliers values(101,'Acme, Inc.','99 Market Street',' Groundsville','CA','95199');
Insert into suppliers values(150,'The High Ground','100 Coffee Lane','Meadows','CA','93966');
 
Insert into coffees(cof_name,sup_id,price) values('Colombian',101,7.99);                                                                                                 
Insert into coffees(cof_name,sup_id,price) values('Colombian_Decaf',101,8.99);                                                                                           
Insert into coffees(cof_name,sup_id,price) values('Espresso',150,9.99);                                                                                                  
Insert into coffees(cof_name,sup_id,price) values('French_Roast',49,8.99);                                                                                               
Insert into coffees(cof_name,sup_id,price) values('French_Roast_Decaf',49,9.99);  

commit;



alter table coffees
 add constraint FK_SUPPLIERS foreign key (SUP_ID) references SUPPLIERS;