6.2 Common Table Expressions (CTEs) and Subqueries

6.6

select
    zipcode,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
    and zipcode in (
        select
            zipcode
        from
            nyc_zips
        where
            population > 100000
    )
group by
    zipcode

6.7

select
    zipcode,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
    and zipcode in ('11226', '11368', '11373')
group by
    zipcode

6.8

select
    zipcode,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    zipcode = (
        select
            zipcode
        from
            nyc_zips
        order by
            population asc
        limit
            1
    )
group by
    zipcode

6.9

with lanes as (
    select
        ogc_fid,
        lanecount
    from
        nyc_bike_routes
    order by
        lanecount desc
    limit
        3
)
select
    *
from
    lanes

6.10

with lanes as (
    select
        ogc_fid,
        lanecount
    from
        nyc_bike_routes
    order by
        lanecount desc
    limit
        3
), lanes_2 as (
    select
        ogc_fid,
        lanecount
    from
        nyc_bike_routes
    order by
        lanecount desc
    limit
        3 offset 12
)
select
    *
from
    lanes 

-- the UNION operator allows us to bring 
-- two tables with matching columns together  

union  
select
    *
from
    lanes_2