What is CREATE DATABASE?

The CREATE DATABASE statement is used in database systems such as MySQL, SQL Server, and PostgreSQL to create a new database. A database provides a container where tables, views, indexes, and other database objects can be stored.

Important: SQLite does not support the CREATE DATABASE statement. SQLite creates a database when a database file is opened or created. Therefore, the interactive examples below use SQLite-compatible SQL so they can be executed directly in a browser-based SQLite editor.

CREATE DATABASE Syntax

In database systems that support it, the basic syntax is:

CREATE DATABASE database_name;

This syntax is valid in database systems that implement CREATE DATABASE, but it will produce an error in SQLite.

CREATE DATABASE Example

For example, a MySQL database named school_db can be created using:

CREATE DATABASE school_db;

This example should be executed in a database system that supports CREATE DATABASE. It is not a SQLite command.

CREATE DATABASE IF NOT EXISTS

Some database systems support IF NOT EXISTS with CREATE DATABASE. It prevents an error when the database already exists.

CREATE DATABASE IF NOT EXISTS school_db;

The exact support for this syntax depends on the database management system. It should not be run in SQLite.

SQLite Database Creation

SQLite does not use CREATE DATABASE. Instead, a SQLite database is normally represented by a database file. When SQLite opens a new database file, the file can be created automatically.

Because the browser-based SQL editor already provides an SQLite database, you can create tables directly.

DROP TABLE IF EXISTS students;

CREATE TABLE students (
  student_id INTEGER,
  student_name TEXT,
  course TEXT
);

INSERT INTO students VALUES
  (1, 'Aarav', 'Computer Science'),
  (2, 'Meera', 'Data Science'),
  (3, 'Kabir', 'Web Development');

SELECT *
FROM students
ORDER BY student_id;

Creating a SQLite Database with a Table

In SQLite, the practical equivalent of starting a new database is to open or create a database file and then create the required tables. The following SQL demonstrates the table portion of that process.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL
);

INSERT INTO products (product_name, price) VALUES
  ('Wireless Mouse', 899.00),
  ('USB Keyboard', 1299.00),
  ('Laptop Stand', 2199.00);

SELECT *
FROM products
ORDER BY product_id;

Creating Multiple Tables in SQLite

A SQLite database can contain multiple related tables. The following example creates a small library database structure.

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
  author_id INTEGER PRIMARY KEY,
  author_name TEXT NOT NULL
);

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO authors VALUES
  (1, 'Ravi Kumar'),
  (2, 'Anita Sharma');

INSERT INTO books VALUES
  (101, 'Learning SQL', 1),
  (102, 'Database Fundamentals', 2),
  (103, 'SQL in Practice', 1);

SELECT
  books.title,
  authors.author_name
FROM books
JOIN authors
  ON books.author_id = authors.author_id
ORDER BY books.book_id;

Checking SQLite Tables

SQLite provides the sqlite_master system table, which can be queried to inspect objects such as tables and indexes.

DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT
);

SELECT
  name,
  type
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;

CREATE DATABASE in Different SQL Systems

CREATE DATABASE vs CREATE TABLE

Important Points

• CREATE DATABASE creates a new database in systems that support the statement.
• SQLite does not support CREATE DATABASE.
• SQLite databases are commonly represented by database files.
• Tables are created inside a database using CREATE TABLE.
• Database creation syntax can vary between database management systems.
• Always check the documentation for the specific SQL database system you are using.

Best Practices

• Choose a clear and meaningful database name.
• Use consistent naming conventions for databases and tables.
• Check whether the database already exists before creating it when supported.
• Understand the differences between your SQL database system and SQLite.
• Plan tables and relationships before adding application data.