SQL CREATE DATABASE & CREATE TABLE: Complete DDL Guide

1. Understanding Data Definition Language (DDL)

SQL operations divide into distinct functional sublanguages. While Data Manipulation Language (DML) queries and modifies data records inside tables, Data Definition Language (DDL) defines, alters, and destroys the database structures that hold those records.

DDL statements establish database catalogs, tables, column definitions, data types, indexes, and referential constraints. When you run a DDL statement, the database engine updates its system catalog (data dictionary) to register the new schema definition.

`DDL Execution Behavior`

DDL statements interact with transactions differently depending on the database engine:

Implicit Commit in MySQL & Oracle:DDL commands like CREATE TABLE or ALTER TABLE trigger an automatic COMMIT. You cannot roll back a DDL operation in MySQL.
Transactional DDL in PostgreSQL & SQLite:Both PostgreSQL and SQLite wrap DDL operations inside transactions. If an ALTER TABLE script fails halfway, the entire migration rolls back cleanly.
Exclusive Schema Locks:DDL operations acquire schema-modification locks on target tables, temporarily blocking read and write operations until schema updates complete.

2. CREATE DATABASE Statement

The CREATE DATABASE statement allocates a new logical container for relational tables, views, stored procedures, and access control policies on the database server.

Database Schema Definition

SQL

Understand database containers, character encodings, and catalog registration

`Character Encoding and Collation`

Always declare character encoding when creating databases to prevent text corruption:

utf8mb4 Encoding:Standard UTF-8 in MySQL that stores full four-byte Unicode characters, including international alphabets and emojis.
Collation (utf8mb4_unicode_ci):Defines string comparison and sorting rules. The _ci suffix stands for case-insensitive comparison.
PostgreSQL Database Creation:Created with ENCODING = 'UTF8' and LC_COLLATE = 'en_US.UTF-8' during execution.

3. DROP DATABASE Statement

The DROP DATABASE statement permanently deletes an existing database along with every table, index, record, and permission catalog entry contained within it.

Dropping Databases and Checking Catalogs

SQL

Review active databases before executing irreversible drop operations

`Destructive Command Safeguards`

Executing DROP DATABASE is irreversible on production systems:

No Undo or Recycle Bin:Dropped databases cannot be restored using SQL commands. Recovery requires replaying binary logs from verified disk backups.
Active Connection Terminations:Engines like PostgreSQL reject DROP DATABASE if other clients have active connections open to the target database.
Role Isolation:Restrict DROP permissions exclusively to administrative automation roles rather than general developer credentials.

4. Managing Databases: SHOW DATABASES and USE

Database management systems host multiple distinct databases on a single server instance. Before executing queries, you must inspect available databases and set your active working context.

Operation	MySQL / MariaDB	PostgreSQL	SQLite
List Databases	SHOW DATABASES;	\l or SELECT datname FROM pg_database;	PRAGMA database_list;
Select Context	USE ecommerce_platform;	\c ecommerce_platform	ATTACH DATABASE 'path.db' AS app;
Check Active Database	SELECT DATABASE();	SELECT current_database();	SELECT file FROM pragma_database_list;

Inspecting Current Database Context

SQL

Query the database catalog to verify your active connection environment

`SQLite Database Architecture`

SQLite uses a serverless file-based architecture rather than a daemon process:

File is the Database:A SQLite database is a single disk file (such as app.db). Creating a database means creating a file connection.
No USE Command:Because each SQLite connection opens a specific database file directly, commands like USE and SHOW DATABASES do not exist in SQLite.
Cross-Database Queries:You can attach multiple database files to a single SQLite connection using ATTACH DATABASE 'analytics.db' AS analytics.

5. CREATE TABLE Statement

The CREATE TABLE statement builds a new relational table structure. Each column definition requires a column name, an assigned data type, and optional column-level integrity constraints.

Creating and Populating a Table

SQL

Build a new schema structure, insert initial records, and query calculated inventory totals

`Essential Column Constraints`

Enforce business rules at the schema layer to maintain data integrity:

PRIMARY KEY:Uniquely identifies each record. Automatically applies both UNIQUE and NOT NULL constraints.
NOT NULL:Prevents missing or unknown values from being inserted into required columns.
UNIQUE:Ensures all non-null values across the column are distinct (for example, usernames, email addresses, or product SKUs).
DEFAULT:Supplies a fallback literal or expression when an INSERT statement omits that specific column.

6. CREATE TABLE IF NOT EXISTS

By default, attempting to create a table that already exists produces a fatal error and terminates script execution. Adding the IF NOT EXISTS clause tells the database engine to check the system catalog first and skip creation silently if the table is already present.

Idempotent Table Creation (IF NOT EXISTS)

SQL

Run repeated creation commands safely without triggering table-already-exists errors

`Idempotency in Schema Migrations`

Understanding how IF NOT EXISTS behaves in automated pipelines:

Idempotency Defined:An operation is idempotent if executing it multiple times produces the exact same outcome without errors.
No Schema Comparison:If the table already exists with three columns and your statement defines five columns, IF NOT EXISTS will NOT update or alter the existing table. It skips execution entirely.
Migration Best Practice:Production migration tools (Flyway, Liquibase, Prisma) use versioned migration tracking tables rather than relying solely on IF NOT EXISTS.

7. CREATE TABLE AS SELECT (Copy a Table)

The CREATE TABLE AS SELECT (often abbreviated as CTAS) statement creates a new table and populates it with the result set of an arbitrary SELECT query in a single atomic step.

Copy Pattern	SQL Syntax	Result
Copy Schema and Data	CREATE TABLE backup_users AS SELECT * FROM users;	Duplicate table populated with all existing records
Copy Schema Only (Empty Table)	CREATE TABLE empty_users AS SELECT * FROM users WHERE 1 = 0;	Identical column schema with zero rows inserted
Copy Filtered Aggregation	CREATE TABLE high_earners AS SELECT id, name FROM staff WHERE salary > 90000;	Derived table storing snapshot subset of query results

Creating a Table from Query Results (CTAS)

SQL

Copy selected columns, apply string concatenation, and filter rows into a new independent table

`The CTAS Constraint Trap`

Be aware of what CTAS does NOT copy from the source table:

Indexes Are Lost:CTAS copies column names and inferred data types, but never copies secondary indexes, primary keys, or unique constraints.
Foreign Keys Dropped:Referential integrity constraints referencing other tables are omitted from the target table.
Auto-Increment Lost:Identity and AUTO_INCREMENT properties do not transfer to tables created via CTAS.
Remedy:Use ALTER TABLE to add primary keys and indexes to your new table after CTAS finishes copying data.

8. ALTER TABLE: ADD COLUMN

As application features evolve, you need to extend existing tables without destroying existing data. The ALTER TABLE ... ADD COLUMN command appends a new column to an existing schema.

Adding Columns to an Existing Table

SQL

Add multiple columns with explicit defaults and verify that existing rows update automatically

`Performance Impact of Adding Columns`

Consider table size and database engine when adding columns to large tables:

Constant-Time (O(1)) in Modern Engines:PostgreSQL (11+), MySQL (8.0+), and SQLite update metadata instantly when adding a nullable column or a column with a constant default.
Full Table Rewrites in Legacy Engines:Older engines rewrite every physical row on disk to populate default values, acquiring exclusive table locks that block traffic.
NOT NULL without DEFAULT:Adding a NOT NULL column without a default to an existing populated table causes an error because existing rows would contain NULL.

9. ALTER TABLE: DROP COLUMN

The ALTER TABLE ... DROP COLUMN statement removes a column from the table definition, eliminating both the schema reference and stored values.

Dropping a Column from a Table

SQL

Remove deprecated columns permanently and inspect the updated table schema

`Dependencies and Safe Deprecation`

Dropping a column breaks downstream components that expect its presence:

Broken Views and Queries:Any view, trigger, stored procedure, or application query that references the dropped column will fail immediately with syntax errors.
SELECT * Fragility:Applications that bind query results by column position rather than column name will corrupt or misalign record parsing.
Expand and Contract Pattern:In production, first deploy application code that stops reading and writing the column. Verify zero traffic to that field, then drop the column in a subsequent migration.

10. ALTER TABLE: RENAME COLUMN and MODIFY COLUMN

Schema modifications often require updating existing column names for domain clarity or altering data types to accommodate larger ranges and stricter constraints.

Modification Action	PostgreSQL	MySQL / MariaDB	SQL Server
Rename Column	RENAME COLUMN old TO new;	RENAME COLUMN old TO new;	sp_rename 't.old', 'new', 'COLUMN';
Change Data Type	ALTER COLUMN col TYPE BIGINT;	MODIFY COLUMN col BIGINT;	ALTER COLUMN col BIGINT;
Change Nullability	ALTER COLUMN col SET NOT NULL;	MODIFY COLUMN col INT NOT NULL;	ALTER COLUMN col INT NOT NULL;

Renaming Columns with ALTER TABLE

SQL

Rename existing columns cleanly without losing stored records

`Type Casting and Data Truncation`

When altering a column data type, the engine validates all existing values:

Expanding Is Safe:Altering INT to BIGINT or VARCHAR(50) to VARCHAR(255) succeeds without data loss.
Narrowing Risk:Shortening VARCHAR(255) to VARCHAR(30) aborts if any stored record contains more than 30 characters.
Explicit Casting in PostgreSQL:When changing incompatible types (like VARCHAR to INTEGER), provide an explicit USING expression: ALTER COLUMN code TYPE INT USING code::integer.

11. RENAME TABLE Statement

Tables can be renamed to align with updated domain terminology, reflect entity deprecation, or execute zero-downtime blue-green data migrations.

Renaming Tables with ALTER TABLE

SQL

Rename a table definition and verify access under the new relation name

`Atomic Table Swapping Architecture`

Multi-table rename statements execute as a single atomic transaction in MySQL:

Zero Downtime ETL:You can populate an entire staging table offline, build all indexes, and then execute an atomic rename swap with the production table.
No Intermediary Window:Because both tables swap in a single statement, concurrent client queries never encounter a missing table error.
PostgreSQL Equivalent:In PostgreSQL, wrap two ALTER TABLE ... RENAME TO statements inside a BEGIN ... COMMIT transaction block to achieve atomic swaps.

12. DROP TABLE Statement

The DROP TABLE statement completely removes one or more table definitions, all stored row data, indexes, constraints, triggers, and permissions associated with the table.

Dropping Tables Safely

SQL

Create and drop a table, then verify its removal from the database catalog

`Foreign Key Referential Safeguards`

Foreign key constraints prevent accidental orphaned data during drops:

RESTRICT (Default):If table B has a foreign key referencing table A, dropping table A triggers an immediate error rejecting the drop.
CASCADE:Adding CASCADE drops the target table and automatically removes dependent foreign keys or dependent views in PostgreSQL.
Drop Order Rule:Always drop child tables (tables containing the foreign key) before dropping the parent table (table containing the primary key).

13. TRUNCATE TABLE Statement

The TRUNCATE TABLE statement purges every record from an existing table while keeping the schema structure, column definitions, constraints, and indexes intact for subsequent inserts.

Purging Table Rows (TRUNCATE / DELETE)

SQL

Empty all rows from a table while preserving the schema structure

`How TRUNCATE Operates Internally`

Why TRUNCATE operates substantially faster than DELETE:

Data Page Deallocation:Instead of scanning each row, creating individual rollback entries, and checking triggers, TRUNCATE deallocates the table underlying disk data pages directly.
Identity Counter Reset:In MySQL and SQL Server, TRUNCATE resets the AUTO_INCREMENT identity counter back to its initial seed (typically 1).
Foreign Key Lockout:Most engines disallow TRUNCATE on a table that is referenced by any active foreign key constraint, even if the child table currently contains zero rows.

14. DELETE vs TRUNCATE vs DROP: Direct Comparison

Choosing the correct removal command is fundamental to database performance, transactional safety, and operational reliability. The comparison table below highlights the architectural differences:

Evaluation Factor	DELETE	TRUNCATE	DROP
SQL Sublanguage	DML (Data Manipulation)	DDL (Data Definition)	DDL (Data Definition)
Execution Scope	Specific filtered rows or all rows	All rows simultaneously	Entire table and schema metadata
WHERE Clause Support	Yes (Row-level filtering)	No (Purges whole table)	No (Destroys object)
Speed on Large Tables	Slow (Row-by-row logging)	Fast (Page deallocation)	Fast (Drops catalog entry)
Rollback Support	Always rollbackable in transactions	Rollbackable in Postgres; Not in MySQL	Rollbackable in Postgres; Not in MySQL
Auto-Increment Counter	Maintained (Continues counting)	Reset to starting seed (1)	Table structure deleted
Fires Triggers	Fires ON DELETE triggers	Does not fire triggers	Does not fire triggers
Schema Status Afterward	Table structure intact	Table structure intact	Table completely deleted

`Decision Guide: Selecting the Right Command`

Apply these operational rules when deciding how to clean up data:

Use DELETE:When removing specific rows with WHERE conditions, or when audit triggers must execute on every deleted row.
Use TRUNCATE:When wiping staging tables or test databases where you need immediate page deallocation and reset auto-increment sequences.
Use DROP:When decommissioning a feature, discarding temporary tables, or replacing obsolete schemas permanently.

15. Temporary Tables

Temporary tables store intermediate result sets during complex ETL procedures, batch calculations, or analytical workflows. They exist only for the lifespan of the current database session and automatically vanish when the client disconnects.

Using Temporary Tables for Staging Data

SQL

Build a session-scoped temporary table from an aggregate query and query the staging dataset

`Temporary Tables vs Common Table Expressions (CTEs)`

Choosing between temporary tables and CTEs in analytical queries:

Temporary Tables for Multi-Step Pipelines:Ideal when you need to index the intermediate dataset, query the data across multiple queries in the session, or stage massive data volumes.
CTEs for Single-Statement Readability:Ideal for modularizing a single complex query without writing intermediate tables to disk storage.
Automatic Cleanup:Temporary tables do not require manual DROP TABLE commands at teardown time, as the database engine drops them upon session disconnect.

Knowledge Check: SQL DDL, Databases & Tables

1. What happens to indexes when creating a table with CREATE TABLE AS SELECT?

2. Which command removes all rows from a table while resetting auto-increment counters?

3. Why is TRUNCATE faster than DELETE on large tables?

4. What is the lifecycle scope of a temporary table in SQL?

5. Which clause prevents an error if a target table already exists?

6. What is the primary risk of using DROP TABLE CASCADE?