Understanding the SQL CREATE TABLE Statement

SQL CREATE TABLE Statement

The CREATE TABLE statement is a fundamental SQL command used to create a new table within a database. It allows you to specify the table name, define its columns, and assign a data type to each column. You can also apply constraints such as NOT NULL, PRIMARY KEY, and UNIQUE to maintain data integrity.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Basic Syntax

Here is the general syntax for creating a table:

CREATE TABLE table_name (
    column1 data_type constraint,
    column2 data_type constraint,
    column3 data_type constraint,
    ...
    columnN data_type constraint
);

Key Points

• Table Name: Every table should have a unique name.
• Columns and Data Types: Each column requires a name and a data type that defines the kind of data it can store, such as INT, VARCHAR, or DATE.
• Constraints: Constraints such as NOT NULL and PRIMARY KEY can be used to enforce rules on the data stored in the table.

Note: Data types can vary between database systems. For example:

• Oracle uses NUMBER for numeric values.
• MySQL uses INT for integer values.

Example: Creating a STUDENTS Table

The following example creates a STUDENTS table with ID defined as the primary key:

CREATE TABLE STUDENTS (
    ID INT NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(25),
    PRIMARY KEY (ID)
);

To verify the structure of the newly created table, use the DESC command:

DESC STUDENTS;

The output displays the structure of the STUDENTS table, including its columns, data types, and constraints.

Creating Tables in Different SQL Databases

MySQL Example

CREATE TABLE Employee (
    EmployeeID INT,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Email VARCHAR(255),
    AddressLine VARCHAR(255),
    City VARCHAR(255)
);

Oracle Example

CREATE TABLE Employee (
    EmployeeID NUMBER(10),
    FirstName VARCHAR2(255),
    LastName VARCHAR2(255),
    Email VARCHAR2(255),
    AddressLine VARCHAR2(255),
    City VARCHAR2(255)
);

Microsoft SQL Server Example

CREATE TABLE Employee (
    EmployeeID INT,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Email VARCHAR(255),
    AddressLine VARCHAR(255),
    City VARCHAR(255)
);

Creating a Table Using an Existing Table

You can create a new table by copying the structure and data from an existing table.

Syntax

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM old_table_name
WHERE condition;

Example

CREATE TABLE EmployeeCopy AS
SELECT EmployeeID, FirstName, Email
FROM Employee;

The new EmployeeCopy table will contain the selected columns and rows from the Employee table according to the specified query.

Adding a Primary Key with CREATE TABLE

The PRIMARY KEY constraint uniquely identifies each record in a table.

Single-Column Primary Key (MySQL)

CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255),
    City VARCHAR(255),
    PRIMARY KEY (EmployeeID)
);

Single-Column Primary Key (SQL Server / Oracle)

CREATE TABLE Employee (
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255),
    City VARCHAR(255)
);

YT:- DecodeIT

Composite Primary Key

A PRIMARY KEY can also be defined using multiple columns. The constraint can be given a name for clarity:

CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255),
    City VARCHAR(255),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID, FirstName)
);

Download New Real Time Projects :- Click here

create table in sql with primary key
Understanding the SQL CREATE TABLE Statement
insert into table sql
create table student details in sql
create table and insert data sql
create student table in sql and insert values
SQL CREATE TABLE Statement
sql data types
sql online compiler
create table in sql server
sql create table primary key
sql insert into
sql create table statement example
sql create table statement w3schools
sql create table statement oracle