Most SQL interview lists give you questions and no answers, which trains you to recognise a question without being able to answer it. Every question below has the answer an interviewer is listening for, and where a question has a trap in it, the trap is named.<\/p>\n
The questions are grouped by the stage of interview they usually appear at. If you are preparing for a first analyst role, the fresher and intermediate sections are where the marks are.<\/p>\n
These come up in phone screens and first rounds. They are checking that you have actually written SQL rather than read about it.<\/p>\n
What is SQL and what is it used for?<\/strong><\/p>\n SQL stands for Structured Query Language. It is the standard language for querying and modifying data held in relational databases. Analysts use it mainly to retrieve and aggregate data with SELECT statements, rather than to create or change database structure.<\/p>\n What is the difference between WHERE and HAVING?<\/strong><\/p>\n WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has aggregated them. If you want orders above \u20b910,000 you use WHERE; if you want customers whose total spend is above \u20b910,000 you use HAVING, because that total only exists after grouping.<\/p>\n What are the main types of JOIN?<\/strong><\/p>\n INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table with NULLs where the right has no match. RIGHT JOIN does the reverse. FULL OUTER JOIN returns everything from both sides. CROSS JOIN pairs every row with every row, and SELF JOIN joins a table to itself.<\/p>\n What is a primary key?<\/strong><\/p>\n A column, or combination of columns, that uniquely identifies each row in a table. It cannot contain NULL values and no two rows can share the same value. A table has at most one primary key, and it is usually what other tables reference as a foreign key.<\/p>\n What is the difference between a primary key and a foreign key?<\/strong><\/p>\n A primary key uniquely identifies rows within its own table. A foreign key is a column in one table that points at the primary key of another, creating the relationship between them. A foreign key can contain duplicates and, depending on the design, NULLs.<\/p>\n What does DISTINCT do?<\/strong><\/p>\n It removes duplicate rows from the result set. Applied to a single column it returns the unique values; applied to several columns it returns unique combinations across all of them, which is a common misunderstanding. SELECT DISTINCT city, state returns unique city and state pairs, not unique cities.<\/p>\n What is the difference between DELETE, TRUNCATE and DROP?<\/strong><\/p>\n DELETE removes selected rows and can carry a WHERE clause, and it can usually be rolled back. TRUNCATE removes every row but keeps the empty table, and is generally not reversible. DROP removes the table itself along with its structure, indexes and constraints.<\/p>\n What are aggregate functions?<\/strong><\/p>\n Functions that collapse many rows into one value: COUNT, SUM, AVG, MIN and MAX. They are normally used with GROUP BY to produce one result per group, such as total revenue per city.<\/p>\n What is the difference between COUNT star and COUNT of a column?<\/strong><\/p>\n COUNT with a star counts all rows including those with NULLs. COUNT of a specific column counts only the rows where that column is not NULL. On a column with missing values the two return different numbers, and interviewers use this to check whether you understand NULL handling.<\/p>\n This is where most analyst interviews are actually decided.<\/p>\n What is a window function and how does it differ from GROUP BY?<\/strong><\/p>\n A window function performs a calculation across a set of rows related to the current row, without collapsing them. GROUP BY returns one row per group; a window function returns every original row with an extra calculated column. Use GROUP BY for a summary table, and a window function when you need the detail rows alongside a running total, a rank or a group average.<\/p>\n Explain the difference between RANK, DENSE_RANK and ROW_NUMBER.<\/strong><\/p>\n All three number rows within a partition. ROW_NUMBER always gives consecutive numbers with no ties. RANK gives tied rows the same number and then skips, so two rows tied at 1 are followed by 3. DENSE_RANK gives tied rows the same number and does not skip, so the next value is 2.<\/p>\n What is a subquery and when would you use a CTE instead?<\/strong><\/p>\n A subquery is a query nested inside another. A CTE, or common table expression, is a named temporary result defined with WITH at the top of the query. They can do the same work, but a CTE is far more readable when the logic has several steps, and it can be referenced more than once in the same query while a subquery cannot.<\/p>\n How do you find duplicate rows in a table?<\/strong><\/p>\n Group by the columns that should be unique and keep the groups with more than one row: SELECT email, COUNT(star) FROM users GROUP BY email HAVING COUNT(star) greater than 1. To see the full duplicate rows rather than just the keys, use ROW_NUMBER partitioned by those columns and filter to rows numbered above 1.<\/p>\n What is the difference between UNION and UNION ALL?<\/strong><\/p>\n Both stack the results of two queries. UNION removes duplicate rows; UNION ALL keeps them. UNION ALL is faster because it skips the deduplication step, so use it whenever you know there are no duplicates or you want them kept.<\/p>\n How do you find the second highest value in a column?<\/strong><\/p>\n The cleanest way is a window function: rank the rows with DENSE_RANK ordered descending and filter to rank 2. The older approach uses a subquery to find the maximum value below the overall maximum. Interviewers often ask for the Nth highest as a follow-up, which is why the window function answer is stronger.<\/p>\n What does a LEFT JOIN return when there is no match?<\/strong><\/p>\n Every row from the left table, with NULL in all columns taken from the right table. This is the basis of the anti-join pattern: LEFT JOIN then filter to rows where the right-hand key IS NULL, which returns exactly the left-hand rows that have no counterpart.<\/p>\n Why might a LEFT JOIN behave like an INNER JOIN?<\/strong><\/p>\n Because a condition on the right-hand table was placed in the WHERE clause instead of the ON clause. NULL fails almost every comparison, so the unmatched rows get filtered out and the outer join collapses. Conditions on the right-hand table belong in the ON clause.<\/p>\n What causes a join to return more rows than expected?<\/strong><\/p>\n The join key is not unique in the second table, so each left-hand row multiplies by the number of matches. This is fan-out, and it is the usual cause of revenue figures that come out several times too high. The fix is to aggregate the second table to one row per key before joining.<\/p>\n What is an index and what is the trade-off?<\/strong><\/p>\n An index is a data structure that lets the database find rows without scanning the whole table, which makes reads much faster. The trade-off is that every INSERT, UPDATE and DELETE must also update the index, so writes get slower and storage grows. Index the columns you filter and join on, not every column.<\/p>\n How do you handle NULL values in SQL?<\/strong><\/p>\n Test for them with IS NULL or IS NOT NULL, never with equals, because NULL is not equal to anything including itself. Replace them in output using COALESCE, which returns the first non-null argument. Be aware that most aggregate functions ignore NULLs, so AVG over a column with missing values divides by the count of non-null rows only.<\/p>\n What is the difference between a clustered and a non-clustered index?<\/strong><\/p>\n A clustered index determines the physical order in which rows are stored, so a table can have only one. A non-clustered index is a separate structure holding the indexed values and pointers to the rows, and a table can have many. Lookups through a non-clustered index need an extra step to fetch the full row.<\/p>\n What are the SQL execution order rules?<\/strong><\/p>\n SQL is written in one order and executed in another. Execution runs FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. This explains why you cannot reference a column alias defined in SELECT inside a WHERE clause: the WHERE has already run by then.<\/p>\n How would you optimise a slow query?<\/strong><\/p>\n Read the execution plan first rather than guessing. Look for full table scans on large tables and add indexes on the filtered and joined columns. Reduce the data as early as possible by filtering before joining. Avoid functions applied to indexed columns in a WHERE clause, because they prevent the index being used. Select only the columns you need instead of everything.<\/p>\n What is database normalisation and when would you denormalise?<\/strong><\/p>\n Normalisation organises data to remove redundancy, typically by splitting repeated groups into separate tables linked by keys. It keeps data consistent and makes writes cheap. You denormalise deliberately in analytics and reporting, where duplicating some data into a wider table avoids expensive joins and makes reads much faster.<\/p>\n What is a correlated subquery and why can it be slow?<\/strong><\/p>\n A correlated subquery references a column from the outer query, so it must be evaluated once per outer row rather than a single time. On a large outer result this means running the inner query thousands of times. Rewriting it as a join or a window function usually gives the same answer far faster.<\/p>\n Explain ACID properties.<\/strong><\/p>\n Atomicity means a transaction either fully completes or fully reverses. Consistency means a transaction moves the database from one valid state to another, respecting all constraints. Isolation means concurrent transactions do not interfere with each other’s intermediate state. Durability means once committed, the change survives a crash.<\/p>\n How would you calculate a month-on-month growth rate in SQL?<\/strong><\/p>\n Aggregate to one row per month, then use the LAG window function to bring the previous month’s value onto the current row, and compute the difference divided by the previous value. Doing it with a self-join on month minus one also works but breaks around year boundaries unless you handle the date arithmetic carefully.<\/p>\n Three things matter more than the number of questions you memorise.<\/p>\n Write queries against real data rather than reading about them. Load any public dataset into PostgreSQL and answer actual questions with it. The gap between recognising a window function and writing one under time pressure is large.<\/p>\n Be able to explain your reasoning aloud. Most interviewers care more about how you approached the problem than whether the syntax was perfect, and many will let you talk through an approach even when the query does not run.<\/p>\n Know your own weak spot and say so. “I use window functions regularly but I have not tuned queries at scale” is a far better answer than a confident wrong one, and it is the kind of honesty that survives a reference check.<\/p>\n Usually between five and ten across the technical rounds, often with a live query exercise where you write SQL against sample tables. Screening rounds tend to use short conceptual questions, while later rounds involve writing actual queries and explaining your approach.<\/p>\n You need solid command of SELECT, WHERE, GROUP BY, HAVING, all the join types, subqueries and CTEs, and window functions. Query optimisation and indexing are usually expected at two years of experience and above rather than for a first role. Database administration is not an analyst’s job.<\/p>\n Joins, by a wide margin, and especially the difference between INNER and LEFT and what happens to unmatched rows. Window functions come second and increasingly separate stronger candidates from the rest. Together these two topics account for most of the technical marks in an analyst SQL round.<\/p>\n You need enough fluency to write a working query without looking things up constantly, but interviewers rarely penalise a forgotten function name. What they test is whether you can decompose a business question into the right sequence of operations. Explaining a correct approach with one syntax slip beats perfect syntax solving the wrong problem.<\/p>\n They overlap but the emphasis differs. Analysts get more questions on querying, aggregation and window functions. Engineers get more on schema design, indexing strategy, partitioning and pipeline performance. Both are expected to understand joins thoroughly.<\/p>\n From a standing start, most people need eight to twelve weeks of consistent practice to handle an analyst-level SQL round, assuming a few hours a week. The concepts take days; the fluency to apply them under pressure takes weeks of writing queries against real data.<\/p>\n Reading answers builds recognition. Writing queries and having someone tell you why yours returns the wrong number builds the skill that gets tested.<\/p>\n SkilloVilla’s SQL: Beginner to Advanced course<\/a> at \u20b933,110 is live rather than recorded, with mentors reviewing your work. If you want SQL inside a full analyst path with Python, statistics and interview preparation, the Data Analytics with Python track<\/a> is \u20b971,999, currently \u20b958,999 and includes placement support.<\/p>\n Fees and ratings last checked August 2026; confirm current numbers with the provider before enrolling.<\/p>\n","protected":false},"excerpt":{"rendered":" 27 SQL interview questions with answers, grouped fresher to advanced, covering joins, aggregates, window functions and query optimisation.<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[200],"tags":[],"class_list":["post-3818","post","type-post","status-publish","format-standard","hentry","category-data-analytics"],"yoast_head":"\nIntermediate level: the technical round<\/h2>\n
Advanced level: senior and specialist rounds<\/h2>\n
How to prepare, honestly<\/h2>\n
Frequently asked questions<\/h2>\n
How many SQL questions are asked in a data analyst interview?<\/h3>\n
What level of SQL is required for a data analyst job in India?<\/h3>\n
Which SQL topic is asked most in interviews?<\/h3>\n
Do I need to memorise SQL syntax for interviews?<\/h3>\n
Are SQL interview questions the same for data analysts and data engineers?<\/h3>\n
How long does it take to get interview-ready with SQL?<\/h3>\n
Practise SQL against real problems, with someone reviewing it<\/h2>\n