Essential SQL CREATE Statements

This section covers fundamental SQL commands for creating database objects: databases, tables, indexes, views, and stored procedures. These commands are essential for setting up and managing your database.

CREATE DATABASE

Creating a New Database

The CREATE DATABASE command creates a new, empty database. You'll need administrator (or equivalent) privileges to use this command.

Syntax


CREATE DATABASE database_name;

Example: Creating the "testDB" Database

Syntax


CREATE DATABASE testDB;

Output


(A new database named 'testDB' is created. You can use SHOW DATABASES; in MySQL (or a similar command for your specific database system) to verify.)

CREATE TABLE

Creating a New Table

The CREATE TABLE command creates a new table within a database. You define the table's name and columns, along with their respective data types.

Syntax


CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
);

Example: Creating the "Persons" Table

Syntax


CREATE TABLE Persons (
  PersonID INT,
  LastName VARCHAR(255),
  FirstName VARCHAR(255),
  Address VARCHAR(255),
  City VARCHAR(255)
);

Output

An empty table named 'Persons' is created with the specified columns and data types.

Creating a Table from Another Table

Creating a Table from a SELECT Statement

You can create a new table by copying data from an existing table using a SELECT statement. This is useful for creating backups or subsets of data.

Syntax


CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name;

Example: Creating "TestTable" from "Customers"

Syntax


CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

Output

A new table named 'TestTable' is created containing 'customername' and 'contactname' from the 'customers' table.

CREATE INDEX

Creating an Index

The CREATE INDEX command creates an index on a table to speed up data retrieval. Indexes create a lookup table that helps the database find data more efficiently. Indexes allow duplicate values.

Syntax


CREATE INDEX index_name ON table_name (column1, column2, ...);

Example: Creating the "idx_lastname" Index

Syntax


CREATE INDEX idx_lastname ON Persons (LastName);

Output

An index named 'idx_lastname' is created on the 'LastName' column of the 'Persons' table.

CREATE UNIQUE INDEX

Creating a Unique Index

The CREATE UNIQUE INDEX command creates a unique index, preventing duplicate values in the specified column(s).

Syntax


CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Example: Creating the "uidx_pid" Index

Syntax


CREATE UNIQUE INDEX uidx_pid ON Persons (PersonID);

Output

A unique index named 'uidx_pid' is created on the 'PersonID' column. Attempts to insert duplicate 'PersonID' values will result in an error.

CREATE VIEW

Creating a View

The CREATE VIEW command creates a view. A view is a virtual table based on the result-set of an SQL statement.

Syntax


CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Example: Creating the "Brazil Customers" View

Syntax


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

Output


(A view named 'Brazil Customers' is created.  It will display customer names and contact names for customers from Brazil when queried.)

CREATE OR REPLACE VIEW

Updating a View

The CREATE OR REPLACE VIEW command updates an existing view or creates it if it doesn't exist.

Syntax


CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Example: Adding a Column to the View

Syntax


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

Output


(The 'Brazil Customers' view is updated to include the 'City' column. If the view didn't exist, it would have been created.)

Querying the View

You can query the view just like a regular table:

Syntax


SELECT * FROM [Brazil Customers];

Output


CustomerName | ContactName | City
----------------------------------
(Data for Brazilian customers from the Customers table will be displayed here.)

CREATE PROCEDURE

Creating a Stored Procedure

A stored procedure is a pre-compiled set of SQL statements. They improve performance and code organization.

Syntax


CREATE PROCEDURE procedure_name
AS
SQL_statements
GO;

Example: SelectAllCustomers Procedure

Syntax


CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Output


(The procedure 'SelectAllCustomers' is created.  No data is directly returned by the CREATE PROCEDURE statement itself.)

Executing a Stored Procedure

Stored procedures are executed using EXEC (or EXECUTE).

Syntax


EXEC procedure_name;

Example


EXEC SelectAllCustomers;

Output


(All rows from the Customers table will be displayed.)