Which Of The Following Commands Can Be Used To Make Structural Changes To An Existing Table?ã¢â‚¬â€¹

---

SQL: ALTER Tabular array Statement

This SQL tutorial explains how to use the SQL Modify Table statement to add a cavalcade, modify a column, drop a column, rename a column or rename a table (with lots of articulate, concise examples). We've also added some do exercises that you can try for yourself.

Description

The SQL Modify TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL Alter TABLE statement is as well used to rename a tabular array.

Add column in table

Syntax

To add a column in a table, the Modify Table syntax in SQL is:

Change TABLE table_name   Add together column_name column_definition;

Case

Let's look at a SQL ALTER Table case that adds a column.

For example:

ALTER Table supplier   Add together supplier_name char(l);

This SQL Change Table instance will add a column chosen supplier_name to the supplier table.

Add multiple columns in table

Syntax

To add multiple columns to an existing 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'south await at SQL Change Table example that adds more than than one column.

For example:

ALTER TABLE supplier   ADD (supplier_name char(l),        urban center char(45));

This SQL Alter TABLE example volition add two columns, supplier_name equally a char(50) field and urban center as a char(45) field to the supplier table.

Change cavalcade in table

Syntax

To modify a column in an existing table, the SQL Modify Tabular array 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   Change Column column_name Type column_definition;

Example

Let'south await at an example of how to modify a column called supplier_name using the Modify Tabular array statement. Note that most databases have a slightly different syntax.

For Oracle:

ALTER Tabular array supplier   Change supplier_name char(100) NOT NULL;

For MySQL and MariaDB:

Alter Table supplier   MODIFY supplier_name VARCHAR(100) NOT NULL;

For SQL Server:

Change TABLE supplier   Change Column supplier_name VARCHAR(100) NOT NULL;

For PostgreSQL:

ALTER Table supplier   Change COLUMN supplier_name Blazon CHAR(100),   ALTER COLUMN supplier_name SET NOT NULL;

Modify multiple columns in tabular array

Syntax

To modify multiple columns in an existing tabular array, 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:

Change Table table_name   Change column_1 column_definition     [ FIRST | Later on column_name ],   MODIFY column_2 column_definition     [ Start | AFTER column_name ],   ... ;

For PostgreSQL:

Alter Tabular array table_name   Change Column column_name Blazon column_definition,   Change Column column_name TYPE column_definition,   ... ;

Case

Let's look at an example that uses the Modify TABLE statement to modify more than 1 column. In this example, nosotros will modify ii columns called supplier_name and city.

For Oracle:

Change Table supplier   Modify (supplier_name char(100) NOT Nix,           urban center char(75));

For MySQL and MariaDB:

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

For PostgreSQL:

ALTER Tabular array supplier   Modify Column supplier_name Blazon CHAR(100),   Modify COLUMN supplier_name Prepare Not Zip,   Alter COLUMN metropolis Blazon CHAR(75);

Drop column in tabular array

Syntax

To driblet a column in an existing tabular array, the SQL Change Table syntax is:

Change TABLE table_name   Drib Cavalcade column_name;

Case

Let'southward look at an case that drops (ie: deletes) a column from a table.

For example:

Alter TABLE supplier   DROP COLUMN supplier_name;

This SQL Change Table example will driblet the cavalcade called supplier_name from the table called supplier.

Rename column in table

Syntax

To rename a cavalcade in an existing table, the SQL Modify TABLE syntax is:

For Oracle and PostgreSQL:

Alter Tabular array table_name   RENAME Cavalcade old_name TO new_name;

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

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

For MySQL and MariaDB:

Change TABLE table_name   CHANGE COLUMN old_name TO new_name;

Example

Allow's await at an instance that renames a column in the supplier table 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 procedure called 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, yous must specify the data type of the column when you rename it.

Rename table

Syntax

To rename a table, the SQL ALTER Tabular array syntax is:

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

Alter Tabular array table_name   RENAME TO new_table_name;

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

sp_rename 'table_name', 'new_table_name';

Example

Let's look at an instance that renames a table called supplier to the new name vendor.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

Modify TABLE supplier   RENAME TO vendor;

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

sp_rename 'supplier', 'vendor';

Practice Practice #1:

Based on the departments table beneath, rename the departments table to depts.

CREATE Tabular array departments ( department_id int Not Nil,   department_name char(50) NOT Nil,   CONSTRAINT departments_pk PRIMARY Key (department_id) );

Solution for Do Exercise #1:

The following SQL Change Table statement would rename the departments table to depts:

Change Tabular array departments   RENAME TO depts;

Practice Exercise #ii:

Based on the employees table beneath, add a cavalcade called bacon that is an int datatype.

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

Solution for Practice Practice #2:

The post-obit SQL ALTER TABLE argument would add together a salary column to the employees table:

ALTER Tabular array employees   Add together salary int;

Practice Do #3:

Based on the customers table below, add 2 columns - one column called contact_name that is a char(fifty) datatype and one column chosen last_contacted that is a date datatype.

CREATE Tabular array customers ( customer_id int NOT NULL,   customer_name char(50) NOT NULL,   address char(l),   metropolis char(50),   state char(25),   zip_code char(10),   CONSTRAINT customers_pk Main Fundamental (customer_id) );

Solution for Practise Practise #3:

The following SQL Modify Table statement would add the contact_name and last_contacted columns to the customers table:

ALTER Table customers   Add together (contact_name char(fifty),        last_contacted date);

Practice Practise #four:

Based on the employees table below, change 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 Tabular array argument would change the datatype for the employee_name column to char(75):

ALTER Table employees   MODIFY employee_name char(75);

Practice Practice #v:

Based on the customers table below, change the customer_name column to Non allow null values and change the state column to a char(2) datatype.

CREATE Tabular array customers ( customer_id int NOT Null,   customer_name char(l),   address char(l),   urban center char(50),   state char(25),   zip_code char(10),   CONSTRAINT customers_pk Principal Primal (customer_id) );

Solution for Practice Do #5:

The following SQL ALTER TABLE statement would modify the customer_name and country columns accordingly in the customers table:

ALTER Tabular array customers   MODIFY (customer_name char(fifty) Not NULL,           state char(two));

Practice Exercise #6:

Based on the employees tabular array below, driblet the bacon cavalcade.

CREATE TABLE employees ( employee_number int Not Nada,   employee_name char(50) Non Null,   department_id int,   salary int,   CONSTRAINT employees_pk Master KEY (employee_number) );

Solution for Exercise Exercise #six:

The following SQL ALTER Tabular array statement would drop the salary column from the employees tabular array:

ALTER Tabular array employees   Drop Column bacon;

Do Exercise #seven:

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

CREATE Tabular array departments ( department_id int NOT NULL,   department_name char(fifty) NOT Cipher,   CONSTRAINT departments_pk Primary KEY (department_id) );

Solution for Practice Exercise #7:

The following SQL ALTER TABLE argument would rename the department_name cavalcade to dept_name in the departments table:

ALTER Tabular array departments   RENAME COLUMN department_name to dept_name;

Source: https://www.techonthenet.com/sql/tables/alter_table.php

Posted by: provostgrou1956.blogspot.com

Share this post