Topic 13 of 52 · Beginner
Database Commands – CREATE, USE, SHOW, DROP DATABASE
Create, list, select and delete whole databases (schemas).
01Database Commands – CREATE, USE, SHOW, DROP DATABASE
Module 02 – Database & Table Management (DDL)
02What is it?
A database (MySQL also calls it a schema) is a named container for tables, views and other objects. CREATE DATABASE makes a new one, SHOW DATABASES lists them, USE selects the default one for your session and DROP DATABASE deletes it with everything inside.
03Why do we need it?
Projects keep separate databases for each application or layer — e.g. raw, staging and analytics in a data platform, or dev and test copies of an app.
USE saves you from prefixing every table with its database name.
04Syntax
CREATE DATABASE [IF NOT EXISTS] db_name
[CHARACTER SET utf8mb4] [COLLATE utf8mb4_0900_ai_ci];
SHOW DATABASES;
USE db_name;
SELECT DATABASE(); -- which database is selected
DROP DATABASE [IF EXISTS] db_name;This browser sandbox simulates extra databases as in-memory schemas: use db.table names (USE only prints a message). In PostgreSQL a schema lives inside a database, and you switch databases by reconnecting.
05Parameter explanation
- IF NOT EXISTS / IF EXISTS
- Skip silently instead of erroring when the database already exists / doesn't exist.
- db_name
- Database name; unique on the server.
- CHARACTER SET / COLLATE
- Default text encoding and sort rules. utf8mb4 supports all languages and emoji.
- USE
- Sets the default database for unqualified table names.
- DROP DATABASE
- Deletes the database and every object in it. Cannot be undone.
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.MySQL checks whether a database called analytics exists.
- 2.It doesn't, so an empty database is created (a folder on disk in MySQL).
- 3.SHOW DATABASES now lists analytics next to the current database.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
A data platform keeps raw, staging and mart databases. Loaders write to raw.orders, transformations read raw and write staging.orders_clean, and BI tools only get access to mart.
CI pipelines create a throwaway test database, run migrations and tests in it, then DROP DATABASE IF EXISTS at the end.
12Common mistakes
DROP DATABASE company_db; -- on production
Deletes every table and row permanently; there's no ROLLBACK for DDL.
Take a backup first and restrict DROP privileges to admins.
CREATE DATABASE analytics; -- run twice
The second run fails with 'database exists'.
CREATE DATABASE IF NOT EXISTS analytics;
SELECT * FROM raw_employees; -- table is in staging, not selected
Unqualified names resolve in the current database only.
USE staging; or SELECT * FROM staging.raw_employees;
13Important comparisons
| Command | Purpose | Reversible? |
|---|---|---|
| CREATE DATABASE | New empty database | Yes (drop it) |
| SHOW DATABASES | List databases you can see | Read-only |
| USE db | Set session default | Yes |
| DROP DATABASE | Delete database + all objects | No (backup only) |
14Practical exercises
Beginner1. Create a database called reporting only if it doesn't exist.
Reveal solution (15)
Re-runnable thanks to IF NOT EXISTS.
Beginner2. List all databases.
Reveal solution (15)
Shows the databases your account can access.
Beginner3. Show which database is currently selected.
Reveal solution (15)
Returns the default database name (NULL if none).
Intermediate4. Create database hr_archive, then drop it safely.
Reveal solution (15)
IF EXISTS prevents an error if it was already gone.
Intermediate5. Create database sandbox and a table sandbox.notes(id INT, txt VARCHAR(50)); insert one row.
Reveal solution (15)
db.table qualifies the name without USE.
Intermediate6. Create database utf_demo with the utf8mb4 character set.
Reveal solution (15)
utf8mb4 is the safe default for multilingual text.
Advanced7. Copy the Engineering employees into a new database eng as table eng.team and count them.
Reveal solution (15)
CTAS across databases.
Advanced8. Write a CI-style script: create test_db, build a table, verify it, then clean up.
Reveal solution (15)
Create → test → drop leaves no trace.
16Key takeaways
- In MySQL DATABASE and SCHEMA are synonyms.
- Use IF [NOT] EXISTS for re-runnable scripts.
- USE sets the default; db.table works without it.
- DROP DATABASE is permanent — back up first.
17Knowledge check
1. In MySQL, CREATE SCHEMA is…
2. Which shows the current default database?
3. DROP DATABASE inside a transaction can be rolled back.
Interview questions
Database vs schema?
In MySQL they are the same thing. In PostgreSQL, SQL Server and Snowflake a database contains several schemas, which group tables inside it.
How do you safely remove an old database?
Back it up (mysqldump), confirm nothing connects to it, revoke access, then DROP DATABASE IF EXISTS.
Lesson test
Take an 8-question test on “Database Commands – CREATE, USE, SHOW, DROP DATABASE” — concepts, output prediction, bug spotting and interview questions.