How to add new column in sql

There are several ways to add a new column in SQL, depending on the specific database management system (DBMS) you are using. Here are some common methods:

Method 1: Using the ALTER TABLE statement

This method is supported by most DBMSs, including MySQL, PostgreSQL, Microsoft SQL Server, and Oracle.

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

For example:

ALTER TABLE customers
ADD COLUMN email VARCHAR(255);

Method 2: Using the ADD COLUMN statement with the CREATE TABLE statement

This method is supported by MySQL and PostgreSQL.

CREATE TABLE table_name (
   ...,
    new_column_name data_type,
   ...
);

For example:

CREATE TABLE customers (
    id INT,
    name VARCHAR(255),
    email VARCHAR(255)
);

Method 3: Using the ALTER TABLE statement with the ADD COLUMN clause

This method is supported by Microsoft SQL Server and Oracle.

ALTER TABLE table_name
ADD COLUMN new_column_name data_type
AS (expression);

For example:

ALTER TABLE customers
ADD COLUMN total_amount AS (price * quantity);

Method 4: Using the ALTER TABLE statement with the ADD clause

This method is supported by PostgreSQL.

ALTER TABLE table_name
ADD COLUMN new_column_name data_type
DEFAULT default_value;

For example:

ALTER TABLE customers
ADD COLUMN email VARCHAR(255) DEFAULT '[email protected]';

Method 5: Using the CREATE TABLE statement with the SELECT statement

This method is supported by MySQL and PostgreSQL.

CREATE TABLE new_table_name AS
SELECT *, new_column_name = expression
FROM table_name;

For example:

CREATE TABLE customers_with_email AS
SELECT *, email = CONCAT(name, '@example.com')
FROM customers;

Note that the ALTER TABLE statement can also be used to modify the data type, default value, or other properties of an existing column.