Understanding the SQL CREATE TABLE Statement

SQL CREATE TABLE Statement

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

Complete Advance AI topics:- CLICK HERE
Complete Python Course with Advance topics:-Click here

Basic Syntax

Here’s 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 must have a unique name.
Columns and Data Types: Each column requires a name and a data type that specifies the kind of data it will hold (e.g., INT, VARCHAR, DATE).
Constraints: Constraints like NOT NULL and PRIMARY KEY enforce rules on the data in the table.

Note: The data types may vary depending on the database system. For instance:

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

Example: Creating a `STUDENTS` Table

The following example demonstrates how to create a STUDENTS table with ID 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 table’s creation, use the DESC command:

DESC STUDENTS;

The output will display 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 table EmployeeCopy will have the same structure as Employee but will only include the specified columns and rows matching the WHERE condition.

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)  
);

Composite Primary Key:

You can define a PRIMARY KEY on multiple columns and name the constraint 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

Post Views: 728