### Basis SQL

## Create a database
DROP DATABASE sampletree;
CREATE DATABASE sampletree;
USE sampletree;

## Create tables species, tree, code and measurement and import data into them.

CREATE TABLE species
(
 speciesID INT,
 species VARCHAR(50),
 seedsize VARCHAR(10),
 PRIMARY KEY(speciesID)
)TYPE=MyISAM;

SHOW species;
DESCRIBE species;

LOAD DATA LOCAL INFILE
"/home/macbook/Documents/WORKSHOP/Sampletreetables/species.txt"
INTO TABLE species IGNORE 1 LINES;

DESCRIBE species;

SELECT * FROM species WHERE speciesID=3;
SELECT seedsize FROM species;
SELECT DISTINCT seedsize FROM species;


## CREATE TREE TABLE
CREATE TABLE tree
(
 treetag INT,
 speciesID INT,
 X DOUBLE,
 Y DOUBLE,
 PRIMARY KEY(treetag)
)TYPE=MyISAM;

SHOW species;
DESCRIBE species;

LOAD DATA LOCAL INFILE "/home/macbook/Documents/WORKSHOP/Sampletreetables/tree.txt" INTO TABLE tree IGNORE 1 LINES;

LOAD DATA LOCAL INFILE "/home/macbook/Documents/WORKSHOP/Sampletreetables/tree.txt"
INTO TABLE tree IGNORE 1 LINES;


## CREATE MEASUREMENT TABLE
CREATE TABLE measurement
(
 measureID INT,
 treetag INT,
 census INT,
 dbh FLOAT DEFAULT NULL,
 date DATE,
 PRIMARY KEY(measureID)
)TYPE=MyISAM;

SHOW species;
DESCRIBE species;

LOAD DATA LOCAL INFILE
"/home/macbook/Documents/WORKSHOP/Sampletreetables/measurement.txt"
INTO TABLE measurement IGNORE 1 LINES;



CREATE TABLE code
(
 measureID INT,
 codes VARCHAR(10)
)TYPE=MyISAM;

SHOW species;
DESCRIBE species;

LOAD DATA LOCAL INFILE
"/home/macbook/Documents/WORKSHOP/Sampletreetables/code.txt"
INTO TABLE code IGNORE 1 LINES;

# Add & update data

CREATE TABLE stagingSpecies Like species;
ALTER TABLE stagingSpecies DROP PRIMARY KEY;
DESCRIBE stagingSpecies;


# TRUNCATE stagingSpecies;



LOAD DATA local infile
"home/gatto/newspecies.txt" into table stagingspecies
lines terminated by '\n' ignore 1 lines
(species,seedsize);


select distinct * from stagingSpecies;
select distinct species,seedsize form stagingSpecies;

select * from stagingSpecies where species like "APEIBA%";
select * from stagingSpecies where species like "%IBE";

select * from stagingSpecies where species like "Andria%";
UPDATE stagingSpecies set speciesID=4 WHERE species like "Andria%";
select * from stagingSpecies where species like "Annona%";
UPDATE stagingSpecies set speciesID=5 WHERE species like "Annona%";
select * from stagingSpecies where species like "Apeiba%";
UPDATE stagingSpecies set speciesID=6 WHERE species like "Apeiba%";

insert into species select distinct * from stagingSpecies;



 
