%LOAD ../../lodes.db

Creating New Tables for Future Use

So far, we’ve mostly just been exploring the data without making any changes to the database. However, there might be times when we might want to create new tables. We can do this using CREATE TABLE. Let’s use a previous example to create a new table.

CREATE TABLE joinedtable AS 
SELECT * FROM ca_wac_2015
LEFT JOIN ca_xwalk 
ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010;

Error: table joinedtable already exists

This should look mostly familiar, since everything after the first line is stuff we’ve already done. The first line creates a new table called joinedtable from the output.

This is a bit of a mess, though. We usually don’t need everything from the tables that we do join, so we can choose what we keep. Let’s create a new table that has just the information we need.

CREATE TABLE joinedtable2 AS 
SELECT a.w_geocode AS blockid, a.c000 AS total_jobs, b.cty AS county 
FROM ca_wac_2015 a
LEFT JOIN ca_xwalk b
ON a.w_geocode = b.tabblk2010;

First, notice that we use aliasing to help make refering to tables easier. That is, in the third and fourth lines, we put “a” and “b” after each table to give it that alias. We can then use “a” and “b” whenever we refer to either table, which makes the SELECT statement easier.

Along those lines, notice that we specify which table each variable was from. If the column name is unique between the two tables (i.e. both tables don’t have a column with the same name), then you don’t need to specify the table as we’ve done. However, if they aren’t unique and both tables have a variable with that name, you need to specify which one you want.

Lastly, we’ve made the table easier to read by changing the name of the variable in the new table, using AS in the SELECT part of the query.

Dropping Tables

Conversely, you can also drop, or delete, tables. We created a table in the previous section that we won’t need, so let’s drop it.

DROP TABLE joinedtable;

DROP TABLE joinedtable2;

You might be tempted to avoid dropping tables since it seems relatively harmless to simply not use the table anymore without dropping them. However, it is important to keep databases clean and consider the amount of space each table takes up.

Checkpoint: Making Tables

Try creating a new table using a query you’ve practiced previously. After you’re done, drop the table.