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

"""
Explanation: Title: Create Data Type And Value Rules
Slug: create_rules_constraining_data
Summary: Create Data Type And Value Rules 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.
End of explanation
"""


%%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)

);


"""
Explanation: Create New Table With Constraints On What Data Can Be Inserted
End of explanation
"""


%%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);

"""
Explanation: Add Data To Table
End of explanation
"""


%%sql

SELECT *

FROM criminals

"""
Explanation: View Table
End of explanation
"""


