SQL ALTER TABLE statement

The ALTER TABLE statement in SQL is used to add, delete/drop or modify columns in an existing table. It's also used to add and drop various constraints on an existing table.

Let's go through some common uses of ALTER TABLE.

1. Add Column

To add a column to a table, you use the ADD clause:

ALTER TABLE table_name
ADD column_name datatype;

For example, to add a column called email to a table called employees:

ALTER TABLE employees
ADD email varchar(255);

2. Drop Column

To drop a column from a table, you use the DROP COLUMN clause:

ALTER TABLE table_name
DROP COLUMN column_name;

For example, to drop the email column from the employees table:

ALTER TABLE employees
DROP COLUMN email;

3. Modify Column

To modify the data type of a column in a table, you use the ALTER COLUMN clause:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

For example, to modify the data type of the email column in the employees table to text:

ALTER TABLE employees
ALTER COLUMN email text;

4. Add a NOT NULL Constraint

To add a NOT NULL constraint to a column, you need to first make sure that column has no NULL values. Then, you can use the ALTER COLUMN clause:

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

For example, to add a NOT NULL constraint to the email column in the employees table:

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

5. Drop a NOT NULL Constraint

To remove a NOT NULL constraint from a column, you can use the ALTER COLUMN clause:

ALTER TABLE table_name
ALTER COLUMN column_name DROP NOT NULL;

For example, to drop a NOT NULL constraint from the email column in the employees table:

ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

Please note that the SQL syntax can vary slightly between different SQL databases. For example, in some databases, you may not be able to drop a column, or changing the data type might be different. It's always a good idea to check the documentation for your specific database.

Adding a new column with ALTER TABLE statement: Use the ADD COLUMN clause to add a new column to an existing table.
```
ALTER TABLE TableName
ADD COLUMN NewColumnName DataType;
```
Dropping a column in SQL ALTER TABLE: Use the DROP COLUMN clause to remove an existing column from a table.
```
ALTER TABLE TableName
DROP COLUMN ColumnName;
```
Modifying column data types with ALTER TABLE: Change the data type of an existing column using the MODIFY clause.
```
ALTER TABLE TableName
MODIFY COLUMN ColumnName NewDataType;
```
Renaming a column using ALTER TABLE: Rename an existing column with the RENAME TO clause.
```
ALTER TABLE TableName
RENAME COLUMN OldColumnName TO NewColumnName;
```
Adding constraints with ALTER TABLE in SQL: Add constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.
```
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CONSTRAINT_TYPE (Column1, Column2);
```
Removing constraints in ALTER TABLE statement: Remove constraints using the DROP CONSTRAINT clause.
```
ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;
```
Changing column order in SQL ALTER TABLE: Change the order of columns within a table.
- This is a more complex operation and may involve creating a new table.
```
ALTER TABLE TableName
MODIFY COLUMN Column1 DataType FIRST;
```
Setting default values with ALTER TABLE: Set default values for a column using the SET DEFAULT clause.
```
ALTER TABLE TableName
ALTER COLUMN ColumnName SET DEFAULT DefaultValue;
```