Understanding JOINs is critical for API and backend testing. INNER JOIN returns matching records from both tables. LEFT JOIN returns all records from the left table and matched records from the right. Testers frequently use these to validate complex data mappings between user accounts and transaction histories.
Senior QA interviews focus heavily on subqueries, correlated subqueries, and window functions (like ROW_NUMBER and RANK). Interviewers often ask you to find the "nth highest salary" or identify duplicate records—tasks that prove you can handle complex database testing scenarios.
The best way to master database testing is through hands-on practice. Use our SQL Practice Sandbox to write queries against a live dummy database. Practice finding orphans, checking referential integrity, and validating data migrations.
After more than 12 years conducting and sitting on both sides of QA technical interviews, I can tell you that writing correct SQL is only half the battle. Interviewers at companies like Accenture, Deloitte, and Persistent Systems are equally evaluating how you write your SQL — your logical approach, your readability habits, and your understanding of performance implications. The eight tips below are the exact habits I look for when assessing a candidate's database testing maturity.
MAX(salary) mean nothing to a reviewer scanning a report. Always write MAX(salary) AS highest_salary. This habit signals that you think like an engineer who writes for others to read, not just for the machine to execute.
EXISTS short-circuits as soon as it finds the first match, making it significantly faster than IN which evaluates the entire subquery. Example: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) is preferred over WHERE id IN (SELECT customer_id FROM orders).
WITH active_users AS (SELECT * FROM users WHERE status = 'active') dramatically improves readability and is easier to debug step-by-step during an interview walkthrough.
SELECT * is a red flag. It signals lazy coding, can return unintended columns, and creates fragile queries that break when tables are altered. Explicitly list needed columns: SELECT employee_id, full_name, department_id FROM employees.
WHERE column = NULL never returns rows. The correct form is WHERE column IS NULL. Failing to handle NULLs in your queries is one of the most common ways QA engineers introduce data validation defects.
DENSE_RANK() OVER (ORDER BY salary DESC). Modern interviewers at senior levels specifically look for window function usage as proof that you understand performance-conscious query design.
Even experienced testers make predictable SQL mistakes under interview pressure. Recognizing these patterns in advance — and knowing the corrected approach — gives you a significant edge. Here are the five most common pitfalls I see candidates make, along with precise fixes.
Wrong: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id WHERE COUNT(*) > 5; — This throws a syntax error because WHERE cannot reference aggregate functions. Fix: Replace WHERE with HAVING: GROUP BY department_id HAVING COUNT(*) > 5;
When asked to find departments with no employees, candidates often write an INNER JOIN which silently excludes null matches. The correct approach is a LEFT JOIN followed by a WHERE e.id IS NULL condition to capture unmatched parent records.
Using a simple LIMIT 1 OFFSET 1 to find the second highest salary breaks immediately when two employees share the top salary. Always use DENSE_RANK() which correctly handles ties, or use the MAX/WHERE subquery pattern shown in Question 1 above.
When joining two tables that share a column name (like id or name), always prefix columns with the table alias: e.name, m.name. Unqualified column references in multi-table queries produce ambiguous column errors that are easy to avoid and make you look careless.
Placing a subquery directly in the SELECT clause (e.g., to fetch a count per row) executes that subquery once per row in the outer query — a classic N+1 problem. The fix is to pre-aggregate in a CTE or derived table and then JOIN the result, which processes the aggregation only once regardless of dataset size.
Avoiding these five pitfalls will immediately elevate the quality of your SQL in any interview. Practice writing queries under time pressure in our SQL Practice Lab to build the muscle memory to produce clean, correct SQL on demand.
Writing a query that works is only step one. During my 12 years of conducting QA technical interviews, the candidates who stand out write SQL that is readable, maintainable, and optimized. Here is my actionable advice for live coding rounds:
SELECT * FROM users u JOIN orders o. Write SELECT u.first_name, o.order_date FROM users u JOIN orders o. It prevents ambiguity.EXISTS is generally more performant than IN because it stops scanning once a match is found.ON clause of a JOIN rather than waiting for the WHERE clause to reduce the dataset size early.COUNT(column_name) counts all rows. It only counts non-null rows. Use COUNT(*) to count everything.WHERE name LIKE 'John' instead of WHERE name LIKE '%John%'.