DBMS Theory and Live SQL round·Engineering·Medium·20 min

Tata Consultancy Services Ninja Technical — DBMS Theory and Live SQL

20 min · 1 credit · scorecard at the end
Z
ZeroPitch Research Desk
Updated 2026-06-09 · Newly added · be among the first to take it
Field
Engineering
Company
Tata Consultancy Services
Role
Assistant System Engineer Trainee (Ninja)
Duration
20 min
Difficulty
Medium
Completions
New
Updated
2026-06-09

What this round is about

This is the TCS Ninja technical interview — the round that determines whether you get the Ninja profile offer. It runs for 20 minutes and is conducted by a TCS engineer with 3 to 8 years of experience. The interviewer is not trying to trick you. They are asking one question: can you actually write SQL that runs, and do you understand the database concepts behind it? This round covers DBMS theory including normalization, keys, ACID properties, JOIN types, DELETE versus TRUNCATE versus DROP, and indexing, followed by live SQL writing on the whiteboard. The candidate who memorised definitions the night before and the candidate who has actually used a database are indistinguishable until the SQL question arrives.

What strong answers look like

Strong candidates apply normalization to a concrete table — they name the functional dependency and show the split, not just define the forms. On ACID, they give the banking transfer scenario immediately rather than reciting the acronym. On JOINs, they choose the correct type for the use case and explain what happens to unmatched rows. On the SQL whiteboard, they write a query that would actually run: correct clause order, HAVING not WHERE for aggregate filters, DISTINCT in the salary query, and they handle the edge case where two employees share the highest salary. They pace themselves and move on after answering rather than over-explaining.

What weak answers look like (and how to avoid them)

The most common failure is accurate definitions with no application. Saying normalization eliminates redundancy and describing 1NF, 2NF, and 3NF correctly is not enough if you cannot normalise a specific table. Similarly, expanding the ACID acronym perfectly but having no banking example is a red flag. On SQL: writing WHERE COUNT(*) greater than 5 instead of HAVING COUNT(*) greater than 5 will fail silently or produce a syntax error on a real database. Omitting DISTINCT from the second-highest salary query means two employees sharing the top salary will hide the true runner-up. The interviewer does not correct these mistakes — they point at the line and wait. Recognise your own errors.

Pre-interview checklist (2 minutes before you start)

  • Can you normalise a student-course-grade table to 3NF out loud right now?
  • Can you write SELECT MAX(salary) FROM employee WHERE salary less than (SELECT MAX(salary) FROM employee) without looking it up?
  • Can you explain HAVING in one sentence that does not use the word WHERE?
  • Do you know what TRUNCATE does to an open transaction?
  • Can you draw a Venn diagram showing INNER JOIN vs LEFT JOIN and explain the NULL column on the right side?

How the AI behaves

The AI interviewer plays Arvind Krishnamurthy, a TCS Senior Software Engineer from the Chennai centre. He is professional, calm, and direct. He does not hint or confirm partial answers. If your SQL has a syntax error, he will point at the specific line and say nothing — it is your job to find and fix it. He will push you to the second form of the salary query if you only write one. He moves on after 90 seconds on any theory question. He is not hostile, but he is not your tutor. Treat this exactly as you would treat the real interview.

Common traps in this type of round

  • Spending too long on definitions and running out of time before reaching the SQL questions, which carry the most weight
  • Writing GROUP BY after HAVING in the clause order, which causes a syntax error
  • Using WHERE to filter on COUNT, SUM, AVG, or any other aggregate function
  • Forgetting DISTINCT in the LIMIT OFFSET form of the second-highest salary query
  • Claiming TRUNCATE is the same as DELETE without a WHERE clause — the critical difference is rollback ability
  • Saying an index slows down writes without explaining the mechanical reason of B-tree update on every write

Sample problems you'll face

The 2 problems below are the same ones you'll work through in the live session — no surprises. Read the constraints carefully; the AI persona will refer you to the on-canvas card by problem number.

  1. 1Second-Highest Salary (SQL)

    Given an Employee table with columns id (int) and salary (int), write a single SQL query that returns the second-highest distinct salary, aliased as second_highest. If no second-highest salary exists, the query must return NULL (not an empty result).

    Example inputEmployee: id | salary 1 | 100 2 | 200 3 | 300
    Example outputsecond_highest 200
    • Distinct salaries only: duplicate values count as one
    • Return NULL, not an empty set, when fewer than two distinct salaries exist
    • A single SQL statement: no stored procedures or procedural loops
    • ANSI SQL; MySQL 8 syntax is acceptable
    • Use the on-canvas card to read the prompt; write your query on the whiteboard. The AI sees what you write.
  2. 2Department-wise Headcount with HAVING (SQL)

    Given an Employee table with columns id (int), name (varchar), and dept_id (int), write a SQL query that returns each dept_id with its employee count aliased as headcount, including only departments that have more than 3 employees, ordered by headcount in descending order.

    Example inputEmployee dept_id per row: 10, 10, 10, 10, 10, 20, 20, 30, 30, 30, 30
    Example outputdept_id | headcount 10 | 5 30 | 4
    • Filter the aggregated count with HAVING, not WHERE
    • Include only departments with headcount greater than 3
    • Order by headcount in descending order
    • Alias the aggregate column as headcount
    • Use the on-canvas card to read the prompt; write your query on the whiteboard. The AI sees what you write.

Interview framework

You will be scored on these 5 dimensions. The full rubric with definitions is below.

Normalization Application Accuracy
Can the candidate apply normalization rules to a given table, not just recite the definitions? Scores highest when candidate walks through each form with a concrete example and names the functional dependency.
25%
SQL Syntax Correctness
Does the candidate write SQL that would actually execute? Covers correct clause order, aggregate placement, GROUP BY completeness, DISTINCT handling, and NULL behaviour on the second-highest salary query.
25%
Aggregate Filtering Logic
Does the candidate correctly distinguish WHERE from HAVING and explain the execution order difference? Penalises attempts to use WHERE to filter COUNT results.
20%
Join Type Selection
Does the candidate select the appropriate JOIN for a query requirement and articulate the behaviour of unmatched rows? Tests INNER vs LEFT vs FULL OUTER reasoning with a Venn model.
15%
Transaction And Index Reasoning
Can the candidate illustrate ACID with a banking example and explain the write cost of indexing? Rewards concrete scenarios over abstract definitions.
15%

What we evaluate

Your final scorecard breaks down across these dimensions. The full rubric and tier criteria are revealed inside the interview itself.

  • Normalization Application25%
  • Live SQL Correctness25%
  • Aggregate Filtering Reasoning20%
  • JOIN Type Selection15%
  • Transaction and Index Depth15%
  • DELETE vs TRUNCATE vs DROP Distinction0%

Common questions

What DBMS topics does the TCS Ninja technical interview cover?
The TCS Ninja technical interview covers normalization (1NF through BCNF), primary, foreign, candidate, and super keys, DELETE vs TRUNCATE vs DROP, all JOIN types, ACID properties with a banking example, indexing trade-offs, and live SQL writing including second-highest salary queries and GROUP BY with HAVING.
How long is the TCS Ninja technical interview?
The TCS Ninja technical interview typically lasts 20 to 30 minutes and is conducted via Microsoft Teams. It is followed by a managerial or HR round on the same day.
What SQL queries are commonly asked in TCS Ninja interviews?
The most commonly asked SQL queries are finding the second highest salary (both the subquery MAX form and the LIMIT 1 OFFSET 1 form), counting employees per department using GROUP BY and HAVING, and writing joins with correct syntax. The interviewer checks whether your query would actually run.
What is the difference between WHERE and HAVING in SQL and why does TCS ask about it?
WHERE filters individual rows before aggregation happens. HAVING filters groups after GROUP BY aggregation. TCS interviewers ask this because you cannot use WHERE to filter on COUNT or other aggregate functions — HAVING is the only correct choice for that. Candidates who confuse the two cannot write working aggregate queries.
How should I explain normalization in a TCS Ninja interview?
Start with a denormalized example table such as an Orders table with repeated customer details, then walk through making it 1NF by removing repeating groups, 2NF by eliminating partial dependencies on composite keys, and 3NF by removing transitive dependencies. BCNF requires every functional determinant to be a candidate key. Do not just recite the definitions — apply them to the table.
What is the difference between DELETE, TRUNCATE, and DROP in SQL?
DELETE removes specific rows using a WHERE clause and can be rolled back within a transaction. TRUNCATE removes all rows from a table faster by not logging individual row deletions and cannot be rolled back in most databases. DROP removes the entire table structure, all data, and all associated constraints permanently.
How do I write a second highest salary query in SQL?
Two standard forms: (1) SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee) — this returns NULL when no second salary exists, which is correct. (2) SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1 — the DISTINCT handles duplicate top salaries correctly. Always be ready to explain what happens when two employees share the highest salary.
What ACID properties should I know for TCS interviews?
Atomicity means a transaction is all-or-nothing — if a bank transfer debits one account but fails before crediting the other, the debit rolls back. Consistency means the database moves from one valid state to another. Isolation means concurrent transactions do not interfere. Durability means committed data survives system crashes.
How does indexing work and why does it slow down writes?
An index is a B-tree data structure built on one or more columns that speeds up SELECT queries by allowing the database to find matching rows without scanning the entire table. However, every INSERT, UPDATE, and DELETE must also update the index, adding overhead. That is the write cost trade-off TCS interviewers want you to articulate.
What JOIN types are tested in TCS Ninja interviews?
INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right with NULL if no match. RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both tables. Self-join joins a table to itself, useful for finding employees with the same department or manager.
How can I practice the TCS Ninja DBMS interview on ZeroPitch?
ZeroPitch provides a realistic AI interviewer modeled on an Indian TCS technical panel. You get 20 minutes of live questioning on normalization, SQL, JOIN types, ACID, and indexing, plus a whiteboard to write your SQL queries. The AI evaluator checks whether your queries are syntactically correct and scores your conceptual depth separately from your live coding accuracy.
What is the difference between a candidate key and a primary key in DBMS?
A super key is any set of attributes that uniquely identifies a tuple. A candidate key is a minimal super key — no attribute can be removed without losing uniqueness. A primary key is the one candidate key chosen by the database designer to be the main identifier for a table. A table can have multiple candidate keys but only one primary key.

Sources this interview is built on

Real candidate-report URLs (Glassdoor / AmbitionBox / PrepInsta / GeeksforGeeks / Medium) reviewed when authoring the questions, persona, and rubric. Verify the realism yourself.