VMware ESXi and vSphere Cluster Management

SQL CREATE DATABASE Statement

Learn how CREATE DATABASE works, including syntax, permissions, naming, DBMS-specific options, connection context, and common errors.

The CREATE DATABASE statement creates a new database in a supported database management system. It is usually one of the first administrative steps before you define tables and store application data.

What CREATE DATABASE Does

A database is an organized container for related data and database objects. Depending on the DBMS, it can contain tables, views, schemas, procedures, functions, indexes, and stored data.

A DBMS, or Database Management System, is the software that stores and manages databases. Common DBMSs include MySQL, PostgreSQL, SQL Server, Oracle Database, and SQLite.

CREATE DATABASE is a DDL statement. DDL, or Data Definition Language, consists of SQL commands that define or modify database structure.

Creating a database is different from creating a table:

• CREATE DATABASE creates the top-level database container.
• CREATE TABLE creates a table inside a database.
• Views, schemas, procedures, and other objects are also created separately.

Basic Syntax

CREATE DATABASE database_name;

• CREATE DATABASE is the statement that requests a new database.
• database_name is a placeholder for the identifier you choose, such as MyDatabase.
• The semicolon is a commonly used SQL statement terminator. Some client tools can execute statements without displaying it, but using it makes statement boundaries clear.

The simple form is broadly recognizable, but optional clauses and administrative behavior vary between DBMS products.

Basic Creation Example

CREATE DATABASE MyDatabase;

If the command succeeds, the DBMS creates an empty database container named MyDatabase. It does not automatically create application tables, columns, rows, views, or procedures.

Tables are created afterward with another DDL statement, for example:

CREATE TABLE Customers (
    CustomerID INTEGER,
    CustomerName VARCHAR(100)
);

The exact table syntax and data types can differ by DBMS. Before running this statement, make sure the session is working in the intended database.

Working in the New Database

Many systems require you to select, connect to, or change the connection context before creating objects. Connection context means the database or server target used by the current SQL session.

Using USE

MySQL and SQL Server support the following general approach:

USE MyDatabase;

After changing context, subsequent table statements normally target MyDatabase. Run USE only in DBMSs that support it; it is not portable SQL.

Using a Client Tool

Many SQL clients let you choose the database in a connection dialog, connection string, object browser, or session setting. After creating the database, open a connection that names the new database, or select it in the client interface.

Connecting Directly in PostgreSQL

PostgreSQL commonly treats the database as part of the connection target. After creating a database, connect to it using the client or connection configuration rather than relying on USE.

Permissions and Administration

Creating a database is usually an administrative operation. The account must have a database-creation privilege, meaning permission granted by the DBMS security model, or must belong to an administrative role that includes that permission.

Permission requirements differ by product and configuration. A normal application account often should not be allowed to create databases in a production environment.

• Use an authorized administrative account when appropriate.
• Request the required privilege from the database administrator if your account is restricted.
• For a managed cloud database service, check whether databases must be provisioned through the provider console, API, deployment system, or administrator account.
• Do not attempt to bypass an insufficient-permissions error by changing unrelated security settings.

An administrative configuration may conceptually look like granting or assigning database-creation privileges through the DBMS security model, but the exact command is product-specific.

Choosing a Database Name

The database name is an identifier assigned to the database. Choose a name that is meaningful, unique in its server or service scope, and consistent with your organization’s naming conventions.

• Prefer simple names containing letters, numbers, and underscores where supported.
• Avoid spaces and unusual special characters.
• Avoid reserved words, which have special meaning in SQL.
• Check the DBMS limit on identifier length.
• Check whether the DBMS treats uppercase and lowercase letters as equivalent or distinct.

Identifier rules vary. If a name contains spaces, reserved words, or special characters, it may require quoting. Quoting syntax is not the same on every platform, so a simple unquoted name is usually the most portable choice.

DBMS-Specific Syntax and Options

The basic name-only syntax is useful for introductory SQL, but database creation is strongly DBMS-specific when you add ownership, storage, encoding, files, or locale settings.

MySQL Character Set and Collation

CREATE DATABASE app_data
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

This example sets default text-handling choices for the database. The appropriate character set and collation depend on the application’s languages, comparison requirements, and organizational standards. These clauses are MySQL-specific and should not be copied unchanged to another DBMS.

PostgreSQL Owner

CREATE DATABASE app_data
  OWNER app_owner;

The app_owner role must already exist. PostgreSQL also supports database options involving encoding, locale, template, and tablespace. These options affect how the database is initialized, who owns it, and where its data is associated with storage.

SQL Server Storage Configuration

SQL Server accepts the simple form:

CREATE DATABASE AppData;

Administrative scripts can additionally define data files, log files, filegroups, initial sizes, and growth behavior. Use those settings when a storage design requires them; otherwise, the simple form is easier to understand and maintain.

Safe Repeated Execution

Running the same creation command twice usually fails because the database already exists. Some DBMSs support a conditional form such as IF NOT EXISTS, but support and exact syntax are DBMS-dependent.

When supported by the target DBMS, a conditional pattern can prevent an error when the database is already present. Do not assume that the same clause works on every platform.

Where conditional creation is unavailable, an administrative script can:

1. Check the DBMS catalog or administration interface for the database name.
2. Create the database only when it is absent.
3. Report or handle the existing-database case explicitly.

Always verify an existing database before changing it. Do not drop an existing database merely to rerun a setup script, because dropping it can destroy data and dependent objects.

Common Errors and Troubleshooting

Table Creation Fails After Database Creation

If CREATE TABLE fails after the database was created, the session may still be connected to a different database. Change the active database with the appropriate command or select the correct database in the client, then confirm the connection context before executing more DDL.

CREATE DATABASE and Later SQL Statements

After the database exists and the session is connected to it, CREATE TABLE is normally the next structural step. You can then define columns, constraints, indexes, views, and other objects.

UNION belongs to a later stage of SQL use. It is a query operation that combines the result sets of compatible SELECT statements. It does not create a database and is unrelated to database provisioning.

Exam-Relevant Notes

• CREATE DATABASE is a DDL statement that creates a database container.
• The general introductory form is CREATE DATABASE database_name;.
• Creating a database does not create tables.
• Database creation commonly requires an administrative privilege.
• USE is supported by systems such as MySQL and SQL Server, but it is not universal.
• PostgreSQL commonly uses a new connection to select the database.
• Optional clauses for character sets, collations, ownership, files, locales, and storage are DBMS-specific.
• IF NOT EXISTS support is not universal.
• SQLite commonly creates or opens a database file through a connection rather than using a server-oriented CREATE DATABASE command.
• UNION combines query result sets; it is not part of database creation.

Next Step

Once you have created and selected the database, continue with the SQL database structure lessons and learn how to define tables and their columns with CREATE TABLE.