LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

Creating Databases and Tables – CREATE, ALTER and DROP

SQL Server uses DDL commands to create and manage database structures. CREATE creates databases and tables, ALTER changes existing structures, and DROP removes database objects.

Creating Databases and Tables – CREATE, ALTER and DROP

Quick Answer: SQL Server uses DDL commands to create and manage database structures. CREATE creates databases and tables, ALTER changes existing structures, and DROP removes database objects.

1. Why Do We Need CREATE, ALTER and DROP?

Imagine Priya manages a grocery shop with 500 different products. She needs to maintain product names, prices and available stock.

Initially, she records only product names and prices. Later, she wants to track stock quantities too.

How can she create the database structure and change it when business needs grow?

The problem without database structure management

Priya could store product details inside one text column. This works for basic storage, but it makes future changes difficult.

Example 1: Store product details in one text column

Assume the GroceryDB database already exists.

CREATE TABLE ProductNotes
(
    ProductDetails VARCHAR(200)
);

INSERT INTO ProductNotes
VALUES
    ('Rice | 1200 | 20'),
    ('Sugar | 800 | 15'),
    ('Milk | 600 | 30');

SELECT ProductDetails
FROM ProductNotes;

Expected output

ProductDetails
--------------------
Rice | 1200 | 20
Sugar | 800 | 15
Milk | 600 | 30

The records are stored successfully. However, this design creates problems.

  1. Product names, prices and quantities are not separate columns.
  2. Searching products by price becomes difficult.
  3. Changing the structure needs additional work.
  4. Maintaining consistent product information becomes harder.

Now imagine Priya managing 500 products and updating stock daily. She needs properly designed database tables.

We need commands to create database structures. We also need commands to change and remove them.

The solution: CREATE, ALTER and DROP

DDL = Data Definition Language. It manages database structures.

CREATE = creates a new database object.

ALTER = changes the structure of an existing object.

DROP = removes an existing database object.

Table = a database structure that stores information in rows and columns.

We will use these commands to manage Priya's grocery database. Each command has a different responsibility.

Animation 1: Managing a grocery database structure

Follow Priya as she creates, changes and removes database objects.

Priya Grocery shop owner
creates the database
CREATE DATABASE GroceryTrainingDB
creates the table
CREATE TABLE Products: ID, Name, Price
adds stock tracking
ALTER TABLE Adds StockQuantity column
removes a temporary table
DROP TABLE TemporaryImport removed
Final structure Products with stock tracking
Database creation Table changes Object removal Current animation stage

Priya creates her database, builds a table, adds stock tracking and removes temporary data structures.

2. Implement CREATE, ALTER and DROP in SQL Server

Let's implement these commands using Priya's grocery shop example.

Create a database using CREATE DATABASE

CREATE DATABASE = creates a new database inside a SQL Server instance.

Priya first needs a separate database for grocery shop information.

Example 2: Create and manage a grocery database

Run this complete script on a SQL Server instance where GroceryTrainingDB does not already exist.

CREATE DATABASE GroceryTrainingDB;
GO
USE GroceryTrainingDB;
GO

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
GO

INSERT INTO Products
    (ProductId, ProductName, Price)
VALUES
    (1, 'Rice', 1200),
    (2, 'Sugar', 800);
GO

ALTER TABLE Products
ADD StockQuantity INT NOT NULL
    CONSTRAINT DF_Products_Stock DEFAULT 0;
GO

CREATE TABLE TemporaryImport
(
    ImportId INT
);
GO
DROP TABLE TemporaryImport;
GO

SELECT ProductId, ProductName, Price, StockQuantity
FROM Products;
GO

Notice how CREATE DATABASE creates Priya's database. The CREATE TABLE statement defines product columns.

The ALTER TABLE command adds stock tracking. Existing products receive the default stock value of zero.

The temporary table is created and removed using DROP TABLE. The Products table remains available.

Expected output

ProductId  ProductName  Price     StockQuantity
---------  -----------  --------  -------------
1          Rice         1200.00   0
2          Sugar         800.00   0

Important: GO is a batch separator understood by tools such as SSMS. It is not a Transact-SQL statement sent directly to the SQL Server engine.

Understand DROP DATABASE

DROP DATABASE = removes an entire database from the SQL Server instance.

For example, the following command removes the training database:

DROP DATABASE GroceryTrainingDB;

Warning: Do not execute this command while you still need the database. Dropping a database removes its database objects and stored data. Always check the database name and take required backups first.

3. Quick Summary

CREATE vs ALTER vs DROP

Comparison of SQL Server DDL commands
Command Purpose Example
CREATE Creates a database object. CREATE TABLE Products
ALTER Changes an existing object. ALTER TABLE Products
DROP Removes an existing object. DROP TABLE Products

The important terms to remember

  • DDL: Commands used to manage database structures.
  • CREATE: Creates databases, tables and other objects.
  • ALTER: Adds, changes or removes table columns and constraints.
  • DROP: Removes database objects.
  • GO: Separates batches in SQL Server tools.

4. Common Mistakes and Best Practices

1. Creating a database that already exists

Running the same CREATE DATABASE statement again causes an error.

Better approach: Check whether the database exists before creating it.

2. Using DROP without checking the object

A wrong table or database name can cause unwanted data loss.

Better approach: Verify the object name and take backups before destructive changes.

3. Adding a NOT NULL column to an existing table

Existing records need valid values for the new column.

Better approach: Provide a suitable DEFAULT value or populate existing records before enforcing NOT NULL.

Design tip: Use meaningful database and column names. Plan table structures before production deployment. Review schema changes before applying them.

5. How CREATE, ALTER and DROP Come Up in Interviews

Question 1: What is DDL in SQL Server?

What the interviewer expects: Explain database structure management commands.

Strong answer: "DDL stands for Data Definition Language. It includes commands such as CREATE, ALTER and DROP. We use them to manage database structures."

Question 2: What is the difference between ALTER and DROP?

What the interviewer expects: Explain modification versus removal.

Strong answer: "ALTER changes an existing database object's structure. DROP removes the object itself. For example, ALTER can add a stock column, while DROP removes a table."

Question 3: What happens when we add a column using ALTER TABLE?

What the interviewer expects: Explain the effect on existing records.

Strong answer: "ALTER TABLE adds a column to an existing table. If the column allows NULL, existing records can contain NULL. If it has a suitable DEFAULT constraint, SQL Server can populate existing rows with that default value."

6. Hands-On Practice

Beginner: School database

Create a database named SchoolDB. Create a Students table with StudentId and StudentName. Add a Class column using ALTER.

Expected result: A Students table containing all three columns.

Intermediate: Hospital database

Create a database named HospitalDB. Create a Patients table and add an AppointmentDate column later.

Hint: Use CREATE DATABASE, CREATE TABLE and ALTER TABLE.

Advanced: Grocery database changes

Extend Priya's Products table with additional business information.

  • Add a Category column.
  • Change the Price column to a suitable precision.
  • Create a temporary import table.
  • Remove the temporary table using DROP.

Challenge: Perform the changes without deleting existing product records.

7. Next Steps and Community

Previous article: Databases, Tables, Rows, Columns and Data Types in SQL Server

Next article: SQL Server Constraints – Primary Key, Foreign Key, Unique and Check

For more details, read the official Microsoft documentation:

Practice creating and changing Priya's grocery database. Share your SQL scripts and discuss different database design choices with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.