SQL DDL: CREATE TABLE and ALTER TABLE

Used to create a new table in a database
Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
Example: CREATE TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255));
Can specify constraints, such as PRIMARY KEY, FOREIGN KEY, and CHECK
Can also specify default values and auto-incrementing columns

Used to improve query performance by creating an index on one or more columns
Syntax: CREATE INDEX index_name ON table_name (column_name);
Example: CREATE INDEX idx_name ON customers (name);
Types of indexes:
- Unique index: ensures uniqueness of values in a column
- Composite index: indexes multiple columns
- Clustered index: physically reorders table data to match index
Indexes can be created on columns used in WHERE, JOIN, and ORDER BY clauses

Used to enforce data integrity and consistency
Types of constraints:
- PRIMARY KEY: uniquely identifies each row in a table
- FOREIGN KEY: links to the PRIMARY KEY of another table
- UNIQUE: ensures uniqueness of values in a column
- CHECK: enforces a condition on a column or set of columns
- DEFAULT: sets a default value for a column
Syntax: CREATE TABLE table_name (column_name datatype CONSTRAINT constraint_name);
Example: CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(255));
Can be used to enforce business rules and maintain data consistency

The CREATE TABLE statement is used to create a new table in a database
The syntax for creating a table is CREATE TABLE table_name (column1 datatype, column2 datatype,...);
A table can be created with columns that have different data types, such as integers and strings
Constraints can be specified during table creation, including PRIMARY KEY, FOREIGN KEY, and CHECK
Default values and auto-incrementing columns can also be specified during table creation

The ALTER TABLE statement is used to modify the structure of an existing table
The syntax for modifying a table is ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype;
Columns can be added to or removed from an existing table
The data type of an existing column can be modified
Constraints can be added or removed from an existing table
Columns or tables can be renamed using the ALTER TABLE statement

The DROP TABLE statement is used to delete a table from a database
The syntax for deleting a table is DROP TABLE table_name;
Deleting a table also deletes all the data stored in the table
The DROP TABLE statement should be used with caution, as it permanently deletes data

An index is a data structure that improves query performance
Indexes can be created on one or more columns of a table
The syntax for creating an index is CREATE INDEX index_name ON table_name (column_name);
Unique indexes ensure that all values in a column are unique
Composite indexes are created on multiple columns
Clustered indexes physically reorder table data to match the index
Indexes can be created on columns used in WHERE, JOIN, and ORDER BY clauses

Creates a new table in a database
Basic syntax: CREATE TABLE table_name (column1 datatype, column2 datatype,...)
Includes column definitions with datatypes (e.g., INT, VARCHAR, DATE, TIME, etc.)
Example: CREATE TABLE Customers (CustomerID INT, CustomerName VARCHAR(255), ContactName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255), PostalCode VARCHAR(255), Country VARCHAR(255));

Modifies the structure of an existing table
Used for:
- Adding new columns
- Deleting existing columns
- Modifying column definitions
Basic syntax: ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype
Examples:
- Adding a new column: ALTER TABLE Customers ADD Email VARCHAR(255);
- Dropping an existing column: ALTER TABLE Customers DROP COLUMN ContactName;
- Modifying a column definition: ALTER TABLE Customers MODIFY CustomerName VARCHAR(300);

Define rules for data in a table
Types of constraints:
- PRIMARY KEY: ensures unique values in each row
- UNIQUE: ensures unique values in a column
- FOREIGN KEY: ensures values match another table
- CHECK: ensures conditions are met before inserting or updating
- NOT NULL: ensures columns cannot be left blank
Basic syntax varies by type:
- PRIMARY KEY: CREATE TABLE table_name (column1 datatype PRIMARY KEY,...);
- UNIQUE: CREATE TABLE table_name (column1 datatype UNIQUE,...);
- FOREIGN KEY: CREATE TABLE table_name (column1 datatype, FOREIGN KEY (column1) REFERENCES other_table_name(column1));
- CHECK: CREATE TABLE table_name (column1 datatype CHECK (condition),...);
- NOT NULL: CREATE TABLE table_name (column1 datatype NOT NULL,...);
Examples:
- PRIMARY KEY: CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255),...);
- FOREIGN KEY: CREATE TABLE Orders (OrderID INT, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));