📘 MASTERING SQL SERVER (MS SQL)
From DBMS Fundamentals to Advanced Analytics & Real-World Projects
PART 1 — DATABASE FOUNDATIONS (DEFINITIONS + VISUALS)
Chapter 1: Data, Information, and Databases
1.1 Data
Definition: Data is a collection of raw, unprocessed facts without context.
Raw Data: [^101] [Ashutosh] [^50000]
1.2 Information
Definition: Information is processed data that provides meaning.
↓ Context Added
[Employee Ashutosh (ID:101) earns ₹50,000/month]
1.3 Database
Definition: A database is a structured and persistent collection of related data stored
electronically to support efficient retrieval, insertion, and modification.
Real-World Examples:
┌─────────────────┐ ┌──────────────────┐
│ BANK SYSTEM │ │ E-COMMERCE │
├─────────────────┤ ├──────────────────┤
│• Customers │ │• Users │
│• Accounts │ │• Products │
│• Transactions │ │• Orders │
└─────────────────┘ └──────────────────┘
Chapter 2: DBMS vs RDBMS (EXAM + INTERVIEW LEVEL)
2.1 DBMS
Definition: A DBMS manages data as files without enforcing relationships between datasets.
Problems:
[Link]: 101,Ashu,50000 ← Data REDUNDANCY (Ashu repeated)
[Link]: 101,SBIN,2500
[Link]: 101,HDFC,3000 ← NO integrity/relationships
2.2 RDBMS
Definition: An RDBMS organizes data into relations (tables) and enforces relationships using
keys while following ACID properties.
Why RDBMS matters:
Customers Table ──PK──┐
│ FK Relationship
Orders Table ────────┘
Account MUST belong to valid Customer ✓
Chapter 3: Relational Model & Keys
3.1 Table (Relation)
Definition: A table consists of rows (records/tuples) and columns (attributes).
┌──────┬────────────┬──────────┐
│ ROW1 │ FullName │ Email │ ← Columns
├──────┼────────────┼──────────┤
│ 101 │ Ashutosh │ a@gmail │ ← Row (Tuple)
│ 102 │ John │ j@gmail │
└──────┴────────────┴──────────┘
3.2 Keys (CLEAR DEFINITIONS)
Key Definition Example Visual
Primary Key Uniquely identifies a row AccountID ID* ← UNIQUE
References PK of another
Foreign Key CustomerID CustID ──→ [Link]
table
Email/Phone → Could be
Candidate Key Potential PKs Email, Phone
PK
Composite
Combination of columns OrderID+ProductID (OrderID, ProductID)*
Key
Chapter 3.5: Normalization (1NF-3NF/BCNF)
Definition: Normalization designs tables to reduce redundancy and dependency.
Unnormalized → 1NF → 2NF → 3NF
┌──────┬──────────────┐ ┌──────┐ ┌──────┐ ┌──────────┐
│John │Math,Physics │ │John │ │John │ │Students │
│Jane │Math │→ │Math │→ │Math │→ │• SID │
└──────┴──────────────┘ │Physics│ │Jane │ │• SName │
└──────┘ └──────┘ └──────────┘
┌──────────┐
│Courses │
│• CID │
│• CName │
└──────────┘
PART 2 — SQL SERVER BASICS (T-SQL)
Chapter 4: What is SQL?
Definition: SQL (Structured Query Language) is a declarative language used to define,
manipulate, and query relational data. MS SQL uses: T-SQL (Transact-SQL)
Chapter 5: SQL Command Categories
Category Purpose
DDL Define structure
DML Modify data
DQL Query data
DCL Access control
TCL Transactions
PART 3 — DDL (STRUCTURE CREATION)
Chapter 6: CREATE / ALTER / DROP
CREATE DATABASE BankDB;
GO
CREATE TABLE Customers (
CustomerID INT IDENTITY PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
CreatedAt DATETIME DEFAULT GETDATE()
);
Visual: Each row = one bank customer ✓
6.1 Additional Constraints
CHECK Constraint: Enforces domain rules.
ALTER TABLE Customers ADD CONSTRAINT CK_Age CHECK (Age >= 18);
PART 4 — DML (DATA OPERATIONS)
Chapter 7: INSERT / UPDATE / DELETE
INSERT INTO Customers (FullName, Email) VALUES ('Ashutosh Panda', 'ashu@[Link]');
UPDATE Customers SET Email = 'ashutosh@[Link]' WHERE CustomerID = 1;
DELETE vs TRUNCATE:
DELETE TRUNCATE
WHERE allowed No WHERE
Fully Logged Minimally logged
Rollbackable Not rollbackable
Sample Data:
INSERT INTO Customers VALUES ('John Doe', 'john@[Link]');
INSERT INTO Orders (OrderID, CustomerID, Amount) VALUES (1, 1, 1000);
PART 5 — QUERYING DATA (DQL)
Chapter 8: SELECT Execution Order
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
WHY IMPORTANT: Aliases don't work in WHERE (processed later).
8.1 WHERE Clause (VISUAL)
Definition: Filters rows after FROM but before GROUP BY.
Customers ──FROM──┐
▼
┌──────┬──────────┐ WHERE Email LIKE '%@gmail'
│101 │ashu@ym │ ✗ Filtered OUT
│102 │john@gm │ ✓ PASSED ──→ Next Stage
│103 │jane@hot │ ✗ Filtered OUT
└──────┴──────────┘
8.2 GROUP BY Clause (VISUAL)
Definition: Groups rows after WHERE, before HAVING. Used with aggregates.
Orders ──WHERE──┐
▼ GROUP BY CustomerID
┌──────┬──────┐ ┌──────────┬──────────┐
│101 │2500 │ ──▶ │Cust101 │SUM=7500 │
│101 │5000 │ ──▶ │Cust102 │SUM=3000 │
│102 │3000 │ └──────────┴──────────┘
└──────┴──────┘
8.3 String/Date Functions
SUBSTRING(FullName, 1, 3) → 'Joh'
DATEADD(DAY, 30, CreatedAt)
8.4 Views
Definition: Virtual table for simplification/security.
CREATE VIEW ActiveCustomers AS
SELECT * FROM Customers WHERE CreatedAt > DATEADD(YEAR, -1, GETDATE());
8.5 Set Operations
SELECT CustomerID FROM Customers UNION SELECT CustomerID FROM Orders;
PART 6 — JOINS (PROPERLY EXPLAINED)
Chapter 9: JOIN Types (VISUAL VENN DIAGRAMS)
Tables Used:
Customers: ┌──────┬──────┐ Orders: ┌──────┬────────┐
│101 Ash│102 Jn│ │1 101 │2 103 │
└──────┴──────┘ └──────┴────────┘
9.1 INNER JOIN
Definition: Returns only matching rows from both tables.
INNER JOIN Result: ┌──────────┐
Customers ●═══● Orders │Ash│2500 │ ← Only 101 matches
└──────────┘
9.2 LEFT JOIN
Definition: All left table rows + matching right (NULL if no match).
LEFT JOIN Result: ┌──────────┬──────────┐
│Ash │2500 │
│John │NULL │ ← 102 preserved
└──────────┴──────────┘
9.3 RIGHT JOIN, FULL OUTER JOIN, SELF JOIN (as previously defined)
PART 7 — AGGREGATION & ANALYTICS
Chapter 10: Aggregate Functions
Function Use
SUM Total sales
AVG Average salary
COUNT Records
MIN/MAX Limits
10.1 PIVOT/UNPIVOT
SELECT * FROM Sales PIVOT (SUM(Amount) FOR Month IN ([Jan], [Feb])) AS p;
PART 8 — WINDOW FUNCTIONS (VERY IMPORTANT)
Chapter 11: Window Functions
Definition: Performs calculations across a window frame (set of rows) without collapsing
them.
11.1 ROW_NUMBER() (VISUAL)
Definition: Sequential number per row in partition, ordered by expression.
Salary DESC → ROW_NUMBER()
┌──────────┬────────┐ ┌──────────┬──────────────┐
│Ashutosh │100000 │ ──▶ │Ashutosh │1 │
│John │85000 │ ──▶ │John │2 │
│Jane │75000 │ ──▶ │Jane │3 │
└──────────┴────────┘ └──────────┴──────────────┘
11.2 PARTITION BY (VISUAL)
DeptA: [100k→1] [85k→2] DeptB: [90k→1] [60k→2]
│ Partition A │ Partition B
11.3 RANK vs DENSE_RANK
100k, 90k, 90k, 80k
RANK: 1, 2, 2, 4 ← Gap after tie
DENSE: 1, 2, 2, 3 ← No gap
11.4 LAG/LEAD (VISUAL)
Jan[^100] ──LAG→ NULL Growth: N/A
Feb[^120] ──LAG→ 100 Growth: +20
Mar[^90] ──LAG→ 120 Growth: -30
PART 9 — SUBQUERIES & CTE
Chapter 12: CTE (WITH) (VISUAL)
Definition: Temporary named result set for single query readability.
WITH HighEarners AS ( ┌─────────────┐
Salary > 80k ──────▶│Ashu(90k) │
) SELECT * ────────────▶│John(85k) │
└─────────────┘
PART 10 — TRANSACTIONS & CONCURRENCY
Chapter 13: ACID
Property Meaning
Atomicity All or nothing
Consistency Valid state
Isolation No interference
Durability Permanent
PART 11 — PROCEDURAL SQL (T-SQL)
Chapter 14: Stored Procedures & Triggers (as previously defined)
14.1 Error Handling (TRY-CATCH)
BEGIN TRY -- Code END TRY
BEGIN CATCH -- Error handling END CATCH
14.2 Cursors (Use sparingly)
14.3 Dynamic SQL
EXEC sp_executesql N'SELECT * FROM Customers WHERE ID = @ID', N'@ID INT', @ID=1;
PART 12 — PERFORMANCE & OPTIMIZATION
Indexes: CREATE NONCLUSTERED INDEX idx_email ON Customers(Email);
12.1 Columnstore Indexes
CREATE COLUMNSTORE INDEX ON Sales; ← Analytics optimized.
12.2 Query Hints
SELECT * FROM Customers WITH (INDEX(idx_email));
PART 13 — ADVANCED SQL SERVER FEATURES
Chapter 15: Temporal Tables
ALTER TABLE Customers SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Link]))
Chapter 16: JSON Support
JSON_VALUE(json_col, '$.name')
PART 14 — SQL SERVER SPECIFICS & MAINTENANCE
Chapter 17: SSMS Tips
IntelliSense: Auto-complete (Ctrl+Space)
Object Explorer: Browse DBs/tables
Execution Plan: Ctrl+M
Chapter 18: Backup/Restore
BACKUP DATABASE BankDB TO DISK = 'C:\[Link]';
RESTORE DATABASE BankDB FROM DISK = 'C:\[Link]';
PART 15 — REAL-WORLD PROJECTS (CV READY)
PROJECT 1-3 (as previously defined)
Exercise: Top 3 Customers by Sales
WITH RankedSales AS (
SELECT [Link], SUM([Link]) AS Total,
ROW_NUMBER() OVER (ORDER BY SUM([Link]) DESC) AS rn
FROM Customers c JOIN Orders o ON [Link] = [Link]
GROUP BY [Link]
)
SELECT Name, Total FROM RankedSales WHERE rn <= 3;
Result: ┌──────────┬────────┐
│Ashutosh │7500 │ ← Rank 1
│John │3000 │
│Jane │2000 │
└──────────┴────────┘
This complete guide with definitions + visual execution diagrams is interview-ready and
PDF-convertible!
⁂
1. [Link]
2. [Link]
3. [Link]
4. [Link]
5. [Link]
6. [Link]
7. [Link]
8. [Link]
9. [Link]