# SQL Tools
# Shameema Esufali 
# Last Edited 12 September 2012
# SELECT 
DESCRIBE Country;

SELECT * FROM Site;

SELECT * FROM Tree LIMIT 5;

SELECT TreeID,Stemtag FROM Stem LIMIT 5;

SELECT speciesid,speciesname,genusid,fieldfamily
FROM Species LIMIT 5;

SELECT COUNT(*) FROM Species;

SELECT COUNT(DISTINCT genusid) 
FROM Species;

SELECT SUM(dbh) FROM DBH;

# WHERE CLAUSES

SELECT SUM(dbh) 
FROM DBH WHERE censusid=1;

SELECT SUM(dbh) 
FROM DBH WHERE primarystem='main';

SELECT genus 
FROM Genus WHERE genusid=3;

SELECT family 
FROM Family WHERE familyid=5 OR familyid=7; 

# JOINS: LEFT and RIGHT JOIN

SELECT genus,speciesname 
FROM Genus JOIN Species USING (genusid) LIMIT 5;

SELECT family,genus,speciesName
FROM Family JOIN Genus USING(familyid) 
JOIN Species ON Genus.genusid =Species.genusid LIMIT 5; 

SELECT d.stemid,d.dbh,a.tsmid 
FROM DBH d LEFT JOIN DBHAttributes a 
USING(DBHID,censusid) LIMIT 10;

SELECT d.stemid,d.dbh,a.tsmid 
FROM DBH d RIGHT JOIN DBHAttributes a 
USING(dbhid,censusid) LIMIT 10;

# To find stems without any attributes
SELECT d.stemid,d.dbh,a.tsmid 
FROM DBH d LEFT JOIN DBHAttributes a 
USING(dbhid,censusid) 
WHERE a.tsmid IS NULL;

# To find stems without attributes in the second census
SELECT d.stemid,d.dbh,a.tsmid 
FROM DBH d LEFT JOIN DBHAttributes a 
USING(dbhid,censusid) 
WHERE a.tsmid IS NULL
AND censusid=173 LIMIT 10;
 

#TABLE ALIASES

SELECT family,genus,speciesName
FROM Family f JOIN Genus g USING(familyid) 
JOIN Species s ON g.genusid = s.genusid LIMIT 5; 

# GROUP BY CLAUSE WITH HAVING

SELECT speciesid, COUNT(*) 
FROM Tree GROUP BY speciesid limit 5;

SELECT t. speciesid,g.genus,s.speciesname
FROM Tree t JOIN Species s USING (speciesid)
JOIN Genus g ON s.genusid=g.genusid
GROUP BY t. speciesid,g.genus,s.speciesname
HAVING COUNT(*) <2;

SELECT t. speciesid,g.genus,s.speciesname, count(*)
FROM Tree t JOIN Species s USING (speciesid)
JOIN Genus g ON s.genusid=g.genusid
GROUP BY t. speciesid,g.genus,s.speciesname
HAVING COUNT(*) <5
ORDER BY count(*) DESC, g.genus,s.speciesname;


# CREATE TABLE SYNTAX
# CREATE TABLE (IF NOT EXISTS)

CREATE TABLE IF NOT EXISTS tempperson (
firstname CHAR(20),
lastname CHAR(20)
);



CREATE TABLE tempsp ( 
Mnemonic CHAR(10), 
Genus CHAR(32), 
SpeciesName CHAR(64), 
FieldFamily CHAR(32), 
Authority VARCHAR(128), 
IDLevel CHAR(8), 
GenusID INT(10), 
SpeciesID INT(10), 
Family CHAR(32), 
Errors VARCHAR(256), 
TempID INT UNSIGNED AUTO_INCREMENT, 
PRIMARY KEY (TempID) 
) ENGINE=MyISAM 
;


# CREATE TABLE (SELECT)

CREATE TABLE tempsp AS SELECT * FROM Species;

CREATE TABLE tempcopy AS SELECT speciesid,mnemonic FROM Species;


CREATE TABLE IF NOT EXISTS tempemptypersonnel LIKE Personnel; 

# INSERT
# INSERT VALUES

INSERT INTO tempemptypersonnel (personnelid,firstname,lastname)
VALUES(1,'May','Lee'), (2,'Raja','Ghosh'); 

INSERT INTO tempemptypersonnel (firstname,lastname)
VALUES('I.D.','Automatically'), ('Gen','Erated'); 


CREATE TABLE stagepersonnel (
firstname varchar(25),
lastname varchar(25),
role varchar(25)
);


# LOAD DATA
LOAD DATA LOCAL INFILE 'personnel.txt' 
INTO TABLE stagepersonnel
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '\t' IGNORE 1 LINES;

# INSERT SELECT
INSERT INTO tempemptypersonnel (firstname,lastname)
SELECT DISTINCT firstname,lastname FROM stagepersonnel;


# UPDATE
# SINGLE ROW UPDATE

UPDATE tempsp SET mnemonic='UNIDEN' WHERE mnemonic='AAAAAA';

# MULTIPLE ROW UPDATE

UPDATE tempsp SET idlevel ='Species' WHERE genusid=2;

UPDATE tempsp SET description=fieldfamily, currenttaxonflag=1;

# UPDATE ON JOIN 
UPDATE tempsp JOIN Genus USING (genusid) 
SET tempsp.Authority =Genus.Authority
WHERE Genus.genus='Abarema';


UPDATE tempsp JOIN Genus USING (genusid)
SET tempsp.Authority =Genus.Authority
WHERE Genus.genus IN ('Agrostistachys','Alstonia');


# DELETE
# DELETE A ROW

DELETE FROM tempemptypersonnel WHERE personnelid =1;

# DELETE A SET OF ROWS

DELETE FROM tempemptypersonnel WHERE firstname like 'S%';

# DELETE ON JOIN USING TWO OR MORE TABLES

DELETE table1 
FROM table1 LEFT JOIN table2 ON table1.tag=table2.tag 
WHERE table2.tag IS NULL;
 
# Only records from table1 are deleted when condition is met
 
DELETE table1, table2 
FROM table1 JOIN table2 JOIN table3 
WHERE table1.tag=table2.tag AND table2.tag=table3.tag;

#  ADVANCED TOPICS
# USE OF EXISTS, IN AND ANY - SUBQUERIES

SELECT count(*) FROM Genus 
WHERE EXISTS 
 (SELECT genusid FROM Species 
  WHERE Genus.genusid=Species.genusid);

SELECT count(*) FROM Species 
WHERE speciesid IN (SELECT speciesid FROM Tree 

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);
                       
# Notice that the subquery contains a reference to a column of t1, 
# even though the subquery's FROM clause does not mention a table t1. 
# So, MySQL looks outside the subquery, and finds t1 in the outer query.

# Here is how to use a subquery in the FROM clause, using the example table:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

# Result: 2, '2', 4.0.

# Here is another example: Suppose that 
# you want to know the average of a set of 
# sums for a grouped table. This does not work:

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
# However, this query provides the desired information:

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;
        
# For more information on subqueries see
#  https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html 

# For subqueries in HAVING or ORDER BY clauses, MySQL also looks 
# for column names in the outer select list.

# MEMORY VARIABLES
# 1.SYSTEM
#  A full list of system variables is available at 
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
# Most of these are beyond our scope.  The most often used are below. 

SELECT SYSDATE();
SELECT NOW();

# 2.USER DEFINED
SET @mydb="bci";
SET @myusername="shameema";
SET @maxdbh=4000;

SET @mydate=sysdate();
SELECT @mydate;

SELECT @nextid:=max(personnelid) FROM Personnel;

# USE IN INSERTs

INSERT INTO tempemptypersonnel(personnelid,firstname,lastname) 
SELECT @nextid:=@nextid+1, firstname,lastname FROM Personnel;



