Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Fundamentals

Database and Table Creation

In this lesson you build ShopDB — the small shop database used throughout the SQL Server track.

Beginner 5 min Lesson 3 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:Database Fundamentals
0/31 in SQL Server
On this page · 5 min · Beginner

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Syntax
  5. Code example
  6. Step by step
  7. Real-world use
  8. Common mistakes
  9. Best practices
  10. Remember this
  11. Practice exercise
  12. Mini quiz
  13. Related topics
  14. My notes
Reading time
5 min
Difficulty
Beginner
Updated
Oct 27, 2026

Related tutorials

Interview questions

In simple words#

Creating a table is drawing the columns of a new register before writing any entries: “Roll No (number), Name (text), Date of Birth (date)”.

CREATE DATABASE
Makes a new database.
CREATE TABLE
Defines columns and their types.
IDENTITY
Auto-numbering column (1, 2, 3…).

Learning objectives#

  • Create a database
  • Create tables with appropriate data types
  • Add IDENTITY, PRIMARY KEY and FOREIGN KEY
  • Insert sample data

Explanation#

CREATE DATABASE creates an empty database. USE switches your session to it.

CREATE TABLE defines columns and their data types. IDENTITY(1,1) makes SQL Server generate increasing numbers automatically — ideal for surrogate primary keys.

REFERENCES creates a foreign key so an order cannot point to a customer that does not exist.

GO is not T-SQL; it is a batch separator understood by SSMS and sqlcmd.

Syntax#

SQL Illustrative
CREATE TABLE TableName (
  Id INT IDENTITY(1,1) PRIMARY KEY,
  Column1 DataType NOT NULL,
  OtherId INT REFERENCES OtherTable(Id)
);

Code example: Building ShopDB#

SQLshopdb.sql Illustrative
CREATE DATABASE ShopDB;
GO
USE ShopDB;
GO
CREATE TABLE Customers (
  CustomerId INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(60) NOT NULL,
  City NVARCHAR(40) NOT NULL
);
CREATE TABLE Products (
  ProductId INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(60) NOT NULL,
  Category NVARCHAR(30) NOT NULL,
  Price DECIMAL(10,2) NOT NULL CHECK (Price >= 0)
);
CREATE TABLE Orders (
  OrderId INT PRIMARY KEY,
  CustomerId INT NOT NULL REFERENCES Customers(CustomerId),
  OrderDate DATE NOT NULL,
  Status NVARCHAR(20) NOT NULL DEFAULT 'Pending'
);
CREATE TABLE OrderItems (
  OrderId INT NOT NULL REFERENCES Orders(OrderId),
  ProductId INT NOT NULL REFERENCES Products(ProductId),
  Qty INT NOT NULL CHECK (Qty > 0),
  UnitPrice DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (OrderId, ProductId)
);


INSERT INTO Customers (Name, City) VALUES (N'Asha', N'Pune'), (N'Ravi', N'Delhi'), (N'Meera', N'Pune'), (N'Kabir', N'Mumbai');
INSERT INTO Products (Name, Category, Price) VALUES
  (N'Laptop', N'Electronics', 55000), (N'Mouse', N'Electronics', 700),
  (N'Desk', N'Furniture', 12000), (N'Chair', N'Furniture', 4500),
  (N'Pen', N'Stationery', 20), (N'Notebook', N'Stationery', 60);
INSERT INTO Orders VALUES (101, 1, '2026-01-05', 'Delivered'), (102, 2, '2026-01-07', 'Delivered'),
                          (103, 1, '2026-02-10', 'Pending'),   (104, 3, '2026-02-12', 'Cancelled');
INSERT INTO OrderItems VALUES (101, 1, 1, 55000), (101, 2, 2, 700), (102, 3, 1, 12000),
                              (103, 5, 10, 20), (103, 6, 5, 60), (104, 4, 2, 4500);


SELECT (SELECT COUNT(*) FROM Customers) AS Customers, (SELECT COUNT(*) FROM Products) AS Products,
       (SELECT COUNT(*) FROM Orders) AS Orders, (SELECT COUNT(*) FROM OrderItems) AS OrderItems;
Expected output
Customers  Products  Orders  OrderItems
4          6         4       6

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. The database is created and selected with USE.
  2. Customers and Products use IDENTITY keys, so ids 1, 2, 3… are generated automatically.
  3. Orders.CustomerId references Customers, so every order must belong to a real customer.
  4. OrderItems has a composite primary key (OrderId, ProductId): a product appears at most once per order.
  5. The N prefix (N'Asha') marks Unicode strings for NVARCHAR columns.
  6. The final query counts rows to confirm everything was inserted.

Real-world use case#

Almost every business application starts with a script like this, kept in source control and applied by migrations.

Common mistakes#

Creating child tables first

OrderItems references Orders and Products, so those must exist first. Create parent tables before child tables.

Forgetting USE

Without USE ShopDB, tables are created in whatever database your session is in — often master.

Best practices#

  • Keep schema scripts in source control
  • Add NOT NULL wherever a value is required
  • Use CHECK constraints for simple business rules
  • Name constraints explicitly in production code

Remember this#

CREATE DATABASE → USE → CREATE TABLE.

Every table needs a primary key.

IDENTITY(1,1) = auto-numbering Id.

Practice exercise#

Your task: Add a Categories table (CategoryId, Name) and explain how Products would reference it instead of storing the category name.

Show solution
SQL Illustrative
CREATE TABLE Categories (CategoryId INT IDENTITY PRIMARY KEY, Name NVARCHAR(30) NOT NULL UNIQUE);
-- Products would then have: CategoryId INT NOT NULL REFERENCES Categories(CategoryId)

Mini quiz#

Database and Table Creation quizMultiple choice Question 1 of 2

What does IDENTITY(1,1) do?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.