databases... 

SQL: structured query language.

DDL: data definition language: define database structure (define tables).

   create table -- create a new table (new structure for data).
   alter table (change structure of existing table).
   drop table  -- remove existing table.

DML: data manipulation language: CRUD: create-retrieve-update-delete on data.
   
   insert  -- create new records in database
   select  -- retrieve records
   update  -- update existing records.
   delete  -- delete existing records.


Business object Customer: 

  customer(custid, fname, lname, dob, street1,street2, city, state, zip)

DDL:
  drop table customer;     
  -- some databases also support: drop table if exists customer;
  create table customer (
    custid bigint,
    fname varchar(30),
    lname varchar(30),
    dob date,
    street1 varchar(50),
    street2 varchar(50),
    city varchar(20),
    state varchar(2),
    zip varchar(9)
  );

--alter table to add email column:
alter table customer add email varchar(50);



Postgres primer: http://theparticle.com/cs/bc/dbsys/postgresql.pdf 

# psql -h localhost -U fall2021 -W -d fall2021

insert into customer(custid, fname,lname,dob,state, email) values (1,'John','Doe', '1999-12-31','NY','jdoe@gmail.com');
insert into customer(custid, fname,lname,dob,state, email) values (2,'Jane','Doe', '1999-11-30','NJ','jdoe@mns.com');
insert into customer(custid, fname,lname,dob,state, email) values (3,'Bill','Johnson', '1999-10-30','NJ','bj@mns.com');
insert into customer(custid, fname,lname,dob,state, email) values (4,'Jack','Johnson', '1998-10-30','NJ','jj@gmail.com');

select * from customer;
select * from customer order by custid;


--- now John Doe shows up, and says he lives in NYS.




update customer set state='NY' where custid=1;

--- delete only customer John Doe (there may be more than 1).
delete from customer where fname='John' and lname='Doe';

--- delete all customers:
delete from customer;

--- regular functions....

upper
lower
substr
log
sin/cos
exp
concat
position  --- locates a character in a string.

select fname,lname, concat(upper(substr(fname,1,1)),upper(substr(lname,1,1))) initials from customer;

case statement: 

   case when <condition1>  then <result1>
        when <condition2>  then <result2>
        when <condition3>  then <result3>
        when <condition4>  then <result4>
        else <otherresult>
   end


--label customers who are over 21 vs not; we want a 'Y' or 'N' flag.

select a.*, 
   case when extract(years from age(dob)) >= 21 then 'Y' else 'N' end   
from customer a;



---- Aggregates ---

counts...

select count(*) from customer;

---count customers in NYS

select count(*) from customer where state='NY';

---count number of customers born before 2000-01-01....

select count(*) from customer where dob < '2000-01-01';


---count customers by state, for each state, get count of customers

---group by statement.

select state, count(*) 
from customer
group by state;


--- fraction of customers who live in NYS ?

select 100.0* sum( case when state='NY' then 1.0 else 0.0 end )/sum(1.0) 
from customer;


--- fraction of customers who live in NJ ?

select 100.0* sum( case when state='NJ' then 1.0 else 0.0 end )/sum(1.0) 
from customer;



---count customers by their email domain?

select substr(email,position('@' in email)+1,100) as domain,count(*) from customer group by 1;

---


--HW2

product(productid,description,)
customer(customerid,username,fname,lname,street1,street2,city,state,zip)
purchase(purchaseid,purchasetimestamp,customerid,productid,quantity,price)


create table product(productid bigint,description varchar(10000));

create table customer(customerid bigint,username varchar(100),fname varchar(20),lname varchar(20),
  street1 varchar(10),street2 varchar(10),city varchar(10),state varchar(10),zip varchar(20));




1. What is the description of productid=42?

select description 
from product
where productid=42;


2. What's the name and address of customerid=42?

select fname,lname, street1, street2, city,state,zip 
from customer
where customerid=42;


3. What products did customerid=42 purchase?
4. List customers who bought productid=24?
5. List customer names who have never puchased anything.
....
10. What is the most popular (purchased most often) product in NY state?


---- short preview ---

sub-queries; solve mini-sub-problems then combine them into bigger solutions.

CTEs (common table expressions); if query requires multiple conceptual steps, you can break up the steps into CTEs.