36 practice questions with answers for SQL interviews in Ahmedabad.
SQL jobs in Ahmedabad
Ahmedabad and GIFT City have a growing market for IT services, fintech and product start-ups. Companies here value hands-on skills and the ability to work across tools, especially in smaller teams.
Below are 36 questions that match the mix of service, product and domain employers hiring data analyst (sql)s in Ahmedabad.
Solved for AhmedabadStart 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 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.
02
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.
Constraints enforce rules on data: PRIMARY KEY for unique row identity, FOREIGN KEY for valid references, UNIQUE for no duplicates, NOT NULL for required values, CHECK for conditions like age >= 18, and DEFAULT for automatic values. They protect data quality at the database level, so bad data is rejected even if an application has a bug.
04
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.
A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.
Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.
07
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.
Atomicity: all statements in a transaction succeed or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not interfere and behave as if run in some order, depending on isolation level. Durability: once committed, changes survive crashes because they are written to logs. Banking and payment systems depend on ACID.
09
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.
In standard SQL, GROUP BY and HAVING are evaluated before SELECT, so aliases are not available there. MySQL and PostgreSQL allow aliases in GROUP BY, and MySQL allows them in HAVING, but SQL Server does not. For portable code, repeat the expression or wrap the query in a subquery or CTE and filter on the alias in the outer query.
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.
12
What is a deadlock and how do databases handle it?
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
13
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
14
A retention report needs the percentage of users active in each month after signup. How would you build it in SQL?
Compute each user's signup month with MIN over their activity, then derive month number as the difference between activity month and signup month. Group by cohort month and month number, counting distinct users. Divide by the cohort size using FIRST_VALUE or a window SUM over month zero within each cohort. Validate with a few known users and handle users with no activity.
15
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').
16
What is the difference between an index scan and an index seek?
An index seek navigates the B-tree directly to the matching keys, reading only the needed rows, which is efficient for selective filters. An index scan reads the whole index or a large range of it. A table scan reads every row. Scans are not always bad; for queries returning a large share of rows, a scan can be cheaper than many seeks.
17
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.
18
An interviewer asks for the Nth highest salary in a reusable way. How do you answer?
Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.
19
Someone ran an UPDATE without WHERE on production. What would you do?
Stop further changes and inform the lead and DBA immediately. If the session is still in an open transaction, roll back. Otherwise, restore affected rows from the latest backup or point-in-time recovery to a separate database and update the production table from it, or use audit or history tables if available. Afterwards, add safeguards such as safe-update mode, reviews and restricted write access.
20
Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?
Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.
21
Tell me about a time you used advanced SQL to solve a business problem.
Situation: the product team wanted to know where users dropped off in the onboarding flow. Task: build a funnel from raw event logs. Action: I used window functions to order events per user, LEAD to find the next step and time taken, and grouped the results into a funnel with conversion rates. Result: we found a 40 percent drop at KYC upload, and the redesign improved completion by 15 percent.
22
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.
23
Write SQL to create a students table with a primary key and constraints.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
age INT CHECK (age >= 17),
city VARCHAR(50) DEFAULT 'Delhi',
joined_on DATE
);
PRIMARY KEY ensures unique non-null IDs, UNIQUE blocks duplicate emails, and CHECK validates age.
24
What is parameter sniffing and how do you handle it?
In databases that cache plans, such as SQL Server, the plan for a parameterised query is compiled using the first parameter values. If those values are unusual, the cached plan may be poor for typical values, causing sudden slowness. Fixes include OPTION (RECOMPILE) for that query, OPTIMIZE FOR hints, plan guides, splitting procedures for different data shapes, or updating statistics.
CHAR(n) stores fixed-length strings and pads shorter values with spaces, so every value uses n characters. VARCHAR(n) stores variable-length strings up to n and uses only the needed space plus a small length overhead. CHAR suits fixed codes such as country code or PIN format, while VARCHAR suits names, emails and addresses whose length varies.
26
Describe a time you reviewed someone else's SQL and suggested improvements.
Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.
27
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.
28
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts all rows including those with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. In a table of 100 orders where 10 have no coupon and 30 distinct coupons were used, COUNT(*) is 100, COUNT(coupon) is 90 and COUNT(DISTINCT coupon) is 30.
29
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.
30
Inserts into a heavily used table have become slow. What could be causing it?
Too many indexes increase write cost, so audit and remove unused ones. Random UUID primary keys cause page splits and fragmentation. Triggers, foreign key checks without indexes on child columns, lock contention from long transactions, and log disk bottlenecks also slow inserts. Use batch inserts, sequential keys, proper indexing of foreign keys and monitor waits to find the actual bottleneck.
31
Get the top 2 highest paid employees in each department.
WITH r AS (
SELECT emp_id, name, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT * FROM r WHERE rn <= 2;
Use DENSE_RANK instead of ROW_NUMBER if tied salaries should all be included.
32
You need each customer's latest address from a history table. How do you query it?
Use ROW_NUMBER partitioned by customer and ordered by updated date descending, then keep rn = 1: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM address_history) SELECT * FROM r WHERE rn = 1. Add a tiebreaker such as id DESC in case two updates share the same timestamp.
33
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.
35
Compare this month's and last month's sales per product using a full outer join.
SELECT COALESCE(c.product_id, p.product_id) AS product_id,
COALESCE(c.sales, 0) AS this_month,
COALESCE(p.sales, 0) AS last_month
FROM cur_month c
FULL OUTER JOIN prev_month p
ON c.product_id = p.product_id;
Products sold in only one of the months still appear, with zero for the other month.
36
Find departments where every employee earns above 40000.
SELECT d.department_id, d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e
WHERE e.department_id = d.department_id
AND e.salary <= 40000
)
AND EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
Double negation expresses for all; the EXISTS excludes empty departments.
Frequently asked questions
Which companies hire SQL professionals in Ahmedabad?
Companies with large offices in Ahmedabad include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are SQL interviews in Ahmedabad different from other cities?
The questions depend more on the company than the city. Ahmedabad's mix of employers decides the style: Companies here value hands-on skills and the ability to work across tools, especially in smaller teams
How do I find SQL jobs in Ahmedabad?
Shortlist companies with Ahmedabad offices, keep your CV focused on SQL projects, apply on company career pages and job portals with the Ahmedabad filter, and practise the questions on this page before each interview.