SQL - Structured Query Language
---------------------------------


1. Multi faceted: DML + DDL (query and data definition)

2. Case insensitive
   statements end with ;
   strings delimiter: single quote

3. Typed

4. Standard: be careful

5. Bag semantics

{1,1,1,2} 

6. Main structure

SELECT
FROM
WHERE

Flow: FROM -> WHERE -> SELECT
-----------------

SELECT
    baker
    , fullname
FROM
    bakers ; 

SELECT
    *
FROM
    bakers ; 


SELECT
    baker
    , rank
FROM
   technicals;


-- remove duplicate tuples

SELECT DISTINCT
    baker
    , rank
FROM
   technicals;

-- select is the project in relational algebra

SELECT 
    fullname
    , left(fullname, strpos(fullname, ' ')) as firstname
    , UPPER(substring(fullname from strpos(fullname, ' ')+1)) as lastname
    , 'baker' as position  -- constant
    , occupation || ' from: ' || hometown as label   --concatenation
FROM
   bakers ;

----------



FROM  relation R
   --> inspect each tuple r in R
WHERE
     boolean condition C
  --> if tuple r passes condition C

CONSTRUCT an output tuple in SELECT

-------


SELECT
   *     --produce all attributes
FROM
   episodes
WHERE
   firstaired > date '2018-10-01'
   and viewers7day > 9.0 ;


SELECT
   firstaired
   , now() - firstaired as numdays
FROM
   episodes
WHERE
   firstaired > date '2018-10-01'
   and viewers7day > 9.0 ;

Date/Time data types:

Date
Time
Timestamp
Interval


Date + Time = Timestamp
Date - Date = Interval


date '2020-01-28' + 2 = date '2020-01-30'   --default assumption of day
date '2020-01-28' + interval '2 day' = timestap '2020-01-30 00:00:00'
date '2020-01-28' + interval '3 hours' = timestamp '2020-01-28 03:00:00'
timestamp '2020-01-28 03:00:00' + interval '10 hours' = timestamp '2020-01-28 13:00:00'
time '12:00:00' + interval '8 hours' = time '20:00:00'
date '2020-05-19' - date '2020-01-28' = 112   -- integer number of days

----------

LIKE -> % -> 0 or more chars, _ -> 1 char

SELECT
   *
FROM
   signatures
WHERE
   lower(make) LIKE '%coconut%';

Single quote:  

SELECT
   *
FROM
   signatures
WHERE
   lower(make) LIKE '%''%';


SELECT
   *
FROM
   signatures
WHERE
   lower(make) LIKE '%z%%' ESCAPE 'z';

-------------------

NULL values

-> there is no value for that attributes
-> I don't know if there is a value for the attribute
-> there is a value but I don't know it


create table tmp(id int, name varchar(10));

insert into tmp values(1, 'abc');
insert into tmp values(2, '');
insert into tmp values(3, NULL);
insert into tmp values(NULL, 'def');
insert into tmp values(NULL, NULL);


SELECT
    *
FROM
    tmp
WHERE
   id <= 10
;

SELECT
    *
FROM
    tmp
WHERE
   length(name)>1;


Unknown OR TRUE = True
Unknown OR FALSE = Unknown
Unknown OR Unknown = Unknown

Unknown AND TRUE = Unknown
Unknown AND FALSE = False
Unknown AND Unknown = Uknown

not (Unknown) = Unknown

IS NULL!

SELECT
    *
FROM
    tmp
WHERE
    id <= 10 OR id is NULL;


SELECT
    *
FROM
    tmp
WHERE
    id is NULL or name is NULL;


SELECT
    *
FROM
    tmp
WHERE
    name is NOT NULL;