SQL DDL: CREATE TABLE and ALTER TABLE

SQL DDL

CREATE 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

ALTER TABLE

• Used to modify the structure of an existing table
• Syntax: ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype;
• Example: ALTER TABLE customers ADD address VARCHAR(255);
• Can add or remove columns, modify data types, and change constraints
• Can also rename columns or tables

DROP TABLE

• Used to delete a table from a database
• Syntax: DROP TABLE table_name;
• Example: DROP TABLE customers;
• Deletes the table and all its data
• Be cautious when using, as it permanently deletes data

INDEXING

• 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

CONSTRAINTS

• 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

SQL DDL

Creating Tables

• 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

Modifying Tables

• 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

Deleting Tables

• 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

Indexing

• 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

Constraints

• Constraints are used to enforce data integrity and consistency
• PRIMARY KEY constraints uniquely identify each row in a table
• FOREIGN KEY constraints link to the PRIMARY KEY of another table
• UNIQUE constraints ensure that all values in a column are unique
• CHECK constraints enforce a condition on a column or set of columns
• DEFAULT constraints set a default value for a column
• Constraints can be used to enforce business rules and maintain data consistency

SQL DDL

CREATE TABLE

• 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));

ALTER TABLE

• 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);

DROP TABLE

• Deletes an existing table from a database
• Basic syntax: DROP TABLE table_name;
• Example: DROP TABLE Customers;
• Note: Use with caution, as it permanently deletes the table and its data.

CONSTRAINTS

• 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));