Alter Table

Overview

SQL ALTER TABLE is an essential SQL command that database developers use to change the structure of a table. An SQL table structure might require modification as business needs evolve. Therefore, the ALTER TABLE statement is crucial when it comes to adding, modifying, or deleting columns from an existing table. In addition to these functionalities, this SQL command can be used to add and drop various constraints to an existing table. For instance, developers can add foreign key constraints to ensure referential integrity. The ALTER TABLE statement is also used to enable or disable constraints, rename tables, or set storage parameters for tables. Therefore, SQL ALTER TABLE is a versatile SQL command that can be used to make a wide range of modifications to an existing table in a database.

ALTER TABLE ADD Column statement in SQL

Syntax:

ALTER TABLE table_name 
ADD COLUMN column_name datatype;

ALTER TABLE Employees 
ADD COLUMN Salary INT;

This code adds a new column named Salary to the Employees table with a data type of INT (integer). This means that the values stored in the Salary column will be numbers.

This code adds a column called 'Salary' with an integer data type to the Employees table.

ALTER TABLE MODIFY Column statement in SQL

Syntax

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example

ALTER TABLE Employees MODIFY COLUMN Salary DECIMAL(10,2);

This code is used to alter the table named Employees. It modifies the column named Salary to have a data type of DECIMAL(10,2). This means that the column will store numbers totaling 10 digits, with 2 digits after the decimal point.

This statement changes the data type of the email column in the customer's table to VARCHAR(255).

ALTER TABLE DROP Column statement in SQL

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE employees
DROP COLUMN salary;

This statement will delete the "salary" column from the "employees" table.

ALTER TABLE RENAME Column statement in SQL

Syntax

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Example

ALTER TABLE employees RENAME COLUMN last_name TO surname;

This statement renames the column named 'lastname' to 'surname' in the table 'employees'.

Conclusion

This lesson clarifies the SQL Alter TABLE command, which is utilized to adjust the structure of an existing table in a database. The command can be utilized to add, adjust, or erase columns, include or drop constraints, rename tables, and set storage parameters. The document gives illustrations of the syntax for adding, adjusting, dropping, and renaming columns in a table.

Key takeaways

1. Alter TABLE is utilized to alter the structure of an existing table.
2. Modify TABLE can be utilized to add, adjust, or drop columns, adjust column properties, or rename tables.
3. To include a column in a table, utilize the Include COLUMN clause.
4. To adjust the properties of an existing column, utilize the Alter COLUMN clause.
5. To drop a column from a table, utilize the DROP COLUMN clause.
6. To rename a table, utilize the RENAME TO clause.
7. Be beyond any doubt to check the information sort and estimate of the column sometime recently, including or altering it.
8. Be beyond any doubt to utilize the right sentence structure when utilizing the Modify TABLE explanation.

Quiz

1. What does the ALTER TABLE statement do?

1.  Delete a table 
2. Update the data in a table 
3. Add, delete, or modify columns in a table
4.  Create a new table

Answer: C) Add, delete, or modify columns in a table

2. What is the syntax for adding a column to an existing table? 

1.  ALTER TABLE table_name ADD column_name datatype; 
2. ALTER TABLE table_name ADD column_name; 
3. ALTER TABLE table_name INSERT column_name datatype; 
4. ALTER TABLE table_name INSERT column_name;

Answer: A) ALTER TABLE table_name ADD column_name datatype;

3. What happens when you use the ALTER TABLE statement to modify the data type of a column? 

1. The data type of the column is changed, and all the data in the column is converted to the new data type 
2. The data type of the column is changed, but the data remains unchanged 
3. The data type of the column is changed, and the column is deleted 
4. The data type of the column is changed, and new data is added to the column

Answer: B) The data type of the column is changed but the data remains unchanged

4. What is the syntax for deleting a column from an existing table? 

1. ALTER TABLE table_name DROP column_name; 
2. ALTER TABLE table_name DELETE column_name; 
3. ALTER TABLE table_name REMOVE column_name; 
4. ALTER TABLE table_name DEL column_name;

Answer: A) ALTER TABLE table_name DROP column_name;