SQL Create, Alter, Drop, and Update Tables

Sat Apr 20, 2024

In this article we will learn the following topics:

What is a table
Creating a Table
Inserting data into the Table
Updating data in the Table
Altering a Table
Difference Between Alter Table Add Column and Alter Table Alter Column
Interview related Questions.

In SQL Server, A table is a database object that stores and organizes data in a structured manner. It consists of rows and columns, where each row represents a single record of data and each column represents a specific attribute or field of that record.
Each column is assigned a specific data type that determines the type of data it can accommodate such as numbers, strings, or temporal data.

Let’s learn how we can create a table in SQL Server

There are two ways to create a new table –
1. Using T-SQL Script
2. Using Table Designer

Create table using T-SQL Script

Create a Database
In case if you haven’t created a database yet, you need to create a database first where the table will be stored. You can use the below Query for creating a database. And, after creating a database we have to use database, so we will use the USE command.

CREATE DATABASE Demo_database;

USE Demo_database;

Create a Table using T-SQL Script: Now, we will create a table under the Demo_database.
Table named “Employees” with columns for Employee ID, first name, last name, email, age, and city. The CustomerID column is set as the primary key.

Use below syntax for creating a table.

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Email VARCHAR(100),

Age INT,

City VARCHAR(50)

);

OR

We can also create a table using table designer in SSMS

Here’s an example of how to create a table using the Table Designer in SQL Server:

Open SQL Server Management Studio and connect to the database you want to create the table in.
Right-click on the Tables folder in Object Explorer and select “New Table”. This will open the Table Designer.

In the Table Designer, you can add columns to your table by clicking on the first cell under “Column Name” and typing in the name of your column. Press Enter to move to the next row.
For each column, you can specify the data type, whether it allows null values, and any other properties you want to set. Let’s create a table called “Employees” with the following columns:
- EmployeeID (INT, PRIMARY KEY)
- FirstName (VARCHAR(50))
- LastName (VARCHAR(50))
- Email (VARCHAR(100))
- Age (INT)
- City (VARCHAR(50))

To add the first column, click on the first cell under “Column Name” and type “EmployeeID”. Press Enter to move to the next row.
Under “Data Type”, select “INT”. Under “Identity Specification”, set “Is Identity” to “Yes” and “Identity Increment” to “1”. Under “Column Properties”, set “Primary Key” to “Yes”.
Add the remaining columns in the same way, setting their data types and any other properties as appropriate.
Once you have defined all the columns and properties for your table, click on the “Save” button in the Table Designer toolbar to save the table.
Give your table a name (for example: “Employees”) and click “OK”.
You can then close the Table Designer and your new table will be created in the database.

So, it is up to you from which way you want to create a table.
Inserting Rows into Table:Below query will insert one row in our Employees Table—INSERT INTO Employees (EmpID, FirstName, LastName, Email, Age, City)
VALUES
(1, ‘John’, ‘Doe’, ‘johndoe@example.com’, 25, ‘New York’);

We can also insert multiple Rows at a time, as shown in below query –

INSERT INTO Employees (EmpID, FirstName, LastName, Email, Age, City)

VALUES

(2, ‘Jane’, ‘Smith’, ‘janesmith@example.com’, 30, ‘Los Angeles’),

(3, ‘Bob’, ‘Johnson’, ‘bobjohnson@example.com’, 40, ‘Chicago’);

This will insert two rows into the Employees table, and you can insert multiple data using above syntax.You can then use SELECT statements to retrieve the data from the table.

Execute the below syntax for view the table’s data:

SELECT * FROM Employees;

Result:

Alter Table:
In SQL Server, the ALTER TABLE statement is used to modify the structure of an existing table. Here are some examples of how to use the ALTER TABLE statement:

Adding a new column:

A developer working with databases may often find the need to add a new column to an existing table. This can be achieved through the use of the ALTER TABLE ADD COLUMN command, which will append the new column to the end of the table.

The syntax for adding a new column is as follows:

ALTER TABLE table_name

ADD column_name datatype;

Example: Add one more column named Employee Salary in Employees table, which table we have created.

ALTER TABLE Employees

ADD Salary MONEY NULL;

Result:

Here you can see the new column ‘Salary’, has been added.

Modifying a column’s data type:

Using ALTER command, we can also change column’s data type. We can do this by using following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name new_datatype;

For example, to change the data type of a column named “Salary” in a table named “Employees” from MONEY to DECIMAL(10, 2), you can use the following command:

ALTER TABLE Employees

ALTER COLUMN Salary DECIMAL (10, 2);

EXEC sp_rename ‘table_name.old_column_name‘, ‘new_column_name‘, ‘COLUMN’;

As you can see in above images, before modifying the column’s data type it was MONEY and after modifying data type into decimal it is showing decimal data type. Same way you can change the data type of a column.

Renaming a column:

To rename a column in a table, use the following syntax:

EXEC sp_rename ‘table_name.old_column_name‘, ‘new_column_name‘, ‘COLUMN’;

For example, to rename a column named “City” to “Location” in a table named “Employees”, you can use the following command:

EXEC sp_rename ‘Employees.City’, ‘Location’, ‘COLUMN’;

Result:

Adding a Primary key:

By using ALTER command, we can add also add a primary key in a column. Use the following syntax for adding a primary key:

ALTER TABLE table_name

ADD CONSTRAINT pk_constraint_name PRIMARY KEY (column_name);

For example, to add a primary key constraint named “PK_Employees ” to a table named “Employees” on a column named “EmpID”, you can use the following command:

ALTER TABLE Employees

ADD CONSTRAINT PK_Employees PRIMARY KEY (Emp_ID);

Adding a Foreign key:

Let’s say we have two more tables, “Orders” and “Customers”, and we want to add a foreign key constraint to the “Orders” table on the “CustomerID” column, which references the “CustomerID” column in the “Customers” table.Here’s the SQL code to create the “Customers” and the “Orders” table:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50)

);

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

OrderDate DATE,

CustomerID INT,

);

Syntax for Adding a Foreign Key:

ALTER TABLE table_name

ADD CONSTRAINT fk_constraint_name

FOREIGN KEY (column_name)

REFERENCES parent_table (parent_column_name);

ADD CONSTRAINT fk_constraint_name

FOREIGN KEY (column_name)

REFERENCES parent_table (parent_column_name);

Now, to add the foreign key constraint to the “Orders” table using ALTER TABLE, you can use the following SQL code:

ALTER TABLE Orders

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

In this example, we’re using the ALTER TABLE statement to add a foreign key constraint to the “Orders” table. The CONSTRAINT keyword is used to specify the name of the foreign key constraint (in this case, “FK_Orders_Customers”). The FOREIGN KEY keyword is used to specify that we’re adding a foreign key constraint. The CustomerID column in the “Orders” table is then specified as the column that the foreign key constraint applies to, and the REFERENCES keyword is used to specify the table and column that the foreign key references (in this case, “Customers” table and “CustomerID” column)Once you run the above SQL code, the foreign key constraint will be added to the “Orders” table, and you’ll be able to enforce referential integrity between the two tables.

Remove a constraint:

Syntax for Drop a constraint using ALTER command:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example: Remove a foreign key constraint named “FK_Orders_Customers” from a table named “Orders”.

you can use the following command:

ALTER TABLE Orders

DROP CONSTRAINT FK_Orders_Customers;

Dropping a column:

For dropping a column from a table, use the following syntax:

ALTER TABLE table_name

DROP COLUMN column_name;

For example, to drop a column named “OrderDate” from a table named “Orders”, you can use the following command:

ALTER TABLE Orders

DROP COLUMN OrderDate;

Delete a Table:For Dropping a table, follow the below syntax:

TABLE Table_name;

Drop table named “Orders”.

DROP TABLE Orders;

Update Table:The UPDATE statement is used to modify the existing records in a table.As we have a table named “Employees”, let’s modify its record using UPDATE statement.For example: Update the Location as “London” whose Employee ID is 1.Syntax:

UPDATE Employees

SET Location = ‘London’

WHERE EmpID = 1

SET Location = ‘London’

WHERE EmpID = 1

In the same way you can update any record using UPDATE statement.While updating data in SQL Server using the UPDATE statement, there are a few points to keep in mind:

Always use a WHERE clause to specify which rows to update. If you don’t include a WHERE clause, all rows in the table will be updated.
Be careful when updating multiple rows at once, as a mistake could result in unintended changes to the data.
Make sure to test your UPDATE statement on a small sample of the data before running it on the entire table.
Keep backups of your data before running any major UPDATE statements, in case you need to revert back to a previous version of the data.
Remember to use the correct data types and syntax when specifying the new values in the SET clause. Incorrect syntax could result in errors or unexpected behaviors.

Difference between the ALTER TABLE ADD COLUMN and ALTER TABLE ALTER COLUMN statements:The ALTER TABLE ADD COLUMN and ALTER TABLE ALTER COLUMN statements are used to modify existing tables in SQL Server, but they serve different purposes.
The ALTER TABLE ADD COLUMN statement is used to add a new column to an existing table. The new column is added at the end of the existing columns.
On the other hand, the ALTER TABLE ALTER COLUMN statement is used to modify the data type or other properties of an existing column in a table.Important interview related questions:

How do you create a table in SQL Server? What are some important considerations when creating a table?
What is the difference between the ALTER TABLE ADD COLUMN and ALTER TABLE ALTER COLUMN statements?
How do you insert data into a table in SQL Server? Can you insert multiple rows at once?
How do you update data in a table in SQL Server? What is the syntax for updating a single row versus updating multiple rows?
What are some best practices for updating data in SQL Server? What precautions should you take to avoid unintended changes to the data?
How do you add a foreign key constraint to a table in SQL Server? What are some benefits of using foreign key constraints?
How do you drop a table in SQL Server? What is the syntax for dropping a table?
Can you rename a table in SQL Server? If so, how?
How do you modify an existing table in SQL Server? What are some common modifications that you might make to a table?

Vijay Kashyap
SQL in simplified manner

SQL Create, Alter, Drop, and Update Tables

In this article we will learn the following topics:

You may also be interested in