A T-SQL Field Manual · SQL Server Edition
SQL
The Vintage Field Manual
From SELECT to Window Functions —
learn SQL by understanding what the database is thinking,
not by memorising a pile of syntax.
SQL SERVER · T-SQL
☞ Before We Touch SQL
“SQL is not about writing commands.
It is about transforming one table into another.”
When an interviewer gives you a complicated SQL problem,
do not immediately start typing.
Ask one question:
What should ONE output row represent?
One customer?
One order?
One department?
One day?
That answer determines much of the SQL that follows.
🗺 The SQL Map
flowchart TD
A["SELECT
What do I want?"]
B["FROM
Where is it?"]
C["JOIN
Need another table?"]
D["WHERE
Filter rows"]
E["GROUP BY
Choose the grain"]
F["AGGREGATE
COUNT / SUM / AVG"]
G["HAVING
Filter groups"]
H["CASE WHEN
Business rules"]
I["NULL / COALESCE
Missing data"]
J["CTE / SUBQUERY
Break into steps"]
K["WINDOW FUNCTION
Analyse without collapsing"]
L["LAG / LEAD
Previous / Next"]
M["DATE FUNCTIONS"]
N["STRING FUNCTIONS"]
O["UNION / UNION ALL"]
P["ORDER BY
Present the result"]
A --> B --> C --> D --> E --> F --> G
G --> H --> I --> J --> K --> L
L --> M --> N --> O --> P
🧭 The Sequence
01 SELECT — choose the information
02 FROM — choose the starting table
03 DISTINCT — remove duplicate results
04 WHERE — filter rows
05 AND / OR / NOT — combine conditions
06 IN / BETWEEN / LIKE — convenient filtering
07 JOIN — connect tables
08 GROUP BY — choose analytical grain
09 COUNT / SUM / AVG / MIN / MAX — measure
10 HAVING — filter groups
11 ORDER BY — sort
12 CASE — business logic
13 NULL / COALESCE — missing values
14 SUBQUERY — nested reasoning
15 CTE — named intermediate reasoning
16 UNION — combine result sets
17 WINDOW FUNCTIONS — analyse without collapsing
18 ROW_NUMBER / RANK / DENSE_RANK — ranking
19 LAG / LEAD — compare rows
20 DATE FUNCTIONS — time analysis
21 STRING FUNCTIONS — text transformation
📜 The Example Database
Throughout this guide, imagine this tiny e-commerce database.
| customers |
orders |
customer_id
name
city
|
order_id
customer_id
order_date
amount
status
|
FIELD NOTE 01
SELECT
Question → What information do I want?
SELECT name, city
FROM customers;
Why?
The database may contain dozens of columns.
SELECT tells SQL which information you actually want to carry forward.
Think of it as:
“Which fields belong in my answer?”
FIELD NOTE 02
FROM
Question → Where is my starting data?
SELECT *
FROM customers;
Why?
Every SQL problem needs a starting point.
FROM establishes the table you are currently standing on.
FIELD NOTE 03
DISTINCT
Question → Do duplicate values matter?
SELECT DISTINCT city
FROM customers;
Why?
DISTINCT means:
“Treat identical result values as one.”
Useful when the question says “unique”.
For example:
SELECT COUNT(DISTINCT hacker_id)
FROM Submissions;
means:
How many unique hackers?
FIELD NOTE 04
WHERE
Question → Which individual rows should survive?
SELECT *
FROM orders
WHERE status = 'completed';
Why?
WHERE is row-level filtering.
Imagine 10,000 rows entering a room.
WHERE says:
“Only these rows may continue.”
FIELD NOTE 05
AND / OR / NOT
SELECT *
FROM orders
WHERE status = 'completed'
AND amount > 500;
Why?
Real questions contain multiple conditions.
AND means:
both must be true.
OR means:
at least one must be true.
NOT reverses the condition.
FIELD NOTE 06
IN / BETWEEN / LIKE
SELECT *
FROM customers
WHERE city IN ('Kolkata','Delhi');
SELECT *
FROM orders
WHERE amount BETWEEN 500 AND 1000;
SELECT *
FROM customers
WHERE name LIKE 'A%';
Why?
These are simply convenient ways of expressing common business questions.
IN → “Is it in this set?”
BETWEEN → “Is it inside this range?”
LIKE → “Does it match this pattern?”
FIELD NOTE 07
JOIN
Question → Is the information I need in another table?
SELECT
c.name,
o.amount
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id;
Why?
Relational databases intentionally separate information into tables.
JOIN reconstructs the relationship when you need information from both.
JOIN = connect related facts.
FIELD NOTE 08
LEFT JOIN
SELECT
c.name,
o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;
Why?
LEFT JOIN means:
“Everyone from my left table must survive.”
If a customer has no order, the order columns become NULL.
The interview question to remember:
“Who must survive even without a match?”
FIELD NOTE 09
GROUP BY
Question → One output row per WHAT?
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
The big idea.
GROUP BY chooses the
grain of your analysis.
GROUP BY customer_id →
one row per customer.
GROUP BY city →
one row per city.
This is the question to ask every time you see GROUP BY:
“At what level am I analysing the data?”
FIELD NOTE 10
Aggregation
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total,
AVG(amount) AS average,
MIN(amount) AS minimum,
MAX(amount) AS maximum
FROM orders
GROUP BY customer_id;
Why?
GROUP BY creates the buckets.
Aggregation measures what's inside them.
GROUP BY → create buckets
SUM/COUNT/AVG → measure buckets
FIELD NOTE 11
HAVING
SELECT
customer_id,
SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;
Why?
WHERE works on individual rows.
HAVING works after grouping.
Remember:
WHERE → filter rows
HAVING → filter groups
FIELD NOTE 12
ORDER BY
SELECT *
FROM orders
ORDER BY amount DESC;
Why?
ORDER BY doesn't change what the data means.
It changes how you want to inspect the result.
ASC = smallest → largest
DESC = largest → smallest
FIELD NOTE 13
CASE WHEN
SELECT
amount,
CASE
WHEN amount >= 1000 THEN 'HIGH'
WHEN amount >= 500 THEN 'MEDIUM'
ELSE 'LOW'
END AS order_category
FROM orders;
Why?
CASE is SQL's business-rule engine.
Raw data:
₹700
Business meaning:
MEDIUM order
CASE converts raw values into business categories.
SQL evaluates WHEN conditions from top to bottom.
FIELD NOTE 14
NULL
SELECT *
FROM customers
WHERE city IS NULL;
Don't write:
WHERE city = NULL;
Why?
NULL represents missing or unknown information.
It isn't zero.
It isn't an empty string.
It means:
“We don't have a value here.”
FIELD NOTE 15
COALESCE
SELECT
name,
COALESCE(city, 'Unknown') AS city
FROM customers;
Why?
COALESCE provides a fallback.
“If the real value is missing, use this one.”
In SQL Server, COALESCE is standard T-SQL and can accept multiple expressions.
FIELD NOTE 16
SUBQUERY
SELECT *
FROM orders
WHERE amount >
(
SELECT AVG(amount)
FROM orders
);
Why?
The problem has two thoughts:
1. Find the average.
2. Find orders above that average.
A subquery lets one query provide the answer needed by another.
FIELD NOTE 17
CTE
WITH customer_total AS
(
SELECT
customer_id,
SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_total
WHERE total > 1000;
Why?
A CTE gives a name to an intermediate result.
Instead of writing one giant SQL monster:
“Do everything!”
you create:
Step 1 → Step 2 → Step 3.
This makes complicated SQL much easier to reason about.
FIELD NOTE 18
UNION
SELECT name
FROM customers_kolkata
UNION
SELECT name
FROM customers_delhi;
Use:
UNION ALL
when duplicates should remain.
Why?
JOIN combines tables horizontally.
UNION combines compatible result sets vertically.
JOIN
→ columns side-by-side
UNION
→ rows one-after-another
FIELD NOTE 19
WINDOW FUNCTIONS ⭐
Question → Can I analyse rows without collapsing them?
SELECT
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders;
This is the important distinction.
GROUP BY:
3 Alice rows
↓
1 Alice row
Window function:
3 Alice rows
↓
3 Alice rows
+
customer total
GROUP BY collapses.
WINDOW FUNCTIONS preserve.
FIELD NOTE 20
PARTITION BY
SUM(amount) OVER (
PARTITION BY customer_id
)
Why?
PARTITION BY creates a separate analytical window.
Think:
Alice's rows → Window A
Bob's rows → Window B
Charlie → Window C
It resembles GROUP BY logically, but rows remain visible.
FIELD NOTE 21
ROW_NUMBER
SELECT
customer_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS rn
FROM orders;
Why?
Within every customer, we establish an order.
That gives us:
1st, 2nd, 3rd...
This is the classic tool for:
- latest record
- top record
- first transaction
- deduplication
- top-N per group
FIELD NOTE 22
RANK / DENSE_RANK
SELECT
name,
salary,
RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees;
For:
100
100
90
RANK:
1
1
3
DENSE_RANK:
1
1
2
ROW_NUMBER:
1
2
3
Why?
The difference is simply:
“What should happen when two rows tie?”
FIELD NOTE 23
LAG
SELECT
order_date,
amount,
LAG(amount) OVER (
ORDER BY order_date
) AS previous_amount
FROM orders;
Why?
Normal SQL sees the current row.
LAG lets the current row ask:
“What happened immediately before me?”
Useful for:
- previous transaction
- month-over-month comparison
- salary changes
- price changes
FIELD NOTE 24
LEAD
SELECT
order_date,
amount,
LEAD(amount) OVER (
ORDER BY order_date
) AS next_amount
FROM orders;
Why?
LAG looks backward.
LEAD looks forward.
LAG ← past | current | future → LEAD
FIELD NOTE 25
ORDER BY inside OVER()
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
)
Important.
This ORDER BY does not necessarily sort the final output.
It tells the window function:
“What sequence should you use for the calculation?”
Compare:
ORDER BY outside OVER()
→ final display order
ORDER BY inside OVER()
→ analytical sequence
FIELD NOTE 26
DATE FUNCTIONS — SQL SERVER
SELECT
order_date,
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAY(order_date) AS order_day
FROM orders;
Calculate difference:
SELECT
DATEDIFF(day, '2026-01-01', order_date)
FROM orders;
Add time:
SELECT
DATEADD(day, 7, order_date)
FROM orders;
Why?
Dates are not just values.
They are analytical dimensions:
day → week → month → quarter → year.
Time-based analysis is everywhere in analytics.
FIELD NOTE 27
STRING FUNCTIONS — SQL SERVER
SELECT
UPPER(name) AS upper_name,
LOWER(name) AS lower_name,
LEN(name) AS name_length
FROM customers;
Combine:
SELECT
CONCAT(name, ' - ', city) AS customer_info
FROM customers;
Why?
Real-world text is messy.
String functions turn raw text into useful analytical fields.
Clean → transform → analyse.
⚙️ What SQL Server Logically Thinks
One of the most useful interview mental models is the logical processing order.
FROM
↓
JOIN
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
DISTINCT
↓
ORDER BY
↓
TOP / OFFSET-FETCH
The written order of a SELECT statement is different from the logical processing order.
This explains many SQL interview questions.
Philosophy:
Don't think:
“SQL reads my query from top to bottom.”
Think:
“SQL logically builds the result in stages.”
🧠 The Interview Pocket Card
| When the interviewer says... |
Think... |
| Show me... |
SELECT |
| From this table... |
FROM |
| Only these records... |
WHERE |
| Information from another table... |
JOIN |
| One row per customer... |
GROUP BY customer_id |
| Total / average / count... |
SUM / AVG / COUNT |
| Groups above... |
HAVING |
| Classify into categories... |
CASE |
| Unique... |
DISTINCT |
| Missing... |
NULL / COALESCE |
| Previous record... |
LAG |
| Next record... |
LEAD |
| Top 1 per group... |
ROW_NUMBER() |
| Rank with ties... |
RANK / DENSE_RANK |
| Analyse but keep every row... |
WINDOW FUNCTION |
| Break complicated query into steps... |
CTE |
| Combine result sets... |
UNION / UNION ALL |
| Previous/next date... |
LAG / LEAD + ORDER BY |
🏆 The Golden Rules
Rule #1
Before writing SQL:
What should one output row represent?
Rule #2
Ask whether your problem wants:
COLLAPSE
↓
GROUP BY
or
KEEP ROWS
↓
WINDOW
Rule #3
Remember:
WHERE
→ rows
HAVING
→ groups
Rule #4
Remember:
JOIN
→ horizontal
UNION
→ vertical
🧩 The Universal SQL Algorithm
1. What should ONE output row represent?
2. What is my starting table?
3. Do I need another table?
4. Which raw rows should survive?
5. Do I need groups?
6. What should I calculate per group?
7. Do I need to filter those groups?
8. Do I need business rules?
9. Can values be NULL?
10. Is the problem becoming multi-step?
11. Do I need to preserve rows while analysing?
12. Do I need previous/next rows?
13. Do I need ranking?
14. Do I need date transformation?
15. Do I need string transformation?
16. Do I need to combine result sets?
17. How should the final result be sorted?
The real SQL skill:
You are not trying to remember 30 commands.
You are decomposing a business question into a sequence of small table transformations.
☞ One Last Mental Picture
BUSINESS QUESTION
│
▼
"What is ONE row?"
│
▼
FROM
│
▼
JOIN
│
▼
WHERE
│
▼
┌────────┴────────┐
│ │
COLLAPSE KEEP ROWS
│ │
▼ ▼
GROUP BY WINDOW
│ │
▼ ▼
AGGREGATION RANK / LAG
│ │
└────────┬────────┘
▼
CASE
│
▼
NULL HANDLING
│
▼
CTE / SUBQUERY
│
▼
FINAL TABLE
│
▼
ORDER BY
SQL becomes much less scary when you stop seeing syntax
and start seeing table transformations.