# Create a new database with your spreadsheet data and play detective.

CREATE DATABASE green;
USE green;

# CHANGE PATH TO THIS FILE
SOURCE ~/Documents/databasemanagement/sampledatabases/green.sql;

SHOW TABLES;
DESCRIBE stagecensus;
DESCRIBE stagespecies;

SELECT * FROM stagecensus limit 3;

SELECT * FROM stagespecies limit 3;

SELECT count(distinct spcode) FROM stagecensus;
SELECT count(distinct codes) FROM stagecensus;

SELECT count(distinct quadrat) FROM stagecensus;
SELECT count(distinct subquadrat) FROM stagecensus;

SELECT min(xmm),max(xmm) FROM stagecensus;
SELECT min(ymm),max(ymm) FROM stagecensus;

SELECT quadrat,min(xmm),max(xmm) FROM stagecensus GROUP BY quadrat ORDER BY quadrat;
SELECT quadrat,min(ymm),max(ymm) FROM stagecensus GROUP BY quadrat ORDER BY quadrat;

# Check min and max diameters for each stem. 
SELECT min(dbhmm),max(dbhmm) FROM stagecensus;
SELECT min(dbhstem1),max(dbhstem1) FROM stagecensus;
SELECT min(dbhstem2),max(dbhstem2) FROM stagecensus;
SELECT min(dbhstem3),max(dbhstem3) FROM stagecensus;

# Why the null diameters
SELECT * FROM stagecensus WHERE dbhmm ="" limit 5;

# Remove blank lines.  Be careful not to remove the one with data no dbh
SELECT * FROM stagecensus WHERE tag ="" OR TAG IS NULL;

# Since that query reveals only blank lines we can remove them.  Delete the lines. 
DELETE * FROM stagecensus WHERE tag ="" OR tag IS NULL;

SELECT count(*) FROM stagecensus WHERE dbhmm is null;
SELECT count(*) FROM stagecensus WHERE dbhmm ="";


# Check max and min dbh for each species
SELECT spcode, min(dbhmm),max(dbhmm) FROM stagecensus GROUP BY spcode ORDER BY spcode;

# Find duplicate tags
SELECT count(distinct tag) FROM stagecensus;
SELECT tag, count(*) FROM stagecensus GROUP BY tag HAVING COUNT(*) > 1;

# Check if species are valid
SELECT COUNT(*) FROM stagecensus 
 WHERE spcode NOT IN (SELECT spcode FROM stagespecies);
 
SELECT COUNT(DISTINCT spcode) FROM stagecensus 
 WHERE spcode NOT IN (SELECT spcode FROM stagespecies);
 
SELECT DISTINCT spcode FROM stagecensus 
 WHERE spcode NOT IN (SELECT spcode FROM stagespecies);
 
SELECT DISTINCT spcode FROM stagecensus 
 WHERE spcode NOT IN (SELECT spcode FROM stagespecies) ORDER BY spcode;
 

CREATE TABLE tempbadspecies 
 AS SELECT DISTINCT spcode FROM stagecensus 
 WHERE spcode NOT IN (SELECT spcode FROM stagespecies) ORDER BY spcode;
 
SELECT count(*) FROM tempbadspecies 
 WHERE spcode IN (SELECT  oldcode1 FROM stagespecies);

# Correct reported errors 
UPDATE stagecensus c, stagespecies s
 SET c.spcode=s.spcode
 WHERE c.spcode=s.oldcode1;
 
UPDATE stagecensus c, stagespecies s
 SET c.spcode=s.spcode
 WHERE c.spcode=s.oldcode3;

# Investigate and correct miss-spellings.  Be very sure about these.  
SELECT * FROM stagespecies WHERE spcode LIKE 'DIOS%';
UPDATE stagespecies SET spcode ='DIOSDI' WHERE spcode='DIOSD1';

SELECT * FROM stagespecies WHERE spcode LIKE 'MALL%';
UPDATE stagecensus SET spcode ='MALLLE' WHERE spcode='MALLE';
  
 
