Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to modify an existing table in SQL. You can add, delete, or modify columns, as well as change constraints like primary keys.
Syntax
— Add a new column
ALTER TABLE table_name
ADD column_name datatype;
— Delete an existing column
ALTER TABLE table_name
DROP COLUMN column_name;
— Modify an existing column
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
— Rename a column
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
Example:
1 Adding a New Column
Suppose you want to add an Email column to the Students table:
ALTER TABLE Students
ADD Email VARCHAR(100);
This command adds a new column named Email to the Students table.
2 Deleting a Column
If you no longer need the Grade column in the Students table:
ALTER TABLE Students
DROP COLUMN Grade;
This command removes the Grade column from the Students table.
3 Modifying a Column
Let’s say you want to change the data type of the Salary column in the Employees table from INT to DECIMAL:
ALTER TABLE Employees
MODIFY COLUMN Salary DECIMAL(10, 2);
This command modifies the Salary column to store decimal values instead of integers.
4 Modifying a Column
Let’s say you want to change the data type of the Salary column in the Employees table from INT to DECIMAL:
ALTER TABLE Employees
MODIFY COLUMN Salary DECIMAL(10, 2);
This command modifies the Salary column to store decimal values instead of integers.
Tips to Remember
- Backup Data: Always back up your table before making changes.
- Check Compatibility: Ensure the new data type is compatible when modifying a column.
- Be Careful with DROP: Dropping a column permanently deletes all data in that column.
Frequently Asked Questions
Q1: Can I add multiple columns at once?
A1: Yes, you can add multiple columns in one statement:
ALTER TABLE Students
ADD Email VARCHAR(100),
ADD Address VARCHAR(255);
Q2: What if I try to drop a non-existent column?
A2: SQL will return an error. Use IF EXISTS in some systems to avoid this.
Q3: Can I rename and modify a column simultaneously?
A3: It depends on the SQL dialect, but typically, these actions are done separately.
Q4: How do I verify that changes were made?
A4: Use a DESCRIBE or SHOW COLUMNS command to check the table structure.
Q5: How often can I alter a table?
A5: There’s no limit, but excessive alterations can complicate the table structure.
