Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve data from relational databases like MySQL, Oracle, SQL Server, Postgres, etc. In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command.
ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.
Step 1: Create a database employee.
CREATE DATABASE employee;
Step 2: Create a table
Now create a table employee_info.
CREATE TABLE employee_info
( Employee_id INT,
INSERT INTO employee_info
Step 3: To view a database schema we use the following query.
EXEC sp_help 'dbo.employee_info';
Now, let’s add a new column Gender in the table. Then we use ALTER table command.
Step 4: Alter the table.
ALTER TABLE employee_info ADD Gender CHAR(1) CHECK (Gender IN ('M','F'));
Now, the new column gets added after City i.e. at the last successfully.
Take another case using a query, If we want the Gender column after Last_Name, then we can write the query as shown below.
SELECT Employee_Id,First_Name,Last_Name,Gender,Salary,City FROM employee_info;
To Rename a Column in a MySQL Table
The old column can be replaced with a new name in MySQL using the ALTER TABLE CHANGE COLUMN statement. The syntax to use for this is as follows:
ALTER TABLE table_name
CHANGE GCOLUMN old_column_new_column_name column_definition[FIRST|AFTER exisiting_column];
In the above condition:
- The table name needs to be specified first.
- The new column name and its definition must be specified after the CHANGE COLUMN clause, along with the old column name. Even though the column definition won’t change, we still need to specify it.
- Finally, we must specify whether the keyword is FIRST or AFTER. The information indicating when to change the column name at a particular position is optional.
Let’s take an example that we have one table and we will add one rename one column in the table.
CREATE TABLE employees (
id INT PRIMARY KEY,
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John Doe', 'Sales', 50000),
(2, 'Jane Smith', 'Marketing', 60000),
(3, 'Bob Johnson', 'HR', 45000),
(4, 'Sara Lee', 'IT', 80000);
ALTER TABLE employees
RENAME COLUMN salary TO annual_salary;
Before removing a column from the table, keep in mind the following important details:
- The schema of one table may be dependent on the columns of another table in relational databases where MySQL is used. Therefore, removing a column from one table will have an impact on all related tables as well. As you remove the column, keep the following in mind:
- Triggers, stored procedures, and views are just a few examples of objects that will be impacted when columns are removed from a table. Let’s say we remove a column that the trigger is referencing. The changed code for the dropped column depends on other application code, which requires time and effort.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation
Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation