can everyone read this? 


Installing PostgreSQL

http://www.postgresql.org/   "google for postgresql" 

--- define DDL (data definition language) for company db.

-- drop table employee;
create table employee (
    ename varchar(20),
    street varchar(40),
    city varchar(40)
);
create table works (
    ename varchar(20),
    cname varchar(20),
    salary numeric(12,2)
);
create table company(
    cname varchar(20),
    city varchar(40)
);
create table manages (
    ename varchar(20),
    mname varchar(20)
);

--- insert 1 record.
insert into employee(ename,street,city) 
    values ('John Doe','1 Broadway','New York');

-- trick to generate lots of data

create table fname (fname varchar(100));
insert into fname values ('John');
insert into fname values ('Jane');
insert into fname values ('Jack');
insert into fname values ('Jason');
insert into fname values ('Joe');
insert into fname values ('Jackie');
insert into fname values ('Jeremy');
insert into fname values ('Jim');
insert into fname values ('James');
insert into fname values ('Joseph');
insert into fname values ('Jay');

echo "Smith
Johnson
Jones
Jackson
Bush
Obama
Romney
Kerry
Clinton
">lnames.txt

create table lname (lname varchar(100));

\copy lname from 'lnames.txt';

select fname||' '||lname from fname,lname;

create table city (city varchar(20));
insert into city values ('New York');
insert into city values ('Boston');
insert into city values ('Los Angeles');
insert into city values ('San Francisco');
insert into city values ('D.C.');



-- generate "random" employe data 
select 
    a.fname||' '||b.lname ename,
    ''||(random()*100)::int||' '||c.lname||' '||
        (case when random()<0.5 then 'Ave' else 'Street' end),
    city
from fname a,lname b,lname c, city d
where random() < 0.1;


-- dump data into employee table.
insert into employee 
    select 
        a.fname||' '||b.lname ename,
        ''||(random()*100)::int||' '||c.lname||' '||
            (case when random()<0.5 then 'Ave' else 'Street' end),
        city
    from fname a,lname b,lname c, city d
    where random() < 0.1;

-- remove duplicate names
create table employee2 as 
    select ename,max(street) street,max(city) city 
    from employee
    group by ename;

drop table employee;
alter table employee2 rename to employee;

insert into manages
    select a.ename,b.ename
    from employee a, employee b
    where a.ename != b.ename and
        random() < 0.1;
        
insert into works
    select a.ename,
        ''||(case when random()<0.5 then 'Global' else 'Local' end)
        ||' '||c.lname||' '||
        (case when random()<0.5 then 'Corp' else 'LLC' end),
        (50000 + random()*150000)::int
    from employee a,
        lname c
    where random() < 0.2;

insert into company 
    select a.cname,b.city
    from (select distinct cname from works) a,
        city b
;
        
--- Find John Doe's manager's name.

select *
from manages
where ename='John Smith';

--- Find employees whom john doe manages.
select *
from manages
where mname='John Smith';


--- select companies that have exactly 14 employes

select cname,count(*) cnt
from works
group by cname
having count(*)=14

select cname,sum(1) cnt
from works
group by cname
having sum(1)=14



-- (get from finance.yahoo.com: msft.quotes.csv)

create table cts ( 
    tdate date,
    symbol varchar(16),
    open numeric(32,8),
    high numeric(32,8),
    low numeric(32,8),
    close numeric(32,8),
    vol bigint
);


cat msft.quotes.csv |dbload.pl db=blah table=cts 

grab sqlrunner from: http://theparticle.com/sqlrunner

properties used for "blah" db setup above:
cat ~/.sqlrunner/db.properties 

blah_user=alex
blah_pass=12345
blah_dbname=blah
blah_host=localhost
blah_port=5432
blah_url=jdbc:postgresql://&&host:&&port/&&dbname
blah_driver=org.postgresql.Driver



-- what is the max price micorsoft has ever (until 2005) achieved?

select max(high),min(low)
from cts
where symbol='MSFT';

--- find date of high price
select *
from cts
where symbol='MSFT' and high=(select max(high) from cts where symbol='MSFT')


--- find historically good buying opportunities
--- In other words, price is below 2 SD of 20 day moving avearge 
create table msft_prices as 
select a.*,
    avg(close) over (partition by symbol order by tdate rows between 20 preceding and current row) avg20,
    stddev(close) over (partition by symbol order by tdate rows between 20 preceding and current row) sd20
from cts a
where symbol='MSFT'
order by 1,2,3;

select * 
from msft_prices 
    -- current close is less than 2 standard deviations than average
where close - avg20 < -(2*sd20)


--- find all employees who salary is an outlier (>2 sd)

select *
from works a
    natural inner join    -- join on cname; that's only field that's common.
    (
        select cname,avg(salary) avg, stddev(salary) sd
        from works
        group by cname
    ) b
where 
    a.salary > avg+1*sd
    

select a.* 
from (
    select a.*,
        avg(salary) over (partition by cname) avg,
        stddev(salary) over (partition by cname) sd
    from works a
) a
where 
    a.salary > avg+1*sd
    

---