1.2 DDL: CREATE, ALTER, DROP

DDL means Data Definition Language. It defines the structure of database objects. The three most common words are:

• CREATE: create an object.
• ALTER: change an object's structure.
• DROP: remove an object.

Objects can be tables, indexes, views, functions, and more. This section starts with tables.

CREATE TABLE

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  level VARCHAR(30) NOT NULL,
  published_on DATE
);

This example tries to stay general: integers, strings, dates, primary keys, and NOT NULL constraints are common relational concepts.

Data types are not fully uniform

Common types include:

• Integer: INTEGER.
• Decimal: DECIMAL(p, s).
• String: VARCHAR(n).
• Date: DATE.
• Timestamp: TIMESTAMP.
• Boolean: BOOLEAN, though internal implementation differs in some products.

ALTER TABLE

ALTER TABLE courses
ADD published_on DATE;

This adds a column to an existing table. ALTER support differs noticeably across databases. Changing column types, renaming columns, and adding constraints may use different syntax.

DROP TABLE

DROP TABLE courses;

DROP removes the object itself, not just its rows. In real projects, confirm:

• You are connected to the correct environment.
• Backups exist.
• Views, foreign keys, functions, reports, or jobs do not depend on the table.
• Users of the data have been notified if needed.

Common auto-increment differences

A general design may start as:

course_id INTEGER PRIMARY KEY

But automatic ID generation has obvious product differences.

Product-specific examples:

• MySQL often uses: course_id INTEGER PRIMARY KEY AUTO_INCREMENT
• PostgreSQL often uses: course_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
• SQL Server often uses: course_id INT IDENTITY PRIMARY KEY
• SQLite: has its own rowid behavior around INTEGER PRIMARY KEY.