AI, data and statistics

SQL interview questions

Retrieving, joining and validating structured datasets.

6 questions, each with a worked answer

What is the difference between INNER JOIN and LEFT JOIN in a scientific dataset? Fundamentals Concept
How would you investigate unexpectedly duplicated rows after a join? Intermediate Scenario
How can a LEFT JOIN silently change denominators in cohort analysis? Intermediate Scenario
What is the difference between COUNT(*) and COUNT(column)? Intermediate Concept
When would a window function be preferable to GROUP BY? Advanced Scenario
How would you preserve a source-to-claim audit trail in relational tables? Advanced Scenario

About SQL

SQL is the language for querying relational databases, where much scientific, clinical and operational data lives. Day to day it means selecting, filtering, joining and aggregating tables to answer a question. Correctness depends on understanding keys, nulls, duplicates and what each join does to row counts.

Analysts, data engineers and LIMS specialists use it to pull and check data before any analysis begins.

Fundamentals: 1
Intermediate: 3
Advanced: 2

What these questions test

Write traceable SQL joins: Join records at their intended grain without silently duplicating or excluding observations.

References

Common pitfalls and recommended practices. scikit-learn User Guide. scikit-learn.org/stable/common_pitfalls.html.
postgresql.org/docs/current/queries-table-expressions.html.
postgresql.org/docs/current/functions-aggregate.html.
postgresql.org/docs/current/tutorial-window.html.
postgresql.org/docs/current/ddl-constraints.html.

Practice material written by TheBench.Bio and checked against the references above, reviewed October 23, 2026. Written to practise with, not collected from real interviews.