Tables are the building blocks of a database, storing data in a structured format. In this article, we’ll explore how to create and alter tables.

​
What Is a Table?

A table is a collection of rows and columns. Each column represents a specific data field (e.g., name, age), while each row represents a single record. Designing tables effectively is critical for managing your data efficiently.

​
Basic Syntax for Creating Tables

The CREATE TABLE statement defines the structure of a table, specifying column names, data types, and optional constraints.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

​
Adding Data to a Table

Once your table is created, use the INSERT INTO statement to populate it with data like you did in the previous article.

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', '2023-05-01', 55000.00);

​
Viewing Table Structure

To understand a table’s structure, you can use database-specific commands.

EXEC sp_help 'employees';

​
Altering Table

The ALTER TABLE statement lets you modify an existing table by adding, altering, or dropping columns.

​
Adding a Column

ALTER TABLE employees
ADD department VARCHAR(50);

​
Modifying a Column

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12, 2);

Dropping a Column

ALTER TABLE employees
DROP COLUMN department;

​
Deleting a Table

If a table is no longer needed, you can delete it using the DROP TABLE statement. Be careful, as this

DROP TABLE permanently removes the table and its data.

DROP TABLE employees;

​
Key Takeaways

  • The CREATE TABLE statement is used to define a table’s structure, including columns and their data types.
  • Use ALTER TABLE to make changes to an existing table—add, modify, or drop columns.
  • Always double-check before using DROP TABLE, as it removes the table and all the data.

Next, we’ll explore how to establish relationships between tables using foreign keys. This will help you connect data effectively across your database.