9 - SQL Data Definition Language (DDL) — Module Topics

Introduction to SQL DDL

• What is SQL DDL? — Data Definition Language (DDL) is a subset of SQL used to define, create, and manage the structure of database objects.
• DDL vs. Other SQL Sublanguages — SQL is divided into several sublanguages, each serving a distinct purpose in database management.
• The Role of DDL in Database Management — DDL is essential for establishing the blueprint of a database before any data can be stored or queried.
• Database Objects Defined by DDL — DDL commands can create and manage a variety of database objects beyond just tables.
• Why DDL Skills Are Essential for Developers — Proficiency in DDL is a foundational skill for anyone who designs, builds, or maintains relational databases.

The CREATE Command

• Introduction to the CREATE Statement — The CREATE statement is a foundational DDL command used to build new database objects such as databases, schemas, and tables.
• Creating a New Database — Before creating tables, a database must exist as the top-level container for all related schemas and tables.
• Creating a Schema — A schema is a logical namespace within a database that groups related tables and objects together.
• Creating a Table with Column Definitions — The CREATE TABLE statement defines a new table by specifying its name, column names, and associated data types.
• Choosing Appropriate Data Types — Selecting the correct data type for each column is critical to ensuring data integrity, storage efficiency, and query performance.
• Defining a Table Within a Specific Schema — When working with multiple schemas, tables should be created explicitly within the intended schema using dot notation.
• Best Practices for Writing CREATE Statements — Following consistent conventions when writing CREATE statements improves readability, maintainability, and collaboration across database projects.

Data Types and Column Definitions

• Numeric Data Types — SQL provides several numeric data types to store whole numbers and decimal values efficiently depending on the range and precision required.
• Character and String Data Types — String data types are used to store text values such as names, descriptions, and codes, and differ primarily in how they handle length.
• Date and Time Data Types — Date and time data types allow columns to store temporal information such as birth dates, timestamps, and durations in a structured, queryable format.
• Boolean and Binary Data Types — Beyond text and numbers, SQL supports Boolean types for true/false logic and binary types for storing raw data such as images or files.
• Choosing Appropriate Data Types for Data Integrity — Selecting the correct data type for each column is a foundational practice that enforces data integrity by restricting the kind of data that can be stored.
• Defining Columns in a CREATE TABLE Statement — When creating a table, each column definition specifies the column name, its data type, and any additional constraints or default values.

Constraints in SQL

• PRIMARY KEY Constraint — A PRIMARY KEY constraint uniquely identifies each record in a table and ensures no two rows have the same value in the designated column(s).
• FOREIGN KEY Constraint — A FOREIGN KEY constraint creates a relational link between two tables by referencing the PRIMARY KEY of another table, enforcing referential integrity.
• NOT NULL Constraint — The NOT NULL constraint ensures that a column cannot store a NULL value, requiring every inserted or updated row to provide a value for that column.
• UNIQUE Constraint — The UNIQUE constraint ensures that all values in a column or combination of columns are distinct across all rows in the table.
• DEFAULT Constraint — The DEFAULT constraint assigns a predefined value to a column automatically when no value is explicitly provided during an INSERT operation.
• Applying Constraints During Table Creation — Constraints can be defined inline with column definitions or at the table level within the CREATE TABLE statement, giving developers flexibility in how rules are structured.

The ALTER Command

• Introduction to ALTER TABLE — The ALTER TABLE command is used to modify the structure of an existing database table without deleting or recreating it.
• Adding Columns with ALTER TABLE — New columns can be added to an existing table using the ADD clause within an ALTER TABLE statement.
• Modifying Existing Columns — The MODIFY or ALTER COLUMN clause (syntax varies by database system) allows you to change a column's data type, size, or default value.
• Dropping Columns with ALTER TABLE — The DROP COLUMN clause removes an existing column and all of its data permanently from the table.
• Adding Constraints with ALTER TABLE — Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK can be added to existing tables using ALTER TABLE.
• Dropping Constraints with ALTER TABLE — Existing constraints can be removed from a table using the DROP CONSTRAINT clause within an ALTER TABLE statement.
• Evolving Table Designs Without Losing Data — A key advantage of ALTER TABLE is the ability to iteratively refine a table's structure as application requirements change, while keeping existing data intact.

The DROP and TRUNCATE Commands

• Introduction to the DROP Command — The DROP command is a DDL statement used to permanently remove an entire database object, such as a table, view, index, or database, from the system.
• Syntax and Usage of DROP — The DROP command follows a straightforward syntax: DROP OBJECT_TYPE object_name, where OBJECT_TYPE specifies what kind of database object is being removed.
• Introduction to the TRUNCATE Command — The TRUNCATE command is a DDL statement used to quickly remove all rows from a table while preserving the table's structure, columns, and constraints.
• Key Differences Between DROP and TRUNCATE — Although both DROP and TRUNCATE remove data, they operate at fundamentally different levels — DROP removes the object entirely while TRUNCATE only clears the rows within it.
• TRUNCATE vs. DELETE: Understanding the Distinction — TRUNCATE is often compared to the DELETE command, but there are important differences in how they operate and when each should be used.
• Risks and Best Practices for DROP and TRUNCATE — Both DROP and TRUNCATE are powerful and potentially destructive commands that require careful consideration before execution, especially in production environments.

Working with Schemas

• What Is a Database Schema? — A database schema is a logical container that groups related tables, views, and other database objects together under a single namespace.
• Creating a Schema — The CREATE SCHEMA statement is used to define a new schema within a database, establishing the namespace under which objects will be organized.
• Using Schema-Qualified Object Names — Objects within a schema are referenced using dot notation, combining the schema name and the object name to form a fully qualified identifier.
• Creating Tables Within a Schema — When creating a table, you can assign it to a specific schema by including the schema name as part of the table name in the CREATE TABLE statement.
• Dropping a Schema — The DROP SCHEMA statement removes a schema from the database, and its behavior depends on whether the schema still contains objects.
• Schema Benefits for Database Organization — Using schemas to structure a database provides significant advantages for maintainability, security, and multi-team development environments.