The CREATE TABLE statement defines a new table in your database. When creating a table, you specify:

• The table name
• Column names and their data types
• Optional constraints for each column
• Table-level constraints like primary keys

Key Concepts:

• Each column must have a name and data type
• Column names should be descriptive and follow naming conventions
• Data types ensure proper storage and validation
• Constraints maintain data integrity

Common Data Types:

• TEXT - For storing strings
• INTEGER - For whole numbers
• REAL - For decimal numbers
• BLOB - For binary data
• NULL - For missing or undefined values

Let's create a simple "customers" table with basic information.

Write a query that creates a table with these columns:

• id (INTEGER)
• name (TEXT)
• email (TEXT)
• age (INTEGER)

Make sure to:

• Use appropriate data types
• Make the id column the primary key
• Ensure name and email cannot be NULL

Tip: Primary keys uniquely identify each row and should auto-increment.

Default values help maintain data consistency when inserting rows. They provide fallback values when no specific value is provided.

Create a "products" table with these columns:

• id (INTEGER, primary key)
• name (TEXT, not null)
• price (REAL, not null, default 0.0)
• in_stock (INTEGER, default 1)
• created_at (TEXT, default CURRENT_TIMESTAMP)

Tips for defaults:

• Use sensible defaults that make logical sense
• CURRENT_TIMESTAMP is great for tracking creation times
• Boolean values are often stored as 0/1 in INTEGER columns

Foreign keys create relationships between tables. They ensure referential integrity by linking rows in different tables.

Create an "orders" table that references the customers table:

• id (INTEGER, primary key)
• customer_id (INTEGER, foreign key to customers.id)
• order_date (TEXT, default to current timestamp)
• total_amount (REAL, not null, default 0.0)

Important Foreign Key Concepts:

• The referenced column must be unique (usually a primary key)
• Foreign keys prevent orphaned records
• They enable JOIN operations between tables
• Consider ON DELETE and ON UPDATE actions

Unique constraints ensure certain columns or combinations of columns have distinct values across all rows.

Create a "categories" table with:

• id (INTEGER, primary key)
• name (TEXT, not null, unique)
• slug (TEXT, not null, unique)
• parent_id (INTEGER, can be null, foreign key to categories.id)

Use Cases for UNIQUE:

• Usernames or email addresses
• Product SKUs or codes
• URL slugs or paths
• Natural keys that should never duplicate

CHECK constraints validate data before it's inserted or updated. They help maintain data quality by enforcing business rules.

Create a "products_v2" table with these validations:

• id (INTEGER, primary key)
• name (TEXT, not null)
• price (REAL, not null, must be positive)
• stock_level (INTEGER, not null, must be >= 0)
• status (TEXT, must be 'active' or 'discontinued')

Benefits of CHECK Constraints:

• Enforce business rules at the database level
• Prevent invalid data entry
• Make data validation consistent across applications
• Document expected value ranges