Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About HCLTech hiring for SQL roles
HCLTech is an Indian multinational IT services and consulting company providing digital engineering, cloud, infrastructure, software products and data services. It works with clients in sectors such as financial services, manufacturing, life sciences and telecom. In India it hires for software engineers, test engineers, data analysts and data engineers through campus drives and programmes like TechBee.
For SQL roles, this HCLTech practice set focuses most on Window Functions (7), SQL Basics (6), Joins (4), Subqueries and CTEs (4). It has 15 easy, 15 medium questions, so you can start easy and work up to the harder ones.
Company type
IT services
Headquarters
Noida, India
Main India offices
Noida, Chennai, Bengaluru, Hyderabad, Lucknow
HCLTech SQL interview process
Online assessment (aptitude + technical)
Technical interview
Managerial round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for HCLTechStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is the difference between a join and a union?
A join combines columns from different tables side by side based on a matching condition, making rows wider. A union stacks results of queries with the same column structure vertically, making the result longer. Use a join to bring customer names next to orders, and a union to combine 2024 and 2025 order tables into one list.
Window functions perform calculations across a set of rows related to the current row without collapsing them into one row like GROUP BY. They use the OVER clause with optional PARTITION BY, ORDER BY and a frame. Examples are ROW_NUMBER, RANK, running SUM, LAG and LEAD. You can show each order alongside the customer's total, which GROUP BY alone cannot do.
03
Write a transaction to transfer money between two accounts.
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1 AND balance >= 5000;
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 2;
COMMIT;
If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.
A semi join returns rows from the first table that have at least one match in the second, without duplicating them, usually written with EXISTS or IN. An anti join returns rows with no match, written with NOT EXISTS or LEFT JOIN with WHERE right.key IS NULL. For example, customers who never ordered is an anti join. They avoid duplicates that a normal join could create.
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates such as COUNT or SUM. For example, WHERE city = 'Pune' keeps Pune rows, and HAVING COUNT(*) > 5 keeps only groups with more than five rows. Filtering early with WHERE is also more efficient.
06
What are equi joins, non-equi joins and natural joins?
An equi join uses equality in the condition, such as a.id = b.id, the most common case. A non-equi join uses other operators like BETWEEN or greater than, for example matching salary to a grade range table. A natural join automatically joins on columns with the same names, which is risky because adding a column can silently change results, so explicit ON is preferred.
07
Rewrite a nested query using CTEs for readability.
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
),
ranked AS (
SELECT customer_id, total,
NTILE(10) OVER (ORDER BY total DESC) AS decile
FROM order_totals
)
SELECT * FROM ranked WHERE decile = 1;
Each CTE is a named step, making top 10 percent customer logic easy to read.
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY customers DESC;
HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.
09
You need to delete duplicate rows but keep one copy of each. How do you do it?
Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.
10
What is the difference between ROWS and RANGE frames?
ROWS counts physical rows, so 2 PRECEDING means exactly two rows before. RANGE works on values of the ORDER BY column, so rows with the same value are treated together, and value ranges like INTERVAL '7 days' PRECEDING are possible in some databases. With duplicate dates, a RANGE running total jumps for all ties at once, while ROWS increases row by row.
11
What is the logical order of execution of a SELECT query?
The logical order is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or OFFSET (or TOP/FETCH). This explains why a column alias defined in SELECT cannot be used in WHERE but can be used in ORDER BY in most databases. The optimiser may execute differently physically, but results follow this logical order.
INSERT INTO students (student_id, name, city)
VALUES (101, 'Anita', 'Noida');
UPDATE students
SET city = 'Gurugram'
WHERE student_id = 101;
DELETE FROM students
WHERE student_id = 101;
Always use WHERE with UPDATE and DELETE; without it every row changes.
13
Can you use window functions with aggregate functions in the same query?
Yes. Window functions are evaluated after GROUP BY, so they can operate on aggregated values. For example, SELECT region, SUM(sales) AS s, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk FROM orders GROUP BY region ranks regions by total sales. The aggregate is computed per group first, and then the window function runs over the grouped rows.
14
An interviewer asks to show each employee's salary next to their department's average. How do you do it?
Use a window aggregate: SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff FROM employees. Unlike GROUP BY, every employee row is kept. Mention that a join with a grouped subquery produces the same result but is longer.
A subquery is a query nested inside another statement. It can appear in WHERE, such as salary > (SELECT AVG(salary) FROM employees), in FROM as a derived table, in SELECT as a scalar value, and in HAVING. It can return a single value, a list for IN, or a table. Subqueries break complex logic into smaller steps.
16
What is the CASE expression and where can it be used?
CASE adds conditional logic in SQL, returning a value based on conditions: CASE WHEN marks >= 60 THEN 'First' WHEN marks >= 45 THEN 'Second' ELSE 'Fail' END. It can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates, for example SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) to pivot data. Conditions are checked in order and the first true one wins.
WITH spend AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total,
NTILE(4) OVER (ORDER BY total DESC) AS quartile
FROM spend;
Quartile 1 contains the highest spenders. Use this for targeted offers or loyalty tiers.
18
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
For salaries 900, 800, 800, 700 ordered descending: ROW_NUMBER gives 1, 2, 3, 4 with unique numbers even for ties; RANK gives 1, 2, 2, 4, skipping after ties; DENSE_RANK gives 1, 2, 2, 3 without gaps. Use ROW_NUMBER for deduplication or picking one row, and DENSE_RANK for Nth highest value questions where ties should share a position.
SELECT emp_id, name, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_rank
FROM employees;
PARTITION BY restarts ranking for each department, and DENSE_RANK keeps equal salaries at the same rank.
20
What is the difference between SUM with CASE and filtering with WHERE?
WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').
21
Two users book the last seat at the same time and both succeed. How do you fix it?
This is a race condition. Wrap the check and booking in one transaction and lock the seat row with SELECT ... FOR UPDATE so the second transaction waits and then sees it is taken. Alternatively use an atomic update such as UPDATE seats SET booked_by = :user WHERE seat_id = :id AND booked_by IS NULL and check affected rows, plus a unique constraint as a final safety net.
22
What are referential actions such as ON DELETE CASCADE?
Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.
An index is a separate data structure, usually a B-tree, that stores column values in sorted order with pointers to rows, so the database can find matching rows without scanning the whole table, like a book index. Indexes speed up WHERE, JOIN, ORDER BY and GROUP BY, but use storage and slow down inserts, updates and deletes because the index must be maintained.
A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.
25
How do you document a database you designed so others can use it?
Situation: I built a reporting schema that three new analysts needed to use. Task: help them work independently. Action: I created an ER diagram, wrote a data dictionary with table purpose, grain, keys and column meanings, added comments in the database, and shared sample queries for common questions. Result: new analysts started writing correct queries in their first week with very few doubts.
26
Find all customers whose email is missing or empty.
SELECT customer_id, name
FROM customers
WHERE email IS NULL
OR TRIM(email) = '';
NULL needs IS NULL, because email = NULL never matches. Empty strings and spaces are separate values, so TRIM handles both. Consider fixing data at entry with NOT NULL and CHECK constraints.
27
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows using a WHERE clause, is logged row by row, fires triggers and can be rolled back inside a transaction. TRUNCATE removes all rows quickly by deallocating pages, usually resets identity values and cannot use WHERE. DROP removes the whole table including structure, indexes and permissions. Use DELETE for selective removal, TRUNCATE to empty staging tables and DROP to remove objects.
An execution plan shows how the database will run a query: which tables are scanned or seeked, which indexes are used, the join algorithms, sort and aggregate steps, and estimated row counts and costs. Use EXPLAIN in MySQL and PostgreSQL, EXPLAIN ANALYZE for actual timing, and the estimated or actual plan in SQL Server. Reading plans is the first step in tuning slow queries.
29
Tell me about a production incident involving a database and how you handled it.
Situation: during a sale, order placement started failing with lock timeouts. Task: restore service quickly. Action: I identified a long-running reporting query holding locks on the orders table, coordinated with the team to kill it, and moved that report to the read replica. Result: orders recovered within ten minutes, and we added query timeouts and a rule that reports never run on the primary.
30
Find employees earning more than the company average.
SELECT emp_id, name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
)
ORDER BY salary DESC;
The inner query is a non-correlated scalar subquery that runs once and returns one value used for comparison.
Tips to crack the HCLTech SQL interview
Revise programming basics, SQL and networking fundamentals, as HCLTech roles span applications and infrastructure services.
Learn about HCLTech's software products business, HCLSoftware, and its engineering and R&D services.
Be clear and honest about your preferred domain and shift flexibility, as some roles involve support or global time zones.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; HCLTech interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the HCLTech SQL interview?
Candidates usually go through 4 stages: Online assessment (aptitude + technical); Technical interview; Managerial round; HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the HCLTech SQL interview?
This practice set leans on Window Functions (7), SQL Basics (6), Joins (4), Subqueries and CTEs (4). Revise those first, then practise explaining one or two of your own projects end to end, because most HCLTech interviewers ask you to walk through real work.
Are these the exact questions asked at HCLTech?
No. These are curated practice questions that match the skills and difficulty usually tested in it services interviews like HCLTech's. They are not a leaked or verified list of past questions.
Where does HCLTech hire SQL professionals in India?
HCLTech has large teams in Noida, Chennai, Bengaluru, Hyderabad, Lucknow. Openings and locations change often, so check the HCLTech careers page and filter by city and experience.