Creating Your First Table | Lecture 4

Learning databases from scratch. This lecture covers the CREATE TABLE command in SQL. We have our environment set up, and now it’s time to build the container for our data. In a relational database, that container is a table.

The Blueprint: CREATE TABLE

The command to create a new table is CREATE TABLE. When we use this command, we need to define two main things:

1. The name of the table.
2. The columns that will be in the table, along with the type of data each column will hold.

Let’s create a simple address book to store our friends’ names and phone numbers. We’ll call the table Friends

Choosing Data Types

Every column in a table must have a data type. This tells the database what kind of information to expect. For our `Friends` table, we need to store:

• A unique ID for each friend.
• The friend’s name.
• The friend’s phone number.

The most common data types we’ll use are:

• INTEGER: For whole numbers (e.g., 1, 5, 100).
• TEXT: For strings of text (e.g., “Amna Najeeb”, “555-1234”).
• REAL: For numbers with decimal points (e.g., 19.99).
• BLOB: For storing raw data (we won’t use this much).

Writing the Code

Here is the SQL code to create our `Friends` table. Type this into your SQLite tool and press “Run“.

CREATE TABLE Friends (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    PhoneNumber TEXT
);

Let’s break this down:

• CREATE TABLE Friends (...): This tells the database we want to create a new table named `Friends`.
• ID INTEGER PRIMARY KEY: This creates a column named `ID` that will hold whole numbers. The special phrase PRIMARY KEY is very important. It means this column will uniquely identify each row in the table. The database will ensure that no two rows ever have the same ID. It will even automatically generate a new, unique ID for us whenever we add a new friend!
• Name TEXT: This creates a column named `Name` to store text.
• PhoneNumber TEXT: This creates a column named `PhoneNumber` to store text. We use `TEXT` for phone numbers because they often contain characters like `-` or `()` which are not numbers.
• ;: The semicolon marks the end of our SQL command.

Verifying the Table

After you run the command, you won’t see any data as a result. However, the table has been created. In most SQLite tools, you will see the new `Friends` table appear in a list of database objects on the left side of the screen. This confirms your command was successful.

In the Next Lecture…

Our `Friends` table is empty. It’s like an empty filing cabinet drawer. In the next lecture, we will learn how to put data *into* the table using the INSERT command.

Setting Up Our Environment | Lecture 3