Basic about ALTER TABLE Statement – using and example

Isaac
Posted by By Isaac 1 year Ago 10 Min Read

This SQL tutorial explains how to use the SQL ALTER TABLE declaration to add a column, adjust a column, drop a column, rename a column or rename a desk (with a lot of clear, concise examples). We’ve additionally brought some practice workouts that you can attempt for yourself.

Description

The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE announcement is also used to rename a table.

Add column in table

Syntax

To add a column in a table, the ALTER TABLE syntax in SQL is:

ALTER TABLE table_name
  ADD column_name column_definition;

Example

Let’s appear at a SQL ALTER TABLE instance that adds a column.

For example:

ALTER TABLE supplier
  ADD supplier_name char(50);

This SQL ALTER TABLE instance will add a column called supplier_name to the provider table.

Add multiple columns in table

Syntax

To add a couple of columns to an present table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column_definition,
       column_2 column_definition,
       ...
       column_n column_definition);

Example

Let’s look at SQL ALTER TABLE instance that adds more than one column.

For example:

ALTER TABLE supplier
  ADD (supplier_name char(50),
       city char(45));

This SQL ALTER TABLE instance will add two columns, supplier_name as a char(50) area and metropolis as a char(45) subject to the dealer table.

Modify column in table

Syntax

To adjust a column in an current table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name
  MODIFY column_name column_type;

For SQL Server:

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition;

Example

Let’s seem to be at an instance of how to adjust a column known as supplier_name using the ALTER TABLE statement. Note that most databases have a barely one of a kind syntax.

For Oracle:

ALTER TABLE supplier
  MODIFY supplier_name char(100) NOT NULL;

For MySQL and MariaDB:

ALTER TABLE supplier
  MODIFY supplier_name VARCHAR(100) NOT NULL;

For SQL Server:

ALTER TABLE supplier
  ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;

For PostgreSQL:

ALTER TABLE supplier
  ALTER COLUMN supplier_name TYPE CHAR(100),
  ALTER COLUMN supplier_name SET NOT NULL;

Modify multiple columns in table

Syntax

To adjust a couple of columns in an present table, the SQL ALTER TABLE syntax is:

For Oracle:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

For MySQL and MariaDB:

ALTER TABLE table_name
  MODIFY column_1 column_definition
    [ FIRST | AFTER column_name ],
  MODIFY column_2 column_definition
    [ FIRST | AFTER column_name ],
  ...
;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition,
  ALTER COLUMN column_name TYPE column_definition,
  ...
;

Example

Let’s seem to be at an example that uses the ALTER TABLE assertion to adjust more than one column. In this example, we will adjust two columns called supplier_name and city.

For Oracle:

ALTER TABLE supplier
  MODIFY (supplier_name char(100) NOT NULL,
          city char(75));

For MySQL and MariaDB:

ALTER TABLE supplier
  MODIFY supplier_name VARCHAR(100) NOT NULL,
  MODIFY city VARCHAR(75);

For PostgreSQL:

ALTER TABLE supplier
  ALTER COLUMN supplier_name TYPE CHAR(100),
  ALTER COLUMN supplier_name SET NOT NULL,
  ALTER COLUMN city TYPE CHAR(75);

Drop column in table

Syntax

To drop a column in an current table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let’s appear at an example that drops (ie: deletes) a column from a table.

For example:

ALTER TABLE supplier
  DROP COLUMN supplier_name;

This SQL ALTER TABLE instance will drop the column called supplier_name from the desk known as supplier.

Rename column in table

Syntax

To rename a column in an present table, the SQL ALTER TABLE syntax is:

For Oracle and PostgreSQL:

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

For SQL Server (using the stored manner called sp_rename):

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE table_name
  CHANGE COLUMN old_name TO new_name;

Example

Let’s seem to be at an instance that renames a column in the provider desk from supplier_name to sname.

For Oracle (9i Rel2 and up) and PostgreSQL:

ALTER TABLE supplier
  RENAME COLUMN supplier_name TO sname;

For SQL Server (using the stored process referred to as sp_rename):

sp_rename 'supplier.supplier_name', 'sname', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE supplier
  CHANGE COLUMN supplier_name sname VARCHAR(100);

In MySQL and MariaDB, you must specify the records kind of the column when you rename it.

Rename table

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE table_name
  RENAME TO new_table_name;

For SQL Server (using the stored system called sp_rename):

sp_rename 'table_name', 'new_table_name';

Example

Let’s seem at an instance that renames a table known as provider to the new identify vendor.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE supplier
  RENAME TO vendor;

For SQL Server (using the saved method referred to as sp_rename):

sp_rename 'supplier', 'vendor';

Practice Exercise #1:

Based on the departments desk below, rename the departments desk to depts.

CREATE TABLE departments
( department_id int NOT NULL,
  department_name char(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #1:

The following SQL ALTER TABLE announcement would rename the departments table to depts:

ALTER TABLE departments
  RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column known as income that is an int datatype.

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  department_id int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #2:

The following SQL ALTER TABLE declaration would add a salary column to the employees table:

ALTER TABLE employees
  ADD salary int;

Practice Exercise #3:

Based on the clients desk below, add two columns – one column referred to as contact_name that is a char(50) datatype and one column referred to as last_contacted that is a date datatype.

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #3:

The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the clients table:

ALTER TABLE customers
  ADD (contact_name char(50),
       last_contacted date);

Practice Exercise #4:

Based on the employees table below, alternate the employee_name column to a char(75) datatype.

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  department_id int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #4:

The following SQL ALTER TABLE declaration would change the datatype for the employee_name column to char(75):

ALTER TABLE employees
  MODIFY employee_name char(75);

Practice Exercise #5:

Based on the customers desk below, trade the customer_name column to NOT permit null values and change the kingdom column to a char(2) datatype.

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50),
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #5:

The following SQL ALTER TABLE announcement would adjust the customer_name and state columns thus in the customers table:

ALTER TABLE customers
  MODIFY (customer_name char(50) NOT NULL,
          state char(2));

Practice Exercise #6:

Based on the employees desk below, drop the profits column.

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  department_id int,
  salary int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #6:

The following SQL ALTER TABLE announcement would drop the income column from the personnel table:

ALTER TABLE employees
  DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments
( department_id int NOT NULL,
  department_name char(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #7:

The following SQL ALTER TABLE declaration would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments
  RENAME COLUMN department_name to dept_name;