Tip Sheet: SQL

This is a loose aggregation of puzzles I've helped students solve.

Filling one column with values from another

SQL is a very precise language. To fill a column, you're really just updating it from its current value (nothing) to a new value that you've described. So if you have a table called "distances" and a column it it called "miles" you can add a new column, "square_miles" and fill it thus:

UPDATE distances SET square_miles = miles*miles

SQL isn't actually case sensitive, but I want you to stick with the convention of putting commands in ALL CAPS so that when I look over your queries I know what you think is a command and what you think is a name or value from your table. UPDATE, SET, WHERE are all SQL commands. distances and miles and square_miles are terms you introduced when you named your table and columns.

The * asterisk is a funny little character. In an SQL query it means "anything and everything." So SELECT * FROM {table_name} will just select everything in that table. But it is also avialble to us as a mathmatical operator: miles * miles multiplies miles by miles.

Splitting one value into two (or three)

If you've got your data in a MySQL table or a Postgres table or a CartoDB table (which is really a PostGIS table), you can still do a lot of manipulation on it.

Let's say you've got a column of "borocd" values -- plenty of NYC data comes with just that. The first digit (the hundreds) is the boro code, the next two (tens and ones) are the CD. And you need to separate them. You can do that in Excel or Calc with a =RIGHT() function. PostgreSQL is not much different -- it, too, includes string queries that will take n characters from the right or left of a string:

SELECT big_string, RIGHT(big_string,2), LEFT(big_string, 1) FROM {table_name}

You can run that query in CartoDB to see it in action. It does exactly what =RIGHT() and =LEFT() do: it takes two variables -- the string to trim from and the number of characters to grab. If you're happy with the results, you can use an UPDATE query to populate new columns. For now use CartoDB's GUI to add two columns, boro and cd.

UPDATE {table_name} SET boro = LEFT(borocd,1), cd =  RIGHT(borocd,2)

If you get an error, make sure that borocd is a string column. If it isn't, you'll have to "recast" it in the query:

UPDATE {table_name} SET boro = LEFT(borocd::text,1)

The :: is a CAST operator. It's the same as saying:

UPDATE {table_name} SET boro = LEFT(CAST(borocd AS text),1)

Recast isn't a word we use often in common speech, but it just means To reproduce in a new form, or, as a noun, An utterance translated into another grammatical form. So we're taking one form (a number) and translating it to another form (text). If you think about it, it makes perfect sense: first, recast borocd as text. Then take the leftmost one character it and use that to fill boro.

We can even stack queries up:

UPDATE {table_name} SET boro = LEFT(CAST(borocd AS text),1), cd = RIGHT(CAST(borocd AS text), 2)

Mastering the WHERE clause

If you're trying to make your data readable, you probably want something more useful than 1, 2, 3, 4, 5 for your borough names. So try to figure out what this query is doing and then make sure it is what you want:

UPDATE {table_name} SET boro = 'Manhattan' WHERE boro = '1'

Reading Error Messages

Postgres and MySQL do a really good job of telling you what went wrong when a query fails. Sometimes, however, they're a bit cryptic in their explanations if you're not used to their language.

If I run this query: UPDATE {table_name} SET {column_two} = LEFT({column_one},2) and get an error like function left(double precision, integer) does not exist instead of throwing up my hands and declaring it broken, I need to try to take it apart. I was trying to use a function. Two, really: UPDATE and LEFT. The error suggests there's a problem with my LEFT function, that there's no such function that takes a "double precision" and an "integer" as inputs. It would help if I knew what the heck "double precision" means. So look it up.. Now, I know that the LEFT() function exists, but if I look a the documentation it says left(str text, n int) -- LEFT() expects a string, and I gave it a double precision which is a fancy way of saying I gave it a number. So I have to figure out how to give the function a text string instead of a number.

If I get that first bit worked out, I might get a new error: column "{column_two}" is of type double precision but expression is of type text but I can use the same logic to take the statement apart and resolve the error.