SQL CREATE & DROP DATABASE: Schema Creation, Encodings & Collations

In relational database systems, a Database (or Schema) is the top-level logical container that houses all related tables, views, stored procedures, triggers, and security permissions. In this class, we learn how to create and delete databases, configure character sets and collations for international multilingual support, and select active databases.

The CREATE DATABASE Statement

To create a new relational database, use the DDL command CREATE DATABASE followed by the desired database identifier:

CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name;

Adding IF NOT EXISTS is a critical software engineering practice. It prevents your initialization and deployment migration scripts from crashing if the database already exists on the target server:

-- Create college management database safely
CREATE DATABASE IF NOT EXISTS veewom_university;

Production Best Practice: Character Sets & Collations

Modern web applications deal with multilingual names, international characters, and emojis. By default, always specify utf8mb4 character encoding and utf8mb4_unicode_ci (case-insensitive) collation:

CREATE DATABASE IF NOT EXISTS veewom_store
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

• CHARACTER SET utf8mb4: Ensures 4-byte true UTF-8 encoding supporting every language (Hindi, Japanese, Arabic, English) plus emojis.

• COLLATE utf8mb4_unicode_ci: Defines how string sorting and comparisons occur. The _ci suffix stands for Case-Insensitive, meaning 'Veewom' matches 'veewom'.

Selecting & Viewing Databases

Once created, you tell your database session which active database to use with the USE statement:

-- Show all existing databases on the server
SHOW DATABASES;

-- Select veewom_university as active working database
USE veewom_university;

-- Check currently active database
SELECT DATABASE();

The DROP DATABASE Statement

The DROP DATABASE DDL statement permanently destroys a database along with all its tables, records, triggers, and indices:

-- Permanently remove obsolete database
DROP DATABASE IF EXISTS old_test_db;

Now that our database container is initialized, let us learn how to define tables with columns using CREATE TABLE!