drop table beers;
create table Beers(
  name varchar(20) primary key,
  manf varchar(15) );

insert into beers values('80/-','Caledonian');
insert into beers values('Bigfoot Barley Wine','Sierra Nevada');
insert into beers values('Burragorang Bock','George IV Inn');
insert into beers values('Crown Lager','Carlton');
insert into beers values('Fosters Lager','Carlton');
insert into beers values('Invalid Stout','Carlton');
insert into beers values('Melbourne Bitter','Carlton');
insert into beers values('New','Toohey''s');
insert into beers values('Old','Toohey''s');
insert into beers values('Old Admiral','Lord Nelson');
insert into beers values('Pale Ale','Sierra Nevada');
insert into beers values('Premium Lager','Cascade');
insert into beers values('Red','Toohey''s');
insert into beers values('Sheaf Stout','Toohey''s');
insert into beers values('Sparkling Ale','Cooper''s');
insert into beers values('Stout','Cooper''s');
insert into beers values('Three Sheets','Lord Nelson');
insert into beers values('Victoria Bitter','Carlton');

insert into beers values('Bud','Anheuser-Busch');
insert into beers values('Bud Lite','Anheuser-Busch');
insert into beers values('Michelob','Anheuser-Busch');


drop table bars;
create table Bars(
  name varchar(20) primary key,
  addr varchar(10),
  license varchar(6) );

insert into bars values('Australia Hotel','The Rocks','123456');
insert into bars values('Coogee Bay Hotel','Coogee','966500');
insert into bars values('Lord Nelson','The Rocks','123888');
insert into bars values('Marble Bar','Sydney','122123');
insert into bars values('Regent Hotel','Kingsford','987654');
insert into bars values('Royal Hotel','Randwick','938500');

drop table drinkers;
create table Drinkers(
  name varchar(6) primary key,
  addr VARCHAR(8),
  phone varchar(9) );

insert into Drinkers values('Adam','Randwick','9385-4444');
insert into Drinkers values('Gernot','Newtown','9415-3378');
insert into Drinkers values('John','Clovelly','9665-1234');
insert into Drinkers values('Justin','Mosman','9845-4321');

drop table likes;
create table Likes(
  drinker varchar(6),
  beer varchar(20),
  primary key(drinker,beer) );

insert into Likes values('Adam','Crown Lager');
insert into Likes values('Adam','Fosters Lager');
insert into Likes values('Adam','New');
insert into Likes values('Gernot','Premium Lager');
insert into Likes values('Gernot','Sparkling Ale');
insert into Likes values('John','80/-');
insert into Likes values('John','Bigfoot Barley Wine');
insert into Likes values('John','Pale Ale');
insert into Likes values('John','Three Sheets');
insert into Likes values('Justin','Sparkling Ale');
insert into Likes values('Justin','Victoria Bitter');

insert into Likes values('Sally','Bud');
insert into Likes values('Fred','Bud');


drop table sells;
create table Sells(
  bar varchar(16),
  beer varchar(20),
  price number(4,2),
  primary key(bar,beer));

insert into sells values('Australia Hotel','Burragorang Bock',3.50);
insert into sells values('Coogee Bay Hotel','New',2.25);
insert into sells values('Coogee Bay Hotel','Old',2.50);
insert into sells values('Coogee Bay Hotel','Sparkling Ale',2.80);
insert into sells values('Coogee Bay Hotel','Victoria Bitter',2.30);
insert into sells values('Lord Nelson','Three Sheets',3.75);
insert into sells values('Lord Nelson','Old Admiral',3.75);
insert into sells values('Marble Bar','New',2.80);
insert into sells values('Marble Bar','Old',2.80);
insert into sells values('Marble Bar','Victoria Bitter',2.80);
insert into sells values('Regent Hotel','New',2.20);
insert into sells values('Regent Hotel','Victoria Bitter',2.20);
insert into sells values('Royal Hotel','New',2.30);
insert into sells values('Royal Hotel','Old',2.30);
insert into sells values('Royal Hotel','Victoria Bitter',2.30);

insert into sells values('Joe''s','Bud',2.5);
insert into sells values('Sue''s','Miller',3);


drop table frequents;
create table Frequents(
  drinker varchar(6),
  bar varchar(16),
  primary key(drinker,bar) );

insert into frequents values('Adam','Coogee Bay Hotel');
insert into frequents values('Gernot','Lord Nelson');
insert into frequents values('John','Coogee Bay Hotel');
insert into frequents values('John','Lord Nelson');
insert into frequents values('John','Australia Hotel');
insert into frequents values('Justin','Regent Hotel');
insert into frequents values('Justin','Marble Bar');

commit;