Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/sql-alter-add-drop-modify/ below:

SQL ALTER TABLE - GeeksforGeeks

The SQL ALTER TABLE statement is a powerful tool that allows you to modify the structure of an existing table in a database. Whether you are adding new columns, modifying existing ones, deleting columns or renaming them, the ALTER TABLE statement enables you to make changes without losing the data stored in the table.

• The ALTER TABLE statement in SQL is used to modify an existing table structure in a database without losing any data.
• It allows you to add, remove, or modify columns, change data types or apply constraints to improve data integrity and ensure that the table meets evolving business requirements.
• It allows for structural changes like adding new columns, modifying existing ones, deleting columns and renaming columns within a table.

Syntax:

ALTER TABLE table_name
[ADD | DROP | MODIFY] column_name datatype;

Key Terms

• table_name refers to the name of the table you want to modify.
• ADD is used to add a new column.
• DROP is used to remove an existing column.
• MODIFY is used to change the datatype or definition of an existing column.

The ADD clause is used to add a new column to an existing table. You must specify the name of the new column and its data type.

Query:

ALTER TABLE table_name
ADD column_name datatype;

The MODIFY (or ALTER COLUMN in some databases like SQL Server) clause is used to modify the definition of an existing column, such as changing its data type or size.

Query:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

The DROP clause allows you to remove a column from a table. Be cautious when using this command as it will permanently remove the column and its data.

Query:

ALTER TABLE table_name
DROP COLUMN column_name;

We can rename an existing column using the RENAME COLUMN clause. This allows you to change the name of a column while preserving its data type and content.

Query:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

We can rename an entire table using the RENAME TO clause. This changes the name of the table while preserving its structure and data.

ALTER TABLE table_name
RENAME TO new_table_name;

Below are various examples demonstrating different use cases of the ALTER TABLE statement in SQL. These examples showcase how to add, modify, drop, and rename columns or tables, providing a clear understanding of the flexibility and utility of the ALTER TABLE statement.

To add a new column to an existing table, you can use the ADD clause. Here’s an example where we add a column named email to the employee table:

ALTER TABLE Students
ADD Email varchar(255);

We can remove an existing column from a table using the DROP COLUMN clause. Here’s an example where we remove the phone_number column from the employee table:

ALTER TABLE Students
DROP COLUMN Email;

To change the data type or size of an existing column, you can use the MODIFY clause. For instance, if you want to change the data type of the salary column from INT to DECIMAL, you can use:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Let's explore the ALTER TABLE queries using a Student table with columns ROLL_NO and NAME, and demonstrate various modifications such as adding, modifying, and dropping columns. Here's how you can execute these operations:

To add new columns AGE and COURSE to the Student table, you can use the ALTER TABLE statement with the ADD clause.

Query:

 ALTER TABLE Student ADD 
 (AGE number(3),COURSE varchar(40));

Output: 

Explanation: This adds the AGE column with a numeric data type and the COURSE column with a VARCHAR(40) type. The columns are added to the table, but the values are empty initially.

If you want to reduce the size of the COURSE column from VARCHAR(40) to VARCHAR(20), you can modify the column's data type using the MODIFY clause.

Query:

 ALTER TABLE Student 
 MODIFY COURSE varchar(20);

Explanation: After running the above query, the COURSE column's data type will now allow a maximum of 20 characters instead of 40. This modifies the column's size limit.

To remove the COURSE column completely from the Student table, you can use the DROP COLUMN clause.

Query:

ALTER TABLE Student 
DROP COLUMN COURSE;

Output:

Explanation: After executing this query, the COURSE column is permanently removed from the table, leaving just the ROLL_NO, NAME, and AGE columns.

SQL ALTER TABLE is the key command to alter the structure of a table. If you need to add, drop, or change columns, or even rename columns or the table, ALTER TABLE gives you the necessary flexibility to handle your database schema in an optimal manner. If we use this command and follow best practices, we can ensure the integrity and performance of your database while making the structural changes necessary.

SQL ALTER TABLE - ADD, DROP, MODIFY

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4