SQL CREATE TABLE Statement

Sure! Creating a table in SQL involves defining the structure of the table, including its columns and their data types. Here's a detailed explanation along with examples:

Basic Syntax

The basic syntax for creating a table in SQL is:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);
  • table_name: The name of the table.
  • column1, column2, ...: The names of the columns in the table.
  • datatype: The type of data the column can hold (e.g., INT, VARCHAR, DATE, etc.).
  • constraints: Optional rules that can be applied to the data in the columns (e.g., PRIMARY KEY, NOT NULL, UNIQUE, etc.).

Example 1: Creating a Simple Table

Let's create a table named employees with four columns: id, first_name, last_name, and hire_date.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);

In this example:

  • id is an integer and is the primary key, meaning it uniquely identifies each row in the table.
  • first_name and last_name are variable character fields with a maximum length of 50 characters, and they cannot be null.
  • hire_date is a date field.

Example 2: Creating a Table with Constraints

Let's create a table named products with some constraints.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

In this example:

  • product_id is an integer and the primary key.
  • product_name is a variable character field with a maximum length of 100 characters and cannot be null.
  • price is a decimal field with 10 digits in total, 2 of which are after the decimal point, and it must be greater than 0.
  • stock_quantity is an integer field with a default value of 0.
  • category_id is an integer field that references the category_id column in the categories table (assuming the categories table exists).

Example 3: Creating a Table with Auto-Increment

Let's create a table named orders where the order_id column auto-increments.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example:

  • order_id is an integer that auto-increments with each new row and is the primary key.
  • order_date is a date field and cannot be null.
  • customer_id is an integer field that references the customer_id column in the customers table (assuming the customers table exists).
  • total_amount is a decimal field with 10 digits in total, 2 of which are after the decimal point.

These examples cover some of the common use cases and constraints you might encounter when creating tables in SQL.



twitter-white sharing button
keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right

Online Learner

At Online Learner, we're on a mission to ignite a passion for learning and empower individuals to reach their full potential. Founded by a team of dedicated educators and industry experts, our platform is designed to provide accessible and engaging educational resources for learners of all ages and backgrounds.

twitter-white sharing button

Disclaimer About Us

Copyright 2023-2024 © All rights reserved.

logo
Frontend
Backend