Usage and examples of SQL CREATE keyword

SQL Keyword Reference

CREATE DATABASE

The CREATE DATABASE command is used to create a new SQL database.

The following SQL creates a database named "testDB":

CREATE DATABASE testDB;

CREATE TABLE

The CREATE TABLE command is used to create a new table in the database.

The following SQL creates a table named "Persons", which contains five columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE Persons
(　　　
　　PersonID int,
　　LastName varchar(255),
　　FirstName varchar(255),
　　Address varchar(255),　
　　City varchar(255)　
);

Create a table using another table

The following SQL creates a new table named "TestTables" (this table is a copy of two columns from the "Customers" table):　

　　CREATE TABLE TestTable AS
SELECT customername, contactname
FROM　
　　customers;

CREATE INDEX (Create Index)

The CREATE INDEX command is used to create an index in the table (allowing duplicate values).

Indexes are used to retrieve data from the database very quickly. Users do not see indexes; they are only used to accelerate searches/Query.

The following SQL creates an index named "idx_lastname" on the "LastName" column of the "Persons" table:

CREATE INDEX idx_lastname
ON Persons (LastName);

If you want to create an index on a column combination, you can list the column names within parentheses, separated by commas:

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

Note:The syntax for creating indexes varies between databases. Therefore: please check the syntax for creating indexes in the database first.

CREATE UNIQUE INDEX (Create Unique Index)

The CREATE UNIQUE INDEX command creates a unique index on the table (disallowing duplicate values).

The following SQL creates an index named "uidx_pid" on the "PersonID" column of the "Persons" table:

CREATE UNIQUE INDEX uidx_pid
ON Persons (PersonID);

CREATE VIEW (Create View)

The CREATE VIEW command creates a view.

A view is a virtual table based on the result set of an SQL statement.

The following SQL creates a view selecting all customers from Brazil:

CREATE VIEW [Brazil　
　　Customers] AS
SELECT　
　　CustomerName, ContactName
FROM Customers
WHERE　
　　Country = "Brazil";

CREATE OR REPLACE VIEW (Create or Replace View)

The CREATE OR REPLACE VIEW command updates the view.

The following SQL adds the "City" column to the "Brazil Customers" view:

CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";

Query View

We can query the following views:

SELECT　*　FROM [Brazil　
　　Customers];

CREATE PROCEDURE (Create Stored Procedure)

The CREATE PROCEDURE command is used to create stored procedures.

Stored procedures are saved prepared SQL code that can be reused.

The following SQL creates a stored procedure named 'SelectAllCustomers', which selects all records from the 'Customers' table:

　　CREATE PROCEDURE SelectAllCustomers
AS
SELECT　*　FROM Customers
GO;

Execute the following stored procedure as shown below:

　　EXEC SelectAllCustomers;

SQL Keyword Reference