SQL Create Database: A How-To Guide

Creating a database is one of the first things you do when setting up a new project or installing database software.

Whether you’re using Oracle, SQL Server, MySQL, or PostgreSQL (or any other database), you’ll need to create a database at some point.

A database is a collection of tables and other objects. Many database vendors allow you to have multiple databases as part of the overall database engine.

Let’s take a look at the create database process.

SQL Create Database

Here’s a quick reference on how to do it in each database:

Database	Command
MySQL	CREATE DATABASE database_name;
SQL Server	CREATE DATABASE database_name;
PostgreSQL	CREATE DATABASE database_name;
Oracle	N/A – create a schema and assign privileges

You can find more details below.

 

How to Create a Database in SQL

The easiest way to create a new database is to use the CREATE DATABASE command:

CREATE DATABASE database_name;

Add in the name of your database, run the command, and the new database is created.

This statement will work on MySQL, SQL Server, and PostgreSQL.

For example, to create a new database called employee, run this command:

CREATE DATABASE employee;

A new database called employee is now created. You can then create tables and other objects inside this database.

For more details on creating a database in MySQL, check out this guide: How to Create a Database in MySQL.

What about Oracle?

 

Oracle Create Database

The concept of a database exists differently in Oracle.

Each Oracle installation has a single database, and within that database, there can be multiple schemas. Each schema is tied to a user, and the schemas can have tables and database objects.

So, to create a set of related tables and objects, you would create a schema instead of a database.

To do this, the steps are:

1. Create a new user:

CREATE USER newuser IDENTIFIED BY newpassword;

2. Grant permissions to the new user

GRANT CREATE SESSION TO newuser;
GRANT CREATE TABLE TO newuser;
GRANT CREATE VIEW TO newuser;

You may want to grant other permissions as needed, such as creating sequences or synonyms.

3. Create tables

Follow the same steps as normal to create tables.

 

Show Databases

Once you create a database, you can see if it has been created by looking at the list of databases on the system.

The way to do that depends on the database you’re running.

MySQL

To see a list of the databases in MySQL, run this command:

SHOW DATABASES;

It shows a list of all databases:

Database

performance_schema

sys

employee

 

SQL Server

To see a list of the databases in SQL Server, run this command:

SELECT name
FROM master.sys.databases;

This will show a list of all databases.

name

master

tempdb

model

msdb

rdsadmin

employee

 

PostgreSQL

If you’re using a command line, you can use either the \l or \list command to list databases.

If you’re using an IDE such as pgAdmin or DBeaver, you can run this SQL command:

SELECT datname FROM pg_database;

You’ll see a list of all databases:

datname

postgres

employee

 

Oracle

Because individual databases work differently on Oracle, there is no real “show databases” query on Oracle. You can show a list of users by querying dba_users or all_users, but it’s not really the same thing.

 

Check if Database Is Created

Is there a way to create a new database if it doesn’t already exist?

You can sometimes do this with procedures or tables.

It works on some database engines as well.

MySQL

To check if a database exists before creating it in MySQL:

CREATE DATABASE IF NOT EXISTS database_name;

Adding the IF NOT EXISTS is a simple way to do this.

SQL Server

To check if a database exists before creating it in SQL Server:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'database_name')
BEGIN
  CREATE DATABASE database_name;
END;
GO

This will check if the database exists before creating it.

PostgreSQL

To check if a database exists before creating it in PostgreSQL:

SELECT 'CREATE DATABASE database_name'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'database_name')\gexec

According to the manual, the gexec command sends the query buffer to the server. So, it will execute the create database command if it does not exist.

Oracle

Because databases work differently in Oracle, this command isn’t relevant.

 

Conclusion

So, that’s how you can create a new database in different database engines.