-----------------------------------------------
-- Lecture 18
-----------------------------------------------

-----------------------------
-- Text searches
-----------------------------
--Q1
select distinct state, county
from StatsC
where county like '%Baltimore%'

--Q2
select distinct state, county
from statsC
where (county like '%York%') or (county like '%City%')

--Q3
select distinct state, county
from statsC
where county in ('Jackson','Wayne')

--Q4
select * 
from censusData
where county is NULL

--------------------------
-- Mathematical Functions
--------------------------
select cast(10 as real)/3

-- Q1:
select dd, log(cases)/log(2.0) logcases
from statsS
where state='Alaska'
order by dd

-- Q2:
select dd, cast(cases as float) cases
from statsC
where county='New York City'
order by dd

--Q3:
select date, sum(cases) cases
from statsS
where dd=74

-------------------------
-- String Functions
-------------------------
--Q1
select county, replace(county,'York', 'Yourk')
from censusData
where county='New York City'

--Q2
select charindex('Y',county)
from censusData
where county='New York City'

--Q3
select state, county, len(county) len
from censusData
where state='Maryland'

--Q4
select state, county
from censusData
where state='Maryland'
  and county like '%''%'

--Q5
select state, county, charindex(' ',county,1) c,
	substring(county,charindex(' ',county,1),
		charindex('''',county,1)-charindex(' ',county,1))
from censusData
where state='Maryland'
  and county like '%''%'


--------------------------------
-- Aggregate Functions
--------------------------------
--Q1
select count(*) c
from statsS
where dd=74
  and cases>0

--Q2
select sum(cases) cases
from statsS
where dd=74

--Q3
select avg(cases) a,  avg(deaths) d
from statsS
where dd=74
  and cases>0

--Q4
select avg(cases) a, stdev(cases) s, 
	min(cases) mn, max(cases) mx
from statsS
where dd=74
  and cases>0

--Q4
select count(distinct state)
from statsC

---------------------
-- GROUP BY
---------------------
--Q1
select state, count(*) c
from statsS
where cases>0
group by state
order by c desc

--Q2
select top 100 min(fips) fips, state, county, min(dd) d10
from statsC
where cases>10
group by state, county
order by d10 asc


--Q3
select top 10 state,  sum(cases) s
from statsC
where dd=74
  and cases>10
group by state
having sum(cases)>1000
order by s desc



-----------------------
-- Subqueries
-----------------------
--Q1
select top 10 *, (logPop-logArea) logDensity
from (
    select fips, state,
		log(LandArea)/log(2.0) logArea,
        log(population)/log(2.0) logPop
    from censusData
) x
order by logPop desc



--Q2
select sum(cases) c, sum(deaths) d
from statsS
where fips in (
	select top 10 fips from censusData
	where county is NULL
	order by population desc
)

select sum(cases) c, sum(deaths) d
from statsS
where fips in (
	select top 10 fips from censusData
	where county is NULL
	order by population asc
)

-----------------------
-- UNION
-----------------------
--Q1
select 'States' n, sum(cases) c, sum(deaths) d 
  from statsS
union all
select 'Counties' n, sum(cases) c, sum(deaths) d 
  from statsC

--Q2
select * from (
	select top 10 'hi' cat, fips from censusData
	where county is NULL
	order by population desc
) x
UNION ALL
select * from (
	select top 10 'lo' cat, fips from censusData
	where county is NULL
	order by population asc
) y


-----------------------
-- JOIN
-----------------------
--Q1
select s.state, s.code, c.population
from censusData c, States s
where c.fips<100
  and c.fips = s.fips
order by code

--Q2
select s.state, s.code, c.population
from censusData c JOIN States s
on c.fips = s.fips
order by code

--Q3
select s.fips, min(d.state) state, 
	min(d.county) county, 
	max(cases) c, avg(d.popDensity) pd
from statsC s, censusData d
where s.fips=d.fips
and d.popDensity>3000
group by s.fips
order by c desc

-----------------------
-- SciServer CasJobs
-----------------------
--Q1
select * from censusData where fips<100

--Q2
select * from states
where state='Maryland'

select count(*) from censusData

--Q3
select  log(population) logP,
		log(popDensity) logD
  into pops
  from COVIDNYT.censusData
  where fips<100
    and population>0
    and popDensity>0

--Q3
select state, count(*) c
from statsS
where cases>0
group by state
order by c desc

--Q4
select state, count(*) c
into MyDB.scounts
from statsS
where cases>0
group by state
order by c desc

--Q5
select top 10 * 
from MyDB.scounts 
order by c desc

--Q6
drop table scounts

--Q7
select * from scounts
where state='Maryland'

update scounts
set c=32
where state='Maryland'