- Intro to Postgresql


- Intro to SQL

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

DDL - data definition language
      create tables
      drop tables
      

DML - data manipulation language


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

SQL - industry standard

core SQL

SQL - tuple at a time

SQL - bag semantics implementation (not sets)

   -> relations must have a primary key is you do not want duplicate
      tuples

   -> queries may return copies of tuples unless you remove them!

SQL ->
  - case insensitive
  - ; for end of command
  - strings are delimited by single quotes
  
------------


Select - like projection but no duplicate removal!

SELECT
    *
FROM
    bakers;


SELECT DISTINCT
   age
FROM
   bakers ;


SELECT DISTINCT
   age
   , fullname
FROM
   bakers ;



FROM bakers relation -> return all tuples -> but for each tuple return
attributes in the SELECT

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



-->

WHERE statement


SELECT
   fullname
   , age
FROM
   bakers
WHERE
   age <= 30
   and length (fullname)>15;

SELECT
   fullname
   , age
FROM
   bakers
WHERE
   hometown = 'London';

LIKE:

% -> 0 more occurrence of any char
_ -> 1 character

SELECT
   fullname
   , age
   , occupation
FROM
   bakers
WHERE
   occupation LIKE '%parent' ;

SELECT
   fullname
   , age
   , occupation
FROM
   bakers
WHERE
   lower(occupation) LIKE '%project%' ;

Escape for single quote

baking=> select 'abc''def' ;

select 'abc' || E'\n' || 'def' ;

select * from tmp where name like '%c\%%';

select * from tmp where name like '%cz%%' escape 'z';

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

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 foobar where name like '%';

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

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

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

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

date:  
time: 
timestamp:
interval:

date + time = timestamp

timestamp + time = timestamp

date - date = interval


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


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;
   


























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');