SQL Database Tables

Tables are the fundamental building blocks of any relational database. They store data in a structured format consisting of rows and columns.

Understanding Tables

Each table in a database has a unique name and contains data organized as:

Rows (Records): Each row represents a single entry or record in the table. For example, in a Customers table, each row would represent one customer.
Columns (Fields): Each column represents an attribute or piece of data about the entries. For example, in a Customers table, columns might include CustomerID, FirstName, LastName, Email, etc.

Table Structure and Properties

Primary Key: A column or set of columns that uniquely identifies each row in a table. It ensures data integrity and is crucial for relationships between tables.
Foreign Key: A column or set of columns in one table that refers to the primary key in another table. This establishes relationships and enforces referential integrity.
Data Types: Each column has a specific data type (e.g., INT, VARCHAR, DATE) that defines the kind of data it can store and the operations that can be performed on it.
Nullability: Specifies whether a column can contain NULL values.
Default Values: A value automatically assigned to a column if no explicit value is provided during insertion.

Creating Tables

The CREATE TABLE statement is used to define a new table in the database. Here's a basic syntax:


CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
    PRIMARY KEY (column_name)
);

Example: Creating a Customers Table

This example creates a Customers table with an auto-incrementing primary key, customer name, email, and registration date.


CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE DEFAULT CURRENT_DATE
);

Working with Table Data

Inserting Data (INSERT)

Use the INSERT INTO statement to add new rows to a table.


INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Retrieving Data (SELECT)

Use the SELECT statement to query data from one or more tables.


SELECT column1, column2 FROM table_name WHERE condition;

Updating Data (UPDATE)

Use the UPDATE statement to modify existing rows in a table.


UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Deleting Data (DELETE)

Use the DELETE statement to remove rows from a table.


DELETE FROM table_name WHERE condition;

Table Relationships

Establishing relationships between tables is fundamental to relational database design. Common relationships include:

One-to-One: Each record in Table A corresponds to at most one record in Table B, and vice versa.
One-to-Many: A single record in Table A can be associated with multiple records in Table B, but each record in Table B is associated with only one record in Table A. (Most common)
Many-to-Many: A single record in Table A can be associated with multiple records in Table B, and a single record in Table B can be associated with multiple records in Table A. This typically requires an intermediate "junction" table.

Tip: Proper normalization of your database schema, including well-defined table relationships and appropriate use of primary and foreign keys, is essential for data integrity, efficiency, and maintainability.

Common Table Operations

Operation	Description	SQL Statement Example
Create Table	Defines a new table structure.	`CREATE TABLE ...`
Alter Table	Modifies an existing table (add/drop columns, change data types).	`ALTER TABLE ... ADD COLUMN ...`
Drop Table	Deletes a table and all its data.	`DROP TABLE table_name;`
Truncate Table	Removes all rows from a table, but keeps the table structure.	`TRUNCATE TABLE table_name;`

Understanding and effectively managing tables is a cornerstone of database development. Refer to the specific SQL dialect documentation (e.g., SQL Server, PostgreSQL, MySQL) for syntax variations and advanced features.