WEEK 3 REVIEW
=============

Tri-value logic
---------------

- Expressions can return TRUE, FALSE, or NULL
- NULL is infectious
- In filtering, only TRUE counts

SELECT COUNT(*) FROM Bird_nests
  WHERE floatAge < 7 OR floatAge >= 7;

Only returns a fraction of the rows due to floatAge being NULL

To ask (or not ask) for NULLs: IS NULL, or IS NOT NULL

Relational algebra
------------------

- Everything is a table
- Even SELECT COUNT(*) returns a table
- Whereaver a table is requested, can insert a parenthesized query

- What species are *not* represented in the Bird_nests table?

SELECT * FROM Species WHERE
  Code NOT IN (SELECT DISTINCT Species FROM Bird_nests);

- What if there was no HAVING clause?

SELECT Location, MAX(Area) AS Max_area
  FROM Site
  WHERE Location LIKE '%Canada'
  GROUP BY Location
  HAVING Max_area > 200;

SELECT Location, Max_area FROM
    (SELECT Location, MAX(Area) AS Max_area
      FROM Site
      WHERE Location LIKE '%Canada'
      GROUP BY Location)
  WHERE Max_area > 200;

Joins
-----

Switch to toy.duckdb

- Conceptually, the database performs a Cartesian product of the
  tables and then applies the join condition to filter
- In some databases, can do a Cartesian product saying JOIN
  with no join condition
- But in DuckDB, need to use CROSS JOIN

SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM A CROSS JOIN B;

- Get product of number of rows
- Get sum of number of columns

SELECT * FROM A JOIN B ON acol1 < bcol1;

- Left or right outer join: selects rows not included, supplies NULL for values
  in other table

SELECT * FROM A RIGHT JOIN B ON acol1 < bcol1;

- Notice how get B's row 1 now
- Similar for left side

SELECT * FROM A LEFT JOIN B ON acol1 < bcol1;

- Do a FULL OUTER JOIN to get both missing sides (rare)

- In the case of a join on a foreign key, the effect is to add columns to
  the "many" table

SELECT * FROM House;
SELECT * FROM Student;
.schema

SELECT * FROM Student S JOIN House H ON S.House_ID = H.House_ID;

- more compact:

SELECT * FROM Student JOIN House USING (House_ID);

- switch to ASDN database

.mode line
SELECT COUNT(*) FROM Bird_eggs;
SELECT * FROM Bird_eggs LIMIT 1;
SELECT * FROM Bird_eggs
  JOIN Bird_nests USING (Nest_ID)
  LIMIT 1;

SELECT * FROM Bird_eggs
  JOIN Bird_nests USING (Nest_ID)
  JOIN Species ON Species = Code
  LIMIT 1;

SELECT COUNT(*) FROM Bird_eggs
  JOIN Bird_nests USING (Nest_ID)
  JOIN Species ON Species = Code;

- Conceptually clearer perhaps to list the "many" table first in a join?? Maybe

- Ordering is assuredly lost in a join.  An entirely new table is formed.-
- This is pointless:

SELECT * FROM
  (SELECT * FROM Bird_eggs ORDER BY Width)
  JOIN Bird_nests
  USING (Nest_ID);

GOTCHA WITH GROUPING
- DuckDB not smart enough to recognize primary keys in joins

SELECT Nest_ID, COUNT(*)
  FROM Bird_nests JOIN Bird_eggs USING (Nest_ID)
  GROUP BY Nest_ID;

- Can we add another column from Bird_nests?  Other RDBMs's say yes
- DuckDB: no
- So follow DuckDB's suggestions

SELECT Nest_ID, ANY_VALUE(Species), COUNT(*)
  FROM Bird_nests JOIN Bird_eggs USING (Nest_ID)
  GROUP BY Nest_ID;
SELECT Nest_ID, Species, COUNT(*)
  FROM Bird_nests JOIN Bird_eggs USING (Nest_ID)
  GROUP BY Nest_ID, Species;

######### THIS IS WHERE WE GOT ON MONDAY

VIEWS
- a view looks like a table
- virtual, not materialized like CREATE TABLE AS
- virtual implies always reflects current data in source tables, i.e.,
  view is (re-)executed each time it is referenced
- can make normalized tables more palatable; reduce the number of joins
  one has to do manually

SELECT Year, Site, Name FROM Camp_assignment JOIN Personnel ON Observer = Abbreviation;

CREATE VIEW v AS ....

- Stored in the database!

.schema

- inserts, updates, deletes on a view possible?  maybe,
  depends on DB intelligence
- can't insert/update on a view that uses aggregate functions (obviously)

- can also CREATE TEMP TABLE AS ...
- TEMP TABLE is materialized, static
- handy for caching things in long computation; like a variable of sorts

- DROP TABLE, DROP VIEW

SET OPERATIONS

- UNION, INTERSECT, EXCEPT
- last one is set difference

- BTW: 3rd way to get species that do *not* have nest data: use EXCEPT

- First way
SELECT Code FROM Species WHERE Code NOT IN (SELECT DISTINCT Species FROM Bird_nests);

- Second way
SELECT BN.Species, S.Code FROM Bird_nests BN RIGHT JOIN Species S ON BN.Species = S.Code WHERE BN.Species IS NULL;

- Third day
SELECT Code FROM Species
  EXCEPT
  SELECT DISTINCT Species FROM Bird_nests;

- example of UNION: suppose book b14 measured in inches,
  not millimeters, let's adjust on fly

  SELECT Book_page, Nest_ID, Egg_num, Length*25.4, Width*25.4
      FROM Bird_eggs
      WHERE Book_page LIKE 'b14%'
  UNION
  SELECT Book_page, Nest_ID, Egg_num, Length, Width
      FROM Bird_eggs
      WHERE Book_page NOT LIKE 'b14%';

- ^^ what if Book_page is NULL?
- caution: not really looking at names of columns, just #columns and data types
- so is possible to union nonsensical things together
- UNION throws out duplicates, UNION ALL keeps all

Perhaps better example of UNION: let's create a table of all
bird nests and egg counts, including nests that have no egg data recorded.

SELECT Nest_ID, COUNT(*) AS Num_eggs
  FROM Bird_eggs
  GROUP BY Nest_ID

UNION

SELECT Nest_ID, 0 AS Num_eggs
  FROM Bird_nests
  WHERE Nest_ID NOT IN (SELECT DISTINCT Nest_ID FROM Bird_eggs);