Skip to content

CREATE TABLE Statement 🔨

Prerequisites: CREATE DATABASE, Data Types

Mentor's Note: A Database is the house, but Tables are the rooms. Each room has a purpose (Kitchen, Bedroom) and specific furniture (Stove, Bed). Similarly, a Table has a purpose (Users, Orders) and specific columns (Name, Price). 💡

🌟 The Scenario: The Spreadsheet Grid 📊

Think of a Table like an Excel Sheet. - Table Name: The Sheet Name (e.g., "Students"). - Columns: The Headers (Roll No, Name, Age). - Rows: The Data (101, 'Rahul', 15).

You must define the headers before you can add any data.

🎨 Visual Logic: Table Anatomy

classDiagram
    class STUDENTS {
        +INT id (PK)
        +VARCHAR name
        +DATE dob
    }

💻 1. The Basic Syntax

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

Example: Creating a "Students" Table

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    birth_date DATE,
    email      VARCHAR(100)
);

💻 2. Creating with Constraints (Best Practice)

A professional table always has rules (Constraints).

CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,      -- 🆔 Unique ID
    full_name  VARCHAR(100) NOT NULL,-- 👤 Mandatory
    salary     DECIMAL(10, 2),       -- 💰 Money
    join_date  DATE DEFAULT SYSDATE  -- 📅 Auto-fill today
);

💻 3. Creating a Table from Another Table

Sometimes you want to backup data or create a test table with the exact same structure.

-- 📸 Create a copy
CREATE TABLE employees_backup AS
SELECT * FROM employees;

💡 Naming Conventions

  1. Plural vs Singular: users vs user. (Pick one and stick to it. Plural is common for tables).
  2. Snake_Case: order_details is better than OrderDetails.
  3. Prefixes: Avoid prefixes like tbl_users. It's redundant. We know it's a table.

📈 Learning Path