SQL101: Mastering DDL Statements – Create, Alter, and Drop

When you're learning SQL, understanding Data Definition Language (DDL) statements is fundamental. They let you create, modify, or delete database structures. In this session, we'll walk through key DDL statements—Create, Alter, and Drop—and show you how they work in practice. Whether you’re new to SQL or brushing up on the basics, this guide is for you.

What Are DDL Statements?

DDL stands for Data Definition Language, and its statements define the structure of your database. Unlike commands that deal with data, DDL statements help you work on tables, columns, databases, and how they’re organized.

Here’s what we’ll cover:

Create: Adding new databases or tables.
Alter: Modifying existing structures.
Drop: Deleting tables or databases you no longer need.

These commands are all about shaping the "skeleton" of your database. Let’s dive into each one.

Creating a Database in SQL

Starting with the basics, let’s create a database. Think of this as giving your project a clean workspace.

The Create Database Statement

To make a new database, you use the CREATE DATABASE statement. For this session, we created a database called SQL101 specifically for practice. Here's the syntax:

CREATE DATABASE SQL101;

After running the statement, a message like "Command completed successfully" confirms that the database is ready. To view it in SQL Server Management Studio, follow these steps:

Open the Object Explorer pane.
Expand the "Databases" section.
If the new database isn’t visible, right-click "Databases" and select Refresh.

Once you see your database, you’re all set for the next steps.

Creating a Table

Now that the database exists, let’s add a table to store information. Tables are the core of any database, holding rows and columns of data.

The Create Table Statement

To create a table, use the CREATE TABLE statement. Here's an example to define a Customer table in the SQL101 database:

CREATE TABLE Customer (
    CustID INT IDENTITY(1,1) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    BirthDate DATE NULL,
    Gender CHAR(1) NULL
);

What does this mean?

CustID: An integer column that auto-increments and can't have null values.
FirstName: A string column with a max length of 50 characters, also required.
BirthDate: A nullable column restricted to date values.
Gender: A single-character column, also nullable.

Run the query, and you'll see "Command completed successfully." Go back to the database, refresh, and expand the "Tables" section to confirm the table is there. Clicking on the table reveals the columns and their properties.

Modifying a Table with Alter Statements

Sometimes, tables need adjustments. Maybe you forgot a column or need to change data types. That's where the ALTER TABLE statement comes in.

Adding a Column

To add a new column to the Customer table, use this syntax:

ALTER TABLE Customer
ADD MaritalStatus CHAR(1) NULL;

This adds a column MaritalStatus that can hold a single character and accept null values. After running it, refresh the table view to see the new column added.

Modifying a Column

If you need to change a column’s properties, like increasing its length, use ALTER COLUMN. Here’s an example to extend the FirstName column from 50 to 100 characters:

ALTER TABLE Customer
ALTER COLUMN FirstName NVARCHAR(100) NOT NULL;

The data type and nullability can be adjusted in this way. After executing the command, refresh the table view to see the updated column definition.

Dropping a Column

Did you add a column you no longer need? Use ALTER TABLE with DROP COLUMN:

ALTER TABLE Customer
DROP COLUMN Gender;

Running this will remove the Gender column from the table. Check the structure after refreshing to confirm the update.

Deleting a Table with Drop Statements

At some point, you might want to remove a table entirely. The DROP TABLE command makes it simple.

The Drop Table Statement

Deleting the Customer table looks like this:

DROP TABLE Customer;

After executing the query, refresh the "Tables" section. The Customer table will no longer be listed.

Similarly, you can use DROP DATABASE to delete an entire database. Always be cautious when running these commands, as all data and structures within the target will be permanently deleted.

Wrapping It Up

DDL statements are some of the most powerful tools in SQL. They let you build, change, and clean up the very structure of your database. Here's a quick recap of what we covered:

Use CREATE to set up databases and tables.
Use ALTER for modifications, from adding columns to changing data lengths.
Use DROP to delete unnecessary tables or databases.

These commands are foundational to organizing and managing any database system. Practice them, get comfortable with the syntax, and you’ll soon find working with SQL second nature.

Stay tuned for the next session, where we’ll explore DML (Data Manipulation Language) statements like SELECT, INSERT, and DELETE. Ready to take your SQL skills further? Let’s keep learning!