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 ---