03. Creating Your First Database & Tables

Level: Beginner

ℹ️ What You'll Learn

Create a database in SQL Server
Create your first table (Student table)
Understand data types (INT, VARCHAR, DATE, etc)
Understand primary keys (unique identifier)
Create the first key School Management System tables
See how tables relate to each other

Now that SQL Server is installed, let's create the School Management System database with its tables.

A database is empty until you create tables. Tables are where data lives.

SQL Server (installed)
|
+-- SchoolManagement (database we create)
    |
    +-- Student table  -> stores student records
    |
    +-- Teacher table  -> stores teacher records
    |
    +-- Exam table     -> stores exam info
    |
    +-- More tables    -> added as the system grows

Step 1: Create the Database

Open SSMS, right-click "Databases" -> "New Database":

-- This is what SSMS does behind the scenes
CREATE DATABASE SchoolManagement;

After creation, you'll see it in the left panel:

Databases
+-- master
+-- model
+-- msdb
+-- tempdb
+-- SchoolManagement  <- Your new database!

Step 2: Understand Tables & Columns

A table is like a spreadsheet:

Student Table (like Excel sheet)
-------------------------------------------------
StudentId | Name           | RollNumber | ClassName
-------------------------------------------------
    1     | Ravi Kumar     | 101        | 10-A
    2     | Priya Sharma   | 102        | 10-A
    3     | Arjun Reddy    | 103        | 10-B
-------------------------------------------------

Column names: StudentId, Name, RollNumber, ClassName
Row data: Each student is one row

Step 3: Understand Data Types

Before creating tables, you must specify what kind of data each column holds:

Data Type	What It Stores	Max Size	Example
INT	Whole numbers	-2.1B to 2.1B	101, 2025, -5
VARCHAR(n)	Text (variable length)	n characters	'Ravi Kumar', 'Mathematics'
NVARCHAR(n)	Unicode text (supports all languages)	n characters	'Ravi Kumar', 'Student Name'
DATE	Date only	YYYY-MM-DD	2025-03-15
DATETIME	Date + Time	YYYY-MM-DD HH:MM:SS	2025-03-15 14:30:45
DECIMAL(p,s)	Decimal numbers	p digits, s decimals	92.50, 1000.00
BIT	Boolean (True/False)	0 or 1	1 (true), 0 (false)
GUID	Unique identifier	36 chars	A1B2C3D4-E5F6-7890-ABCD-EF1234567890

For School Management System:

StudentId -> INT
Name -> VARCHAR(100)
RollNumber -> INT
JoiningDate -> DATE
Fees -> DECIMAL(10,2)
IsActive -> BIT

Step 4: Create Student Table

Click "New Query" in SSMS and paste:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    ClassName VARCHAR(10) NOT NULL,
    Section VARCHAR(5),
    DateOfBirth DATE,
    ParentName NVARCHAR(100),
    ParentPhone VARCHAR(15),
    Address NVARCHAR(255),
    Status VARCHAR(20) DEFAULT 'Active'
);

What each line means:

Line	Meaning
`StudentId INT PRIMARY KEY`	Unique ID for each student (unique identifier)
`IDENTITY(1,1)`	Auto-increment starting from 1
`Name NVARCHAR(100) NOT NULL`	Student name, max 100 chars, cannot be empty
`RollNumber INT NOT NULL`	Roll number, cannot be empty
`ClassName VARCHAR(10)`	Class name like "10-A", max 10 chars
`Section VARCHAR(5)`	Section like "A", "B", "C"
`DateOfBirth DATE`	Birth date, optional
`Status VARCHAR(20) DEFAULT 'Active'`	Default value is 'Active' if not specified

Press F5 to execute.

Success message:

Command(s) completed successfully.

Expand "SchoolManagement" -> "Tables" -> You'll see Student table!

Step 5: Create Other Key Tables

Teacher Table

CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    EmployeeCode VARCHAR(20) NOT NULL UNIQUE,
    Qualification VARCHAR(50),
    ExperienceYears INT,
    Salary DECIMAL(10,2),
    JoiningDate DATE,
    IsActive BIT DEFAULT 1
);

Subject Table

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Code VARCHAR(10),
    ClassName VARCHAR(10),
    MaxMarks INT DEFAULT 100,
    PassingMarks INT DEFAULT 40,
    TeacherId INT,
    IsPractical BIT DEFAULT 0
);

Exam Table

CREATE TABLE Exam
(
    ExamId INT PRIMARY KEY IDENTITY(1,1),
    SubjectId INT NOT NULL,
    ExamName VARCHAR(50),
    ExamType VARCHAR(20), -- UnitTest, MidTerm, Final
    ExamDate DATE,
    MaxMarks INT DEFAULT 100,
    Duration INT -- in minutes
);

ExamResult Table

CREATE TABLE ExamResult
(
    ResultId INT PRIMARY KEY IDENTITY(1,1),
    StudentId INT NOT NULL,
    ExamId INT NOT NULL,
    MarksObtained DECIMAL(5,2),
    IsAbsent BIT DEFAULT 0,
    Remarks NVARCHAR(255)
);

FeeAccount Table

CREATE TABLE FeeAccount
(
    FeeAccountId INT PRIMARY KEY IDENTITY(1,1),
    StudentId INT NOT NULL,
    AcademicYear VARCHAR(10), -- 2024-2025
    TotalFees DECIMAL(10,2),
    PaidAmount DECIMAL(10,2) DEFAULT 0,
    DueDate DATE,
    Status VARCHAR(20) DEFAULT 'Pending' -- Pending, Partial, Paid
);

Attendance Table

CREATE TABLE Attendance
(
    AttendanceId INT PRIMARY KEY IDENTITY(1,1),
    StudentId INT NOT NULL,
    SubjectId INT NOT NULL,
    AttendanceDate DATE,
    IsPresent BIT,
    MarkedBy INT -- Teacher ID
);

Step 6: Verify All Tables Created

In SSMS Object Explorer:

SchoolManagement
+-- Tables
    +-- Student
    +-- Teacher
    +-- Subject
    +-- Exam
    +-- ExamResult
    +-- FeeAccount
    +-- Attendance
    +-- ... (more tables in full system)

Right-click "Student" -> "Edit Top 200 Rows" to see the empty table structure.

Understanding Primary Keys

A primary key is the unique identifier for each row:

Student Table
--------------------------
StudentId | Name
--------------------------
1         | Ravi Kumar
2         | Priya Sharma
3         | Arjun Reddy
--------------------------

StudentId is PRIMARY KEY:
- Each StudentId must be unique
- No two students can have same StudentId
- You always use StudentId to find a specific student

Why it matters:

If you say "Update student 1", SQL Server knows exactly which row
Prevents accidental duplicates
Database integrity

Understanding NOT NULL

NOT NULL means the column must have a value -- you can't leave it empty.

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,  -- Must provide name
    RollNumber INT NOT NULL,        -- Must provide roll number
    DateOfBirth DATE                -- Optional (can be NULL)
);

-- This works -- DateOfBirth is optional
INSERT INTO Student (Name, RollNumber, DateOfBirth)
VALUES ('Ravi', 101, NULL);

-- This FAILS -- Name is required
INSERT INTO Student (Name, RollNumber, DateOfBirth)
VALUES (NULL, 102, '2010-05-15');

Understanding IDENTITY (Auto-Increment)

IDENTITY(1,1) means:

Start at 1
Increment by 1 for each new row
SQL Server assigns automatically

-- You don't provide StudentId -- SQL Server assigns it
INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Ravi Kumar', 101, '10-A');
-- SQL Server assigns StudentId = 1 automatically

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Priya Sharma', 102, '10-A');
-- SQL Server assigns StudentId = 2 automatically

Common Table Creation Mistakes

Mistake 1: Creating table without PRIMARY KEY

Wrong:

CREATE TABLE Student
(
    Name NVARCHAR(100),
    RollNumber INT,
    ClassName VARCHAR(10)
);

Result: No unique identifier. Duplicate students possible. Cannot reliably find specific student.

Fix: Always add PRIMARY KEY with IDENTITY:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    RollNumber INT,
    ClassName VARCHAR(10)
);

Mistake 2: Making ALL columns NOT NULL (too restrictive)

Wrong:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    DateOfBirth DATE NOT NULL,
    ParentPhone VARCHAR(15) NOT NULL,
    Address NVARCHAR(255) NOT NULL
);

Result: Cannot insert student without ALL information. Breaks when you don't have phone number or address yet.

Fix: Only mark truly required columns NOT NULL:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,        -- Required
    RollNumber INT NOT NULL,            -- Required
    ClassName VARCHAR(10) NOT NULL,     -- Required
    DateOfBirth DATE,                   -- Optional (can be NULL)
    ParentPhone VARCHAR(15),            -- Optional
    Address NVARCHAR(255)               -- Optional
);

Mistake 3: Using wrong data type

Wrong:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    RollNumber VARCHAR(20),    -- TEXT for roll number!
    Fees VARCHAR(50)           -- TEXT for fees!
);

Result: Cannot do math (RollNumber + 1). Cannot sum fees. Comparisons break (RollNumber > 100 gives wrong results).

Fix: Use correct data types:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    RollNumber INT,             -- Numbers: INT
    Fees DECIMAL(10,2)          -- Money: DECIMAL
);

Mistake 4: Forgetting DEFAULT values

Wrong:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    Status VARCHAR(20)          -- No DEFAULT
);

Result: Must specify Status every insert. If you forget, it's NULL. Inconsistent data.

Fix: Set sensible DEFAULT:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    Status VARCHAR(20) DEFAULT 'Active'   -- Default set
);

-- Now insert without Status -- auto-filled as 'Active'
INSERT INTO Student (Name, RollNumber) VALUES ('Ravi', 101);
-- Status = 'Active' automatically

Best Practices for Table Design

Use meaningful names -- StudentId not ID
Always have a primary key -- Unique identifier for each row
Use NOT NULL wisely -- Only for truly required fields
Use appropriate data types -- INT for numbers, VARCHAR for text
Set sensible defaults -- Status DEFAULT 'Active'
Plan relationships first -- Before creating tables, sketch connections

🎯 Q1: What is a primary key and why does every table need one?

A primary key is a column that uniquely identifies each row.

Why it matters:

Prevents duplicate rows
Allows you to "find this exact row"
Required for database integrity
Used for relationships between tables

Example:

Student table without primary key:
-------------------------
Name           | Roll
-------------------------
Ravi Kumar     | 101
Ravi Kumar     | 101     <- Duplicate! Problem!
Priya Sharma   | 102

Student table with primary key (StudentId):
------------------------------
StudentId | Name           | Roll
------------------------------
1         | Ravi Kumar     | 101
2         | Ravi Kumar     | 102  <- Different StudentId, same name OK
3         | Priya Sharma   | 102

Every table should have exactly one primary key.

🎯 Q2: What's the difference between INT, VARCHAR, and DATE?

Type	Stores	Space	Example
INT	Whole numbers	4 bytes	101, 2025, -50
VARCHAR(50)	Text	1 byte per char	'Ravi Kumar' (10 bytes)
DATE	Date only	3 bytes	2025-03-15
DATETIME	Date + Time	8 bytes	2025-03-15 14:30:45
DECIMAL(10,2)	Decimal numbers	5-17 bytes	92.50, 1000.00

For School Management:

StudentId -> INT
Name -> VARCHAR(100)
RollNumber -> INT
DateOfBirth -> DATE
Fees -> DECIMAL(10,2)

Choose the right type to save space and prevent errors.

🎯 Q3: What does IDENTITY(1,1) mean?

IDENTITY(1,1) = Auto-increment starting at 1, increment by 1.

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(100)
);

-- Insert without providing StudentId
INSERT INTO Student (Name) VALUES ('Ravi');   -- StudentId = 1
INSERT INTO Student (Name) VALUES ('Priya');  -- StudentId = 2
INSERT INTO Student (Name) VALUES ('Arjun');  -- StudentId = 3

SQL Server assigns StudentId automatically. You never manually enter it.

Why useful: No risk of duplicate IDs, SQL Server manages it.

🎯 Q4: What's the difference between NULL and DEFAULT?

Concept	Meaning	Example
NULL	No value (empty)	DateOfBirth is NULL (not provided)
NOT NULL	Must have value	Name NOT NULL (required)
DEFAULT	Automatic value if not provided	Status DEFAULT 'Active'

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,           -- Required, no default
    Status VARCHAR(20) DEFAULT 'Active',   -- Default value is 'Active'
    DateOfBirth DATE                       -- Optional, can be NULL
);

-- This works:
INSERT INTO Student (Name) VALUES ('Ravi');
-- Status auto-filled as 'Active', DateOfBirth is NULL

-- This fails:
INSERT INTO Student (Status) VALUES ('Active');
-- Error: Name is required!

🎯 Q5: How do I know which data type to use for each column?

Guidelines:

What You're Storing	Data Type	Example
Whole number (ID, count)	INT	StudentId, RollNumber, Age
Decimal number (money, percentage)	DECIMAL(10,2)	Fees 5000.50, Percentage 92.75
Text (names, addresses)	VARCHAR(n) or NVARCHAR(n)	Name, Address, City
Date only	DATE	DateOfBirth, JoiningDate
Date + Time	DATETIME	ExamDate 2025-03-15 14:30
True/False	BIT	IsActive, IsAbsent
Unique identifier	INT IDENTITY or UNIQUEIDENTIFIER	StudentId

For School Management System:

StudentId -> INT IDENTITY
Name -> NVARCHAR(100)
RollNumber -> INT
ClassName -> VARCHAR(10)
DateOfBirth -> DATE
Fees -> DECIMAL(10,2)
IsActive -> BIT
JoiningDate -> DATE

Pick based on what the data represents.

🎯 Q6: Can I modify a table after creating it?

Yes! You can add, remove, or change columns:

-- Add a new column
ALTER TABLE Student ADD Email NVARCHAR(100);

-- Remove a column
ALTER TABLE Student DROP COLUMN Email;

-- Change column type
ALTER TABLE Student ALTER COLUMN Name NVARCHAR(150);

But be careful:

Removing column = loses data
Changing type can break existing data
Better to plan carefully before creating

In production, use migrations instead of direct ALTER (covered in later articles).

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Creating databases and tables with SQL Server. Try these prompts:

"Explain what a database is vs a table. Use the School Management System as an example."
"What is a primary key and why does every table need one?"
"What data types would you use for: Student name, Roll number, Fees, Date of Birth, IsActive status?"
"Walk me through creating the Student table step-by-step"
"Quiz me: ask 5 questions about databases, tables, data types, and primary keys"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

04. Inserting Data Into Tables ->

nexcoding.in

Have questions on your tech stack, ongoing projects, or need one-to-one training?

📧 info@nexcoding.in 💬 WhatsApp +91-9951510727 📸 Instagram — NexCoding ▶ YouTube — NexCoding 📢 WhatsApp Channel — Jobs & Tips

Step 1: Create the Database​

Step 2: Understand Tables & Columns​

Step 3: Understand Data Types​

Step 4: Create Student Table​

Step 5: Create Other Key Tables​

Teacher Table​

Subject Table​

Exam Table​

ExamResult Table​

FeeAccount Table​

Attendance Table​

Step 6: Verify All Tables Created​

Understanding Primary Keys​

Understanding NOT NULL​

Understanding IDENTITY (Auto-Increment)​

Common Table Creation Mistakes​

Best Practices for Table Design​

Next Article​

Step 1: Create the Database

Step 2: Understand Tables & Columns

Step 3: Understand Data Types

Step 4: Create Student Table

Step 5: Create Other Key Tables

Teacher Table

Subject Table

Exam Table

ExamResult Table

FeeAccount Table

Attendance Table

Step 6: Verify All Tables Created

Understanding Primary Keys

Understanding NOT NULL

Understanding IDENTITY (Auto-Increment)

Common Table Creation Mistakes

Best Practices for Table Design

Next Article