A Quick Guide to SQL — Chapter 8: Modifying Table Structure

In the previous lesson we talked about the advanced concepts in SQL. In this lesson, we will focus on modifying the structure of database tables using the ALTER TABLE statement. We will explore various modifications such as adding and dropping columns, modifying column data types, and renaming tables and columns. Understanding how to modify table structures is essential for adapting your database schema to changing requirements. Let’s dive in!

Chapters of This Series

1. Chapter 1: Introduction and Basic Syntax
2. Chapter 2: Filtering and Sorting Data
3. Chapter 3: Manipulating Data
4. Chapter 4: Querying Multiple Tables with JOIN
5. Chapter 5: Aggregating Data
6. Chapter 6: Modifying Data
7. Chapter 7: Advanced SQL Concepts
8. Chapter 8: Modifying Table Structure

1. Altering Table Structure with ALTER TABLE

The ALTER TABLE statement is used to modify the structure of an existing table. It allows you to add, modify, or drop columns, as well as rename tables and columns. Here’s the basic syntax for the ALTER TABLE statement:

ALTER TABLE table_name
modification;

2. Adding Columns

To add a new column to an existing table, use the ADD keyword in the ALTER TABLE statement. Specify the column name and its data type. Here’s an example:

ALTER TABLE customers
ADD email VARCHAR(100);

This statement adds a new column named “email” with a VARCHAR data type and a maximum length of 100 characters to the “customers” table.

3. Dropping Columns

To remove a column from an existing table, use the DROP keyword in the ALTER TABLE statement. Specify the column name to be dropped. Here’s an example:

ALTER TABLE customers
DROP COLUMN email;

This statement removes the “email” column from the “customers” table.

4. Modifying Column Data Types

To modify the data type of a column in an existing table, use the ALTER COLUMN keyword within the ALTER TABLE statement. Specify the column name and the new data type. Here’s an example:

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(10, 2);

This statement modifies the “salary” column in the “employees” table, changing its data type to DECIMAL with a precision of 10 and a scale of 2.

5. Renaming Tables and Columns

To rename a table, use the RENAME TO clause within the ALTER TABLE statement. Specify the new table name. Here’s an example:

ALTER TABLE old_table_name
RENAME TO new_table_name;

This statement renames the “old_table_name” to “new_table_name”.

To rename a column, use the RENAME COLUMN clause within the ALTER TABLE statement. Specify the current column name and the new column name. Here’s an example:

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

This statement renames the “old_column_name” to “new_column_name” within the “table_name” table.

6. Conclusion

In this lesson, we learned how to modify the structure of database tables using the ALTER TABLE statement. We explored adding and dropping columns, modifying column data types, and renaming tables and columns. These capabilities allow you to adapt your database schema to evolving requirements, ensuring the flexibility and scalability of your data storage solution.

If you found this course helpful and would like to explore more free courses, I invite you to follow my account on Medium and connect with me on LinkedIn. I regularly share valuable content on these platforms.