Complex expressions
------------------

* String operations: ||, upper, lower, position, substring, trim

SELECT trim(LEADING FROM '  Hello world   ') ;

SELECT E'\t\tHello\tworld\t\n\n' ;

SELECT trim(BOTH E'\t\n' FROM E'\t\t Hello\tworld\t\n\n') ;


* Numerical operations: +, -, *, /, %, ^, !

SELECT -3 % 2 ;

SELECT 2 ^ 10 AS exponentiation;

SELECT factorial(5) AS factorial;


* Mathematical operations: abs, ceil, floor, log, mod, round, sqrt,

SELECT ABS(51 - 99.95) ;


* Utilities: random, now

SELECT now() AS CurrentDT ;

SELECT random() AS LuckyNumber ;


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 ;


SELECT
   age
   , upper(fullname) as fullnameofcontestant
   , 'season 4' as season
   , fullname || ' ' || age::char(4) || ' ' || hometown as bakerinfo
   , substring(fullname from strpos(fullname, ' ') + 1)
FROM
   bakers ;


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

NULL values -> there is no value

NULL

   -> there is no value for this attribute for this tuple
   -> there is a value for this attribute for this tuple, but I don't know it
   -> I don't know if there is a value for this attribute for this tuple or not

select * from tmp where name like '%';

To display '<null>' in psql to make it easy to diiferentiate between an empty string and NULL:
\pset null '<null>'

NULL < 5 => unknown  (not true/not false)
NULL = 5 => unknown  (not true/not false)


 id >= 1 and name = 'ghi';


UNKNOWN   and   TRUE     = UNKNOWN
UNKNOWN   and   FALSE    = FALSE
UNKNOWN   and   UNKNOWN  = UNKNOWN


UNKNOWN   or   TRUE     =  TRUE
UNKNOWN   or   FALSE    =  UNKNOWN
UNKNOWN   or   UNKNOWN  =  UNKNOWN


not (UNKNOWN) = UNKNOWN

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

FROM  relation R, check each tuple, 
if WHERE statement returns true for that tuple, then return the tuple
construct a tuple using attributes in SELECT.

check for null value with IS NULL

baking=> select * from tmp where name is null ;
 id | name 
----+------
  4 | 
(1 row)

baking=> select * from tmp where name is not null ;
 id | name 
----+------
  1 | abc
  2 | def
  3 | 
    | ghi
(4 rows)

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

----------

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);
INSERT INTO tmp values(6, 'a picture of my cat');
INSERT INTO tmp values(7, 'certain c% of shares');
INSERT INTO tmp values(8, E'two\nlines in one string');
INSERT INTO tmp values(9, 'many \ all \ ov\e%r');
INSERT INTO tmp values(10, 'no sp\n''ec;i"al\ v%al_ue');
INSERT INTO tmp values(11, U&'\D834\DD1E');


CREATE TABLE abc (val varchar(10), id int) ;
INSERT INTO abc values('cat');
INSERT INTO abc values('dog');
INSERT INTO abc values(null);
INSERT INTO abc values(null, 5);
INSERT INTO abc values('', 6);
INSERT INTO abc values('NULL', 7);


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 = Unknown

not (Unknown) = Unknown


IS NULL!

SELECT NULL = 5 OR 4 > 5 ;  -- Evaluates to UNKNOWN

SELECT NULL = 5 AND 4 > 5 ;  -- Evaluates to FALSE


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; 


https://www.postgresql.org/docs/15/datatype-datetime.html

date:  
time: 
timestamp:
interval:

date + time = timestamp

timestamp + time = timestamp

date - date = interval

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

SELECT DATE '2020-01-28' + INTERVAL '3 hours' ;

SELECT DATE '2020-05-19' - DATE '2020-01-28' AS duration ;


SELECT (date '2016-03-01', date '2016-03-31') OVERLAPS
       (date '2016-02-25', date '2016-03-04') ;

SELECT (date '2016-03-01', date '2016-03-31') OVERLAPS
       (date '2016-02-25', date '2016-02-29') ;


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 ;

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


SELECT
    attributes
FROM
    R1, R2, R3
WHERE
    join conditions
    and other conditions

R1 x R2 x R3


SELECT
    b.fullname
    , t.episodeid
FROM
    bakers b
    , technicals t
WHERE
    b.baker = t.baker
    and t.rank = 1; 


SELECT DISTINCT
    b.fullname
FROM
    bakers b
    , technicals t
WHERE
    b.baker = t.baker
    and t.rank = 1; 

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

- Find full name of all bakers who used 'Chocolate' in their showstoppers
  bakes and became star baker in that episode.


SELECT DISTINCT
  b.fullname
  , ss.make
FROM
  bakers b
  , showstoppers ss
  , results r
WHERE
  b.baker = ss.baker
  and b.baker = r.baker
  and ss.episodeid = r.episodeid
  and lower(ss.make) like '%chocolate%'
  and r.result = 'star baker';

-----

- Find bakers who used 'chocolate' in back to back episodes in
  showstoppers

SELECT DISTINCT
   b.fullname
FROM
   bakers b
   , showstoppers ss1
   , showstoppers ss2
WHERE
   b.baker = ss1.baker
   and b.baker = ss2.baker
   and lower(ss1.make) like '%chocolate%'
   and lower(ss2.make) like '%chocolate%'
   and ss1.episodeid = ss2.episodeid+1;
   



SQL FROM Clause
-------------------------


SELECT
   b.fullname
   , b.age
FROM
   bakers  b
WHERE
   b.age > 30;



SELECT
   b.fullname
   , r.episodeid
   , r.result
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker ;


Return the name and hometown of bakers who came in first in at least
two different technical challenges.


SELECT DISTINCT
   b.fullname
   , b.hometown
FROM
   bakers b
   , technicals t1
   , technicals t2
WHERE
   b.baker = t1.baker
   and b.baker = t2.baker
   and t1.rank = 1
   and t2.rank = 1
   and t1.episodeid > t2.episodeid;


Return name and hometown of all bakers who used chocolate in their
showstopper challenge of an episode and came first in that episode.


SELECT DISTINCT
   b.hometown
   , b.fullname
FROM
   bakers b
   , showstoppers ss
   , results r
WHERE
   b.baker = ss.baker
   and r.baker = ss.baker
   and r.episodeid = ss.episodeid
   and lower(ss.make) LIKE '%chocolate%'
   and r.result = 'star baker';

Return the fullname of bakers who used ginger in both a showstopper
and a signature challenge.


SELECT DISTINCT
   b.fullname
FROM
   bakers b
   , signatures s
   , showstoppers ss
WHERE
   b.baker = s.baker
   and b.baker = ss.baker
   and lower(s.make) LIKE '%ginger%'
   and lower(ss.make) LIKE '%ginger%'   ;







SET/BAG OPERATIONS// ORDER BY // LIMIT
=========================================



SELECT
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid DESC
;


SELECT
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid DESC
LIMIT
   5
;

SELECT
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid DESC
LIMIT
   5
OFFSET
   5
;

------

Set operators

Unique tuples in each
SELECT FROM WHERE
UNION
SELECT FROM WHERE

Unique common tuples

SELECT FROM WHERE
INTERSECT
SELECT FROM WHERE

Unique tuples only in the top query

SELECT FROM WHERE
EXCEPT            -- set difference
SELECT FROM WHERE


Bag operators

SELECT FROM WHERE
UNION ALL
SELECT FROM WHERE

SELECT FROM WHERE
INTERSECT ALL
SELECT FROM WHERE

SELECT FROM WHERE
EXCEPT ALL
SELECT FROM WHERE

R UNION ALL S = {m+n copies of t |there are m copies of t in R and n
                copies of t is S}

R INTERSECT ALL S = {min(m,n) copies of t| there are m copies of t in R and n
                copies of t is S}

R EXCEPT ALL S = {max(0,m-n) copies of t| there are m copies of t in R and n
                copies of t is S}




SELECT   --q2
   episodeid
FROM
   signatures
WHERE
   lower(make) like '%cardamom%'
   or lower(make) like '%ginger%'
EXCEPT ALL
SELECT    --q1
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid
;

SELECT    --q1
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
EXCEPT ALL
SELECT   --q2
   episodeid
FROM
   signatures
WHERE
   lower(make) like '%cardamom%'
   or lower(make) like '%ginger%'
ORDER BY
   episodeid
;

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

Finds all bakers who were never star bakers.


SELECT  --- all bakers
   baker
   , fullname
FROM
   bakers 
EXCEPT
SELECT    -- bakers whowon once at least
   b.baker
   , b.fullname
FROM
   results r
   , bakers b
WHERE
   b.baker = r.baker
   and r.result = 'star baker'
ORDER BY
   baker
   , fullname
;

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

Find all bakers who won no technicals or have not won star baker. Return their full name.

    Construct slowly, write the following in SQL:
        R1: all bakers
        R2: bakers who won technicals
        R3: bakers who won star baker
    Now we can compute (R1 EXCEPT R2) UNION (R1 EXCEPT R3)


WITH R1 AS (
   SELECT
      fullname
   FROM
      bakers
),
R2 AS (
   SELECT
      b.fullname
   FROM
      bakers b
      , technicals t
   WHERE
      b.baker = t.baker
      AND t.rank = 1
),
R3 AS (
   SELECT
      b.fullname
   FROM
      bakers b
      , results r
   WHERE
      b.baker = r.baker
      AND r.result = 'star baker'
)

(SELECT * FROM R1

EXCEPT

SELECT * FROM R2)

UNION

(SELECT * FROM R1

EXCEPT

SELECT * FROM R3);





SQL AGGREGATES/GROUP BY/HAVING
================================

Min, Max, Count, Avg, Sum, Stdev

SELECT
   min(firstaired)
   , max(firstaired)
   , count(*)           --- number of tuples
   , count(firstaired)  --- number of values this attribute has, not null
FROM
   episodes ;


SELECT
   count(*)
   , count(distinct baker)
   , min(episodeid)
   , max(episodeid)
FROM
   results
WHERE
   result = 'star baker' ;


SELECT
   count(*)
   , min(episodeid)
   , max(episodeid)
FROM
   results
WHERE
   result = 'star baker'
   and baker = 'Rahul';

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

GROUP BY
--------------

SELECT
   baker
FROM
   results
WHERE
   result = 'star baker'
ORDER BY
   baker;


  baker  
---------
 Briony
 Dan
 Kim-Joy
 Kim-Joy
 Manon
 Rahul
 Rahul
 Ruby
 Ruby



SELECT
   baker
   , count(*)
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker
ORDER BY
   baker
;


G1
 Briony
G2
 Dan
G3 
 Kim-Joy
 Kim-Joy
G4 
 Manon
G5 
 Rahul
 Rahul
G6 
 Ruby
 Ruby


SELECT    
   episodeid
   , count(*)  as numbakers
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
GROUP BY
   episodeid
;


SELECT    
   baker
   , count(*)  as numepisodes
FROM
   showstoppers
WHERE
    lower(make) like '%chocolate%'
GROUP BY
   baker
;


SELECT
   b.baker
   , b.fullname
   , count(*) as numepisodes
   , min(e.firstaired)
   , max(e.firstaired)
FROM
   showstoppers ss
   , episodes e
   , bakers b
WHERE
   ss.baker = b.baker
   and ss.episodeid = e.id
   and lower(ss.make) like '%chocolate%'
GROUP BY
   b.baker;



SELECT
   baker
   , count(*)
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker
HAVING
   count(*)>1
;


G1
 Briony
G2
 Dan
G3 
 Kim-Joy
 Kim-Joy
G4 
 Manon
G5 
 Rahul
 Rahul
G6 
 Ruby
 Ruby


---------------
     SQL
---------------

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

SELECT
   attributes to return
FROM
   relations
WHERE
   join conditions and other conditions
GROUP BY
   grouping elements
HAVING
   group rows filtering condition
ORDER BY
   sorting expression(s)
LIMIT
   count

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


bakers(baker,fullname,age,occupation,hometown)
episodes(id,title,firstaired,viewers7day,signature,technical,showstopper)
results(episodeid,baker,result)
technicals(episodeid,baker,rank)
signatures(episodeid,baker,make)
showstoppers(episodeid,baker,make)

Find pair of bakers A,B who are from the same town and have placed in
ranks one place apart (e.g., 2nd 3rd, or 4tg 5th) in the technical
challenge of the same episode.

SELECT DISTINCT
    b1.fullname
    , b2.fullname
FROM
    bakers b1
    , bakers b2
    , technicals t1
    , technicals t2
WHERE
    b1.baker > b2.baker
    and t1.baker = b1.baker
    and t2.baker = b2.baker
    and t1.episodeid = t2.episodeid
    and b1.hometown = b2.hometown
    and abs(t1.rank-t2.rank) = 1
ORDER BY
    b1.fullname asc
    , b2.fullname
;

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


SELECT
   baker as bakershortname
   , fullname as bakerfullname
FROM
   bakers
WHERE
   age >= 30
EXCEPT
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated'
ORDER BY
   bakershortname
;


Find bakers who
   (are either at least 30 years old or have won technical 2 times)
     and were never eliminated.

((30 years old) union (bakers who won 2 times)) - (bakers eliminated)

(SELECT
   baker as baker1
   , fullname as f1
FROM
   bakers
WHERE
   age >= 30
UNION 
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , technicals t1
   , technicals t2
WHERE
   b.baker = t1.baker
   and b.baker = t2.baker
   and t1.episodeid <> t2.episodeid
   and t1.rank = 1
   and t2.rank = 1
)
EXCEPT
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated'
ORDER BY
   baker1
   , f1
;


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



SELECT
   baker as bakershortname
   , fullname as bakerfullname
FROM
   bakers
WHERE
   age >= 30
EXCEPT
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated';


Find bakers who
   (are either at least 30 years old or have won technical 2 times)
     and were never eliminated.

---------

set operations

SELECT FROM WHERE
UNION/INTERSECT/EXCEPT
SELECT FROM WHERE

bag operations!
SELECT FROM WHERE
UNION ALL/INTERSECT ALL/EXCEPT ALL
SELECT FROM WHERE


    - or are at least 30 years old.


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

Aggregates:
min,max,sum,avg,stdev

SELECT
   age
   , hometown
FROM
   bakers;


SELECT
   min(age)
   , max(age)
   , max(age) - min(age) as agedifference
   , min(hometown)
FROM
   bakers;

baking=> select * from tmp ;
 id | name 
----+------
  1 | abc
  2 | def
    | ghi
    | 
  2 | klm
(5 rows)

baking=> select count(*) from tmp ;
 count 
-------
     5
(1 row)

baking=> select count(*), count(id) from tmp ;
 count | count 
-------+-------
     5 |     3
(1 row)

baking=> select count(*), count(id), count(distinct id) from tmp ;
 count | count | count 
-------+-------+-------
     5 |     3 |     2
(1 row)

------------------------------------------------------------
   
SELECT
   b.baker
   , b.hometown
   , count(*)
FROM
   bakers b
   , technicals t1
   , technicals t2
WHERE
   b.baker = t1.baker
   and b.baker = t2.baker
   and t1.episodeid <> t2.episodeid
   and t1.rank = 1
   and t2.rank = 1
GROUP BY
   b.baker
   , b.hometown
;

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

For each bakers, find how many times they won technical.

SELECT
   t.baker
   , count(*) as numtechnicalwon
FROM
   technicals t
WHERE
   t.rank = 1
GROUP BY
   t.baker;


Find bakers who have won technical 2 times.

SELECT
   t.baker
   , count(*) as numtechnicalwon
FROM
   technicals t
WHERE
   t.rank = 1
GROUP BY
   t.baker
HAVING
   count(*) > 1
;

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

For each baker and rank, find how many times they got that rank in
technicals.

SELECT
   b.baker
   , t.rank
   , count(*) as numtimes
FROM
   technicals t
   , bakers b
WHERE
   b.baker = t.baker
GROUP BY
   b.baker
   , t.rank;


For each baker and rank, find how many times they got that rank in
technicals.
   - but only for ranks less than 5
   

SELECT
   b.baker
   , t.rank
   , count(*) as numtimes
FROM
   technicals t
   , bakers b
WHERE
   b.baker = t.baker
   and t.rank < 5
GROUP BY
   b.baker
   , t.rank
;

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

Bakers who competed in more than 4 episodes

SELECT    --- who competed in more than 4 episodes
   t.baker
FROM
   technicals t
GROUP BY
   t.baker
HAVING
   count(*)>4 ;


For each baker and rank, find how many times they got that rank in
technicals.
    - but only for ranks less than 5
--- who competed in more than 4 episodes


SELECT
   b.baker
   , t.rank
   , count(DISTINCT t.episodeid) as numtimes
FROM
   technicals t
   , bakers b
   , signatures s
WHERE
   b.baker = t.baker
   and t.rank < 5
   and s.baker = b.baker
GROUP BY
   b.baker
   , t.rank
HAVING
   count(DISTINCT s.episodeid)>4
;



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

Find bakers who were never eliminated and have won technical at least 2 times.

SELECT    
   t.baker
FROM
   technicals t
WHERE
   t.rank = 1
GROUP BY
   t.baker
HAVING
   count(*)>=2
EXCEPT
SELECT
   baker
FROM
   results
WHERE
   result = 'eliminated';

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


Find the last episode in the series.

Find the baker who won technical in the last episode 

Find the biggest drop in the viewers from one episode to the
next. (Who was eliminated?)

For each month, find the average viewers.

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

  
































drop table episodes2;
CREATE TABLE episodes2 (
    id               int  primary key
    , title          varchar(100)
    , firstaired     date
    , viewers7day  float
    , signature       varchar(100)
    , technical       varchar(100)
    , showstopper     varchar(100)
) ;

INSERT INTO episodes2 values(1,'Biscuits', date '2018-8-28', 9.55,'24 Regional Biscuits','8 Wagon Wheels','3D Biscuit Self-Portrait');
INSERT INTO episodes2 values(2,'Cakes', date '2018-9-4', 9.31,NULL,'Le Gâteau Vert','Chocolate Collar Cake');
INSERT INTO episodes2 values(3,'Bread', date '2018-9-11', NULL,'12 Chelsea Buns','8 Non-Yeasted Garlic Naan Breads','Korovai');
INSERT INTO episodes2 values(4,'Desserts', date '2018-9-18', 8.88,'Meringue Roulade','Raspberry Blancmange with 12 Langues du Chat biscuits','Melting Chocolate Ball Dessert');
INSERT INTO episodes2 values(5,'Spice', NULL, NULL,'Ginger Cake','12 Ma''amoul','Spiced Biscuit Chandelier');
INSERT INTO episodes2 values(6,'Pastry', date '2018-10-2', 9.30,'12 Samosas', NULL, NULL);
INSERT INTO episodes2 values(7,'Vegan', date '2018-10-9', NULL,'8 Savoury Vegan Tartlets','Vegan Tropical Fruit Pavlova','Vegan Celebratory Cake');
INSERT INTO episodes2 values(8,'Danish', date '2018-10-16', 9.69,,NULL,'Kagemand/Kagekone');
INSERT INTO episodes2 values(9,'Pâtisserie (Semi-final)', date '2018-10-23', 9.50,'24 Madeleines','Torta Setteveli','Parisian Pâtisserie Window');
INSERT INTO episodes2 values(10,'Final', date '2018-10-30', 10.34,'12 Iced Doughnuts','6 Campfire Pita Breads','Landscape Dessert');