Announcements
---------------

- No in person lecture on thursday. Please watch the lecture video from
last years. See:

  https://www.cs.rpi.edu/~sibel/csci4380/fall2022/schedule.html

- No lecture next week 10/10 (Indigenous Peoples' day), next in person
  class on 10/13

- No lecture exercise today, but there will be a lecture exercise on
  thursday, from 10/6 4PM till 10/10 4PM.

- Next homework to be handed on thursday hopefully, I will make an
  announcement on Submitty.

- Exam grading to be completed by early next week.

- No office hours this week except for on friday.

Postgresql
----------------


- Database server is up at: rpidbclass.info

It is Postgresql v13.8

- You got email from rpidbsprof@gmail.com with your username and
password. Please do not change your password.

White list this account using instructions here:

https://itssc.rpi.edu/hc/en-us/articles/360010480672-Anti-Spam-Services-Respite-FAQ

- Example database to be used in the early lecture exercises and in
  class: baking.


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

SQL - Standard (Structured query language)

Bag semantics: a table can have duplicate tuples, unless there is a
primary key

Case insensitive

SELECT * FROM bakers ;
select * from BAKERS ;

Strings: single quote:    'Postgres'

Comments: --

SQL:

 - query database
 - insert/update/delete/create

Strongly typed

  - string  (char() varchar())
  - int, float, numeric() --fixed point
  - boolean
  - time/date/timestamp/interval

select lower(baker) || ' ' || cast(age as varchar), 60-age as difffrommax, occupation from bakers ;

date
time
timestamp


date + interval -> timestamp
timestamp - timestamp -> interval
date + time -> timestamp

select now()::date ;

baking=> select date '2022-12-12';
    date    
------------
 2022-12-12
(1 row)

baking=> select date '2022-12-12' - date '2022-10-03';
 ?column? 
----------
       70
(1 row)

baking=> select date '2022-10-03' + interval '70 days' ;
      ?column?       
---------------------
 2022-12-12 00:00:00
(1 row)

baking=> select time '12:10:00' - time '08:00:34' ;
 ?column? 
----------
 04:09:26
(1 row)

baking=> SELECT age
FROM bakers
WHERE age >30;
 age 
-----
  33
  36
  33
  47
  60
  56
(6 rows)

baking=> SELECT DISTINCT age
FROM bakers
WHERE age >30;
 age 
-----
  36
  60
  47
  33
  56
(5 rows)


SELECT baker, age
FROM bakers
WHERE age >40;


select * from bakers
where occupation like '%parent%' ;


select * from episodes e
where date '2022-10-03' - e.firstaired > 1450 ;


NULL values

SELECT * From bakers where age > 40 ;

TRUE/FALSE/UNKNOWN

I will only return tuples in which the where clause evaluates to true.

TRUE  OR  UNKNOWN  = TRUE
TRUE  AND UNKNOWN  = UNKNOWN
FALSE  OR  UNKNOWN = UNKNOWN
FALSE  AND UNKNOWN = FALSE
NOT (UNKNOWN)  = UNKNOWN

IS NULL -> to check if a value is null


---------


SELECT
   b.fullname
   , s.episodeid
   , s.make
FROM
   bakers b
   , showstoppers s
WHERE
   b.baker = s.baker
   and b.baker = 'Rahul';

-- all episodes in which someone make a showstopper that has
-- chocolate in it

SELECT
   s.episodeid
FROM
   bakers b
   , showstoppers s
WHERE
   b.baker = s.baker
   and lower(s.make) like '%chocolate%';

-- all episodes where someone made something with chocolate
-- both in showstopper and signature challenges, return episodeid
-- and baker full name


SELECT
   b.fullname
   , sh.episodeid
FROM
   bakers b
   , showstoppers sh
   , signatures si
WHERE
   b.baker = sh.baker
   and sh.baker = si.baker
   and sh.episodeid = si.episodeid
   and lower(sh.make) like '%chocolate%'
   and lower(si.make) like '%chocolate%'   ;