Top 100+ SQL Query Interview Questions and Answers

25.4K

SQL queries are commands used to talk to a database. They first appeared in the 1970s when Donald D. Chamberlin and Raymond F. Boyce at IBM designed SQL to manage data in relational databases. Since then it has grown into the most common language for storing, retrieving and handling information. Many roles such as developers, testers, analysts and engineers use SQL every day. That is why interviewers ask SQL query questions to check practical skills.This blog includes the top 100+ most commonly asked SQL query interview questions, from beginner-level queries to tricky ones. Each answer is written in a way that is easy to understand and useful for interviews.

What is SQL Query?

Fun Fact: According to the Stack Overflow Developer Survey, 54.1% of professional developers reported using SQL. This shows how important SQL is for working with and managing data in real jobs.

Note: This list of top 100 SQL query interview questions is divided into different categories. It will help you go through the questions faster and find what you need easily.

SQL Query Interview Basics

Note: For better understanding and practical context, we will be using the following sample tables throughout the queries.

1. Employees Table

emp_id	name	department	salary	manager_id	hire_date
101	Alice	HR	50000	NULL	2020-01-15
102	Bob	IT	60000	101	2019-03-10
103	Carol	IT	65000	101	2021-07-01
104	David	Sales	55000	101	2022-11-20
105	Eve	Sales	54000	101	2023-06-05
106	Frank	IT	62000	102	2018-09-25

2. Orders Table

order_id	customer_id	order_date	amount	region
201	C001	2024-01-10	250	North
202	C002	2024-01-15	400	East
203	C001	2024-02-01	300	North
204	C003	2024-02-20	150	South
205	C004	2024-03-05	500	West
206	C002	2024-03-15	200	East

3. Customers Table

customer_id	name	city
C001	John	Delhi
C002	Priya	Mumbai
C003	Ahmed	Bengaluru
C004	Sneha	Kolkata
C005	Ramesh	Hyderabad

Basic SQL Queries for Interview

This is a list of basic interview questions on SQL queries, along with their answers.

What does the SELECT statement do in SQL?

The SELECT statement is used to fetch data from one or more tables in a database. You can choose specific columns or use * to select all columns.

Input:

SELECT name, department FROM employees;

Output:

name	department
Alice	HR
Bob	IT
Carol	IT

How do you retrieve unique records from a table?

This is one of the most common basic SQL query interview questions you might come across.

To remove duplicates, use the DISTINCT keyword. It ensures each value appears only once in the result.

Input:

SELECT DISTINCT city FROM customers;

Output:

city
Delhi
Mumbai
Bengaluru
Kolkata
Hyderabad

What is the difference between WHERE and HAVING clauses?

WHERE filters rows before aggregation; HAVING filters results after aggregation. WHERE works on raw data; HAVING works on grouped results.

How do you sort records in SQL?

Use ORDER BY to arrange results. By default, it sorts in ascending order. You can use DESC for descending.

Input:

SELECT name, salary 
FROM employees 
ORDER BY salary DESC;

Output:

name	salary
Carol	65000
Frank	62000
Bob	60000
David	55000
Eve	54000

What is the use of the LIMIT clause?

LIMIT controls how many rows appear in the result. It’s helpful when you want only a sample of data.

Input:

SELECT * FROM orders LIMIT 3;

Output:

order_id	customer_id	order_date	amount	region
201	C001	2024-01-10	250	North
202	C002	2024-01-15	400	East
203	C001	2024-02-01	300	North

What is a primary key in SQL?

A primary key uniquely identifies each row in a table. It must be unique and cannot be null. Each table can have only one primary key.

What is the difference between UNION and UNION ALL?

UNION combines results from two queries and removes duplicates. UNION ALL combines results but keeps duplicates, making it faster.

Input:

-- UNION (removes duplicates)
SELECT city FROM customers
UNION
SELECT region FROM orders;

-- UNION ALL (keeps duplicates)
SELECT city FROM customers
UNION ALL
SELECT region FROM orders;

Output:

city
Delhi
Mumbai
North
East
South

With UNION, duplicate values like East appear only once. With UNION ALL, duplicates are kept.

Write a query to fetch customers who placed orders using IN and EXISTS.

Both IN and EXISTS can be used to check if a customer has placed an order. IN compares values directly, while EXISTS checks for row existence in a subquery.

Input:

-- Using IN
SELECT name 
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Using EXISTS
SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Output:

name
John
Priya
Ahmed
Sneha

Write a query to display employee and manager names using SELF JOIN.

A self-join is used when a table is joined with itself. Here, each employee is linked to their manager using manager_id.

Input:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

Output:

employee	manager
Bob	Alice
Carol	Alice
David	Alice
Eve	Alice
Frank	Bob

Write a query to get all possible combinations of employees and departments using CROSS JOIN.

A cross join pairs every row from one table with every row from another, giving all possible combinations.

Input:

SELECT e.name, d.department
FROM employees e
CROSS JOIN (SELECT DISTINCT department FROM employees) d;

Output:

name	department
Alice	HR
Alice	IT
Alice	Sales
Bob	HR
Bob	IT

Write a query to display customers who have placed at least one order using INNER JOIN.

An inner join returns only the rows where there is a match in both tables, so only customers with orders appear in the result.

Input:

SELECT c.customer_id, c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Output:

customer_id	name	order_id
C001	John	201
C001	John	203
C002	Priya	202
C002	Priya	206
C003	Ahmed	204
C004	Sneha	205

Only customers who actually placed orders appear. Customers with no orders (like Ramesh) are excluded.

How do you fetch the column names and types for a specific table?

You can query the system catalog information_schema.columns to see column names and their data types.

Input:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'employees';

Output:

column_name	data_type
emp_id	integer
name	varchar
department	varchar
salary	numeric
hire_date	date

What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of rows and return a single value.

Examples:

SELECT 
  COUNT(*) AS total_employees,
  SUM(salary) AS total_salary,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary,
  MIN(salary) AS min_salary
FROM employees;

Output:

total_employees	total_salary	avg_salary	max_salary	min_salary
6	346000	57667	65000	50000

What are constraints in SQL and name a few types?

Constraints define rules on columns to maintain data integrity.

• PRIMARY KEY – Uniquely identifies each row.
• FOREIGN KEY – Ensures values match in another table.
• NOT NULL – Column cannot be null.
• UNIQUE – Ensures all values are different.
• CHECK – Enforces a condition on values.

Note: Basic SQL query-based interview questions often include simple commands, filtering, sorting, joins, and basic aggregations.

Intermediate SQL Query Interview Questions

Here are the commonly asked SQL query interview questions that go beyond the basics and test your practical problem-solving skills.

Write a query to find the third highest salary without using window functions.

To get the third highest, find the maximum salary that is less than the second highest salary (using nested subqueries).

Input:

SELECT MAX(salary) AS third_highest
FROM employees
WHERE salary < (SELECT MAX(salary) 
                FROM employees 
                WHERE salary < (SELECT MAX(salary) FROM employees));

Output:

third_highest
60000

How do you write a FULL OUTER JOIN query?

A full outer join returns all rows from both tables. If there is no match, NULL values are shown for missing columns.

Input:

SELECT c.customer_id, c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Output:

customer_id	name	order_id
C001	John	201
C001	John	203
C002	Priya	202
C002	Priya	206
C004	Sneha	205
C005	Ramesh	NULL

Write a query to find gaps in sequential invoice IDs.

By checking if the next number (id + 1) exists in the table, you can find missing IDs in a sequence.

Input:

SELECT id + 1 AS missing_id
FROM invoices i
WHERE NOT EXISTS (
  SELECT 1 FROM invoices WHERE id = i.id + 1
);

Output:

missing_id
104
109

Write a query to simulate pivoting department data without using the PIVOT operator.

You can simulate pivoting by combining CASE expressions with aggregation functions like SUM().

Input:

SELECT department,
       SUM(CASE WHEN salary > 60000 THEN 1 ELSE 0 END) AS high_salary,
       SUM(CASE WHEN salary <= 60000 THEN 1 ELSE 0 END) AS low_salary
FROM employees
GROUP BY department;

Output:

department	high_salary	low_salary
HR	0	1
IT	2	1
Sales	0	2

What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

• INNER JOIN – returns only matching rows from both tables.
• LEFT JOIN – returns all rows from the left table, and matching rows from the right (NULL if no match).
• RIGHT JOIN – opposite of LEFT JOIN, keeps all rows from the right table.
• FULL JOIN – returns all rows from both tables, with NULLs for missing matches.

Write a query to join three tables and filter based on a product category.

Multi-table joins are used to connect related data. Here, we link customers → orders → products and filter for a category.

Input:

SELECT c.name AS customer, p.product_name, p.category
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE p.category = 'Electronics';

Output:

customer	product_name	category
John	Laptop	Electronics
Priya	Headphones	Electronics

Write a query using SQL joins to validate data mappings between a source and target table.

This is used in testing to make sure values match after migration or ETL.

Input:

SELECT s.id, s.name, t.name AS target_name
FROM source_table s
JOIN target_table t ON s.id = t.id
WHERE s.name <> t.name;

Output:

id	name	target_name
102	Bob	Bobby

Write a query to check data consistency between two related tables.

Use EXCEPT or NOT EXISTS to find mismatches between tables.

Input:

SELECT id, status FROM source_table
EXCEPT
SELECT id, status FROM target_table;

Output:

id	status
110	Active

Write a query to verify the record count between a source and a target table during testing.

Comparing counts is a quick way to check if rows match after ETL loads.

Input:

SELECT 
  (SELECT COUNT(*) FROM source_table) AS source_count,
  (SELECT COUNT(*) FROM target_table) AS target_count;

Output:

source_count	target_count
1000	1000

Write a SQL query to compare row-level data between staging and target tables.

Use FULL OUTER JOIN to identify mismatched or missing records.

Input:

SELECT *
FROM staging s
FULL OUTER JOIN final f ON s.id = f.id
WHERE s.value <> f.value OR s.value IS NULL OR f.value IS NULL;

Output:

id	s.value	f.value
205	NULL	Active
210	Inactive	NULL

Write a query to test transformation logic when values are modified or derived.

Apply transformation rules manually and compare with the target.

Input:

SELECT id, source_value, source_value * 1.1 AS expected, transformed_value
FROM target_table
WHERE ROUND(source_value * 1.1, 2) <> transformed_value;

Output:

id	source_value	expected	transformed_value
301	200	220.00	219.50

Write a query to check for NULLs in key fields after transformation is complete.

Key fields should never be NULL after loading, so this check is mandatory.

Input:

SELECT *
FROM final_table
WHERE key_column IS NULL OR date_field IS NULL;

Output:

key_column	date_field	value
NULL	2024-01-01	Test

Write a query to validate referential integrity between parent and child tables in a data pipeline.

Input:

SELECT *
FROM child_table c
WHERE c.parent_id NOT IN (SELECT id FROM parent_table);

Output:

parent_id	child_value
999	Test Row

Write a query to rank employees by salary within each department.

Use DENSE_RANK() with PARTITION BY to rank within groups.

Input:

SELECT name, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Output:

name	department	salary	rank
Carol	IT	65000	1
Frank	IT	62000	2
Bob	IT	60000	3

Write a query to fetch the top 2 highest paid employees per department.

Use ranking with a filter on rank value.

Input:

SELECT * FROM (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 2;

Output:

name	department	salary	rnk
Carol	IT	65000	1
Frank	IT	62000	2
Bob	IT	60000	3

What is a transaction in SQL?

A transaction is a sequence of operations performed as a single logical unit. It guarantees either all steps succeed or none do.

Also Read - Top 25+ PostgreSQL Interview Questions and Answers

Advanced SQL Queries for Interview

Let’s go through some advanced SQL query interview questions and answers.

How do you split comma-separated values into rows in SQL?

Use built-in string-splitting functions to convert a CSV string into multiple rows.

Input (PostgreSQL):

SELECT unnest(string_to_array('A,B,C', ',')) AS item;

Input (SQL Server):

SELECT value FROM STRING_SPLIT('A,B,C', ',');

Output:

item
A
B
C

Also Read - Top 50+ SQL Server Interview Questions and Answers

Using the employees table, write a SQL query to transpose department names into columns with total salary.

Use CASE with SUM() to pivot rows into department-wise salary totals.

Input:

SELECT
  SUM(CASE WHEN department = 'HR' THEN salary ELSE 0 END) AS HR_Salary,
  SUM(CASE WHEN department = 'IT' THEN salary ELSE 0 END) AS IT_Salary,
  SUM(CASE WHEN department = 'Sales' THEN salary ELSE 0 END) AS Sales_Salary
FROM employees;

Output:

HR_Salary	IT_Salary	Sales_Salary
50000	187000	109000

Using the employees table, find employees earning more than the average salary across the company.

Compare each employee’s salary with the overall average salary using a subquery.

Input:

SELECT emp_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Output:

emp_id	name	salary
103	Carol	65000
106	Frank	62000

Using the orders table, write a query to count the number of orders placed by each customer.

Use COUNT() with GROUP BY to get order counts per customer.

Input:

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

Output:

customer_id	total_orders
C001	2
C002	2
C003	1
C004	1

Write a query to calculate the median salary of employees.

Median is the middle salary, calculated using PERCENTILE_CONT(0.5).

Input:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

Output:

median_salary
57000

How do you calculate the 90th percentile of salaries?

The 90th percentile shows the salary above which only 10% earn.

Input:

SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees;

Output:

p90_salary
64500

Write a recursive query to display the employee hierarchy based on manager relationships.

Recursive CTE builds hierarchy by linking employees to their managers.

Input:

WITH RECURSIVE emp_hierarchy AS (
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy;

Output:

emp_id	name	manager_id	level
101	Alice	NULL	1
102	Bob	101	2
103	Carol	101	2
104	David	101	2
105	Eve	101	2
106	Frank	102	3

How do you write a stored procedure to update multiple tables?

A stored procedure groups multiple SQL statements for reuse and consistency.

Input:

CREATE PROCEDURE update_data()
BEGIN
   UPDATE orders SET amount = amount + 50 WHERE order_id = 201;
   UPDATE customers SET city = 'Delhi' WHERE customer_id = 'C001';
END;

How do you use IF…ELSE in SQL scripts?

Use conditional logic to execute different statements based on conditions.

Input:

IF (SELECT AVG(salary) FROM employees) > 60000
   PRINT 'High average salary'
ELSE
   PRINT 'Normal average salary';

What are temporary tables and when should you use them?

Temporary tables store intermediate results for session use, helpful in complex queries.

Input:

CREATE TEMPORARY TABLE temp_emps AS
SELECT name, salary FROM employees WHERE salary > 60000;

Note: Each SQL question and answer here is designed to test advanced-level concepts in interviews.

Query Optimization & Tuning Questions

Here are the frequently asked SQL optimization questions that test how well you understand performance, indexing, and query tuning.

What’s the impact of join order on query performance?

The join order affects how many rows are processed. Filtering early and joining smaller sets improves speed.

How do you list indexes created on a table?

Indexes speed up queries. Use catalog views to see indexes.

Input:

SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'employees';

Output:

indexname	indexdef
employees_pkey	CREATE UNIQUE INDEX employees_pkey ON employees(emp_id)
emp_dept_idx	CREATE INDEX emp_dept_idx ON employees(department)

How do you implement concurrency control in SQL?

Use transaction isolation levels (READ COMMITTED, SERIALIZABLE) and locks to avoid conflicts in multi-user environments.

What are the properties of a transaction (ACID) that affect performance?

• Atomicity – rollback adds overhead.
• Consistency – constraints can slow inserts.
• Isolation – higher levels mean more locking.
• Durability – logging ensures safety but impacts write speed.

What are views and how are they used for simplifying complex queries?

Views are saved queries that simplify joins and filters. They improve readability but may affect performance if nested.

Input:

CREATE VIEW high_salary_emps AS
SELECT name, salary FROM employees WHERE salary > 60000;

How do you test transformation logic using SQL when values are modified or derived?

Apply the same formula manually and compare results with transformed values.

Input:

SELECT id, source_value, source_value * 1.1 AS expected, transformed_value
FROM target_table
WHERE ROUND(source_value * 1.1, 2) <> transformed_value;

Output:

id	source_value	expected	transformed_value
301	200	220.00	219.50

How can filtering early and joining smaller result sets improve execution time?

Filtering reduces data scanned, so joins run faster. It avoids unnecessary processing on large datasets.

SQL Queries Scenario-Based Interview Questions

These are some scenario-based SQL queries interview questions with answers.

Using the orders table, write a query to calculate the running total of order amounts by customer.

Use the SUM() window function to keep a cumulative total ordered by date per customer.

Input:

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

Output:

customer_id	order_date	amount	running_total
C001	2024-01-10	250	250
C001	2024-02-01	300	550
C002	2024-01-15	400	400
C002	2024-03-15	200	600

From the orders table, find customers who placed more than one order.

Use GROUP BY with HAVING to filter customers with order counts greater than one.

Input:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Output:

customer_id
C001
C002

Using the customers table, find and remove duplicate customer records.

First, identify duplicates using GROUP BY. Then remove extra rows with ROW_NUMBER().

Input (to identify):

SELECT name, city, COUNT(*) 
FROM customers
GROUP BY name, city
HAVING COUNT(*) > 1;

Output:

name	city	count
John	Delhi	2
Priya	Mumbai	2

Input (to remove duplicates):

DELETE FROM customers
WHERE customer_id NOT IN (
   SELECT customer_id FROM (
       SELECT customer_id,
              ROW_NUMBER() OVER (PARTITION BY name, city ORDER BY customer_id) AS rn
       FROM customers
   ) t WHERE rn = 1
);

Write a query to calculate monthly user retention using order or login data.

Use a self-join on users across months to check if they return in the following month.

Input:

SELECT a.user_id, a.login_month, b.login_month AS retained_month
FROM logins a
JOIN logins b ON a.user_id = b.user_id
WHERE a.login_month = '2024-01' 
  AND b.login_month = '2024-02';

Output:

user_id	login_month	retained_month
U001	2024-01	2024-02
U004	2024-01	2024-02

How do you write a SQL query to identify user churn over time?

Churned users are those with only one recorded activity or no activity after a certain date.

Input:

SELECT user_id
FROM activity
GROUP BY user_id
HAVING COUNT(*) = 1;

Output:

user_id
U003
U007

How can you identify slowly changing dimension (SCD) changes using SQL?

Compare current vs. historical data to find attribute changes where old records are still active.

Input:

SELECT h.id, h.address AS old_address, c.address AS new_address
FROM history_table h
JOIN current_table c ON h.id = c.id
WHERE h.address <> c.address 
  AND h.end_date IS NULL;

Output:

id	old_address	new_address
105	Delhi	Gurugram
109	Bangalore	Hyderabad

Note: Scenario-based SQL queries questions for interview often test your problem-solving skills and real-world understanding.

Tricky SQL Queries for Interview

This is a list of tricky SQL queries interview questions and their answers.

Using the orders table, find customers who didn’t place an order in the last 30 days.

Use NOT IN with a subquery to filter out customers who recently ordered.

Input:

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Output:

customer_id
C004
C007

Calculate the percentage of total order amount contributed by each region.

Use SUM() with window functions to compute percentage share by region.

Input:

SELECT region,
       SUM(amount) AS total,
       ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER(), 2) AS percentage
FROM orders
GROUP BY region;

Output:

region	total	percentage
North	1200	30.00
South	1800	45.00
West	1000	25.00

Using the orders table, write a query to find the number of days between order_date and today.

Use date arithmetic to subtract order_date from the current date.

Input:

SELECT order_id, customer_id, 
       CURRENT_DATE - order_date AS days_since_order
FROM orders;

Output:

order_id	customer_id	days_since_order
201	C001	45
202	C002	30
203	C001	10

Write a query to find customers who have placed orders in all regions.

You might also come across complex SQL queries interview questions like this one. Use HAVING COUNT(DISTINCT region) equal to total distinct regions in orders.

Input:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT region) = (SELECT COUNT(DISTINCT region) FROM orders);

Output:

customer_id
C001
C004

Note: Tricky SQL-based interview questions are often asked in technical rounds to assess logical thinking and query skills.

Using the orders table, calculate the cumulative percentage of sales per region.

Use a window function with SUM() to get cumulative totals and divide by overall sales.

Input:

SELECT region, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY order_date) * 100.0 / SUM(amount) OVER() AS cumulative_pct
FROM orders;

Output:

region	amount	cumulative_pct
North	250	5.5
North	300	12.1
East	400	21.0
East	200	25.5

Using the employees table with manager_id, flatten the reporting hierarchy using a recursive CTE.

A recursive CTE retrieves all employee-manager relationships and levels in the hierarchy.

Input:

WITH RECURSIVE hierarchy AS (
  SELECT emp_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, h.level + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM hierarchy;

Output:

emp_id	name	manager_id	level
101	Alice	NULL	1
102	Bob	101	2
103	Carol	101	2
106	Frank	102	3

Write a query to calculate running average of order amount by customer.

Use AVG() as a window function ordered by order_date for each customer.

Input:

SELECT customer_id, order_date,
       AVG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_avg
FROM orders;

Output:

customer_id	order_date	running_avg
C001	2024-01-10	250
C001	2024-02-01	275
C002	2024-01-15	400
C002	2024-03-15	300

Note: Complex SQL queries questions for interview often focus on nested queries, advanced joins, and performance logic.

SQL Queries Interview Questions for Freshers

Here are some frequently asked interview questions on SQL queries and their answers.

What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and work with relational databases. It allows you to create, read, update, and delete data stored in tables.

What is an SQL query?

An SQL query is a command written in SQL to perform an action on a database – like retrieving data, updating records, or deleting rows. For example, SELECT * FROM users; is a query to fetch all user records.

What are the different types of SQL statements?

SQL statements fall into categories like:

• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DDL (Data Definition Language): CREATE, ALTER, DROP
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

How do you insert data into a table?

Use the INSERT INTO statement to add new rows.

Input:

INSERT INTO employees (emp_id, name, department, salary)
VALUES (107, 'George', 'Finance', 58000);

Output (new row added):

emp_id	name	department	salary
107	George	Finance	58000

What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows based on a condition and can be rolled back. TRUNCATE removes all rows quickly but can’t be rolled back in most systems.

How do you update a record in SQL?

Use the UPDATE statement with a WHERE clause to modify specific rows.

Input:

UPDATE employees
SET salary = 60000
WHERE emp_id = 102;

Output (after update):

emp_id	name	department	salary
102	Bob	IT	60000

What is the purpose of the GROUP BY clause?

GROUP BY groups rows that have the same values in specified columns. It’s often used with aggregate functions like SUM, AVG, and COUNT.

Note: SQL query-related interview questions for freshers include basic syntax, simple queries, filtering, sorting, and joins.

Also Read - Top 25+ SQL DBA Interview Questions and Answers

SQL Interview Queries for Experienced

Let’s go through some important SQL query interview questions for experienced professionals and their answers.

How do you optimize a slow-running query?

I usually start by checking indexes on columns used in joins or filters. I also look at the execution plan to identify full table scans or bottlenecks. Rewriting subqueries, reducing joins, and limiting returned columns often helps.

What is a correlated subquery?

A correlated subquery depends on the outer query. It runs once for each row processed by the outer query.

Example:

SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

How do indexes impact query performance?

Indexes speed up searches by letting the database locate data faster, especially on large tables. However, they can slow down insert/update operations since indexes need to be updated too.

What are window functions and where are they used?

Window functions perform calculations across rows without grouping. They’re used for ranking, totals, and comparisons.

Input:

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Output:

name	department	salary	dept_rank
Carol	IT	65000	1
Frank	IT	62000	2
Bob	IT	60000	3

How do you debug a failing SQL query in production?

I break down the query and run parts of it to isolate the issue. I also check for missing joins, invalid column names, or bad data. Reviewing logs and query plans helps too.

Note: SQL query-related interview questions for experienced candidates often include complex joins, subqueries, indexing, and real-world scenarios.

SQL Queries Interview Questions for 2 Years Experienced

Here are some SQL queries asked in interview questions for candidates with 2 years of experience.

• How would you find duplicate records in a table?
• How can you find the second highest salary from a table?
• How do you get the top 3 employees by department based on salary?
• Write a query to count the number of employees in each department.
• Write a query to fetch employees hired in the last 6 months.

SQL Queries Interview Questions for 3 Years Experienced

SQL queries asked in interview for professionals with three years of experience.

• How would you write a query to check for gaps in sequential IDs?
• What is the difference between INNER JOIN and LEFT JOIN?
• When would you use a FULL OUTER JOIN?
• Write a query to find employees who do not have a manager assigned.
• Write a query to calculate the total salary expense per department.

SQL Query Interview Questions for 5 Years Experienced

These are some common SQL interview queries for 5 years experienced candidates.

• How do you join more than two tables?
• What is normalization in SQL?
• What is the difference between OLTP and OLAP?
• Write a query to identify the highest earning employee per department.
• How do you create and use a view in SQL?

SQL Query Interview Questions for 7 Years Experienced

Here are some frequently asked queries in SQL in interviews for professionals with 7 years of experience.

• What is a foreign key constraint?
• Write a query to reverse a string using SQL.
• Write a query to pivot table data without using PIVOT.
• Explain indexing strategies for improving query performance.
• Write a recursive query to fetch an organizational hierarchy.

Also Read - Top 30+ PL/SQL Interview Questions and Answers

Company-Specific SQL Query Interview Questions

This is a list of top SQL queries asked in interviews at various IT companies in India.

Oracle SQL Query Questions

These are some queries asked in SQL in oracle interview.

• How do you use the ROWNUM pseudocolumn in Oracle SQL?
• What is the difference between RANK() and DENSE_RANK() in Oracle SQL?
• How do you write a hierarchical query using CONNECT BY?
• How do you use MERGE statements for UPSERT operations in Oracle SQL?

Note: Interview questions on Oracle SQL queries often include PL/SQL blocks, functions, joins, and performance tuning.

Also Read - Top 25+ Oracle SQL Interview Questions With Answers

SQL Queries for Amazon Interview

Here are some common SQL queries asked in interview questions at Amazon.

• Write a query to get the most viewed product in each category.
• Write a query to find users who bought the same item more than once.
• How do you write a query to find products with increasing daily sales for 3 days in a row?
• Write a query to calculate conversion rate per user session.

TCS Interview Questions on SQL Queries

At TCS, you might come across SQL query-based interview questions.

• How do you write a query to remove duplicate rows using ROW_NUMBER()?
• How do you find employees who never received a bonus?
• How do you write a query to fetch alternate rows from a table?
• Write a query to get the highest and lowest salary in each department.
• Write a query to count how many employees joined each month.

Teradata SQL Queries Interview Questions

These are some SQL queries asked in interview questions at Teradata.

• What is QUALIFY in Teradata SQL and how is it used?
• Write a query using RANK in Teradata to get top 3 salaries per department.
• How do you use CASE in SELECT clause in Teradata?
• How do you handle large datasets efficiently using Teradata SQL?

SQL Query MCQs

Here are 10 SQL questions for practice. These SQL questions and answers for practice will boost your confidence before interviews.

1. Which SQL statement is used to fetch data from a database?

• a) GET
• b) SELECT
• c) FETCH
• d) EXTRACT

Answer: b) SELECT

2. Which of the following clauses is used with aggregate functions?

• a) WHERE
• b) HAVING
• c) ORDER BY
• d) DISTINCT

Answer: b) HAVING

3. What does the DISTINCT keyword do in SQL?

• a) Sorts records
• b) Returns only unique values
• c) Deletes duplicate rows permanently
• d) Groups rows by key

Answer: b) Returns only unique values

4. Which SQL command removes all rows but keeps the table structure?

• a) DELETE
• b) DROP
• c) TRUNCATE
• d) REMOVE

Answer: c) TRUNCATE

5. What is the default sorting order in SQL when using ORDER BY?

• a) Descending
• b) Ascending
• c) Random
• d) None

Answer: b) Ascending

6. Which JOIN returns only matching rows between two tables?

• a) LEFT JOIN
• b) RIGHT JOIN
• c) INNER JOIN
• d) FULL JOIN

Answer: c) INNER JOIN

7. Which SQL keyword is used to combine the result of two SELECT statements while removing duplicates?

• a) UNION
• b) UNION ALL
• c) INTERSECT
• d) MERGE

Answer: a) UNION

8. Which of the following is a valid aggregate function in SQL?

• a) SUM()
• b) LENGTH()
• c) ROUND()
• d) SUBSTR()

Answer: a) SUM()

9. Which constraint ensures that a column cannot have NULL values?

• a) UNIQUE
• b) NOT NULL
• c) PRIMARY KEY
• d) FOREIGN KEY

Answer: b) NOT NULL

10. What does the COUNT(*) function return?

• a) Number of columns
• b) Number of rows including NULLs
• c) Number of rows excluding NULLs
• d) Sum of numeric column values

Answer: b) Number of rows including NULLs

How to Prepare for SQL Query Interview Questions?

Here are some practical tips to help you prepare for SQL query interview questions:

• Go through SQL query questions and answers for practice to build strong fundamentals.
• Solve practical SQL coding questions daily for hands-on skills.
• Revise SQL query examples with answers to learn common patterns.
• Focus on top SQL queries interview questions often asked in hiring rounds.
• Practice real SQL queries in interviews like joins, grouping, and indexing.
• Review key SQL theory questions to strengthen concepts.
• Use sample databases and break queries step by step for clarity.

Also Read - Top 50+ MySQL Interview Questions and Answers

Wrapping Up

And this wraps up the 100 most commonly asked SQL query interview questions and answers. Practice these regularly, understand the logic, and you’ll feel more confident during interviews.

Looking for SQL jobs? Visit Hirist – an online job portal built for tech professionals. You can easily find the top SQL jobs in India, all in one place.

FAQs