Title: Add A Column
Slug: add_a_column
Summary: Add a column in a table in SQL.
Date: 2016-05-01 12:00
Category: SQL
Tags: Basics
Authors: Chris Albon

Note: This tutorial was written using Catherine Devlin's SQL in Jupyter Notebooks library. If you have not using a Jupyter Notebook, you can ignore the two lines of code below and any line containing %%sql. Furthermore, this tutorial uses SQLite's flavor of SQL, your version might have some differences in syntax.

For more, check out Learning SQL by Alan Beaulieu.


In [1]:
# Ignore
%load_ext sql
%sql sqlite://
%config SqlMagic.feedback = False

Create Data


In [2]:
%%sql

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);


Out[2]:
[]

View Table


In [3]:
%%sql

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals


Out[3]:
pid name age sex city minor
412 James Smith 15 M Santa Rosa 1
234 Bill James 22 M Santa Rosa 0
632 Stacy Miller 23 F Santa Rosa 0
621 Betty Bob None F Petaluma 1
162 Jaden Ado 49 M None 0
901 Gordon Ado 32 F Santa Rosa 0
512 Bill Byson 21 M Santa Rosa 0
411 Bob Iton None M San Francisco 0

Delete Column


In [4]:
%%sql

-- Edit the table
ALTER TABLE criminals

-- Add a column called 'state' that contains text with the default value being 'CA'
ADD COLUMN state text DEFAULT 'CA'


Out[4]:
[]

View Table


In [5]:
%%sql

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals


Out[5]:
pid name age sex city minor state
412 James Smith 15 M Santa Rosa 1 CA
234 Bill James 22 M Santa Rosa 0 CA
632 Stacy Miller 23 F Santa Rosa 0 CA
621 Betty Bob None F Petaluma 1 CA
162 Jaden Ado 49 M None 0 CA
901 Gordon Ado 32 F Santa Rosa 0 CA
512 Bill Byson 21 M Santa Rosa 0 CA
411 Bob Iton None M San Francisco 0 CA