36 practice questions with answers for SQL interviews in Kochi.
SQL jobs in Kochi
Kochi's Infopark and SmartCity host IT-services firms, product companies and start-ups, making it Kerala's largest tech job market along with Thiruvananthapuram. Testing and data roles are common in its service and product teams.
Below are 36 questions that match the mix of service, product and domain employers hiring data analyst (sql)s in Kochi.
Solved for KochiStart 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.
Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.
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.
03
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.
04
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.
It depends on the database. SQL Server and MySQL usually inline CTEs like subqueries. PostgreSQL before version 12 always materialised CTEs, which could hurt performance; since version 12 non-recursive CTEs referenced once are inlined, and you can force behaviour with MATERIALIZED or NOT MATERIALIZED. Referencing a heavy CTE many times may cause repeated work, so check the plan.
06
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.
07
Produce region subtotals and a grand total in one query.
SELECT COALESCE(region, 'All Regions') AS region,
COALESCE(city, 'Subtotal') AS city,
SUM(sales) AS sales
FROM store_sales
GROUP BY ROLLUP(region, city)
ORDER BY region, city;
In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.
08
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.
09
Your NOT IN query returns zero rows even though you expect results. What do you check?
Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.
10
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.
11
A join between two large tables takes minutes. How would you speed it up?
Check the execution plan for scans, join type and row estimates. Ensure join keys have indexes and matching data types, because implicit conversion blocks index use. Filter both sides early, select only needed columns, update statistics, and avoid functions on join columns. For very large data, consider partitioning, pre-aggregated tables, or batch processing outside peak hours.
12
Write a query to show each employee with their manager's name.
SELECT e.emp_id,
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.
13
What is the difference between a unique index and a normal index?
A unique index enforces that no two rows have the same value in the indexed columns, and it is created automatically for primary keys and unique constraints. A normal index only speeds up lookups and allows duplicates. Both speed up searches, but a unique index also protects data integrity, for example preventing two users from registering with the same email.
A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
They produce multiple grouping levels in one query. ROLLUP(region, city) gives city totals, region subtotals and a grand total. CUBE(region, product) gives all combinations of subtotals. GROUPING SETS lets you list exact combinations, such as (region), (product) and (). The GROUPING function identifies subtotal rows, which is useful for labelling them in reports.
SQL, or Structured Query Language, is the standard language for working with relational databases. It is used to create tables, insert, update and delete data, query data with filters, joins and aggregations, and control access. Almost every application, analytics team and reporting tool relies on SQL, which is why it is tested in interviews for developers, analysts, testers and data engineers.
17
How do you explain a technical database issue to a non-technical manager?
Situation: my manager asked why a report was late every Monday. Task: explain and get approval for a fix. Action: I compared the database to a busy shop counter where reports and customers queue together, showed the timing data, and proposed a separate reporting copy. Result: he understood the trade-off in five minutes, approved the read replica, and Monday reports became on time.
Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.
19
Calculate a 7-day moving average of daily active users.
SELECT activity_date, dau,
ROUND(AVG(dau) OVER (
ORDER BY activity_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 1) AS dau_7d_avg
FROM daily_users
ORDER BY activity_date;
This assumes one row per day; fill missing dates with a calendar table first, or ROWS will span more than seven days.
20
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.
21
Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?
Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.
22
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 primary key uniquely identifies each row in a table and cannot be NULL or duplicate, such as student_id. A foreign key is a column in one table that references the primary key of another, such as orders.customer_id referencing customers.customer_id. Foreign keys enforce referential integrity, preventing orders for customers that do not exist.
24
Join three tables to get total sales by product category.
SELECT cat.category_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.category_name
ORDER BY revenue DESC;
Join from the most detailed table outward and aggregate at the end.
25
What are database statistics and why do they matter?
Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.
PARTITION BY divides rows into groups and the window function restarts for each group, like per department. ORDER BY defines the order of rows within each partition, needed for ranking, running totals and LAG or LEAD. Without PARTITION BY the whole result is one partition. Without ORDER BY, aggregate windows compute over the entire partition.
27
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.
28
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.
29
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').
Aggregate functions compute a single value from many rows: COUNT, SUM, AVG, MIN and MAX are standard, and databases add others such as STRING_AGG, GROUP_CONCAT, STDDEV and MEDIAN or PERCENTILE_CONT. Used with GROUP BY they return one value per group; without it they return one value for the whole table. Except COUNT(*), they ignore NULL values.
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.
A CROSS JOIN returns the Cartesian product, every row of the first table combined with every row of the second, so 10 rows by 5 rows gives 50 rows. It is useful for generating combinations, such as all stores by all dates to build a calendar grid and then left join actual sales to show zero-sales days. Accidental cross joins from missing conditions produce huge results.
33
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.
34
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.
35
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.
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 Kochi?
Many Kochi candidates also apply to teams in Bangalore and to remote or hybrid roles. Practise the Bangalore set as well.
Companies with large offices in Kochi include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are SQL interviews in Kochi different from other cities?
The questions depend more on the company than the city. Kochi's mix of employers decides the style: Testing and data roles are common in its service and product teams
How do I find SQL jobs in Kochi?
Shortlist companies with Kochi offices, keep your CV focused on SQL projects, apply on company career pages and job portals with the Kochi filter, and practise the questions on this page before each interview.