CREATE, ALTER and DROP SQL Tables | SQL Server Tutorial

The cornerstone of any SQL database is the table. Basically, these structure functions is very similar to spreadsheets, which store data in very organized grid format. In this section, you will learn how to Create, Drop, Delete, and more related to Table.

Create ,ALter and Drop SQL Tables

1 - SQL CREATE TABLE

To create a new table in the database, use the SQL CREATE TABLE statement. A table’s structure, including column names, data types, and constraints like NOT NULL, PRIMARY KEY, and CHECK, are defined when it is created in SQL.

Syntax:-

CREATE table table_name

(

Column1 datatype (size),

column2 datatype (size),

.

.

columnN datatype(size)

);

Example:-

CREATE TABLE Customer( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50), LastName VARCHAR(50), Country VARCHAR(50), Age INT CHECK (Age >= 0 AND Age <= 99), Phone int(10) );

2 - SQL DROP TABLE

The DROP TABLE command in SQL is a powerful and essential tool used to permanently delete a table from a database, along with all of its data, structure, and associated constraints such as indexes, triggers, and permissions. When executed, this command removes the table and all its contents, making it unrecoverable unless backed up.

Syntax:-

DROP TABLE table_name;

Example:-

SQL Query: DROP TABLE Customer;

3 - SQL ALTER TABLE

The structure of an existing table can be changed by users using the SQL ALTER TABLE command. If we need to rename a table, add a new column, or change the name of an existing column in SQL, the ALTER command is crucial for making schema changes without affecting the data that is already there. This is essential for tasks like:

• Renaming a table.

• Changing a column name.

• Adding or deleting columns.

• Modifying the data type of a column.

Syntax:-

1. Renaming a Table : ALTER TABLE table_name RENAME TO new_table_name;

2. Renaming a Column :ALTER TABLE table_nameRENAME COLUMN old_column_name TO new_column_name;

3. Adding a New Column: ALTER TABLE table_nameADD column_name datatype;

4. Modifying a Column Data Type: ALTER TABLE table_nameMODIFY COLUMN column_name new_datatype;

Summary

From the preceding all examples we have learned how to Use Alter , Create and drop SQL Tables. I hope you understand it.