Spatial SQL: Code Reference

5.8 Basic SQL Operators

5.33

select
    *
from
    nyc_2015_tree_census
where
    health = 'Fair'

5.34

select
    *
from
    nyc_2015_tree_census
where
    stump_diam > 0

5.35

select
    *
from
    nyc_2015_tree_census
where
    stump_diam :: numeric > 0

5.36

select
    spc_common
from
    nyc_2015_tree_census
where
    spc_common > 'Maple'

5.37

select
    *
from
    public.nyc_311
where
    complaint_type = 'Illegal Fireworks'
    and city = 'BROOKLYN'
limit
    25

5.38

select
    *
from
    public.nyc_311
where
    complaint_type = 'Illegal Fireworks'
    or agency = 'NYPD'
limit
    25

5.39

select
    *
from
    nyc_311
where
    complaint_type IN ('Illegal Fireworks', 'Noise - Residential')
limit
    25

5.40

select
    *
from
    nyc_311
where
    complaint_type IN ('Illegal Fireworks', 'Noise - Residential')
    and descriptor IN ('Loud Music/Party', 'Banging/Pounding')
limit
    25

5.41

select
    *
from
    nyc_311
where
    complaint_type IN ('Illegal Fireworks', 'Noise - Residential')
    and descriptor NOT IN ('Loud Music/Party', 'Banging/Pounding')
limit
    25

5.42

select
    *
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:05:00'

5.43

-- All return true since it matches the exact word

select
    'maple' like '%maple', --true  
    'maple' like 'maple%', --true  
    'maple' like '%maple%' --true  
    
-- The first returns false since the phrase does not end in the word "maple" 
select  
    'maple syrup' like '%maple', --false  
    'maple syrup' like 'maple%', --true  
    'maple syrup' like '%maple%' --true   

-- The second returns false since the phrase does not begin with the word "maple"  

select  
    'red maple' like '%maple', --true  
    'red maple' like 'maple%', --false  
    'red maple' like '%maple%' --true

5.44

select
    spc_common
from
    public.nyc_2015_tree_census
where
    spc_common like '%maple%

5.45

SELECT
    'maple' like 'm___', --false
    'maple' like 'm____', --true 
    'maple' like 'm_____' --false

5.46

select
    *
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_longitude IS NULL

5.47

select
    distinct spc_common
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
limit
    5