36 practice questions with answers for SQL interviews in Thiruvananthapuram.
SQL jobs in Thiruvananthapuram
Thiruvananthapuram's Technopark is one of India's oldest IT parks, hosting IT-services, product and engineering-services companies. Data and testing teams here often work with global clients in finance, travel and healthcare.
Below are 36 questions that match the mix of service, product and domain employers hiring data analyst (sql)s in Thiruvananthapuram.
Solved for ThiruvananthapuramStart 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.
NTILE(n) splits ordered rows into n roughly equal buckets, useful for quartiles or deciles. PERCENT_RANK returns (rank - 1) / (rows - 1), a relative position between 0 and 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. They are used for customer segmentation, percentile reports and scoring.
02
Write a query for monthly revenue for the current year.
SELECT EXTRACT(MONTH FROM order_date) AS month,
SUM(amount) AS revenue,
COUNT(DISTINCT customer_id) AS buyers
FROM orders
WHERE order_date >= DATE '2025-01-01'
AND order_date < DATE '2026-01-01'
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;
A date range in WHERE keeps an index on order_date usable.
03
What is the difference between a primary key and a unique key?
Both enforce uniqueness. A table can have only one primary key, and its columns cannot contain NULL. A table can have many unique constraints, and in most databases a unique column can hold NULL, often more than one, though SQL Server allows only one NULL by default. Primary keys often create a clustered index in SQL Server; unique keys create non-clustered indexes.
04
How would you design a database for an online food delivery app at a high level?
Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.
05
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.
06
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.
Never build SQL by concatenating user input. Use parameterised queries or prepared statements, where input is sent separately from the SQL text, or ORMs that do this automatically. Validate and whitelist inputs such as sort column names, give application users least privilege, and avoid dynamic SQL in procedures unless parameters are bound. Input like ' OR 1=1 -- then cannot change the query logic.
08
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.
09
You need to update 50 million rows in production. How do you do it safely?
Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.
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.
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.
12
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.
13
Find employees who earn more than their department average.
SELECT e.emp_id, e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
To see full duplicate rows:
SELECT * FROM users
WHERE email IN (
SELECT email FROM users
GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email;
Normalise case with LOWER(email) if needed.
15
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.
16
What is the difference between GROUP BY and window functions?
GROUP BY collapses rows into one row per group, so detail columns are lost. Window functions keep every row and add the computed value alongside, such as each employee with their department average. You can combine both: group first, then apply a window over the grouped result, for example percent of total using SUM(SUM(sales)) OVER ().
17
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.
Use string aggregation: STRING_AGG(name, ', ') in PostgreSQL and SQL Server, GROUP_CONCAT(name SEPARATOR ', ') in MySQL, and LISTAGG in some other databases. Most support ordering inside, for example STRING_AGG(name, ', ' ORDER BY name). It is used to list products in an order or skills of an employee in one row, but watch length limits.
19
You added an index but the query still does a full table scan. Why might that be?
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
20
Write a query to pivot order counts by status into columns.
SELECT
customer_id,
SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returned
FROM orders
GROUP BY customer_id;
Conditional aggregation works in every database, unlike vendor-specific PIVOT syntax.
21
What is a window frame and what is the default frame?
A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.
22
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.
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.
24
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.
25
Your running total jumps by large amounts on some dates instead of row by row. Why?
With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.
A view is a saved SELECT query that behaves like a virtual table. It simplifies complex joins for users, hides sensitive columns for security, and provides a stable interface when underlying tables change. A normal view does not store data, so it runs the query each time. Simple views can be updatable, while views with aggregates or joins usually are not.
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.
28
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.
29
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.
30
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.
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.
32
A query that ran in 1 second now takes 40 seconds after a data load. What do you check?
Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.
33
Design tables for students, courses and enrolments.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL
);
CREATE TABLE enrolments (
student_id INT REFERENCES students(student_id),
course_id INT REFERENCES courses(course_id),
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);
The junction table resolves the many-to-many relationship.
34
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.
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.
36
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.
Few big employers listed in Thiruvananthapuram?
Many Thiruvananthapuram candidates also apply to teams in Bangalore and to remote or hybrid roles. Practise the Bangalore set as well.
Which companies hire SQL professionals in Thiruvananthapuram?
Companies with large offices in Thiruvananthapuram include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are SQL interviews in Thiruvananthapuram different from other cities?
The questions depend more on the company than the city. Thiruvananthapuram's mix of employers decides the style: Data and testing teams here often work with global clients in finance, travel and healthcare
How do I find SQL jobs in Thiruvananthapuram?
Shortlist companies with Thiruvananthapuram offices, keep your CV focused on SQL projects, apply on company career pages and job portals with the Thiruvananthapuram filter, and practise the questions on this page before each interview.