Sunday, September 20, 2026

SQL Server — The Vintage Field Manual
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.

✦ SQL SERVER / T-SQL FIELD MANUAL ✦

Built for interview thinking, analytics, and real-world problem solving.

SELECT → FILTER → CONNECT → GROUP → CALCULATE → ANALYSE → TRANSFORM

No comments:

Post a Comment