SQL Keyword CREATE

The CREATE keyword is used along with other keywords to perform all the below operations.

Keyword Description
CREATE DATABASE Creates a new database.
CREATE TABLE Creates a new table.
CREATE INDEX Creates a new index on a table, which allows duplicates.
CREATE UNIQUE INDEX Creates a new unique index on a table, which doesn't allow duplicates.
CREATE VIEW Creates a view, where the view is a virtual table based on the result set of an SQL statement.
CREATE OR REPLACE VIEW Creates or Updates a view.
CREATE PROCEDURE Creates a stored procedure.

CREATE DATABASE

The below SQL statement creates a new database.

Run this on IDE

CREATE DATABASE test_db;

After creating a database, we can use the below SQL to list all the available databases.

SHOW DATABASES;

CREATE TABLE

The below SQL statement creates a new table, with four columns as mentioned below.

CREATE TABLE users (
    id INT,
    name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255)
);

The below SQL statement creates a new table, with data from another table.

CREATE TABLE test AS
SELECT customer_name, address, city
FROM customers;

CREATE INDEX

A table index is used to retrieve the data from a table faster as they speed up the search queries.

Though SEARCH statements become faster, the indexes make the UPDATE statements slow, as they need to update the index as well.

So, make sure to create indexes only on frequently fetched columns.

The below SQL statement creates an index on the table, using a single column.

CREATE INDEX idx_name
ON users (name);

The below SQL statement creates an index on the table, using multiple columns.

CREATE INDEX idx_address
ON users (address, city);

CREATE UNIQUE INDEX

The below SQL statement creates a unique index on the table, using a single column.

CREATE UNIQUE INDEX uidx_pid
ON users (id);

The below SQL statement creates a unique index on the table, using multiple columns.

CREATE UNIQUE INDEX uidx_user
ON users (id, name);

CREATE VIEW

The below SQL statement creates a view, containing all the customers from Spain.

CREATE VIEW [Spain Customers] AS
SELECT customer_name, address
FROM customers
WHERE country = "Spain";

After successful execution, the view data can be retrieved using the below statement.

SELECT * FROM [Spain Customers];

CREATE OR REPLACE VIEW

The below SQL statement creates (if not existing) or updates (if already exists) a view.

  • It will add another column "city" to the view created in the earlier statement.
CREATE OR REPLACE VIEW [Spain Customers] AS
SELECT customer_name, address, city
FROM customers
WHERE country = "Spain";

CREATE PROCEDURE

A stored procedure is a prepared SQL query, that can be saved and reused anytime. We usually create procedures for the most frequently used SQL queries.

The below SQL creates a stored procedure.

CREATE PROCEDURE all_customers
AS
SELECT * FROM customers
GO;

We can execute the stored procedure using its name as below.

EXEC all_customers;

Related Links