36 practice questions with answers for SQL interviews in Chandigarh.
SQL jobs in Chandigarh
Chandigarh, Mohali and Panchkula form the Tricity IT cluster, with IT-services firms, SaaS companies and start-ups in Mohali's IT City and Chandigarh's IT Park. Interviews usually check strong fundamentals and practical tool skills.
Below are 36 questions that match the mix of service, product and domain employers hiring data analyst (sql)s in Chandigarh.
Solved for ChandigarhStart 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
A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?
Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.
02
Average order value looks too low in a report. What could be wrong?
Check the grain: if the query averages order items instead of orders, each line counts separately and the average drops. Joins can also duplicate rows. Verify whether cancelled or zero-value orders are included and how NULL amounts are treated. Compute SUM(amount) / COUNT(DISTINCT order_id) at order level and compare with business definitions before changing the report.
03
Create a simple stored procedure that returns orders for a customer.
MySQL:
DELIMITER //
CREATE PROCEDURE get_orders(IN p_customer INT)
BEGIN
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = p_customer
ORDER BY order_date DESC;
END //
DELIMITER ;
CALL get_orders(101);
Parameters prevent SQL injection compared with building query strings in code.
04
Describe a time you found and fixed a wrong number in a report.
Situation: a monthly dashboard showed 30 percent higher revenue than finance figures. Task: find the cause before the leadership review. Action: I traced the SQL, found a join to the shipments table that duplicated orders with split shipments, rewrote it with a pre-aggregated CTE and reconciled totals with finance. Result: numbers matched exactly and I added a row-count test to the report pipeline.
05
What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.
07
What is the difference between a CTE, a subquery, a temp table and a view?
A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.
08
What is the difference between a stored procedure and a function?
A function must return a value or table and can be used inside SELECT or WHERE, but in most databases cannot change data or manage transactions. A stored procedure is executed independently, can return multiple result sets or output parameters, can modify data and control transactions. Use functions for reusable calculations and procedures for workflows such as month-end processing.
09
Why should we hire you for a SQL or data role as a fresher?
Situation: in college I built a library management project with a MySQL backend. Task: make queries fast and correct. Action: I designed normalised tables, wrote queries with joins and group by, and practised over 150 interview problems online. Result: I can write clean, tested SQL today. I learn quickly, ask good questions and want to grow into a strong data professional with your team.
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
A COUNT(DISTINCT) query on a huge events table is very slow. What options do you have?
Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.
A lateral join, written as JOIN LATERAL in PostgreSQL and MySQL 8 or CROSS APPLY and OUTER APPLY in SQL Server, lets a subquery in FROM reference columns of tables listed before it. It acts like a correlated subquery that can return multiple rows and columns, ideal for top N per group, for example the three latest orders for each customer.
13
Implement keyset pagination instead of OFFSET for large tables.
Slow for deep pages:
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000;
Keyset pagination:
SELECT * FROM orders
WHERE id > :last_seen_id
ORDER BY id
LIMIT 50;
OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.
14
Tell me about a time you had to deliver a report under a tight deadline.
Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.
15
Management wants the percentage of each category in total sales. How do you write it?
Use a window aggregate over the grouped result: SELECT category, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 2) AS pct FROM sales GROUP BY category. The inner SUM is per category and the outer SUM over an empty window gives the grand total. Multiplying by 100.0 avoids integer division in some databases.
16
Write an upsert that inserts a row or updates it if it exists.
PostgreSQL:
INSERT INTO inventory (sku, qty)
VALUES ('A100', 10)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
MySQL:
INSERT INTO inventory (sku, qty) VALUES ('A100', 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
SQL Server and others use MERGE. A unique key on sku is required.
AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.
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.
A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.
20
Add a foreign key and a check constraint to an existing table.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);
ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0);
First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.
21
Two systems store customer IDs differently, one as INT and one as VARCHAR with leading zeros. How do you join them?
Convert consistently, for example CAST(b.cust_code AS INT) = a.customer_id, but understand this prevents index use on b.cust_code and fails on non-numeric values. Better options are a computed or persisted column with the converted value and an index, or a mapping table built during ETL. Also validate unmatched rows to catch formatting issues such as spaces.
22
How do you read an EXPLAIN ANALYZE output in PostgreSQL?
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 101;
Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.
23
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').
24
What is the difference between optimistic and pessimistic locking?
Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.
25
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.
26
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.
27
Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?
Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.
28
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.
29
What is the difference between OLTP and OLAP databases?
OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.
30
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.
31
Match each employee's salary to a grade using a range table.
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
32
After joining orders with payments, revenue doubled. What went wrong?
Some orders have multiple payment rows, such as partial payments or retries, so each order row repeats and SUM(order amount) counts it multiple times. This is join fan-out. Fix it by aggregating payments per order in a subquery or CTE before joining, or summing order amounts from the orders table alone. Always check row counts before and after a join.
33
Find the number of employees and average salary in each department.
SELECT department_id,
COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;
Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.
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.
Common types are INT, BIGINT and SMALLINT for whole numbers, DECIMAL or NUMERIC for exact values like money, FLOAT for approximate values, CHAR for fixed-length and VARCHAR for variable-length text, DATE, TIME, DATETIME or TIMESTAMP for dates, and BOOLEAN where supported. Choosing correct types saves space, improves performance and prevents errors such as storing amounts in FLOAT and getting rounding issues.
36
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.
Few big employers listed in Chandigarh?
Many Chandigarh candidates also apply to teams in Gurgaon and to remote or hybrid roles. Practise the Gurgaon set as well.
Which companies hire SQL professionals in Chandigarh?
Companies with large offices in Chandigarh include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are SQL interviews in Chandigarh different from other cities?
The questions depend more on the company than the city. Chandigarh's mix of employers decides the style: Interviews usually check strong fundamentals and practical tool skills
How do I find SQL jobs in Chandigarh?
Shortlist companies with Chandigarh offices, keep your CV focused on SQL projects, apply on company career pages and job portals with the Chandigarh filter, and practise the questions on this page before each interview.