Try the Complete SQL Course Free!

Using the CREATE Statement in SQL- Structured Query Language for Databases

/ / Latest, SQL

For the complete tutorial, click here

 

 

 

 

 

 

 

 

 

The CREATE Statement

The first statement that you will learn in SQL is the CREATE statement. The CREATE statement is often the first statement that you will execute in SQL if you are using SQL to design a database within a relational database management system.

This statement is commonly used to create a database, table, index, or stored procedure. However, within some implementations, such as “T-SQL,” you will find that the CREATE statement can make many types of database objects, including indexes and schemas.

 

The CREATE DATABASE Statement

Within many relational database management systems, the CREATE DATABASE statement is used to create the database. The core SQL of the statement is shown below.

CREATE DATABASE database_name

In this statement, the “database_name” parameter is the name you want to give to the database. Note that while most versions of SQL will require a semicolon at the end of the statement, some may not. Also, not every vendor implements this SQL command. In Microsoft Access, for example, you create a database using the graphic user interface. SQLite simply uses its own separate command of “sqlite3 database_name” to create a database.

While this is the bare-bones version of the statement, many vendors will also follow this statement with many vendor-specific clauses that will define the specific characteristics of the database. In MySQL 5.7, for example, the statement “CREATE SCHEMA” is a synonym for “CREATE DATABASE.”

The following is a listing of hyperlinks that displays the vendor-specific SQL used and the various clauses available for the CREATE DATABASE statement within MySQL 5.7 and SQL Server 2012. Note that this statement is not available as an SQL statement within Access.

MySQL 5.7:

https://dev.mysql.com/doc/refman/5.7/en/create-database.html

SQL Server 2012:

https://technet.microsoft.com/en-us/library/ms176061.aspx

 

The CREATE TABLE Statement:

One of the most commonly used SQL statements when creating a database is the CREATE TABLE statement, which is used to create tables within a database. The general syntax is shown below.

CREATE TABLE table_name

(

field_namedata_type(size),

field_name1 data_type(size),

field_name2 data_type(size),

)

 

The “table_name parameter is the name you want to assign to the table. The “field_name parameters are the names of the fields, or columns, within the table. The “data_type parameter is the declaration of the data type of each field. Each vendor will have vendor-specific names for the data types available. You should check the documentation for the database management system you are using to determine the specific name of each data type available. For example, in Microsoft Access, a text field with a 255 character limit is declared by the “TEXT” data type, but the same type of field is declared by the “TINYTEXT” data type within MySQL. The “size” parameter, if needed for the “data_type selected, will define the number of characters to store within the field.

While the above CREATE TABLE statement is the core SQL standard, you will find that the statement tends to be more complex within the actual vendor implementations. Below is a listing of hyperlinks that display the SQL used to implement the CREATE TABLE statement within MySQL 5.7, SQL Server 2012, and Access 2013.

MySQL 5.7:

https://dev.mysql.com/doc/refman/5.7/en/create-table.html

SQL Server 2012:

https://technet.microsoft.com/en-us/library/ms174979.aspx

Access 2013:

https://msdn.microsoft.com/en-us/library/office/ff837200.aspx

 

 

TOP