SQL is one of the most important technical skills for
professionals working in Data Analytics, Business Intelligence, Data
Engineering, Database Development, and Data Science. While beginners are
generally asked about SELECT statements, WHERE clauses, and basic JOINs,
experienced candidates are expected to solve more complex database problems.
If you are preparing for a Data Analyst, SQL Developer,
Business Intelligence, or Data Engineer interview, these Advanced SQL
Interview Questions and Answers can help you strengthen your preparation
and understand the concepts commonly discussed during technical interviews.
1. What is the difference between WHERE and HAVING in
SQL?
WHERE filters individual rows before data is grouped,
whereas HAVING filters aggregated results after the GROUP BY operation.
For example, if you want to select employees from a
particular department, you can use WHERE. However, if you want to find
departments having more than 10 employees, you would typically use GROUP BY
with HAVING.
Understanding this difference is important because
interviewers frequently test whether candidates know the correct sequence of
SQL query processing.
2. What are Window Functions in SQL?
Window Functions perform calculations across a group of
related rows without combining those rows into a single result.
Popular SQL Window Functions include ROW_NUMBER(),
RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and AVG() OVER().
For example:
SELECT
employee_name,
department,
salary,
RANK() OVER (
PARTITION BY
department
ORDER BY
salary DESC
) AS salary_rank
FROM employees;
This query ranks employees according to salary within each
department.
Window Functions are extremely useful for ranking, running
totals, trend analysis, comparing previous records, and analytical reporting.
3. What is the difference between RANK(), DENSE_RANK(),
and ROW_NUMBER()?
ROW_NUMBER() assigns a unique sequential number to every
row.
RANK() gives identical values the same ranking but may leave
gaps in subsequent ranks.
DENSE_RANK() also assigns the same ranking to identical
values but does not leave gaps.
These functions are frequently used when solving SQL
interview problems involving the highest salary, top-performing products,
customer rankings, or departmental analysis.
4. What is a Common Table Expression (CTE)?
A Common Table Expression, commonly called a CTE, is
a temporary named result set created using the WITH keyword.
Example:
WITH HighSalaryEmployees AS (
SELECT
employee_id, employee_name, salary
FROM employees
WHERE salary >
100000
)
SELECT *
FROM HighSalaryEmployees;
CTEs can make complicated SQL queries easier to read and
maintain. Recursive CTEs can also be used for hierarchical data such as
employee-manager relationships.
5. What is the difference between a CTE and a Subquery?
A subquery is written inside another SQL statement, whereas
a CTE creates a named result set that can make complex queries easier to
understand.
Both can solve similar problems, and the best choice depends
on database engine, query structure, readability, maintainability, and
execution plan.
Interviewers may ask candidates to rewrite a subquery using
a CTE to test their understanding.
6. What are SQL JOINs?
JOINs combine records from multiple tables based on related
columns.
The major types include INNER JOIN, LEFT JOIN, RIGHT
JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN.
INNER JOIN returns matching records from both tables, while
LEFT JOIN returns all records from the left table and matching records from the
right table.
Understanding JOINs is essential for Data Analysts because
business information is usually distributed across multiple database tables.
7. How would you find the second-highest salary in SQL?
This is one of the most popular SQL interview questions.
One approach is:
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
Another approach uses DENSE_RANK():
WITH SalaryRanking AS (
SELECT
salary,
DENSE_RANK()
OVER (
ORDER BY
salary DESC
) AS salary_rank
FROM employees
)
SELECT DISTINCT salary
FROM SalaryRanking
WHERE salary_rank = 2;
The second approach is especially useful when duplicate
salaries exist.
8. How can you identify duplicate records in SQL?
Duplicate values can be identified using GROUP BY and
HAVING.
SELECT email, COUNT(*) AS total
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
This technique is useful in real-world Data Cleaning
using SQL, where duplicate customer, transaction, or employee records need
to be identified.
9. What is the difference between DELETE, TRUNCATE, and
DROP?
DELETE removes selected records and can normally use a WHERE
condition.
TRUNCATE removes all rows from a table without individually
filtering them.
DROP removes the database object itself, such as the table
and its definition.
The exact transactional, logging, identity-reset, and
rollback behavior can vary between database systems, so experienced candidates
should understand the database platform being used.
10. What are indexes in SQL?
An index is a database structure designed to improve data
retrieval performance.
Indexes can significantly improve SELECT queries,
particularly when searching or joining large datasets. However, maintaining
indexes introduces additional storage requirements and can increase the cost of
INSERT, UPDATE, and DELETE operations.
Candidates preparing for Advanced SQL Interview Questions
should understand both the advantages and disadvantages of indexing.
11. What is Query Optimization in SQL?
SQL Query Optimization involves improving query performance
while producing the same required result.
Common techniques include selecting only required columns,
creating appropriate indexes, avoiding unnecessary calculations, reducing
unnecessary data processing, examining execution plans, optimizing JOIN
conditions, and filtering data efficiently.
For large databases, query optimization can dramatically
improve application and reporting performance.
12. What is the difference between UNION and UNION ALL?
UNION combines results from multiple queries and removes
duplicate rows.
UNION ALL combines the results without performing duplicate
elimination.
Because UNION ALL does not need to remove duplicates, it can
be more efficient when duplicate removal is unnecessary.
13. What is a Stored Procedure?
A Stored Procedure is a reusable set of SQL statements
stored in a database.
Stored procedures can accept parameters and are commonly
used to implement database operations, reporting processes, validation logic,
and application workflows.
Candidates interviewing for SQL Developer and Data
Engineering positions may be expected to understand stored procedures,
functions, triggers, and transactions.
14. What is Database Normalization?
Database Normalization is the process of organizing
relational data to reduce unnecessary duplication and improve data integrity.
Common normalization levels include First Normal Form
(1NF), Second Normal Form (2NF), and Third Normal Form (3NF).
However, analytical systems sometimes intentionally use
denormalized structures to simplify reporting and improve certain query
patterns.
15. What SQL skills should a Data Analyst master?
A Data Analyst should be comfortable with SELECT statements,
JOINs, GROUP BY, HAVING, CASE expressions, aggregate functions, subqueries,
CTEs, Window Functions, date functions, string functions, data cleaning, and
query optimization.
Advanced candidates should also practice solving real
business scenarios instead of memorizing syntax.
For example, you may be asked to find the top three
products by sales in each category, calculate month-over-month revenue
growth, identify customers who have not purchased recently, calculate running
totals, detect duplicate transactions, or compare employee performance within
departments.
Conclusion
Preparing for an Advanced SQL Interview requires a
combination of theoretical knowledge and hands-on problem-solving skills.
Interviewers want to understand not only whether you know SQL syntax but
whether you can use SQL to solve practical business problems.
Start with JOINs, GROUP BY, subqueries, and aggregate
functions before progressing to CTEs, Window Functions, RANK, DENSE_RANK,
ROW_NUMBER, indexes, and SQL Query Optimization.
Most importantly, practice writing queries using realistic
datasets. Try solving problems involving customers, employees, sales, products,
transactions, and financial information.
Strong SQL skills combined with Advanced Excel, Power BI, Python, Tableau, and Data Analytics knowledge can help candidates prepare for modern Data Analyst and Business Intelligence roles.
