/* Create table in the STATS782 database
 
DJS 10/10/07 */
use STATS782;

drop table if exists cellar;

create table cellar (
  WineID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Location VARCHAR(10)NOT NULL,
  Row TINYINT UNSIGNED NOT NULL,
  Tile TINYINT UNSIGNED NOT NULL,
  Year VARCHAR(4) NULL,
  Winery VARCHAR(50) NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Grape VARCHAR(50) NOT NULL,
  Country VARCHAR(20) NOT NULL,
  Type VARCHAR(20) NOT NULL,
  Price VARCHAR(10) NOT NULL,
  Closure VARCHAR(10) NULL,
  Drink VARCHAR(50) NULL,
  Composition VARCHAR(50) NULL
);

### on windows
load data infile "c:/dscott/Teaching/782/Lectures/mySQLExamples/Cellar.csv"
	into table cellar
	fields terminated by ','
        optionally enclosed by '"'
	lines terminated by '\r\n'
	ignore 1 lines
	(Location, Row, Tile, @Year, Winery, Name, Grape,
         Country, Type, @Price, Closure, Drink, Composition)
	set Year = if(@Year="NV",NULL,@Year),
            Price = substring(@Price,2)
;

### Code to test this
select * from cellar order by WineID limit 10;

alter table cellar
  modify Year Year;

describe cellar;