SQL CREATE DATABASE Statement

SQL create database statement is used to create a database with the specified name. Before you create tables and insert data into the tables, you need to create a database.

SQL Create Database Statement Syntax and Example:

CREATE DATABASE databaseName;

Here CREATE DATABASE is a keyword which needs to be written as it is, the databaseName is the name of the database that you are creating with this statement. For example, to create a database Employee, you can write the create database statement like this:

CREATE DATABASE Employee;

This statement will create a database “Employee”, if the database doesn’t exist. On successful creation of the database, you will see the following output after executing above statement:

"Database created successfully"

MySQL

CREATE DATABASE my_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

PostgreSQL

CREATE DATABASE my_database
  WITH 
  OWNER = my_owner
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1;

SQL Server

CREATE DATABASE my_database
ON PRIMARY 
( 
    NAME = my_database_data, 
    FILENAME = 'C:\Path\To\Data\my_database_data.mdf', 
    SIZE = 10MB, 
    MAXSIZE = 100MB, 
    FILEGROWTH = 5MB 
)
LOG ON 
( 
    NAME = my_database_log, 
    FILENAME = 'C:\Path\To\Data\my_database_log.ldf', 
    SIZE = 5MB, 
    MAXSIZE = 50MB, 
    FILEGROWTH = 5MB 
);

Error, if database already exists

If a database with the name “Employee” is already present in the system, then this statement will throw the following error.

"Can't create database 'Employee'; database exists.

This is to avoid the duplication of database. A database name must be unique so if the database is already present with a specific name, you cannot create the database with the same name until you drop that existing database.

Verify whether database created successfully

You can verify the successful creation of database using show databases statement.

SHOW DATABASES;

This statement, will list down all the databases that are present on the database system. You can easily check the database that you have created in the list to see whether the create database statement successfully created the database.

Example:

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BeginnersBook      |
| AbcTemp            |
| Employee           |
| Customers          |
| Student            |
| Faculty            |
| MyTest             |
| Demo               |
+--------------------+
8 rows in set (0.00 sec)

As you can see the SHOW DATABASES statement listed all the databases. You can also find the “Employee” database in the above list that we have created earlier using the CREATE DATABASE statement.