v Automatically Add Keys To Rows - SQL

Automatically Add Keys To Rows

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.

# Ignore
%load_ext sql
%sql sqlite://
%config SqlMagic.feedback = False

Create Data With 'pid' As An Auto-Generated Primary Key

%%sql

-- Create a table of criminals with pid being a primary key integer that is auto-incremented
CREATE TABLE criminals (pid INTEGER PRIMARY KEY AUTOINCREMENT,
                        name, 
                        age, 
                        sex, 
                        city, 
                        minor);

-- Add a single row with a null value for pid
INSERT INTO criminals VALUES (NULL, 'James Smith', 15, 'M', 'Santa Rosa', 1);
[]

View Table

%%sql

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals
pid name age sex city minor
1 James Smith 15 M Santa Rosa 1

Added More Rows With NULL Values For pid

%%sql

INSERT INTO criminals VALUES (NULL, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (NULL, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (NULL, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Bob Iton', NULL, 'M', 'San Francisco', 0);
[]

View Table

%%sql

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals
pid name age sex city minor
1 James Smith 15 M Santa Rosa 1
2 Bill James 22 M Santa Rosa 0
3 Stacy Miller 23 F Santa Rosa 0
4 Betty Bob None F Petaluma 1
5 Jaden Ado 49 M None 0
6 Gordon Ado 32 F Santa Rosa 0
7 Bill Byson 21 M Santa Rosa 0
8 Bob Iton None M San Francisco 0