How to Create a new table in SQL?

Tables are the foundation of any relational database. It is a critical aspect of database design. Here are some key reasons why creating a table is important

• Data Organization : Table organizes data into rows and columns. Allowing you to store, manage and manipulate data efficiently. With a well-structured table, retrieving, updating a specific data becomes simpler through SQL queries.
• Data consistency : Tables help maintain data consistency by
  1. Defining the structure of the data. For example, using data types such as VARCHAR, INT, DATE, etc. It ensures that only valid data is entered.
  2. Defining constraints like NOT NULL, PRIMARY KEY, FOREIGN KEY, and UNIQUE. For example, a PRIMARY KEY constraint ensures that each row in a table has a unique identifier, preventing duplicate records.
• Normalization : The primary goal of normalization is to ensure that the database is well-structured with minimal duplication of data. Which reduces redundancy and ensures efficient storage. A well-designed table allows for the proper normalization of data.
• Data Management : Once data is organized into tables, you can perform CRUD (Create, Read, Update, Delete) operations easily. For example you can read Employee data or update employee data and other operations through simple SQL query.
• Security and Access Control : Tables enable more granular control over who can access or modify specific data. You can set permissions on a table, restricting access to certain users or roles, which improve data security.

CREATE TABLE TableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...
    [TableConstraints]
);

Example

Creating table without Constraints

CREATE TABLE Employees ( 
 EmployeeId INT,
 Name VARCHAR(100),
 Email VARCHAR(100),
 MobileNumber VARCHAR(15),
 JoiningDate DATE,
 Gender VARCHAR(10),
 DOB DATE,
 Address TEXT );

Creating table with Constraints

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    MobileNumber VARCHAR(15),
    JoiningDate DATE NOT NULL,
    Gender VARCHAR(10),
    DOB DATE NOT NULL,
    Address TEXT
);

If you attempt to create a table using a CREATE TABLE query and the table already exists or running CREATE TABLE query multiple time, the database will throw an error.

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    MobileNumber VARCHAR(15),
    JoiningDate DATE NOT NULL,
    Gender VARCHAR(10),
    DOB DATE NOT NULL,
    Address TEXT
);

Error Code: 1050. Table 'Employees' already exists 0.000 sec

CREATE TABLE IF NOT EXISTS TableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...
    [TableConstraints]
);

you can create a new table from an existing table as per your business requirement.

CREATE TABLE NewTableName LIKE ExistingTableName;
               OR
CREATE TABLE NewTableName AS
SELECT * FROM ExistingTableName WHERE 1 = 0;

CREATE TABLE EmployeeV2 LIKE Employees;

Describe EmployeeV2;

Field	        Type	Null	Key	    Default	Extra
EmployeeId	int	NO	PRI		
Name	   varchar(100)	NO			
Email	   varchar(100)	NO	UNI		
MobileNumber  varchar(15) YES			
JoiningDate  date	 NO			
Gender	    varchar(10)	 YES			
DOB	      date	NO			
Address	      text	YES			

CREATE TABLE NewTableName As
SELECT * FROM ExistingTableName;

CREATE TABLE EmployeeV2 AS
SELECT * FROM Employees;

Field	        Type	Null	Key	    Default	Extra
EmployeeId	int	NO	PRI		
Name	   varchar(100)	NO			
Email	   varchar(100)	NO	UNI		
MobileNumber  varchar(15) YES			
JoiningDate  date	 NO			
Gender	    varchar(10)	 YES			
DOB	      date	NO			
Address	      text	YES			

Select * from EmployeeV2

CREATE TABLE newTableName AS
    SELECT column1, column2, column3, ....
    FROM existingTableName;

CREATE TABLE EmployeeV2 AS
    SELECT EmployeeId, Name, MobileNumber, DOB
    FROM Employees;

Describe EmployeeV2;

Field	        Type	Null	Key	    Default	Extra
EmployeeId	int	NO	PRI		
Name	   varchar(100)	NO				
MobileNumber  varchar(15) YES						
DOB	      date	NO		

CREATE TABLE newTableName AS
    SELECT column1, column2,column3,....
    FROM existingTableName
    WHERE <conditions>;