CREATE

CREATE DATABASE

The CREATE DATABASE command is employed to establish a new SQL database.

Here’s an SQL statement that creates a database named “testDB”:

Example

CREATE DATABASE testDB;

Remember to ensure you have administrative privileges before creating any database. Once a database is created, you can verify its presence in the list of databases using the following SQL command: SHOW DATABASES;

CREATE TABLE

The CREATE TABLE command generates a new table within the database.

Here’s an SQL statement that creates a table named “Persons” with five columns: PersonID, LastName, FirstName, Address, and City:

Example

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

CREATE TABLE Using Another Table

The provided SQL statement generates a new table named “TestTables”, which is a duplication of two columns from the “Customers” table.

Example

CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;

CREATE INDEX

The CREATE INDEX command is utilized to establish indexes within tables, which permit duplicate values.

Indexes facilitate rapid data retrieval from the database. Although users cannot directly observe indexes, they significantly accelerate searches and queries.

This SQL statement establishes an index named “idx_lastname” on the “LastName” column within the “Persons” table.

CREATE INDEX idx_lastname ON Persons (LastName);

When creating an index on a combination of columns, you can specify the column names within parentheses, separated by commas.

CREATE INDEX idx_pname ON Persons (LastName, FirstName);

Keep in mind that the syntax for creating indexes may vary depending on the specific database you’re using. Therefore, it’s essential to verify the syntax for creating indexes in your database.

Remember that updating a table with indexes requires more time than updating a table without indexes because indexes also need to be updated. Therefore, only create indexes on columns that will be frequently used in searches.

CREATE UNIQUE INDEX

The CREATE UNIQUE INDEX command establishes a unique index on a table, ensuring no duplicate values are allowed.

Here’s an example SQL statement that creates an index named “uidx_pid” on the “PersonID” column within the “Persons” table:

CREATE UNIQUE INDEX uidx_pid ON Persons (PersonID);

CREATE VIEW

The CREATE VIEW command generates a view, which is a virtual table derived from the result set of an SQL statement.

The subsequent SQL statement generates a view that retrieves all customers residing in Brazil:

Example

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

CREATE OR REPLACE VIEW

The CREATE OR REPLACE VIEW command modifies a view.

The subsequent SQL statement includes the “City” column in the “Brazil Customers” view:

Example

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

Query The View

We can retrieve data from the aforementioned view using the following query:

Example

SELECT * FROM [Brazil Customers];

CREATE PROCEDURE

The CREATE PROCEDURE command is utilized to establish a stored procedure.

A stored procedure is prewritten SQL code that can be stored for repeated utilization, enabling efficient reuse of the code.

The subsequent SQL statement establishes a stored procedure named “SelectAllCustomers” designed to retrieve all records from the “Customers” table:

Example

CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;

Execute the aforementioned stored procedure in the following manner:

Example

EXEC SelectAllCustomers;