Create Data Type And Value Rules

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 New Table With Constraints On What Data Can Be Inserted

%%sql

-- Create a table of criminals
CREATE TABLE criminals

(

-- With a prisoner ID (pid) that is a primary key and cannot be null
 pid    INT PRIMARY KEY     NOT NULL,

-- With a name variable whose default value is John Doe
 name   TEXT                DEFAULT 'John Doe',

-- With an age variable that is an integer and has to be between 0 and 100
 age    INT                 CHECK(0 < age < 100)

);
[]

Add Data To Table

%%sql

INSERT INTO criminals VALUES (412, 'James Smith', 15);
INSERT INTO criminals VALUES (234, 'Bill James', 22);
INSERT INTO criminals VALUES (632, 'Bill Steve', 23);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL);
[]

View Table

%%sql

SELECT *

FROM criminals
pid name age
412 James Smith 15
234 Bill James 22
632 Bill Steve 23
621 Betty Bob None
162 Jaden Ado 49
901 Gordon Ado 32
512 Bill Byson 21
411 Bob Iton None