-- select-from-where


----------------------------
--slide 23:
----------------------------
--Q1
select * from census

--Q2
select state, county, population from census

--Q3
select county, state, population from census
where state='Maryland'


--------------------------------
-- slide 24: Sorting
--------------------------------
--Q1
select fips, state, population from census
where fips<100
order by population 

--Q2
select fips, state, population from census
where fips<100
order by population desc

--Q3
select fips, state, county, population from census
where fips>100
order by state asc, county desc 

--------------------------------
-- Slide 25: Refining WHERE
--------------------------------
--Q1
select fips, state, population from census
where fips<100
  and  state='Maryland'

--Q2
select fips, state, population from census
where fips<100
  and (state='Maryland' or state='California')

select fips, state, population from census
where fips<100
  and state in ('Maryland','California','Texas')

--Q3
select fips, state, population from census
where fips>=30 and fips<=40

--Q4
select fips, state, population from census
where fips between 30 and 40

--Q5
select fips, state, population from census
where county is NULL

-----------------------------------
-- Slide 26: Expressions in SELECT
-----------------------------------
--Q1
select fips, state, 
  log(population) logPop,
  log(LandArea) logArea 
from census
where fips<100
order by fips 

--Q2
select fips, state, 
  log(population) logPop,
  log(LandArea) logArea 
from census
where fips>100
order by population 

--Q3
select fips, state, county,
  log(population) logP,log(LandArea) logArea, 
  log(population)-log(LandArea) logDens 
from census
where fips>100
order by population

-----------------------------------
-- Slide 27: Expressions in WHERE
-----------------------------------
--Q1
select fips, state, county,
  log(population) logP,log(LandArea) logA,
  log(population)-log(LandArea) logD
from census
where fips>100
  and log(population)-log(LandArea)-1.0331*log(Population) + 6.7577>1.7

--Q2
select fips, state, county, logP, logA, logD, logD-trendline delta
from (
	select fips, state, county,
	  log(population) logP,log(LandArea) logA,
	  log(population)-log(LandArea) logD,
	  1.0331*log(Population) - 6.7577 trendline
	from census
	where fips>100
) x
where x.logD -x.trendline>1.7




























-- show population density vs population for the states

select fips, State, log(Population) logPopulation, log(Population)-log(LandArea) logDensity
from census
where fips<100

-- show population density vs. population in the counties

select fips, State, log(Population) logPopulation, log(Population)-log(LandArea) logDensity
from census
where fips>100

select sum(Population)
from census
where fips>100