The CREATE TABLE Syntax

The Blueprint: CREATE TABLE

The CREATE TABLE statement is your database's architectural blueprint. It defines the structure or 'buckets' that will hold your data before any actual records are added.

Welcome to the foundation of database design. Before we can store information about movies, we must first build the structure using the CREATE TABLE statement. Think of this as defining the columns or 'buckets' that will hold our data later.

• Defines the table structure, not the data itself.
• Acts as a blueprint for future data entry.
• Requires a name and a list of column definitions.

The Database Blueprint

Defining Structure

The CREATE TABLE statement is your primary tool for defining a database schema. Think of it as a blueprint: you aren't adding records yet, but you are building the 'buckets' that will hold your data.

Welcome to the foundation of database design. Before we can store movies, we need to build the structure. The CREATE TABLE statement is the command we use to define this blueprint. Notice how the blueprint translates into empty 'buckets'. Each bucket has a specific shape, which we call a data type, to ensure only the right information fits inside.

• CREATE TABLE defines the table structure.
• It acts as a blueprint for future data.
• Column names and types are the core components.

Anatomy of the Syntax

Core Syntax Structure

SQL follows a specific sequence. Professional developers use UPPERCASE for keywords to distinguish them from custom names.

CREATE TABLE table_name (
  column1_name data_type,
  column2_name data_type
);

Let's break down the standard structure. We start with the command, followed by our table name. Everything else lives inside these parentheses. Commas separate our columns, and a semicolon marks the end of the thought.

• Keywords like CREATE TABLE should be UPPERCASE.
• The entire column list is wrapped in parentheses.
• Individual columns are separated by commas.

Core Syntax & Conventions

Professional SQL follows specific formatting rules. While engines are often case-insensitive, writing SQL KEYWORDS IN UPPERCASE makes your code readable and maintainable.

Let's look at the standard structure. Notice how we use UPPERCASE for the SQL keywords. Inside the parentheses, we list our columns and their types, separated by commas. Finally, we always end with a semicolon to signal the end of the command.

• Keywords (CREATE TABLE) should be uppercase.
• Column definitions are comma-separated.
• The entire column list is wrapped in parentheses.

Mapping the Movie Table

Translating Requirements

Match our Movie Table requirements to the most efficient SQL data types.

• ID: Unique, up to 65,000.
• Title: Variable length text.
• Rating: Fixed 4-character code.

Excellent choice. That type provides the best balance of storage efficiency and data integrity for that specific requirement. Now, let's look at our Movie database requirements. We need to translate these real-world needs into SQL types. Drag each requirement to its best-fit data type.

• SMALLINT UNSIGNED fits 16-bit positive integers.
• VARCHAR(50) saves space for varying text lengths.
• CHAR(4) is ideal for fixed-length strings like ratings.

Matching Requirements

Building a table requires translating real-world needs into SQL Data Types. Match the requirement to the most efficient type for our Movie database.

Now it's your turn. For our movie database, we have three specific requirements. Drag each data requirement to its most efficient SQL data type. Not quite. Remember that fixed-length codes like 'PG13' differ from varying titles like 'The Matrix'. Excellent choice! That type perfectly balances storage efficiency with data constraints.

• Use SMALLINT UNSIGNED for IDs up to 65,000.
• Use VARCHAR(n) for variable-length text.
• Use CHAR(n) for fixed-length codes.

Build the Movie Table

Use the requirements we've discussed to write a complete CREATE TABLE statement for our 'movies' table. Remember to use UPPERCASE for keywords.

It's time to build. Write the SQL statement to create our 'movies' table. Pay close attention to your commas and parentheses!

• Table name: movies
• Columns: movie_id, title, rating
• Types: SMALLINT UNSIGNED, VARCHAR(50), CHAR(4)

Interactive Code Builder

Step-by-Step Construction

Follow the rules to build the movies table. Watch out for the trailing comma pitfall!

It's time to build. Click the code components in the correct order to assemble the full statement for our movies table. Perfect! You've successfully constructed the statement. Notice there is no comma after the rating column, and the semicolon is in place. Good. Keep going, making sure your punctuation is exactly right.

• Descriptive, plural table names.
• No comma after the final column.
• Semicolon to terminate the statement.

Common Syntax Pitfalls

Even experienced developers make simple mistakes. Be on the lookout for the Trailing Comma and Reserved Keywords.

Watch out for these common errors. The most frequent mistake is the trailing comma after the last column—this will cause a syntax error. Also, avoid naming columns after reserved keywords like 'SELECT' or 'TABLE'.

• The last column must NOT have a comma.
• Avoid using words like TABLE or SELECT as names.
• Always end with a semicolon.

Spot the Syntax Errors

Debugging Practice

This code has three errors. Click the parts of the code that will cause the statement to fail.

Even pros make mistakes. Look at this statement. Can you find the three syntax errors that would prevent this table from being created? Correct! That was a classic mistake. Keep looking for the others.

• Avoid using reserved keywords like TABLE as names.
• Remove trailing commas in column lists.
• Ensure data types follow column names.

Final Table Challenge

Write the Schema

Write a SQL statement to create a table named movies with these columns:
1. movie_id (SMALLINT UNSIGNED)
2. title (VARCHAR(50))
3. rating (CHAR(4))

Final challenge. Type out the full CREATE TABLE statement for our movie database. Remember to use uppercase for keywords and check your commas.

• Correct use of CREATE TABLE syntax.
• Applying the specific data types from the lesson.
• Proper punctuation and termination.

Summary: Building Robust Tables

You now have the tools to define a database schema. By selecting the right types and following syntax rules, you ensure your database is both efficient and functional.

Great work! You've successfully translated data requirements into a functional SQL blueprint. In the next lesson, we will look at how to actually insert data into the structure you just built.

• CREATE TABLE starts the blueprint.
• Matching types to requirements saves space.
• Clean syntax prevents errors.