Core Theory / study, don't just checklist

Return every person even if they have no address → LEFT JOIN keeps all left rows; right columns become NULL when unmatched.

SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId;

Trap: an INNER JOIN here silently drops people without an address — the whole point of the question is the LEFT.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

MAX() returns NULL (not an empty set) when there is no second salary — which is exactly the required output. That's why MAX beats LIMIT 1 OFFSET 1, which returns nothing.

SELECT DISTINCT salary AS getNthHighestSalary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
) t
WHERE rnk = N;

DENSE_RANK so tied salaries share a rank and don't skip N. The LIMIT 1 OFFSET N-1 version needs DISTINCT first, else duplicates throw the offset off.

SELECT d.name AS Department, e.name AS Employee, e.salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE (e.departmentId, e.salary) IN (
  SELECT departmentId, MAX(salary)
  FROM Employee GROUP BY departmentId);

Window alternative: RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) = 1. Use RANK (not ROW_NUMBER) so co-highest earners both show.

SELECT d.name AS Department, e.name AS Employee, e.salary
FROM (
  SELECT *, DENSE_RANK() OVER
    (PARTITION BY departmentId ORDER BY salary DESC) AS rk
  FROM Employee
) e
JOIN Department d ON e.departmentId = d.id
WHERE e.rk <= 3;

"Top three distinct salaries" → DENSE_RANK. If it said top-three people, you'd use ROW_NUMBER.

SELECT score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
FROM Scores;

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l2.id = l1.id + 1 AND l2.num = l1.num
JOIN Logs l3 ON l3.id = l1.id + 2 AND l3.num = l1.num;

Three copies offset by id. Window version: compare num against LAG(num) and LEAD(num).

SELECT a.name AS Employee
FROM Employee a
JOIN Employee b ON a.managerId = b.id
WHERE a.salary > b.salary;

Alias the same table twice — a = employee, b = their manager.

SELECT email FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

Why HAVING not WHERE: WHERE filters rows before grouping and can't see COUNT(*). HAVING filters after aggregation.

SELECT name AS Customers FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.customerId = c.id);

Trap: NOT IN (subquery) returns zero rows if the subquery contains a single NULL. Prefer NOT EXISTS or LEFT JOIN … WHERE o.id IS NULL.

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON w1.recordDate = w2.recordDate + INTERVAL 1 DAY
WHERE w1.temperature > w2.temperature;

Trap: don't join on id + 1 — dates can have gaps. Compare the actual dates with DATEDIFF / INTERVAL.

Swap each adjacent pair; the last odd seat stays put.

SELECT
  CASE
    WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM Seat) THEN id
    WHEN id % 2 = 1 THEN id + 1
    ELSE id - 1
  END AS id, student
FROM Seat
ORDER BY id;

Cancellation rate per day, counting only unbanned client and driver.

SELECT request_at AS Day,
  ROUND(AVG(status <> 'completed'), 2) AS 'Cancellation Rate'
FROM Trips
WHERE client_id IN (SELECT users_id FROM Users WHERE banned='No')
  AND driver_id IN (SELECT users_id FROM Users WHERE banned='No')
  AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at;

AVG(boolean) = fraction true — a clean way to compute a rate.

Find runs of ≥3 consecutive ids each with people ≥ 100. Standard trick: id - ROW_NUMBER() is constant within a consecutive run, so group by that difference.

WITH ok AS (
  SELECT *, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
  FROM Stadium WHERE people >= 100)
SELECT id, visit_date, people FROM ok
WHERE grp IN (SELECT grp FROM ok GROUP BY grp HAVING COUNT(*) >= 3)
ORDER BY id;

SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;

Employees with no bonus row have bonus = NULL; you must add IS NULL because NULL < 1000 is unknown, not true.

Joins: INNER = only matching rows · LEFT = all left + matched right (NULLs otherwise) · RIGHT = mirror of LEFT · FULL = all rows from both, NULLs where unmatched.

WHERE vs HAVING: WHERE filters rows before GROUP BY (no aggregates); HAVING filters groups after (aggregates allowed).

Normalisation: 1NF atomic values, no repeating groups · 2NF no partial dependency on part of a composite key · 3NF no transitive dependency (non-key → non-key) · BCNF every determinant is a candidate key.

Indexes are B+ trees that speed up reads/lookups but slow inserts/updates and cost storage; avoid on low-cardinality, write-heavy columns.

ACID: Atomicity (all-or-nothing), Consistency (valid → valid), Isolation (concurrent = serial-like), Durability (survives crash). A transaction is a unit of work committed or rolled back atomically.

Keys: Primary = unique + NOT NULL, one per table · Unique = enforces uniqueness, allows one NULL, many per table · Foreign = references another table's PK (referential integrity).

• Encapsulation — bundle data + methods, hide state behind private, expose via public methods.
• Abstraction — expose what an object does, hide how (interface vs implementation).
• Inheritance — a derived class reuses/extends a base class.
• Polymorphism — one interface, many forms: compile-time (overloading, templates) and run-time (virtual functions).

class Account {
  double bal;                 // encapsulated
public:
  Account(double b): bal(b) {}
  virtual void show() { cout << bal; }   // polymorphic
};
class Savings : public Account {         // inheritance
  using Account::Account;
  void show() override { cout << "S:" << /*...*/ ; }
};

Members: public = everywhere · protected = class + derived classes · private = same class only.

Inheritance mode caps the inherited access:

Base private members are never directly accessible in the derived class regardless of mode.

Copy ctor T(const T&) runs when you: initialise from an existing object, pass an object by value, or return one by value.

class Buf {
  int* p;
public:
  Buf(int n){ p = new int[n]; }
  Buf(const Buf& o){ p = new int[/*n*/]; /* copy */ } // DEEP
  ~Buf(){ delete[] p; }
};

Trap: the default (shallow) copy copies the pointer, so two objects free the same memory → double-free crash. Write a deep copy when you own a pointer.

Destruction runs derived → base (reverse of construction).

Base* b = new Derived();
delete b;   // calls ~Derived() ONLY IF ~Base() is virtual

Trap: if ~Base() is non-virtual, delete b calls only ~Base() → the derived part leaks (undefined behaviour). Always make base destructors virtual when using polymorphism.

Each polymorphic class gets a vtable (array of function pointers). Each object stores a hidden vptr to its class's vtable. A virtual call does obj->vptr->vtable[slot]() — resolved at run time, one extra indirection.

That's why Base* b = new Derived(); b->fn(); calls Derived::fn when fn is virtual.

• Overloading — same name, different parameters, same scope. Resolved at compile time (static polymorphism).
• Overriding — derived redefines a base virtual with the same signature. Resolved at run time (dynamic).

Trap: redefining a non-virtual base method in a derived class is hiding, not overriding — the call resolves by the pointer's static type. Classic "predict the output" bait.

A pure virtual function virtual void f() = 0; makes the class abstract — it can't be instantiated and forces derived classes to implement f.

An abstract class may still have data members and implemented methods. An interface (C++ idiom) is an abstract class with only pure virtuals and no data.

If B and C both inherit A, and D inherits B, C, then D gets two copies of A → ambiguity.

class B : virtual public A { };
class C : virtual public A { };
class D : public B, public C { };  // one shared A

Virtual inheritance makes B and C share a single A subobject.

A static data member is one shared copy for all objects, living for the whole program; you define it once outside the class. A static method has no this, is called on the class, and can only touch static members.

class C { static int count; };
int C::count = 0;   // definition required

A friend can access private/protected members but is not a member (no this). It punches a hole in encapsulation, so use sparingly — the classic legit use is operator<< for printing.

Class = the blueprint/type. Object = a concrete variable built from it, occupying memory. Instance = the same thing as an object — "an instance of the class." Creating one is instantiation.

• const method void f() const promises not to modify members — callable on const objects.
• const ref param const T& avoids a copy and forbids mutation — the default for passing big objects.
• Reference = an alias: can't be null, can't be reseated after init. Pointer = an address: can be null and reassigned, needs */->.

Process = a running program with its own address space (code, data, heap, stack, file descriptors). Thread = a unit of execution inside a process; threads share code + data + heap but each has its own stack + registers + PC.

• Thread↔thread context switch is cheaper (no address-space / TLB swap).
• Isolation: one crashing thread can take down the whole process; separate processes are isolated.
• Communication: threads share memory directly; processes need IPC (pipes, shared memory, message queues).

Trap: "threads share the stack" is false — they share the heap; each thread has its own stack.

Formulas: Turnaround = Completion − Arrival · Waiting = Turnaround − Burst · Response = first-CPU − Arrival. Practise tracing a Gantt chart and averaging.

4 Coffman conditions (all must hold): mutual exclusion · hold and wait · no preemption · circular wait.

• Prevention — break one condition (e.g. request all resources up front, or order them to stop cycles).
• Avoidance — Banker's algorithm: grant a request only if the system stays in a safe state.
• Detection + recovery — allow deadlock, detect via a wait-for graph, then kill/preempt.

Mutex = a lock owned by one thread (lock/unlock by the same thread). Semaphore = a counter with wait()/signal(); binary ≈ mutex, counting allows N holders.

Critical section needs: mutual exclusion, progress, bounded waiting. Producer-consumer uses a bounded buffer with empty/full semaphores + a mutex. Dining philosophers deadlocks on circular wait — fix by ordering forks or limiting concurrent diners.

• Paging — fixed-size frames/pages via a page table; no external fragmentation.
• Segmentation — variable-size logical segments (code/stack/data).
• Virtual memory — demand paging; pages fetched on a page fault.
• TLB — a cache of recent page-table entries to skip the lookup.
• Replacement — FIFO (suffers Belady's anomaly), LRU, Optimal. They make you trace hits/faults for a reference string.

Thrashing = so many page faults the CPU spends all its time swapping, utilisation collapses; fix by reducing the degree of multiprogramming / working-set model.

Internal fragmentation = wasted space inside an allocated block (fixed pages). External fragmentation = enough total free memory but split into holes too small to use (variable segmentation); compaction helps.

• 1NF — atomic values, no repeating groups.
• 2NF — 1NF + no partial dependency (non-key depends on the whole composite key).
• 3NF — 2NF + no transitive dependency (non-key → non-key).
• BCNF — every determinant is a candidate key (stricter 3NF).

Practice: take a table like (StudentID, Course, Instructor, InstructorPhone) and split until each fact lives in one place.

Dirty = read uncommitted data · Non-repeatable = same row differs on re-read · Phantom = new rows appear for the same query.

B+ tree: all data in leaves, leaves linked → great for range scans and ordered reads. Clustered index = the table is physically sorted by that key (one per table). Non-clustered = a separate structure holding key → row-pointer (many per table).

An index hurts when the table is write-heavy (every insert updates the index) or the column has low cardinality (e.g. a boolean).

ER pieces: entities (rectangles), attributes (ovals), relationships (diamonds) with cardinality 1:1 / 1:N / M:N. An M:N becomes a junction table.

Do it now for your project — sketch entities like User, Document, Query, Result and their relationships, so you can draw it live when Oracle asks.

Codd's 12 rules (0–12) define what makes a DBMS truly relational — e.g. the information rule (all data in tables), guaranteed access, systematic treatment of NULLs, a comprehensive data sublanguage, physical/logical data independence. For MCQs, recognise the names; you won't be asked to recite all twelve.

OSI (7): Physical, Data Link, Network, Transport, Session, Presentation, Application. TCP/IP (4): Link, Internet, Transport, Application.

Map the ones they ask about: IP = Network/Internet · TCP & UDP = Transport · HTTP/DNS = Application · MAC/switch = Data Link · router = Network.

TCP — connection-oriented, reliable, ordered, flow + congestion control. 3-way handshake: SYN → SYN-ACK → ACK. Used for web, email, file transfer.

UDP — connectionless, no guarantees, low overhead/fast. Used for DNS, video/voice streaming, online games where speed beats reliability.

DNS resolves the domain to an IP → TCP 3-way handshake → TLS handshake (for https) → browser sends the HTTP request → server responds → browser parses HTML, fetches CSS/JS/images, builds the DOM and renders. Mention caching (DNS cache, browser cache) for bonus points.

Methods: GET (read) · POST (create) · PUT (replace) · PATCH (partial update) · DELETE. Status: 2xx success · 3xx redirect · 4xx client error (401 unauth, 403 forbidden, 404 not found) · 5xx server error. REST = stateless, resource-oriented URLs.

Authentication = who you are (login, JWT). Authorization = what you're allowed (roles/RBAC). Your project's JWT is authn; the role checks are authz.

SDLC phases: requirements → design → implementation → testing → deployment → maintenance. Waterfall runs them once, sequentially. Agile iterates in short sprints with continuous feedback and incremental delivery. Framing an answer explicitly around these phases scores points at ZS/Infosys.

• Profit% = (SP − CP)/CP × 100; Loss% similar.
• SI = P·R·T/100. CI amount = P(1 + R/100)^T; interest = amount − P.
• Successive % changes of a% then b% = a + b + ab/100 (use for population/price chains).
• "x% more" then "x% less" ≠ original — you lose x²/100 %.

• Speed = Distance / Time. Convert km/h → m/s by ×5/18.
• Relative speed: same direction subtract, opposite add.
• Train past a pole = length/speed; past a platform = (length + platform)/speed.
• Boats: downstream = boat + stream, upstream = boat − stream.

If A finishes in a days, A's rate = 1/a per day. Together = 1/a + 1/b; time = reciprocal. Pipes are the same: inlets add, outlets subtract (negative rate). LCM trick: set total work = LCM(a,b) units to avoid fractions.

Average = sum/count. Alligation for mixtures: (cheaper : dearer) = (dearer − mean) : (mean − cheaper) — gives the ratio to mix two things to hit a target price/concentration.

nPr = n!/(n−r)! (order matters) · nCr = n!/(r!(n−r)!) (order doesn't). Probability = favourable/total; for "at least one" use 1 − P(none). Know that nCr = nC(n−r).

LCM × HCF = product of the two numbers. Unit-digit cyclicity (2,3,7,8 repeat every 4). Remainder shortcuts via modular arithmetic; divisibility rules for 3/9 (digit sum), 11 (alternating sum).

Read the axes, units and footnotes first. Approximate aggressively (round to nearby numbers). Watch the difference between percentage and absolute change — the trap in most DI sets. It's the closest thing to the actual consulting job, so ZS/ProcDNA weight it.

Syllogisms → draw Venn diagrams, only "definitely follows" counts. Seating/arrangement → make a grid, place fixed clues first. Blood relations → draw a family tree with +/− for gender. Series → check differences, ratios, alternating patterns, squares/cubes. Coding-decoding → map letter shifts.

RC → find the main idea, don't over-infer. Para-jumbles → locate the opening sentence and connective links (pronouns, "however", "this"). Sentence correction → subject-verb agreement, tense, parallelism, misplaced modifiers.

Memorise: multiplication tables to 25, squares to 30, cubes to 15, and fraction↔percent conversions (1/8 = 12.5%, 1/6 ≈ 16.67%, 1/7 ≈ 14.28%, 1/9 ≈ 11.11%). This alone removes most of the arithmetic time on a speed test.

Start from a big known number (population), segment down with stated assumptions, compute, then sanity-check the magnitude. Say every assumption out loud. e.g. diagnostic labs in Delhi NCR = population ÷ people-per-lab, adjusted for urban density. They grade the structure, not the exact number.

Profitability: Profit = Revenue − Cost; Revenue = Price × Quantity; break each down. Market entry: market attractiveness, capabilities, competition, entry mode. Market sizing: top-down or bottom-up. You need the structure, not consulting jargon — "Case in Point" is the standard book.

ZS works in pharma/healthcare analytics. Read two ZS case studies off their site before the interview and use that vocabulary — almost nobody does this and it visibly lands.

4 litres: fill 5L → pour into 3L (2L left in 5L) → empty 3L → pour the 2L into 3L → fill 5L → top up the 3L (needs 1L) → 4L remains in the 5L jug.

1 litre: fill 3L → pour into 5L → fill 3L again → pour into 5L until full (5L takes 2 more) → 1L remains in the 3L jug.

You can't do it flat. Build a tetrahedron — a triangular pyramid — whose 4 faces are the 4 equilateral triangles. The trick is realising it's a 3-D question. Confirmed at SAP.

Round covers have constant width, so they can't fall through the hole in any orientation (a square cover can fall in diagonally). They also need no aligning and can be rolled.

5 races of 5 (5 races). Race the 5 winners (race 6) → ranks them; the overall winner is #1. Now only certain horses can still be top-3: 2nd & 3rd of the winners' race, 2nd & 3rd from the top group's heats, etc. Race those 5 candidates (race 7) for 2nd and 3rd. Total 7.

Drop the first egg at decreasing gaps so worst case stays constant: floors 14, 27, 39, … (14 + 13 + 12 …). Find smallest x with x(x+1)/2 ≥ 100 → x = 14. If the first egg breaks, linear-scan the small remaining range with the second. Know the DP framing (egg-drop) too.

Weigh 3 vs 3. If balanced, the heavier is in the remaining 2 → weigh those 1 vs 1. If it tips, take the heavier group of 3 → weigh 1 vs 1 (if equal, it's the third). 2 weighings.

100 doors: a door is toggled once per divisor; only perfect squares have an odd number of divisors → doors 1,4,9,…,100 stay open.

3 bulbs 3 switches: turn switch 1 on for a few minutes, off; turn switch 2 on; enter. Lit = 2, off-but-warm = 1, off-and-cold = 3.

100 prisoners: the leader-counts-the-light strategy (one designated counter toggles/counts to 99).

Each rope burns 60 min unevenly. Light rope A at both ends and rope B at one end simultaneously. A finishes in 30 min; at that instant light B's other end — its remaining half now burns in 15 min. 30 + 15 = 45.

1000 bottles, find the poisoned one with ~10 testers (2¹⁰ = 1024). Number bottles 0–999 in binary; tester i drinks from every bottle whose bit i is 1. After the delay, the pattern of which testers die reads off the poisoned bottle's index in binary.

Clock angle = |30·H − 5.5·M| degrees (take 360 − that if >180). Ants on a pole: two ants colliding and reversing is identical to them passing through — so just track max time to fall off. Camel & bananas: optimise segments where the camel makes multiple trips, dropping bananas at intervals.

People cross at different speeds, ≤2 at a time, must carry the one torch; a pair moves at the slower speed. Classic 1/2/5/10: send 1&2 (2), 1 returns (1), send 5&10 (10), 2 returns (2), send 1&2 (2) → 17 min. The trick: let the two slowest cross together, ferried by the two fastest.

You scoped it, built the retrieval pipeline, ran the eval, shipped it as the internship deliverable. STAR it: Situation (what was missing), Task (your mandate), Action (what you built), Result (metric + that it shipped).

Dense vs hybrid vs hybrid+rerank, n = 30, you ran the statistics. Lead with the specific numbers — Dive Deep is scored on the depth of detail you can produce on demand.

An audit of your own repos found three claims that overstated what the code did, and you corrected the paper and the resume rather than shipping them. Interviewers remember this — it demonstrates integrity under your own scrutiny. Make it a crisp STAR.

Shipping the hybrid retriever and measuring it instead of debating architecture forever. Emphasise the speed of the decision and that you validated it after.

The arXiv submission, or the internship deliverable landing on time. Quantify: what shipped, when, against what deadline.

CICR leadership, or teaching yourself the retrieval-eval literature from scratch. Show the gap you spotted and how you closed it.

Have one story for each, even if you reuse a project from a different angle. Don't get caught with a blank on a principle — 16 principles, ~6 stories, mapped to cover the common ones.

Situation → Task → Action → Result. Spend most of the 90 seconds on Action, quantify the Result, and say "I" not "we" — Amazon wants your contribution, not the team's.