Creating a table in SQL

In SQL, there are two ways to create a table:

• Creating from scratch

• Creating by doing some data manipulations on an already existing table

In this tutorial we shall be covering both the aspects.

Creating a new table from scratch

Following syntax is used to create a new table with n columns from scratch in a database:

Syntax:

CREATE TABLE table_name(
column_name_1 data_type constraint,
column_name_2 data_type constraint,
column_name_3 data_type constraint,
...
column_name_n data_type constraint);

Adding a constraint in CREATE TABLE are optional. They can later on be added using ALTER TABLE. Task: Create a new table with no constraints:

CREATE TABLE mytable(
CustName varchar(100),
Gender char(1),
Age numeric,
City varchar(100),
Salary	numeric); 

To add constraints to this table using ALTER TABLE refer to this tutorial: Task: Create a new table with constrains added in CREATE TABLE. Note that CustName is our PRIMARY KEY, Age column has a NOT NULL constraint and City column has a UNIQUE constraint. To understand more about these constraints you can refer to the tutorial:

CREATE TABLE mytable2(
CustName varchar(100) PRIMARY KEY,
Gender char(1),
Age numeric NOT NULL,
City varchar(100) UNIQUE,
Salary	numeric);

Alternatively, you can define PRIMARY KEY CONSTRAINT at the end of the CREATE TABLE as follows: Syntax:

CREATE TABLE mytable(
col1 datatype1,
col2 datatype2,
...
col n datatypeN,
PRIMARY KEY (list of columns for primary key));

CREATE TABLE mytable3(
CustName varchar(100),
Gender char(1),
Age numeric NOT NULL,
City varchar(100) UNIQUE,
Salary	numeric,
PRIMARY KEY (CustName));

Creating a new table derived from an already existing table

Suppose you have an already existing dataset, you want to do some manipulations in it and save the results in another dataset. For this, following syntax can be used:

Microsoft SQL Server Syntax:

SELECT * INTO new_table name FROM
(your SQL query of data manipulation in already existing table) as alias_name

Task: Copy all the rows from patient_info for Cardiology department into a new table

SELECT * INTO new_table FROM
(SELECT * FROM patient_info
WHERE department = 'Cardiology')as a

MySQL Server Syntax:

CREATE TABLE new_table name AS
(your SQL query of data manipulation in already existing table) as alias_name

Task: Copy all the rows from patient_info for Cardiology department into a new table

CREATE TABLE new_table AS
(SELECT * FROM patient_info
WHERE department = 'Cardiology')as a