Introduction to DDL and the CREATE TABLE Syntax

The Blueprint of Data

Welcome to SQL Schema Design

Before you can store a single row of data, you must define the structure that will hold it. Think of a database like a building: you need a blueprint before you start laying bricks. In SQL, we use DDL to create these blueprints.

Welcome to the foundation of database design. Just as an architect draws a blueprint before construction begins, a database developer uses Data Definition Language, or DDL, to define the structure of their tables. Today, you'll learn how to write the code that creates these digital containers.

• DDL defines the structure, not the data itself
• The 'blueprint' analogy helps distinguish schema from records
• CREATE TABLE is the primary DDL command for new structures

What is DDL?

The DDL Toolkit

Data Definition Language (DDL) is the subset of SQL used to manage the structure of database objects.

• CREATE: Build a new object.
• ALTER: Modify an existing structure.
• DROP: Delete an object entirely.

SQL commands are categorized by their purpose. DDL commands focus exclusively on the structure. CREATE builds the table; ALTER modifies it if your requirements change; and DROP removes the table entirely. Remember, DDL changes the box, while other commands change what's inside the box.

• DDL affects the 'blueprint' of the database
• CREATE, ALTER, and DROP are the three core DDL commands
• DDL is distinct from commands that handle individual rows of data

Anatomy of CREATE TABLE

The Syntax Structure

The CREATE TABLE statement follows a strict, logical order. You provide the table name, followed by a list of columns and their data types inside parentheses.

Let's break down the standard ANSI SQL syntax. Every table creation starts with the keywords CREATE TABLE, followed by your chosen name. Inside parentheses, you list your columns. Each column entry must have two parts: the name of the column and its data type, which tells the database what kind of data is allowed.

• Statement starts with the CREATE TABLE keywords
• Parentheses wrap the column definitions
• Each column needs a name and a data type

Mastering Naming Conventions

Professional Naming

Clear names prevent bugs. Practice converting 'messy' names into professional snake_case. Avoid spaces and reserved words.

Professional schemas use clear, predictable naming. Try naming a column for a movie's release date. Type your suggestion in snake_case and see if it meets professional standards.

• Use snake_case (underscores) instead of spaces
• Names should be descriptive (e.g., release_year vs col1)
• Avoid SQL reserved words

Building the Movie Table

The Movie Blueprint

Now, let's build a real-world table for a cinema. We need to store an ID, a Title, and a Rating.

Let's build our Movie table piece-by-piece. First, we add an ID column using SMALLINT, which is perfect for a few thousand movies. Next, the Title uses VARCHAR 50, allowing titles to vary in length. Finally, the Rating uses CHAR 4, because ratings like PG13 are always exactly 4 characters.

• SMALLINT is efficient for IDs up to 65,000
• VARCHAR(50) allows flexible text lengths up to 50 characters
• CHAR(4) is used for fixed-length codes like 'PG13'

Find the Syntax Error

Spot the Pitfall

One of the most common errors for beginners is the trailing comma. Can you find the error in this code block?

Even pros make mistakes. Look closely at this CREATE TABLE statement. Click on the specific character that will cause this code to fail. Not quite. That part of the syntax is actually correct. Look at the very end of the column list. Correct! That trailing comma after the last column definition is a syntax error. SQL expects a closing parenthesis after the final column, not another comma.

• The last column definition must not have a comma
• Parentheses must be closed correctly
• Standard SQL is usually case-insensitive but snake_case is best practice