5. DDL — Building Your First Database

What DDL Actually Is

SQL commands are divided into categories based on what they operate on. DDL — Data Definition Language operates on the structure of the database. It builds the skeleton. It does not put data inside the skeleton — that is a different category called DML, which you will learn in Module 7.

Think of it this way. A DDL command is like an architect drawing a building blueprint and then constructing the walls, rooms, and doors. A DML command is like the people who later move their furniture into those rooms.

The three DDL commands you will use most are:

CREATE — builds something new. A new database. A new table. A new index.

ALTER — modifies something that already exists. Add a column. Change a data type. Add a constraint to an existing table.

DROP — permanently deletes something. A table. A database. An index.

These three commands control the entire structure of your database system.

Opening SSMS and Getting Ready

Before writing any SQL, you need to be properly set up in SSMS. Follow these steps exactly.

Open SQL Server Management Studio from your Start Menu. When the Connect to Server dialog appears, make sure Server Type is set to Database Engine, Server Name is localhost or .\SQLEXPRESS, and Authentication is set to Windows Authentication. Click Connect.

Once connected, look at the left side of SSMS. You will see the Object Explorer panel — a tree of folders showing everything on your server. You will spend a lot of time here. Expand the Databases folder by clicking the small arrow next to it. Right now you will see only system databases: master, model, msdb, and tempdb. These belong to SQL Server itself. Never modify them.

Now click the New Query button at the top of SSMS. A blank white editor window opens. This is where you will type every command in this module. After typing each command, press F5 to execute it.

CREATE DATABASE — Creating Your Workspace

The very first command you need is CREATE DATABASE. You cannot create tables without a database to put them in. Think of the database as the folder, and tables as the files inside that folder.

In your New Query window, type this command:

CREATE DATABASE VoidX_Academy;

Press F5. Look at the Messages tab at the bottom of SSMS. It should say: Commands completed successfully. That means your database was created.

Now look at the Object Explorer on the left. Right-click the Databases folder and click Refresh. You will see VoidX_Academy appear in the list. That is your database, now living on your SQL Server.

Before you can create anything inside it, you must tell SQL Server you want to work inside VoidX_Academy. Run this command:

USE VoidX_Academy;

After pressing F5, the database name in the dropdown at the top-left of the SSMS toolbar changes to VoidX_Academy. Every command you run from now on will operate inside this database until you change it.

SQL Server Data Types — Choosing the Right Column Type

Before creating your first table, you need to understand data types. Every column in a table must be given a data type — it tells SQL Server what kind of information that column is allowed to hold. Choosing the wrong data type wastes storage space, causes errors, and slows down queries.

Here are the most important data types you will use in this track.

INT stores whole numbers with no decimal point. It handles numbers from about negative 2 billion to positive 2 billion. Use it for IDs, counts, ages, and quantities.

BIGINT stores much larger whole numbers. Use it when your numbers might exceed 2 billion — for example, transaction counts in a banking system.

DECIMAL(p, s) stores exact numbers with decimal places. The p is the total number of digits and s is how many of those digits come after the decimal point. DECIMAL(5, 2) can store numbers like 123.45 — five total digits, two after the decimal. Use this for money, grades, and measurements where precision matters.

VARCHAR(n) stores variable-length text up to n characters. If you store the name Kofi in a VARCHAR(100) column, it only uses 4 characters of storage — not 100. Use this for names, emails, descriptions, and any text whose length varies.

NVARCHAR(n) is the same as VARCHAR but supports international characters including accented letters, Arabic, Chinese, and others. Use NVARCHAR when your data might contain characters outside the standard English alphabet.

CHAR(n) stores fixed-length text. Every value uses exactly n characters of storage regardless of how long it actually is. Use this only when all values will always be the same length — for example, a two-letter country code like GH or US.

DATE stores a calendar date: year, month, and day. It does not store a time. Use it for date of birth, enrollment date, and graduation date.

DATETIME stores both a date and a time. Use it when the time of an event matters — for example, the exact moment a transaction was recorded.

BIT stores only two possible values: 1 (true) or 0 (false). Use it for yes/no columns — is the account active, has the student paid their fees, is the course available.

CREATE TABLE — Building Your First Table

Now you will create your first table. A table is a grid of rows and columns — each column has a name and a data type, and each row holds one record.

In your query window, type this command exactly as written:

CREATE TABLE Department (
  DeptID   INT          PRIMARY KEY,
  DeptName VARCHAR(100) NOT NULL,
  Location VARCHAR(100)
);

Press F5. Check the Messages tab — it should say Commands completed successfully.

Now look in Object Explorer. Expand VoidX_Academy, then expand Tables. You will see dbo.Department listed. Expand it further and expand Columns to see DeptID, DeptName, and Location listed individually.

Let us go through every part of the command you just ran so you understand exactly what each piece does.

CREATE TABLE Department tells SQL Server to create a new table and name it Department inside the currently active database.

DeptID INT PRIMARY KEY creates the first column. Its name is DeptID. Its data type is INT — whole numbers only. The words PRIMARY KEY are a constraint — a rule that the database engine will enforce on this column forever. PRIMARY KEY means two things simultaneously: every value in this column must be unique across all rows, and the column can never be empty. It is the identity of each row.

DeptName VARCHAR(100) NOT NULL creates a second column for the department name. VARCHAR(100) means text up to 100 characters. NOT NULL is another constraint — it means this column must always have a value. You cannot save a department without providing its name.

Location VARCHAR(100) creates a third column with no extra constraints. It is optional — you can save a department without specifying its location, and the column will simply contain NULL for that row.

Constraints — The Rules That Protect Your Data

Constraints are rules built directly into the table. They are enforced by the database engine automatically on every single INSERT and UPDATE. No application code is needed to check them — SQL Server enforces them at the database level and rejects any data that violates them, regardless of where the request came from.

Here are all the constraints you will use, explained one by one.

PRIMARY KEY enforces uniqueness and prevents NULL on a column. Every table should have one. It is the unique identifier for each row. Without a primary key, you have no reliable way to refer to a specific row.

NOT NULL means the column must always have a value. SQL Server will reject any INSERT or UPDATE that leaves this column empty. Use it on any column that is logically required — a student must have a name, an order must have a date.

UNIQUE means no two rows can have the same value in this column. Unlike PRIMARY KEY, a UNIQUE column can contain NULL — but only one NULL per column. Use it for email addresses, national ID numbers, and usernames.

FOREIGN KEY creates a link between two tables. It ensures that a value in one table's column must exist as a primary key in another table. This prevents orphaned records — you cannot assign a student to a department that does not exist.

CHECK lets you write a custom condition that every value must satisfy. For example, CHECK (GPA >= 0.0 AND GPA <= 4.0) means no row can have a GPA below zero or above four. SQL Server enforces this rule on every insert and update.

DEFAULT provides an automatic value when none is supplied. If you insert a row without specifying a value for that column, SQL Server fills it in with the default. For example, DEFAULT GETDATE() automatically records the current date and time whenever a new row is inserted.

Creating a Table With All Constraints

Now create the Student table. This one uses several constraints together, including a FOREIGN KEY that links it to the Department table you just created.

CREATE TABLE Student (
  StudentID   INT           IDENTITY(1,1) PRIMARY KEY,
  FirstName   VARCHAR(50)   NOT NULL,
  LastName    VARCHAR(50)   NOT NULL,
  Email       VARCHAR(100)  UNIQUE,
  DateOfBirth DATE,
  GPA         DECIMAL(3,2)  CHECK (GPA >= 0.0 AND GPA <= 4.0),
  IsActive    BIT           DEFAULT 1,
  EnrolledAt  DATETIME      DEFAULT GETDATE(),
  DeptID      INT,
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Press F5. Then expand dbo.Student in Object Explorer under Tables to see every column and type listed.

Let us go through the new parts of this command.

IDENTITY(1,1) is a SQL Server feature that automatically generates the StudentID for you. The first number — 1 — is the starting value. The second number — also 1 — is how much to increase with each new row. So the first student gets ID 1, the second gets ID 2, and so on. You never manually type a StudentID when inserting students.

DECIMAL(3,2) for GPA means three total digits with two after the decimal point — allowing values like 3.75 or 2.90.

CHECK (GPA >= 0.0 AND GPA <= 4.0) is the check constraint. If you try to insert a GPA of 5.0, SQL Server will reject it with a constraint violation error.

BIT DEFAULT 1 for IsActive means every new student is automatically marked as active. You can override it, but if you say nothing when inserting, the student is active by default.

DEFAULT GETDATE() for EnrolledAt automatically records the exact date and time the row was inserted. GETDATE() is a SQL Server built-in function that returns the current moment.

FOREIGN KEY (DeptID) REFERENCES Department(DeptID) links Student to Department. SQL Server will now refuse any insert where DeptID does not match an existing row in the Department table. It will also refuse to delete a department that still has students assigned to it.