Sec 2 many-to-many

drop table equipmentRental;
drop table rental;
drop table equipment;
drop table customer ;
drop sequence RentityID ;
create sequence RentityID start with 1000 increment by 1;

create table customer (
  customerid int default next value for RentityID primary key,
  fname varchar(50)
);

create table equipment(
  equipmentid int default next value for RentityID primary key,
  name varchar(50),
  type varchar(50),
  price decimal(5,2)
);


create table rental(
  rentalid int default next value for RentityID primary key,
  rentalDate datetime default getdate(),
  customerid int not null references customer
);

create table equipmentRental(
  rentalid int references rental,
  equipmentid int references equipment,
  duedate datetime,
  actualPrice decimal(5,2),
  constraint equipmentRental_PK primary key (rentalid,equipmentid)
);

insert into customer(fname) values('Scott'),('Weston'),('John');
insert into equipment(name, type, price) values
  ('Tandem 1','Tandem', '8' ),
  ('Tandem 2','Tandem', '8' ),
  ('Tandem 3','Tandem', '8' ),
  ('Tandem 4','Tandem', '8' ),
  ('Helmet 1','Saftey', '1' ),
  ('Helmet 2','Saftey', '1' ),
  ('Helmet 3','Saftey', '1' ),
  ('Helmet 4','Saftey', '1' );


insert into rental(rentalDate, customerid)
  values('9/1/14', (select customerid from customer where fname='Scott'));

insert into equipmentRental
  values((select max(rentalid) from rental),
         (select equipmentid from equipment where name = 'Tandem 4'),
  '9/9/14',7);

insert into equipmentRental
  values((select max(rentalid) from rental),
         (select equipmentid from equipment where name = 'Helmet 1'),
  '9/9/14',0);



insert into rental(rentalDate, customerid)
  values('9/1/14', (select customerid from customer where fname='Weston'));

insert into equipmentRental
  values((select max(rentalid) from rental),
         (select equipmentid from equipment where name = 'Tandem 1'),
  '9/9/14',7.5);

insert into equipmentRental
  values((select max(rentalid) from rental),
         (select equipmentid from equipment where name = 'Helmet 3'),
  '9/9/14',0);