# Script file for creating the bikedb that is used in many of the MySQL
# examples for the COP 4710 MySQL notes

drop database if exists bikedb;

create database bikedb;

use bikedb;

create table bikes (
    bikename varchar(30) not null,
    size int(2),
    color varchar(15),
    cost int(6),
    purchased date,
    mileage int(6),
    primary key (bikename)
);

insert into bikes values ('Colnago Dream Rabobank',60,'blue/orange',5500,'2002-07-27',4300);
insert into bikes values ('Bianchi/Liquigas FG',58,'celeste/blue',5600,'2005-12-2',0);
insert into bikes values ('Bianchi Evolution 3',58,'celeste',4800,'2003-11-16',2000);
insert into bikes values ('Eddy Merckx Molteni',58,'orange',5100,'2004-08-12',0);
insert into bikes values ('Eddy Merckx Domo',58,'blue/black',5300,'2005-02-02',0);
insert into bikes values ('Battaglin Carrera',60,'red/white',4000,'2001-03-14',11200);
insert into bikes values ('Gianni Motta Personal',59,'red/green',4400,'2000-05-01',8700);
insert into bikes values ('Gios Torino Super',60,'blue',2000,'1998-11-08',9000);
insert into bikes values ('Schwinn Paramount P14',60,'blue',1800,'1992-03-01',200);
insert into bikes values ('Bianchi Corse Evo 4',58,'celeste',5700,'2004-12-22',300);
insert into bikes values ('Colnago Superissimo',59,'red',3800,'1996-03-01',13000);

select * from bikes;


create table bluebikes (
    bikename varchar(30),
    color varchar(15),
    price int(6),
    total_miles int(6),
    primary key (bikename)
);
# Use a form 3 insert statement to load the bluebikes table with
# some of the information on the bikes whose color is blue.
insert into bluebikes
select bikename, color, cost, mileage
from bikes
where color = 'blue';

select * from bluebikes;