Top 30 SQL Interview Questions and Answers
30 multiple-choice questions on SQL, drawn from 30 bites out of the 92 tagged SQL on Tezvyn. Answer them here or read straight down. Every question carries the correct option, why it is correct, and a link to the bite it came from.
30 questions. Pick an answer, or open “Show the answer” to read it.
Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.
Question 1 of 30
What is the main reason to place a unique constraint on a non-primary column such as email?
Show the answer
Answer: c · To enforce business-level uniqueness while keeping a stable primary key for joins
The card gives email as an alternate key that stops duplicate signups while the auto-incrementing primary key stays the stable join target. The distractor that it serves as the main row identifier conflates a unique constraint with a primary key, a red flag the card explicitly warns against.
Read the full bite: What is the difference between primary, foreign, and unique keys?
Question 2 of 30
A team needs to speed up queries on a large table and purge old records. Which task requires DDL?
Show the answer
Answer: b · Adding an index on the date column to speed up filtering
Adding an index changes the database schema, which is the purpose of DDL. Purging old rows may seem structural, but it only deletes data, making it a DML operation.
Question 3 of 30
Which action is a clear use of DML because it changes stored data rather than table structure or read-only querying?
Show the answer
Answer: d · Inserting a new row into an existing table
Inserting a new row is explicitly called a textbook DML operation that alters stored data. Running a SELECT statement is a tempting distractor because the card notes that read-only querying is sometimes classified separately as DQL rather than being grouped with DML.
Question 4 of 30
When adding a new column to a table and then filling it with values, how do the two types of SQL commands involved differ?
Show the answer
Answer: d · The first command is DDL that modifies the schema and may lock the table, while the second is DML that changes row data inside a transaction
Adding a column is DDL because it changes the schema and often locks the table, while updating rows is DML that operates row by row within a transaction. Option B is tempting because it labels the command types correctly, but it is wrong because DDL often auto-commits and cannot be rolled back in many engines, and DML—not DDL—runs inside explicit transactions.
Read the full bite: What is the difference between DDL and DML in SQL?
Question 5 of 30
What is the primary reason to avoid using "natural keys" like email addresses as primary keys?
Show the answer
Answer: a · Their values can change over time, complicating data management and relationships.
The card states that natural keys "can change, which creates a maintenance nightmare" and requires updating the value in every referencing table. This directly points to complications in data management and maintaining relationships. While other options might have some truth in different contexts, the card emphasizes changeability as the core issue.
Read the full bite: Primary Keys: The Unique ID for Every Row
Question 6 of 30
When is it appropriate to intentionally violate 3NF by duplicating a customer name in an orders table?
Show the answer
Answer: c · When read latency is critical and avoiding joins outweighs update anomaly risks
Intentionally violating 3NF trades update anomaly risk for faster reads by eliminating joins, which is appropriate in read-heavy workloads. The most tempting distractor confuses the goal of normalization—preventing transitive dependencies—with a reason to denormalize.
Read the full bite: Describe 1NF, 2NF, 3NF, normalization's purpose, and its performance trade-off.
Question 7 of 30
What primary challenge did the relational model address compared to earlier database systems?
Show the answer
Answer: c · The need for complex procedural code to navigate specific data storage paths
The card states that before the relational model, "Retrieving data required writing complex procedural code to navigate specific data paths," which was inflexible. The relational model aimed to separate the logical data structure from its physical implementation. Option A is a distractor because while hierarchical models were predecessors, the core problem was the procedural navigation within them, not just the representation of hierarchies.
Read the full bite: The Relational Model: Data as Simple Tables
Question 8 of 30
When the Orders table may contain NULL user_ids, which statement correctly explains the safest way to find users who never placed an order?
Show the answer
Answer: b · NOT EXISTS is preferred because it is immune to NULLs in the subquery and avoids duplicate rows.
NOT EXISTS handles NULL values safely and stops at the first match per user, avoiding duplicate rows. Option D is tempting because LEFT JOIN is a common pattern, but it can inflate results when a user has multiple orders unless you add DISTINCT or GROUP BY.
Read the full bite: Find users who never placed an order and explain JOIN choice
Question 9 of 30
What is the main objective of applying database normalization in a relational database?
Show the answer
Answer: a · To ensure data integrity and minimize redundancy by structuring data into smaller, related tables.
Normalization's primary goal is to eliminate data redundancy and prevent modification anomalies (update, insertion, deletion), thereby ensuring data integrity. Option D describes a benefit of denormalization, often used in analytical systems, which is the opposite of normalization's typical effect on JOINs.
Read the full bite: Database Normalization: Tidy Tables, Less Redundancy
Question 10 of 30
When writing a query that groups by department and filters on COUNT(*) > 10, why must the predicate be in HAVING rather than WHERE?
Show the answer
Answer: b · WHERE is evaluated before GROUP BY, so the aggregate count does not yet exist
WHERE filters individual rows before grouping and aggregation occur, so aggregate values like COUNT(*) have not been computed yet and do not exist at that stage. Option D is wrong because repeating the aggregate expression in WHERE does not help; the aggregate still does not exist when WHERE is evaluated.
Read the full bite: What is the difference between WHERE and HAVING in SQL?
Question 11 of 30
By default, what action does a relational database take when a user attempts to delete a record from a "parent" table that is referenced by a foreign key in a "child" table?
Show the answer
Answer: c · It blocks the deletion of the parent record to maintain data integrity.
The card explicitly states that if you try to delete a user referenced by posts, "the database will, by default, block the deletion to avoid creating orphaned posts." This is the standard behavior to enforce referential integrity. Option B describes an 'ON DELETE SET NULL' action, which is a configurable behavior, not the default.
Read the full bite: Foreign Keys: The Glue of Relational Databases
Question 12 of 30
What is the main performance trade-off introduced by adding a database index?
Show the answer
Answer: d · It speeds up data retrieval but slows down data modification operations.
The card explicitly states that indexes speed up data retrieval but add overhead to INSERT, UPDATE, and DELETE operations, as the index structure must also be modified. This trade-off between faster reads and slower writes is the primary consideration. Indexes do not inherently complicate schema design, slow down non-indexed queries, or require manual updates for consistency.
Read the full bite: Database Index: The Phonebook for Your Data
Question 13 of 30
What is the primary distinction between a standard database view and a base table?
Show the answer
Answer: a · A view executes a predefined query against base tables each time it is accessed, without storing its own data.
A standard view stores only its query definition and executes it against the underlying base tables each time it's accessed, as explained in the 'How It Works' section. Option C describes a common misconception; views do not store their own data, unlike materialized views.
Read the full bite: Database Views: A Saved Query That Acts Like a Table
Question 14 of 30
When calculating weekly cohort retention, why is computing week_offset from each user's signup_date preferable to grouping activity by calendar week?
Show the answer
Answer: d · It ensures retention measures each cohort's return behavior relative to its own signup origin
Computing week_offset anchors every activity to the user's specific signup cohort, which is the defining requirement of cohort retention; option C describes grouping all users into calendar activity weeks, which is the classic global active users trap that fails to measure cohort-specific return behavior.
Read the full bite: Describe the SQL and data model for weekly cohort retention
Question 15 of 30
What is the fundamental purpose of relational algebra within a database system?
Show the answer
Answer: b · To provide a formal, procedural model for query execution and optimization.
Relational algebra serves as the formal, procedural model that a database uses to translate declarative SQL queries into an optimizable execution plan. Option C describes SQL itself, which is the high-level, declarative language, not relational algebra.
Read the full bite: Relational Algebra: The Math Behind SQL Queries
Question 16 of 30
What is the primary philosophical goal Codd's 12 Rules aim to achieve for database systems?
Show the answer
Answer: a · Ensuring complete separation between the logical data model and its physical implementation.
Codd's rules were created to formally define data independence, which is the separation of the logical data representation from its physical implementation. They are explicitly stated as a theoretical benchmark, not a practical tool for vendor comparison.
Read the full bite: Codd's 12 Rules: The Relational Database Litmus Test
Question 17 of 30
Under what specific condition might a database designer intentionally opt for a 3NF schema instead of striving for BCNF?
Show the answer
Answer: b · If achieving BCNF would make it impossible to enforce a critical functional dependency using a key constraint.
The card explicitly states that the primary reason not to use BCNF is the trade-off with dependency preservation, where achieving BCNF might prevent enforcing an original functional dependency with a key constraint. Option D describes the goal of BCNF, which is stricter than 3NF in eliminating anomalies, making it an incorrect reason to choose 3NF.
Read the full bite: Boyce-Codd Normal Form (BCNF): Stricter Than 3NF
Question 18 of 30
When is a snowflake schema generally preferred over a star schema in data warehousing?
Show the answer
Answer: a · When dimension tables are very large and complex, and storage efficiency is critical.
The card states that a snowflake schema is considered 'when storage is a major constraint or dimensions are very complex and large' due to its normalized structure. Options A, B, and D describe benefits of a star schema, which prioritizes query performance and simplicity through denormalization and fewer joins.
Read the full bite: Describe star and snowflake schemas and their trade-offs.
Question 19 of 30
What is the primary characteristic that a table must satisfy to be in First Normal Form (1NF)?
Show the answer
Answer: c · There are no repeating groups or multi-valued attributes within any column.
The correct answer B directly states the core principle of 1NF: ensuring each cell contains a single, atomic value by disallowing repeating groups or lists. Option D, while true for relational tables, describes a primary key's role, not the specific atomicity requirement of 1NF.
Question 20 of 30
Under which condition is choosing a snowflake schema over a star schema most justifiable?
Show the answer
Answer: b · When a large dimension table has significant redundancy, and enforcing data integrity and saving storage is critical.
A snowflake schema's main purpose is to normalize large, redundant dimension tables to save storage and improve data integrity. Options A and D describe the primary benefits of a star schema, which prioritizes query speed and simplicity.
Question 21 of 30
Which scenario *requires* the use of a junction table in a relational database?
Show the answer
Answer: b · A product belonging to several categories, and each category containing multiple products.
Option B describes a many-to-many relationship, which is the fundamental problem junction tables are designed to solve. Options A, C, and D all represent one-to-many relationships, which can be modeled by placing a foreign key directly in the 'many' side table without needing a junction table.
Question 22 of 30
Which scenario represents a violation of Third Normal Form (3NF)?
Show the answer
Answer: a · A non-key attribute in a table is fully dependent on another non-key attribute.
Third Normal Form (3NF) specifically addresses transitive dependencies, which occur when a non-key attribute depends on another non-key attribute instead of directly on the primary key. Options A and C describe violations of First Normal Form (1NF) and Second Normal Form (2NF), respectively.
Read the full bite: Third Normal Form (3NF): Nothing But The Key
Question 23 of 30
What is the primary advantage of employing a surrogate key as the primary key for a table containing core business entities?
Show the answer
Answer: a · It ensures that relationships between tables remain stable even if the real-world data they represent changes.
The card emphasizes that surrogate keys solve the stability problem by decoupling internal linking from volatile real-world data, preventing painful cascading updates. Option C is incorrect because surrogate keys are meaningless and exposing them externally is a 'footgun'.
Read the full bite: Surrogate Keys: Stable IDs for Unstable Data
Question 24 of 30
You need to combine customers and transactions on customer_id to compute total spend per customer, including those with zero transactions. Which approach correctly uses pandas?
Show the answer
Answer: b · Use pd.merge with how='left' on customer_id, then group by customer, sum amounts, and fill NaN totals with zero
A left join preserves every customer and shows NaN for missing transactions, which you fill with zero after grouping; an inner join is tempting because it is the pandas default, but it silently drops customers with no matching transactions.
Question 25 of 30
What is the primary problem a point-in-time correct join solves when preparing data for machine learning models?
Show the answer
Answer: b · Data leakage, by ensuring that only historically available feature values are used for each training example.
The card explicitly states that a point-in-time correct join is vital "to prevent data leakage" by ensuring you only retrieve feature values that were available at a specific point in time. Option D is incorrect because the card notes it is "not to use it" for real-time online inference.
Read the full bite: Point-in-Time Correctness: Avoiding Data Leakage in ML
Question 26 of 30
For a star schema to effectively support fast analytical queries, which characteristic is crucial?
Show the answer
Answer: a · Dimension tables are denormalized and wide, containing comprehensive attributes.
The card explicitly states that dimension tables in a star schema are "usually denormalized and wide to avoid extra joins," which is key for fast querying. Normalizing dimensions, as suggested in option B, is described as a "footgun" that negates the speed advantage.
Read the full bite: Star Schema: The Blueprint for Analytics Data
Question 27 of 30
Which query pattern correctly identifies customers who have never placed an order?
Show the answer
Answer: b · LEFT JOIN orders then filter WHERE orders.customer_id IS NULL
C uses the correct anti-join pattern: LEFT JOIN preserves all customers and IS NULL keeps only those with no matching orders. D is tempting because it looks like logical negation, but if orders.customer_id contains any NULLs, NOT IN unexpectedly returns an empty set instead of the desired customers.
Read the full bite: Find customers who have not placed any orders
Question 28 of 30
A user reports a key dashboard, which was previously fast, is now timing out. What is the most effective first step in your investigation?
Show the answer
Answer: a · Get the exact query generated by the dashboard and analyze its execution plan.
The best first step is to analyze the query plan, as it's the cheapest and most direct way to find bottlenecks. Proposing an aggregate table (B) is a common mistake, as it's a solution applied before the actual problem has been diagnosed.
Question 29 of 30
When diagnosing a slow dashboard query, which initial action best reflects a systematic and cost-aware troubleshooting methodology?
Show the answer
Answer: a · Analyze the query's execution plan to identify specific bottlenecks and potential rewrites.
The recommended first step in a systematic diagnosis is to analyze the query's execution plan to find bottlenecks and optimize the query itself. Scaling the warehouse (Option D) is explicitly identified as a major red flag and the most expensive, least targeted solution to start with.
Read the full bite: How would you diagnose a slow dashboard query?
Question 30 of 30
What is the primary advantage of using a star schema over a highly normalized (3NF) model for analytical queries?
Show the answer
Answer: d · It reduces the number of joins required by denormalizing dimensions, leading to faster aggregations.
The correct answer is C because the star schema's main performance benefit comes from denormalized dimensions, which require fewer joins for complex queries. Option A describes the primary benefit of a 3NF model, which is optimized for transactional writes, not analytical reads.
Read the full bite: Explain the star schema and its advantages for analytics
Could you explain these out loud?
That is what an interview actually tests. Tezvyn gives you questions like these with what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.