SQL CREATE TABLE tutorial with examples for beginners

CREATE TABLE Statement in SQL – SQL Tutorial for Beginners

The CREATE TABLE statement in SQL is used to create a new table in a database. It defines the table structure, including column names, data types, constraints, and default values.

In this beginner-friendly SQL tutorial, you will learn:

• What CREATE TABLE does

• Syntax of CREATE TABLE

• Common data types

• Adding constraints

• Creating tables with primary & foreign keys

• Real-world examples

• Best practices

🔹 What Is CREATE TABLE?

CREATE TABLE is an SQL command used to create a new table in a database.

Every table contains:

• Columns

• Data types

• Optional constraints (PRIMARY KEY, UNIQUE, NOT NULL, etc.)

🔸 Basic Syntax

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

🔹 Common SQL Data Types

🔸 Example 1: Create a Simple Table

CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100)
);

🔸 Example 2: Create Table with PRIMARY KEY

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(15)
);

🔸 Example 3: Create Table with AUTO_INCREMENT (MySQL)

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200),
    price DECIMAL(10,2)
);

🔸 Example 4: Create Table with NOT NULL and UNIQUE

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    salary INT
);

🔸 Example 5: Create Table with FOREIGN KEY

Link orders to users.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product VARCHAR(100),
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

🔸 Example 6: Create Table with Default Values

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    username VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active'
);

🔸 Example 7: Create Table with CHECK Constraint

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18)
);

🔹 Real-World Use Cases

✔ User registration system

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255)
);

✔ Product inventory system

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(150),
    stock INT,
    price DECIMAL(10,2)
);

✔ E-commerce order management

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(12,2)
);

🔹 Best Practices for CREATE TABLE

✔ Always define a PRIMARY KEY
✔ Use appropriate data types (avoid oversized VARCHAR)
✔ Use NOT NULL when possible
✔ Use FOREIGN KEY for relational data
✔ Add DEFAULT values where needed
✔ Use CHECK constraints for validation

Summary

In this SQL CREATE TABLE tutorial, you learned:

• How to create tables with columns and data types

• How to use constraints (PRIMARY KEY, UNIQUE, NOT NULL)

• How to add relations using FOREIGN KEY

• Real-world examples and best practices

The CREATE TABLE statement is the foundation of building structured databases.