SQL concepts interview questions — Medium
159 medium-level SQL concepts questions from our SQL bank, each with the correct answer and an explanation of why it is correct.
What this covers
SQL concepts appears throughout SQL interviews. At medium level, interviewers are typically checking that you have used it in practice: what you configured, what broke, and how you knew it worked. Work through these, then explain your answer out loud; the second part is what interviews actually test.
20 example questions
1. When working with string handling in SQL, which statement best reflects how strings are represented and manipulated?
- A. It compiles to no-op.
- B. Use the language's idiomatic string handling feature, which is designed for exactly this purpose.correct
- C. It allocates a new copy every time.
- D. It is handled automatically by the garbage collector.
Why: Idiomatic SQL provides a built-in mechanism for string handling; relying on it (how strings are represented and manipulated) is clearer and safer than the alternatives, which misstate how SQL behaves.
2. A reviewer asks about pattern matching in your SQL code. What is the correct guidance regarding how values are destructured by shape?
- A. It is undefined behavior.
- B. It allocates a new copy every time.
- C. It is identical to the global variant.
- D. Use the language's idiomatic pattern matching feature, which is designed for exactly this purpose.correct
Why: Idiomatic SQL provides a built-in mechanism for pattern matching; relying on it (how values are destructured by shape) is clearer and safer than the alternatives, which misstate how SQL behaves.
3. A reviewer asks about immutability in your SQL code. What is the correct guidance regarding how immutable values are declared and enforced?
- A. It is deprecated in current versions.
- B. It silently ignores the operation.
- C. It is only available in unsafe mode.
- D. Use the language's idiomatic immutability feature, which is designed for exactly this purpose.correct
Why: Idiomatic SQL provides a built-in mechanism for immutability; relying on it (how immutable values are declared and enforced) is clearer and safer than the alternatives, which misstate how SQL behaves.
4. In SQL, what is the idiomatic approach to variable scoping (how block vs function scope affects visibility)?
- A. It allocates a new copy every time.
- B. Use the language's idiomatic variable scoping feature, which is designed for exactly this purpose.correct
- C. It is identical to the global variant.
- D. It is only available in unsafe mode.
Why: Idiomatic SQL provides a built-in mechanism for variable scoping; relying on it (how block vs function scope affects visibility) is clearer and safer than the alternatives, which misstate how SQL behaves.
5. Which option correctly describes function definition in SQL, specifically how reusable functions are declared?
- A. It allocates a new copy every time.
- B. Use the language's idiomatic function definition feature, which is designed for exactly this purpose.correct
- C. It is undefined behavior.
- D. It throws a runtime exception by default.
Why: Idiomatic SQL provides a built-in mechanism for function definition; relying on it (how reusable functions are declared) is clearer and safer than the alternatives, which misstate how SQL behaves.
6. Which option correctly describes generics in SQL, specifically how code is parameterized over types?
- A. Use the language's idiomatic generics feature, which is designed for exactly this purpose.correct
- B. It is deprecated in current versions.
- C. It is identical to the global variant.
- D. It throws a runtime exception by default.
Why: Idiomatic SQL provides a built-in mechanism for generics; relying on it (how code is parameterized over types) is clearer and safer than the alternatives, which misstate how SQL behaves.
7. When working with data serialization in SQL, which statement best reflects how data is serialized to a portable format?
- A. Use the language's idiomatic data serialization feature, which is designed for exactly this purpose.correct
- B. It always returns null.
- C. It is deprecated in current versions.
- D. It requires an external framework.
Why: Idiomatic SQL provides a built-in mechanism for data serialization; relying on it (how data is serialized to a portable format) is clearer and safer than the alternatives, which misstate how SQL behaves.
8. For a SQL codebase, what should you know about dependency management — how third-party libraries are added?
- A. It always returns null.
- B. Use the language's idiomatic dependency management feature, which is designed for exactly this purpose.correct
- C. It is only available in unsafe mode.
- D. It is undefined behavior.
Why: Idiomatic SQL provides a built-in mechanism for dependency management; relying on it (how third-party libraries are added) is clearer and safer than the alternatives, which misstate how SQL behaves.
9. In SQL, what is the idiomatic approach to asynchronous I/O (how non-blocking I/O is performed)?
- A. It requires an external framework.
- B. It is only available in unsafe mode.
- C. Use the language's idiomatic asynchronous I/O feature, which is designed for exactly this purpose.correct
- D. It blocks the main thread until completion.
Why: Idiomatic SQL provides a built-in mechanism for asynchronous I/O; relying on it (how non-blocking I/O is performed) is clearer and safer than the alternatives, which misstate how SQL behaves.
10. For a SQL codebase, what should you know about type system — how types are checked and inferred?
- A. It throws a runtime exception by default.
- B. Use the language's idiomatic type system feature, which is designed for exactly this purpose.correct
- C. It is only available in unsafe mode.
- D. It allocates a new copy every time.
Why: Idiomatic SQL provides a built-in mechanism for type system; relying on it (how types are checked and inferred) is clearer and safer than the alternatives, which misstate how SQL behaves.
11. For a SQL codebase, what should you know about closures and capture — how surrounding state is captured by inner functions?
- A. It is identical to the global variant.
- B. It is deprecated in current versions.
- C. Use the language's idiomatic closures and capture feature, which is designed for exactly this purpose.correct
- D. It is handled automatically by the garbage collector.
Why: Idiomatic SQL provides a built-in mechanism for closures and capture; relying on it (how surrounding state is captured by inner functions) is clearer and safer than the alternatives, which misstate how SQL behaves.
12. Which option correctly describes concurrency model in SQL, specifically how concurrent or parallel work is expressed?
- A. Use the language's idiomatic concurrency model feature, which is designed for exactly this purpose.correct
- B. It allocates a new copy every time.
- C. It requires an external framework.
- D. It is handled automatically by the garbage collector.
Why: Idiomatic SQL provides a built-in mechanism for concurrency model; relying on it (how concurrent or parallel work is expressed) is clearer and safer than the alternatives, which misstate how SQL behaves.
13. In SQL, what is the idiomatic approach to null handling (how absence of a value is represented)?
- A. It is only available in unsafe mode.
- B. It is deprecated in current versions.
- C. It silently ignores the operation.
- D. Use the language's idiomatic null handling feature, which is designed for exactly this purpose.correct
Why: Idiomatic SQL provides a built-in mechanism for null handling; relying on it (how absence of a value is represented) is clearer and safer than the alternatives, which misstate how SQL behaves.
14. A reviewer asks about memory management in your SQL code. What is the correct guidance regarding how memory is allocated and reclaimed?
- A. It is handled automatically by the garbage collector.
- B. It is only available in unsafe mode.
- C. Use the language's idiomatic memory management feature, which is designed for exactly this purpose.correct
- D. It is undefined behavior.
Why: Idiomatic SQL provides a built-in mechanism for memory management; relying on it (how memory is allocated and reclaimed) is clearer and safer than the alternatives, which misstate how SQL behaves.
15. For a SQL codebase, what should you know about module system — how code is organized into modules or packages?
- A. Use the language's idiomatic module system feature, which is designed for exactly this purpose.correct
- B. It is only available in unsafe mode.
- C. It blocks the main thread until completion.
- D. It is deprecated in current versions.
Why: Idiomatic SQL provides a built-in mechanism for module system; relying on it (how code is organized into modules or packages) is clearer and safer than the alternatives, which misstate how SQL behaves.
16. When working with error handling in SQL, which statement best reflects the recommended way to handle and propagate errors?
- A. Use the language's idiomatic error handling feature, which is designed for exactly this purpose.correct
- B. It requires an external framework.
- C. It is identical to the global variant.
- D. It is deprecated in current versions.
Why: Idiomatic SQL provides a built-in mechanism for error handling; relying on it (the recommended way to handle and propagate errors) is clearer and safer than the alternatives, which misstate how SQL behaves.
17. When working with equality semantics in SQL, which statement best reflects the difference between identity and value equality?
- A. It silently ignores the operation.
- B. It compiles to no-op.
- C. Use the language's idiomatic equality semantics feature, which is designed for exactly this purpose.correct
- D. It blocks the main thread until completion.
Why: Idiomatic SQL provides a built-in mechanism for equality semantics; relying on it (the difference between identity and value equality) is clearer and safer than the alternatives, which misstate how SQL behaves.
18. A reviewer asks about performance tuning in your SQL code. What is the correct guidance regarding the most effective optimization strategy?
- A. It is identical to the global variant.
- B. Use the language's idiomatic performance tuning feature, which is designed for exactly this purpose.correct
- C. It always returns null.
- D. It requires an external framework.
Why: Idiomatic SQL provides a built-in mechanism for performance tuning; relying on it (the most effective optimization strategy) is clearer and safer than the alternatives, which misstate how SQL behaves.
19. Which option correctly describes testing approach in SQL, specifically the conventional unit-testing approach?
- A. It is undefined behavior.
- B. It throws a runtime exception by default.
- C. It requires an external framework.
- D. Use the language's idiomatic testing approach feature, which is designed for exactly this purpose.correct
Why: Idiomatic SQL provides a built-in mechanism for testing approach; relying on it (the conventional unit-testing approach) is clearer and safer than the alternatives, which misstate how SQL behaves.
20. In SQL, what is the idiomatic approach to collection iteration (the idiomatic way to iterate a collection)?
- A. Use the language's idiomatic collection iteration feature, which is designed for exactly this purpose.correct
- B. It requires an external framework.
- C. It always returns null.
- D. It is deprecated in current versions.
Why: Idiomatic SQL provides a built-in mechanism for collection iteration; relying on it (the idiomatic way to iterate a collection) is clearer and safer than the alternatives, which misstate how SQL behaves.