Tables (Database Engine)

Tables are the fundamental building blocks of relational databases, used to store data in a structured format. Each table consists of rows (records) and columns (fields), where each column represents a specific attribute of the data.

Creating Tables

You can create tables using the CREATE TABLE Transact-SQL statement. This statement defines the table name, column names, data types for each column, and constraints.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE,
    DepartmentID INT
);

Table Constraints

Constraints are rules enforced on data columns to ensure the accuracy and reliability of the data. Common constraints include:

• PRIMARY KEY: Uniquely identifies each record in a table.
• FOREIGN KEY: A field that uniquely identifies a row of another table; it is used to link two tables.
• UNIQUE: Ensures that all values in a column are different.
• NOT NULL: Ensures that a column cannot have a NULL value.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Inserts a default value if no value is specified.

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL UNIQUE,
    Location VARCHAR(50) DEFAULT 'Unknown'
);

ALTER TABLE Employees
ADD CONSTRAINT FK_Department
    FOREIGN KEY (DepartmentID)
    REFERENCES Departments(DepartmentID);

Modifying Tables

The ALTER TABLE statement is used to modify existing table structures. You can add, delete, or modify columns, add or drop constraints.

ALTER TABLE Employees
ADD Email VARCHAR(100);

ALTER TABLE Employees
DROP COLUMN HireDate;

Data Manipulation

Data within tables is managed using Data Manipulation Language (DML) statements:

• INSERT: Adds new rows to a table.
• SELECT: Retrieves data from one or more tables.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes rows from a table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (101, 'John', 'Doe', 10);

SELECT FirstName, LastName, DepartmentID
FROM Employees
WHERE DepartmentID = 10;