Exercise 39: Creating with SQL

When we talk about the acronym "CRUD" the 'C' stands for "Create" and it doesn't just mean creating tables. It also means inserting data into the tables and using tables and inserts to link tables. Since we need some tables and some data to do the rest of CRUD (Read, Update, Delete) we'll start with learning how to do the most basic creation operations in SQL.

Creating Tables

In the introduction I said that you can do "Create Read Update Delete" operations to the data inside tables. How do you make the tables in the first place? By doing CRUD on the database schema, and the first SQL statement to learn is CREATE:

View Source file sql/ex1.sql Only

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

You could put this all on one line, but I want to talk about each line so it's on multiple ones. Here's what each line does:

• ex1.sql:1: The start of the "CREATE TABLE", which gives the name of the table as person. You then put the fields you want inside parenthesis after this setup.
• ex1.sql:2: An id column, which will be used to exactly identify each row. The format of a column is NAME TYPE, and in this case I'm saying I want an INTEGER that is also a PRIMARY KEY. Doing this tells SQLite3 to treat this column special.
• ex1.sql:3-4: A first_name and a last_name column, which are both of type TEXT.
• ex1.sql:5: An age column that is just a plain INTEGER.
• ex1.sql:6: Ending of the list of columns with a closing parenthesis and then a semi-colon ';' character.