Creating a Table

Definition: The CREATE TABLE statement is used to define the structure of a new table and specify the types of data it will store. In SQL, you cannot store information until you have defined this blueprint.

Why: Beginner SQL curriculums introduce table creation early because it establishes the "schema" (the rules) for your data. By defining columns and datatypes, you ensure that your database remains organized and consistent.

Syntax

To create a table, you must provide a unique table name and a list of columns with their corresponding data types, enclosed in parentheses.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Example

This example creates a table named students with four distinct fields to track student information:

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Explanation

• Table Name: students is the identifier for the entire collection of records.
• INT: This datatype is used for columns like id and age because they store whole numbers (integers).
• VARCHAR(50): This stands for "variable character" and is used for name and city. The (50) limits the text to a maximum of 50 characters to save memory.

Key Notes

• Case Sensitivity: Like database names, table names may be case-sensitive depending on your server's operating system. It is common practice to use lowercase for table names and uppercase for SQL keywords.
• Constraints: You can add extra rules called "constraints," such as PRIMARY KEY (to make a column unique) or NOT NULL (to ensure a column is never left empty).
• Comma Usage: Ensure that every column definition is separated by a comma, except for the very last one before the closing parenthesis.