Bytes

Alter Table In SQL

Module - 3 DDL Commands
Alter Table In SQL

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;
employee_idfirst_namelast_name
1JohnSmith
2JaneDoe
ALTER TABLE Employees 
ADD COLUMN Salary INT;
employee_idfirst_namelast_namesalary
1JohnSmith25000
2JaneDoe30000

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);
employee_idfirst_namelast_namesalary
1JohnSmith25000.00
2JaneDoe30000.00

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;
employee_idfirst_namelast_name
1JohnSmith
2JaneDoe

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;
employee_idfirst_namesurname
1JohnSmith
2JaneDoe

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;

Online SQL Compiler (SQLite 3 Editor)
Open Compiler
Recommended Courses
Certification in Full Stack Data Science and AI
Course
20,000 people are doing this course
Become a job-ready Data Science professional in 30 weeks. Join the largest tech community in India. Pay only after you get a job above 5 LPA.
Masters Program in Data Science and Artificial Intelligence
Course
20,000 people are doing this course
Join India's best Masters program in Data Science and Artificial Intelligence. Get the best jobs in top tech companies. Accredited by ECTS and globally recognised in EU, US, Canada and 60+ countries.

AlmaBetter’s curriculum is the best curriculum available online. AlmaBetter’s program is engaging, comprehensive, and student-centered. If you are honestly interested in Data Science, you cannot ask for a better platform than AlmaBetter.

avatar
Kamya Malhotra
Statistical Analyst
Fast forward your career in tech with AlmaBetter
Explore Courses

Vikash SrivastavaCo-founder & CPTO AlmaBetter

Vikas CTO

Related Tutorials to watch

view Allview-all

Top Articles toRead

view Allview-all
AlmaBetter
Made with heartin Bengaluru, India
  • Official Address
  • 4th floor, 133/2, Janardhan Towers, Residency Road, Bengaluru, Karnataka, 560025
  • Communication Address
  • 4th floor, 315 Work Avenue, Siddhivinayak Tower, 152, 1st Cross Rd., 1st Block, Koramangala, Bengaluru, Karnataka, 560034
  • Follow Us
  • facebookinstagramlinkedintwitteryoutubetelegram

© 2024 AlmaBetter