1. 
SELECT * FROM Site;
SELECT * FROM Site JOIN Coordinates using (plotid) WHERE QuadratID IS NULL;

2.
SELECT PlotID,COUNT(*) FROM Quadrat GROUP BY PlotID;
SELECT CensusID, COUNT(DISTINCT QuadratName) FROM ViewFullTable GROUP BY CensusID;
SELECT CensusID,COUNT(Distinct QuadratID) FROM DBH JOIN Stem USING (StemID) GROUP BY CensusID;

-- Find which quadrats are missing --
SELECT * FROM Quadrat WHERE QuadratName not in (SELECT DISTINCT QuadratName FROM ViewFullTable);

3. 
SELECT PlotID, min(cnt) as min, max(cnt) as max, count(cnt) as counts FROM 
   (SELECT plotid,A.quadratid,count(DISTINCT TreeID) AS cnt 
    FROM Stem A JOIN Quadrat B USING (QuadratID) GROUP BY plotid,A.quadratid) AS tmp GROUP by plotid;
	
4.
SELECT * FROM TSMAttributes WHERE LOWER(Description) LIKE 'main%' OR LOWER(Description) LIKE 'dead%' OR LOWER(Description) LIKE 'stem lost%';
SELECT TSMCode,TSMAttributes.Description AS Description,PlotCensusNumber,count(*) AS cnt 
     FROM DBHAttributes JOIN TSMAttributes USING (TSMID) JOIN Census USING (CensusID) 
	 WHERE TSMAttributes.Description LIKE 'main%' OR TSMAttributes.Description LIKE 'dead%' OR TSMAttributes.Description LIKE 'stem lost%' 
	 GROUP BY TSMCode,PlotCensusNumber;
	 
5.
SELECT Mnemonic, COUNT(*) AS cnt FROM Species WHERE CurrentTaxonFlag=1 GROUP BY Mnemonic HAVING cnt>1;	 
SELECT COUNT(*) as cnt FROM Species WHERE Mnemonic IS NULL OR Mnemonic='';

SELECT IDLevel,COUNT(*) AS cnt FROM Species GROUP BY IDLevel;

SELECT * FROM Species WHERE IDLEVEL='Species' AND (Authority='' OR AUthority IS NULL);

6.
SELECT PlotID, MIN(QX) as minx,MAX(QY) as maxx,MIN(Qy) as miny,MAX(Qy) as maxy FROM Stem JOIN Quadrat USING (quadratID) GROUP BY PlotID;
SELECT *  FROM Stem WHERE QX IS NULL or QY is null;

7.
SELECT PlotID,Tag,count(distinct treeid) as cnt FROM Tree JOIN Stem USING (TreeID) JOIN Quadrat USING (quadratID) GROUP BY PlotID,Tag HAVING cnt>1;
SELECT * FROM Tree WHERE Tag IS NULL or Tag='';

8.
SELECT PlotID,Tag,Stemtag,count(*) FROM Stem JOIN Tree USING (treeid) JOIN Quadrat USING (quadratID) WHERE StemTag IS NOT NULL AND Stemtag<>'' GROUP BY PlotID,Tag,StemTag HAVING COUNT(*)>1;

9. 
SELECT min(stemcnt) as min, max(stemcnt) as max, count(stemcnt) as counts FROM (SELECT treeid,count(*) AS stemcnt FROM Stem GROUP BY treeid) AS tmp;

10.
SELECT * FROM ViewFullTable WHERE ExactDate='0000-00-00';
SELECT CensusID,MIN(ExactDate),MAX(ExactDate) FROM DBH WHERE ExactDate<>'0000-00-00' GROUP BY CensusID;

11.
SELECT censusid,stemid,hom,count(dbh) AS cnt FROM DBH GROUP BY censusid,stemid,hom HAVING cnt>1;

12.
SELECT Tree.SpeciesID,Mnemonic,Genus,SpeciesName,MIN(dbh) AS min, MAX(dbh) AS maxdbh,count(*) AS numrecs 
    FROM Tree JOIN Stem USING (TreeID) JOIN DBH USING (StemID) JOIN Species USING (SpeciesID) JOIN Genus Using (GenusID)
	GROUP BY Tree.SpeciesID ORDER BY Genus,SpeciesName;
	
13.
SELECT censusid,min(hom) as min, max(hom) as max FROM DBH GROUP BY censusid;	

14.
SELECT DBH.CensusID as CensusID,TSMCode,TSMAttributes.Description as descrip,min(dbh) as DBHmin,max(dbh) as DBHmax,count(*) AS numrecs FROM DBHAttributes JOIN DBH USING (DBHID,CensusID) JOIN TSMAttributes USING (TSMID) GROUP BY CensusID,TSMCode ORDER BY CensusID,TSMCode;

15.
SELECT CensusID,PlotCensusNumber,Tag,HOM,count(*) AS cnt FROM ViewFullTable WHERE PrimaryStem='main' GROUP BY CensusID,PlotCensusNumber,Tag,HOM HAVING cnt>1;

16.
SELECT PlotID,CensusID,PlotCensusNumber,Status,count(*) as cnt FROM ViewFullTable GROUP BY PlotID,CensusID,PlotCensusNumber,Status;

17.
SELECT PlotID,CensusID,PlotCensusNumber,TreeID,Tag,COUNT(*) AS cnt FROM (SELECT DISTINCT PlotID,CensusID,PlotCensusNumber,TreeID,Tag,Status FROM ViewFullTable WHERE Status='dead' OR Status='alive') AS tmp GROUP BY PlotID,CensusID,PlotCensusNumber,TreeID HAVING cnt>1;

18.
SELECT count(*) as cnt FROM ViewFullTable WHERE status='dead' AND PlotCensusNumber=1;
