Data Definition Language (DDL) is the subset of SQL commands used to define and manage the structure (schema) of the database and its objects, such as tables, indexes, and constraints.
This chapter covers the essential CREATE DATABASE and CREATE TABLE commands.
1. Creating a Database (CREATE DATABASE)
Before creating tables, you must have a database to contain them.
Syntax:
CREATE DATABASE database_name;
Execution Example:
Let’s create a database called HR_Management for a human resources application.
-- 1. Create the new database
CREATE DATABASE HR_Management;
-- 2. Select the database to make it the active context for subsequent commands
USE HR_Management;
Note: The
USEcommand is crucial as it specifies the database where all subsequent table commands will be executed.
2. Creating a Table (CREATE TABLE)
The CREATE TABLE command defines the structure of a table, including the name of each column and its specific data type.
Syntax:
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
PRIMARY KEY (column_name)
);
3. Essential SQL Data Types
Choosing the correct data type for each column is vital for data integrity and storage efficiency.
| Data Type | Description | Example Use Case |
| INT | A standard integer (whole number). | employee_id, age, quantity |
| DECIMAL(p, s) | A precise fixed-point number. p is total digits, s is digits after the decimal point. | salary (DECIMAL(10, 2)), price |
| VARCHAR(size) | Variable-length string data. You must specify the maximum size. | first_name, email, department |
| TEXT | Large, variable-length strings (for long text). | job_description, comments |
| DATE | Stores a date in ‘YYYY-MM-DD’ format. | hire_date |
| DATETIME | Stores both date and time (‘YYYY-MM-DD HH:MM:SS’). | last_login |
4. Essential Column Constraints
Constraints are rules enforced on data columns to limit the type of data that can be inserted, ensuring reliability and integrity.
| Constraint | Description | Purpose |
| NOT NULL | Ensures a column cannot contain null (empty) values. | Guarantees essential information is provided (e.g., a username). |
| UNIQUE | Ensures all values in a column are different. | Prevents duplicate values (e.g., two users with the same email). |
| PRIMARY KEY | A combination of NOT NULL and UNIQUE that uniquely identifies each record. | Essential for linking tables (relationships). |
| AUTO_INCREMENT | Automatically generates a unique, sequential number for new records. | Ideal for creating primary key IDs. |
5. Execution Example: Creating a Table
Let’s create an Employees table for the HR_Management database.
USE HR_Management;
CREATE TABLE Employees (
employee_id INT AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
hire_date DATE,
salary DECIMAL(8, 2) NOT NULL,
-- Designate the primary key
PRIMARY KEY (employee_id)
);
This single DDL statement creates a highly structured table, ready to accept data while enforcing strict integrity rules.
