Brooklyn Campus Library - LIU, LLC x Ogbonnayajrakpara, LLC

DDL data definition language

drop table blah;
create table blah (blahid bigint, name varchar(10));


DML data manipulation language

CRUD operations: 
C - create: insert records
R - retrieve: select records
U - update: update records
D - delete: delete records

insert into blah(blahid,name) values (1,'blah');
insert into blah(blahid,name) values (2,'glah');
insert into blah(blahid,name) values (3,'mlah');
insert into blah values (4,'klah');

select * from blah where blahid in (2,3);

update blah set name='qlah' where name='klah';

delete blah where blahid > 10;



avoid update and delete: they lose data.


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

pevents(eid,typ,tim,empid,oid,topping,crust,price,size,custid)

---customer orders pizza:
insert into pevents(eid,typ,tim,empid,oid,topping,crust,price,size,custid)
 values(1,'order',now(),null,2332,'cheese','thin',5.99,'med',123)

---pizza starts being prepared.
insert into pevents(eid,typ,tim,empid,oid)
 values(2,'prepare',now(),2523,2332,,,,)

---in oven
insert into pevents(eid,typ,tim,empid,oid,topping,crust,price,size)
 values(3,'inoven',now(),2624,2332)

---is ready (out of oven)
insert into pevents(eid,typ,tim,empid,oid,topping,crust,price,size)
 values(4,'ready',now(),26445,2332)

---picked up
insert into pevents(eid,typ,tim,empid,oid)
 values(5,'pickedup',now(),2644545,2332)



---what's the average time to prepare a pizza?


with avgtim as (
 select oid,max(tim)-min(tim) as len
 from pevents
 where typ in ('prepare','ready')
 group by oid
)
select avg(len)
from avgtim


--- how many pizzas did customer 23253 order?


--- how many pizzas did customer 23253 pickup?

with custpickups as (
 select oid,sum(case when typ='pickup' then 1 else 0 end) cnt,
  max(custid) custid
 from pevents
 group by oid
 having max(custid)===23253 
)
select sum(cnt)
from custpickups

-----what percentage of the pizzas never gets picked up?

select (1 - sum(case when typ='pickup' then 1 else 0 end)/
 sum(1.0) ) * 100.0 prcnt
from pevents

customer(custid,lname,fname,phone)

---find how many pizzas did john doe order?


select count(*)
from customer c
  inner join pevents e
  on c.custid=e.custid
where c.fname='john' and c.lname='doe' 
  and e.typ='order'


---find customers who never ordered a pizza?

select c.*
from customer c
  left outer join pevents e
  on c.custid=e.custid and e.typ='order'
where e.eid is null   --right table has nothing.


---rank (numeric) topings by popularity.

select topping, dense_rank( count(*) ) over () rnk
from pevents
where typ='order'
group by topping
order by 2 desc


---name of customer who never had a large pizza.

with custind as (
 select custid,
  max(case when size='lrg' then 1 else 0 end) ind
 from pevents
 where typ='order' 
 group by custid
)
select b.lname,b.fname
from custind a
  inner join customer b
  on a.custid=b.custid
where a.ind=0


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

date, interest rates records, region, systemid

for same date, always prefer systemid=MAIN over 
everything else, and always pick region NY over
everything else.

with data as (
select a.*,
  row_number() over (partition By date 
   order by (case when systemid='MAIN' then 1 else 2 end),
            (case when region='NY' then 1 else 2 end) ) rn
from rates
)
select * 
from data
where rn=1