SQL Interview Questions in Hyderabad

Show answer

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 query that ran in 1 second now takes 40 seconds after a data load. What do you check?

Indexes and PerformanceMedium

Show answer

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.

Join three tables to get total sales by product category.

JoinsMedium

Show answer

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.

Find employees who earn more than their department average.

Show answer

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.

How do you get the top 3 rows in different databases?

SQL BasicsMedium

Show answer

MySQL and PostgreSQL: SELECT * FROM products ORDER BY price DESC LIMIT 3; SQL Server: SELECT TOP 3 * FROM products ORDER BY price DESC; Standard SQL: SELECT * FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY; Always use ORDER BY, otherwise the top rows are not guaranteed.

How do you prevent SQL injection in applications?

Show answer

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.

Your NOT IN query returns zero rows even though you expect results. What do you check?

Show answer

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.

An interviewer asks to show each employee's salary next to their department's average. How do you do it?

Show answer

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.

What are referential actions such as ON DELETE CASCADE?

Database DesignMedium

Show answer

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.

What is a composite index and why does column order matter?

Indexes and PerformanceEasy

Show answer

A composite index covers multiple columns, such as (customer_id, order_date). It is sorted by the first column, then the second within it. It helps queries filtering on customer_id alone or on both columns, but not queries filtering only on order_date, because of the leftmost prefix rule. Put the most commonly filtered equality column first, followed by range columns.

What is the difference between ROWS and RANGE frames?

Show answer

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.

How do you concatenate values within a group?

Aggregation and GroupingMedium

Show answer

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.

Tell me about a production incident involving a database and how you handled it.

Show answer

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.

Calculate a 7-day moving average of daily active users.

Show answer

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.

Find duplicate email addresses in a users table.

Aggregation and GroupingMedium

Show answer

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.

Tell me about a time you automated a manual data task with SQL.

SQL BasicsMedium

Show answer

Situation: the operations team spent four hours every Monday merging Excel files for a weekly report. Task: reduce the effort. Action: I loaded the files into staging tables, wrote SQL with joins and aggregations to produce the report, and scheduled it as a job. Result: the report was ready automatically at 8 AM, saving about 16 hours a month and removing copy-paste errors.

What are aggregate functions in SQL?

Aggregation and GroupingEasy

Show answer

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.

A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?

Database DesignMedium

Show answer

The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.

Calculate the median salary per department.

Aggregation and GroupingHard

Show answer

PostgreSQL and standard SQL: SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees GROUP BY department_id; SQL Server uses PERCENTILE_CONT as a window function with OVER (PARTITION BY department_id). Median resists outliers better than average for salary analysis.

What is the difference between INNER JOIN and LEFT JOIN?

JoinsEasy

Show answer

INNER JOIN keeps only rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills columns from the right with NULL when there is no match. For customers and orders, INNER JOIN shows only customers who ordered, while LEFT JOIN shows all customers, including those with no orders, which is useful for finding inactive customers.

Find departments where every employee earns above 40000.

Show answer

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.

What are surrogate keys and natural keys?

Database DesignMedium

Show answer

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.

Write a query to get employees from Delhi with salary above 50000, highest first.

SQL BasicsEasy

Show answer

SELECT emp_id, name, salary FROM employees WHERE city = 'Delhi' AND salary > 50000 ORDER BY salary DESC; WHERE filters rows, and ORDER BY DESC sorts from highest to lowest. Add LIMIT 10 in MySQL or PostgreSQL, or TOP 10 in SQL Server, to return only the first ten.

A LEFT JOIN is returning fewer rows than the left table. Why could that happen?

JoinsMedium

Show answer

A LEFT JOIN never removes left rows by itself, so a WHERE condition on the right table is likely filtering out rows where it is NULL. For example WHERE o.status = 'Paid' drops customers without orders. Move the condition into the ON clause or add OR o.status IS NULL. Another cause is a later INNER JOIN in the chain that removes rows.

What are anti joins and semi joins?

JoinsMedium

Show answer

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.

What are database statistics and why do they matter?

Indexes and PerformanceMedium

Show answer

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.

How do you insert, update and delete a row?

SQL BasicsEasy

Show answer

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.

Tell me about a time you used advanced SQL to solve a business problem.

Show answer

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.

What is the difference between a join and a union?

JoinsMedium

Show answer

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.

What is a materialised view?

Show answer

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.

What is a window frame and what is the default frame?

Show answer

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.

What is the difference between SUM with CASE and filtering with WHERE?

Aggregation and GroupingMedium

Show answer

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').

You need to delete duplicate rows but keep one copy of each. How do you do it?

Show answer

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.

Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?

Transactions and Advanced SQLHard

Show answer

Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.

Can you use window functions with aggregate functions in the same query?

Show answer

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.

Create an index to speed up searching orders by customer and date.

Indexes and PerformanceEasy

Show answer

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date); This helps queries like: SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2025-01-01'; Check with EXPLAIN that the index is used, and avoid creating duplicate indexes on the same columns.

Companies with offices in Hyderabad (15)

TCS

SQL interview questions

30 questions
4 rounds

Mumbai, Chennai, Pune +2

Infosys

SQL interview questions

30 questions
4 rounds

Bengaluru, Mysuru, Pune +2

Wipro

SQL interview questions

30 questions
4 rounds

Bengaluru, Hyderabad, Pune +2

Consulting

Accenture

SQL interview questions

30 questions
4 rounds

Bengaluru, Hyderabad, Pune +2

Cognizant

SQL interview questions

30 questions
4 rounds

Chennai, Bengaluru, Pune +2

Capgemini

SQL interview questions

30 questions
4 rounds

Mumbai, Bengaluru, Pune +2

HCLTech

SQL interview questions

30 questions
4 rounds

Noida, Chennai, Bengaluru +2

Tech Mahindra

SQL interview questions

30 questions
4 rounds

Pune, Hyderabad, Bengaluru +2

Product

Amazon

SQL interview questions

30 questions
5 rounds

Bengaluru, Hyderabad, Chennai +2

Product

Microsoft

SQL interview questions

30 questions
5 rounds

Hyderabad, Bengaluru, Noida +2

E-commerce & consumer tech

Flipkart

SQL interview questions

30 questions
5 rounds

Bengaluru, Hyderabad

E-commerce & consumer tech

Swiggy

SQL interview questions

30 questions
4 rounds

Bengaluru, Hyderabad

Banking & finance

JP Morgan Chase

SQL interview questions

30 questions
5 rounds

Mumbai, Bengaluru, Hyderabad

Banking & finance

Goldman Sachs

SQL interview questions

30 questions
5 rounds

Bengaluru, Hyderabad, Mumbai

Consulting

Deloitte

SQL interview questions

30 questions
4 rounds

Hyderabad, Bengaluru, Mumbai +2