In [2]:
# loading the special sql extension
%load_ext sql
In [1]:
# connecting to a database which lives on the Amazon Cloud
# need to substitute password with the one provided in the email!!!
%sql postgresql://dssg_student:password@seds-sql.csya4zsfb6y4.us-east-1.rds.amazonaws.com/dssg2016
Two interesting tables: seattlecrimeincidents first half of 2015 census_data
In [ ]:
# running a simple SQL command
%sql select * from seattlecrimeincidents limit 10;
In [ ]:
# Show specific columns
%sql select "Offense Type",latitude,longitude from seattlecrimeincidents limit 10;
In [ ]:
%%sql
-- select rows
select "Offense Type", latitude, longitude, month from seattlecrimeincidents
where "Offense Type" ='THEFT-BICYCLE' and month = 1
In [ ]:
%%sql
select count(*) from seattlecrimeincidents;
In [ ]:
%%sql
select count(*) from settlecrimeincidents
In [ ]:
%%sql
select count(*) from (select "Offense Type", latitude, longitude, month from seattlecrimeincidents
where "Offense Type" ='THEFT-BICYCLE' and month = 1) as small_table
In [5]:
# use max, min functions
In [6]:
%%sql
select min(latitude) as min_lat,max(latitude) as max_lat,
min(longitude)as min_long,max(longitude) as max_long
from seattlecrimeincidents;
Out[6]:
In [7]:
%%sql
select year,count(*) from seattlecrimeincidents
group by year
order by year ASC;
Out[7]:
In [8]:
%%sql
select distinct year from seattlecrimeincidents;
Out[8]:
In [ ]:
In [ ]:
In [ ]: