CAN EVERYONE READ THIS?

   bob          bob@yahoo.com
  ('bob','bob@yahoo.com')


 relational databases

 often support SQL - structured query language

 relational algebra -
   select (filtering)
   project (select columns)
   join ( the join)
   union
   intersection

 SQL implements relational algebra.

 select f1,f2,f3, sqrt(f4+f1) a1   -- projection
 from whatever
 where f3='something'   -- select operation, filtering

 select f1,f2 from whatever
 union
 select f1,f2 from somethingelse

  -- intersection (not really)
 select f1,f2 from whatever
 except
 select f1,f2 from somethingelse

----------------------------------------
DDL: 2 statements to care about:

create table ITEM (
  itemid bigint,
  name varchar(50)
);
drop table ITEM;

 4 sql statements to care about: crud operations

insert
select
update
delete

insert into ITEM(itemid,name) values (123,'thing');

select * from ITEM;

select itemid from ITEM;

select * from item where name like 't%';

update ITEM set name='thung' where itemid=123;

delete ITEM where thingid=123;



----------------------------------------
data costs $$$, so avoid losing it.
in other words, avoid using "delete"

replace delete with some form of update
(update usually implies add another event record).

----------------------------------------
when we do an update,

update ITEM set name='thung' where itemid=123;

avoid using update, because it loses old value.

e.g. add another event record to do the "update"

-----------------------------------------
--- bank account journal.

journal(tdate,tid,accntid,amount);


-- add $100 to account 123
insert into journal(tid,accntid,amount)
   values(1,123,100);


-- lets say we need to modify balance to be $200;

--option 1 (terrible option)
update journal set amount=200
   where tid=1 and accntid=123;

--the good option

insert into journal(tid,accntid,amount)
   values(2,123,100);


--journal(tdate,tid,accntid,amount);

--- what's the balance on 2018-02-05?
--- for account 123


select sum(amount) bal
from journal
where tdate<='2018-02-05' and accntid=123


--- calculate daily closing balances for all accounts
--- (only for days that have transactions).

with daytots as (
   select tdate,accntid,sum(amount) amount
   from journal
   group by tdate,accntid
)
select tdate,accntid,
  sum(amount) over (partition by accntid
     order by tdate) bal
from daytots


--- create table of daily closing balances
--- (same as above, except in a create table).

create table daily_closing_bal as
  with daytots as (
   select tdate,accntid,sum(amount) amount
   from journal
   group by tdate,accntid
  )
  select tdate,accntid,
    sum(amount) over (partition by accntid
     order by tdate) bal
  from daytots
;

-- using daily_closing_bal, calculate daily
-- percentage gain/loss, if not zero or infinity.

create table daily_closing_prcnt as
 with prevbal as (
  select tdate,accntid,bal,
    lag(bal) over (partition by accntid
      order by tdate) lag_bal
  from daily_closing_bal
 )
 select tdate,accntid,
   (bal-lag_bal)/lag_bal as prcnt
 from prevbal
 where lag_bal>0;


--- we want to know the gain/loss between
--- 2017-01-01 and 2018-01-01

select accntid,
--  product( 1 + prcnt )   -- product doesn't exist
  expr( sum( log(1+prcnt) ) ) gainloss
from daily_closing_prcnt
where tdate>='2017-01-01' and tdate<'2018-01-01'
group by accntid


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

customer(cid,name,email)
account(aid,cid,...)

-- each customer may have 0..N accounts.

---what's the customer name of account 235

(123,'bob','bob@yahoo.com')   ---customer

(235,123)    --account

--join (inner join)

select b.name
from account a
   inner join customer b
   on a.cid=b.cid
where a.aid=235

--- inner join conditions can be put into "where"
-- e.g.: bad, don't do this:

select b.name
from account a, customer b
where a.cid=b.cid and
   a.aid=235


------------------------------------
customer(cid,name,email)
account(aid,cid,...)

-- find all customers who don't have an account.

(123,'bob','bob@yahoo.com')   ---customer

(null,null)    --account

-- customer a left outer join account b
--- customer is on the LEFT
--- account is on the RIGHT

select a.cid
from customer a
   left outer join account b
   on a.cid=b.cid
where b.cid is null


----------------------------------------
--- find customers who have more than 10 accounts.

select a.cid
from customer a
   inner join account b
   on a.cid=b.cid
group by a.cid
having count(*) > 10

--- find customers who have <=2 accounts.
---?
select a.cid
from customer a
   left outer join account b
   on a.cid=b.cid
group by a.cid
having count(b.cid) <= 2

--- what's the avg number of accounts per customer?

with custaccntcnt as (
  select a.cid,count(b.cid) cnt
  from customer a
   left outer join account b
   on a.cid=b.cid
  group by a.cid
)
select avg(cnt)
from custaccntcnt



------------------------------------
customer(cid,name,email)
account(aid,cid,...)
journal(tid,aid,tim,amount)

--- current balance of aid=123
select sum(amount) bal
from journal
where aid=123

--- find balance for all accounts of cid=235?
select sum(amount) bal
from account a
   inner join journal b
   on a.aid=b.aid
where a.cid=235

--- find balance of customer with email bob@yahoo.com

select sum(amount) bal
from customer a
   inner join account b
   on a.cid=b.cid
   inner join journal c
   on b.aid=c.aid
where
   a.email = 'bob@yahoo.com'

------------------------------------
-- balances by email host?

 'bob@yahoo.com'
position('@' in 'bob@yahoo.com')  will return 4
(or zero, if not found).

-- chop away the user from email.
--- 'bob@yahoo.com' into 'yahoo.com'

with custdomain as (
 select a.*,
  substr(email,position('@' in email),1000) edomain
 from customer a
)
select edomain,sum(amount) bal
from custdomain a
   inner join account b
   on a.cid=b.cid
   inner join journal c
   on b.aid=c.aid
group by edomain
order by 2 desc


----------------------------------------------
-- everything is an event.
--- something happens during some state...