Fun SQL statements for CartoDB

sql_statements.sql

--PART 2
--	a. 
		SELECT * FROM tornados
		SELECT cartodb_id FROM tornados
--	b. 
		SELECT * FROM tornados LIMIT 1
		SELECT * FROM tornados LIMIT 1 OFFSET 1
		SELECT * FROM tornados ORDER BY damage DESC LIMIT 10
--	c. 
		SELECT * FROM tornados WHERE cartodb_id < 30
		SELECT * FROM tornados WHERE damage > 1000 ORDER BY damage ASC
--	d. 
		SELECT count(*) FROM tornados
		SELECT sum(damage) FROM tornados
		SELECT avg(damage) FROM tornados
--PART 3
--	ST_GeometryType
		SELECT st_geometrytype(the_geom) geomtype FROM tornados
		SELECT st_geometrytype(the_geom) geomtype FROM us_counties
		SELECT st_getsrid(the_geom) srid FROM tornados
		SELECT st_getsrid(the_geom_webmercator) srid FROM tornados
		SELECT st_astext(the_geom) astext FROM tornados
		SELECT st_xmax(the_geom) xmax FROM tornados
		SELECT CDB_LatLng(0,0) the_geom
--	Geometry Calculations
		SELECT ST_Area(the_geom) area FROM us_counties
		SELECT ST_NumGeometries(the_geom) n FROM us_counties
		SELECT * FROM us_counties WHERE ST_NumGeometries(the_geom) > 1
		SELECT ST_Distance(the_geom, CDB_LatLng(0,0)) FROM tornados
		SELECT * FROM counties ORDER BY ST_Distance(the_geom, CDB_LatLng(0,0)) ASC LIMIT 1
		SELECT * FROM counties ORDER BY the_geom <-> CDB_LatLng(0,0) ASC LIMIT 1
--	Geography
		SELECT ST_Area(the_geom::geography) area FROM us_counties
		SELECT ST_Area(the_geom::geography)/1000000 area_sqkm FROM us_counties
		SELECT ST_Distance(the_geom::geography, CDB_LatLng(0,0)::geography)/1000 distance_km FROM tornados
--PART 4
		SELECT * FROM tornados WHERE ST_DWithin(the_geom, CDB_LatLng(kansas city), 1)
		SELECT * FROM tornados WHERE ST_DWithin(the_geom::geography, CDB_LatLng(kansas city)::geography, 50000)
		SELECT * FROM us_counties WHERE name = 'Bedford city'
		SELECT count(*) FROM tornados WHERE ST_Intersects(the_geom, (SELECT the_geom FROM us_counties WHERE name = 'Bedford city'))
		SELECT (SELECT count(*) FROM tornados WHERE ST_Intersects(the_geom, u.the_geom)) FROM us_counties u WHERE name = 'Bedford city'
		-- create new column in us_counties 'tornado_count'
		UPDATE us_counties u SET tornado_count = (SELECT count(*) FROM tornados WHERE ST_Intersects(u.the_geom, the_geom))
		-- create new column, normalized tornados 'tornado_sqkm'
		UPDATE us_counties SET tornado_sqkm = tornado_count/(ST_Area(the_geom::geography)/1000000)

SELECT
ST_Intersection(
ST_Union(the_geom_webmercator),
ST_Transform(ST_Buffer(CDB_LatLng(43, -118)::geography, 1000000)::geometry, 3857)) the_geom_webmercator
FROM us_counties
WHERE
ST_Distance(the_geom::geography, CDB_LatLng(43, -118)::geography)/1000 < 1000

tornadoes has an 'e'. i know