SQL SELECT examples, using the philly2007 table.

-- Finishers from DE
select * from philly2007
where state='DE';

-- number of finishers from each state
select state, count(*) as n 
from philly2007
where state is not null
group by state
having count(*) > 3
order by n desc;

-- name of fastest runner from nc
select p.lname, p.fname 
from philly2007 p
where p.state='NC'
and p.clock_time =  (select min(clock_time)
                     from philly2007 p2 
                     where state='NC');

-- fastest time from each state
select state, min(clock_time)
from philly2007
where state is not null
group by state;

-- Name of fastest runner from each state
select * 
from philly2007 r1
where r1.clock_time= (SELECT min(clock_time) 
                      FROM philly2007 r2
                      WHERE r2.state= r1.state)
ORDER BY state;

-- Name of fastest runner from each state, with full state name
select u.state, p3.lname, p3.fname, p3.clock_time 
from usstates u left outer join (
   select * 
   from philly2007 r1
   where r1.clock_time= (SELECT min(clock_time) 
                         FROM philly2007 r2
                         WHERE r2.state= r1.state)) p3 
ON p3.state=u.stateid 
order by u.state


-- top 10 finishers
select lname, fname, sex, clock_time
from (
  select lname, fname, sex, clock_time, rank() over 
      (order by clock_time) t_rank
 from philly2007)
 where t_rank < 10;
 
 
-- simple inner join with usstate table 
select lname, fname, u.state, clock_time
FROM philly2007 p, usstates u
WHERE p.state= u.stateid
ORDER BY clock_time


-- demonstrate not exists: List of states with no runners in top 200
select * 
from usstates u
where not exists (select state 
              from philly2007 p
              WHERE p.state= u.stateid);

-- demonstrate in: list all runners from either NJ or DE
select * 
from philly2007
where state in ('NJ', 'DE');

-- demonstrate any or all
select * 
from philly2007 p
where p.clock_time < any (select clock_time 
                          from philly2007 p2
                          where p2.state='NJ')
order by clock_time;