SQL Create Database, Table, and Index

Create a Database

To create a database:

CREATE DATABASE databaseName;

Create a Table

To create a table in a database:

CREATE TABLE tableName ( columnName1 dataType, columnName2 dataType, ...);

Example

This example demonstrates how you can create a table named "Books", with four columns. The column names will be "Title", "Author", "Publisher", and "Year":

CREATE TABLE Books ( Title varchar(200), Author varchar(100), Publisher varchar(100), Year int(4));

The example demonstrates also how you can specify a maximum length for columns.

The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:

Data Type	Description
int(size)	Hold integers only. The maximum number of digits are specified in parenthesis.
decimal(size, d)	Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".
char(size)	Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.
varchar(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.
date	Holds a date.

Create Index

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.

Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.

A Unique Index

Creates a unique index on a table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX indexName ON tableName (columnName);

The "columnName" specifies the column you want indexed.

A Simple Index

Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

CREATE INDEX indexName ON tableName (columnName);

The "columnName" specifies the column you want indexed.

Example

This example creates a simple index, named "BooksIndex", on the "Title" column of the "Books" table:

CREATE INDEX BooksIndex ON Books (Title);

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:

CREATE INDEX BooksIndex ON Books (Title DESC);

If you want to index more than one column you can list the column names within the parentheses, separated by commas:

CREATE INDEX BooksIndex ON Books (Title, Year);

Test your SQL Skills

To test your SQL skills click here.