SQL Interview Essentials: Key Differences and Concepts Quiz

Challenge your knowledge of essential SQL interview questions with this quiz. Test your understanding of topics like stored procedures vs functions, clustered vs non-clustered indexes, DELETE vs TRUNCATE commands, and the use of WHERE and HAVING clauses.

1. Stored Procedures vs Functions – Parameters

   Which statement correctly describes the handling of parameters in stored procedures and functions in SQL?

   1. A stored procedure can have both input and output parameters, while a function can only have input parameters.
   2. Functions can have output parameters, but procedures cannot.
   3. Both stored procedures and functions can have output parameters.
   4. Neither procedures nor functions can use input parameters.

   Explanation: Stored procedures can accept both input and output parameters, allowing two-way data transfer. Functions, on the other hand, only support input parameters and always return a single value. The statement indicating that both can have output parameters is incorrect, as is the idea that functions allow output parameters. The claim that neither can accept input parameters is false.

2. DML Usage in Functions

   Which of the following SQL statements is typically NOT allowed inside a function?

   1. SELECT
   2. INSERT
   3. RETURN
   4. Arithmetic operations

   Explanation: Functions in SQL cannot include DML statements like INSERT, UPDATE, or DELETE to modify data. However, using SELECT statements, returning a value, and performing arithmetic operations are permitted within functions. Thus, INSERT is not allowed, while the other options are acceptable in functions.

3. Using Functions in Queries

   Where can a user-defined function be used in an SQL statement?

   1. Only as a standalone statement
   2. In SELECT, WHERE, and HAVING clauses
   3. Only in WHERE clauses
   4. Only in SELECT statements

   Explanation: User-defined functions can be used almost anywhere an expression is allowed, including SELECT, WHERE, and HAVING clauses. They are not limited to just SELECT or solely the WHERE clause. The statement about standalone usage is inaccurate, and they are not restricted to specific parts of a query.

4. Using Stored Procedures in Queries

   Can you directly use a stored procedure in a SELECT statement’s FROM clause?

   1. Yes, but only with table-valued procedures.
   2. Only if the procedure returns a single value.
   3. No, stored procedures cannot be used in a SELECT statement.
   4. Yes, stored procedures can be used in the FROM clause.

   Explanation: Stored procedures cannot be used directly within a SELECT statement, as they do not return values compatible with table expressions. Table-valued functions, not stored procedures, can be used in SELECT queries. Procedures returning single values or using table-valued procedures are not standard in SQL.

5. Transaction Management in Functions

   Which SQL object supports transaction management: stored procedures or functions?

   1. Stored procedures
   2. Neither supports transaction management
   3. Both stored procedures and functions
   4. Functions

   Explanation: Only stored procedures support transaction management, allowing BEGIN, COMMIT, and ROLLBACK statements. Functions cannot manage transactions directly. The answers stating both or neither support transactions are incorrect. Functions do not have transaction control.

6. Using Try-Catch Blocks

   Which object allows the use of Try-Catch blocks for error handling in SQL?

   1. Stored procedures
   2. Functions
   3. Neither
   4. Both stored procedures and functions

   Explanation: Try-Catch error handling is allowed inside stored procedures but not inside functions. Functions are intentionally restricted from such procedural constructs. The distractors include 'both' and 'functions', but this feature isn’t available for functions.

7. Number of Clustered Indexes per Table

   What is the maximum number of clustered indexes that can be created on a single SQL table?

   1. Unlimited
   2. 10
   3. 249
   4. 1

   Explanation: A table can have only one clustered index, as the clustered index determines the physical ordering of rows. Having more would create ambiguity. Options suggesting 10 or 249 are incorrect limitations for non-clustered indexes, and 'unlimited' is never allowed.

8. Clustered vs Non-Clustered Index Storage

   How does a clustered index differ from a non-clustered index in terms of data storage?

   1. Neither stores actual data; both contain only pointers.
   2. The clustered index stores actual table data at the leaf nodes, while the non-clustered index stores only pointers.
   3. The non-clustered index stores data, while the clustered index stores pointers.
   4. Both store actual data at the leaf nodes.

   Explanation: A clustered index organizes the table on disk and stores the actual row data at its leaf level, while a non-clustered index holds references or pointers to the data rows. The options claiming the reverse, or that both store only pointers, are incorrect about the structural differences.

9. Index IDs

   Which Index ID is typically assigned to a clustered index in SQL?

   1. 1
   2. 999
   3. 249
   4. 0

   Explanation: The clustered index is always assigned Index ID 1. Index ID 0 is usually reserved for heap tables that lack a clustered index. Both 249 and 999 are limits or IDs related to non-clustered indexes, not the clustered one.

10. Primary Key and Index Type

    What type of index does a primary key constraint create by default in SQL?

    1. Clustered index
    2. Heap index
    3. No index is created
    4. Non-clustered index

    Explanation: By default, creating a primary key applies a clustered index unless one already exists or a specification is made. Non-clustered indexes are created by unique constraints by default. Heap is just an unordered table, and 'no index' is incorrect.

11. DELETE vs TRUNCATE – Where Clause

    Which command allows the use of a WHERE clause to filter which records are removed?

    1. DELETE
    2. Neither DELETE nor TRUNCATE
    3. Both DELETE and TRUNCATE
    4. TRUNCATE

    Explanation: The DELETE command supports a WHERE clause to filter and remove specific rows. TRUNCATE, in contrast, removes all records unconditionally, without filtering. The options suggesting both or neither are not correct based on SQL behavior.

12. After TRUNCATE on a Table with Identity Column

    What happens to the identity column when TRUNCATE TABLE is executed?

    1. The identity value continues from the last used value.
    2. No impact on the identity column.
    3. All identity values are changed to NULL.
    4. The identity value is reset to its seed value.

    Explanation: After TRUNCATE, the identity column (if present) is reset to its original seed value. Unlike DELETE, which retains the identity sequence, TRUNCATE discards previous numbering. The options stating NULL or 'no impact' are incorrect.

13. Command Type: DML or DDL

    Which pair accurately matches the command type for DELETE and TRUNCATE?

    1. DELETE is DML; TRUNCATE is DDL.
    2. Both are DML.
    3. Both are DDL.
    4. DELETE is DDL; TRUNCATE is DML.

    Explanation: DELETE is a Data Manipulation Language (DML) operation, used for row-level actions, while TRUNCATE is regarded as a Data Definition Language (DDL), as it makes broad structural changes. The other combinations mislabel the commands' categories.

14. Table Locks in DELETE vs TRUNCATE

    Which locking method does the DELETE command primarily use, compared to TRUNCATE?

    1. Both use only page locks.
    2. Neither uses locks during execution.
    3. DELETE uses row locks; TRUNCATE uses table locks.
    4. DELETE uses table locks; TRUNCATE uses row locks.

    Explanation: DELETE locks rows individually to facilitate precise deletions, while TRUNCATE typically locks the entire table for fast, bulk removal. The distractors referencing inverse locks or no locks aren't accurate. Page locks may be involved but aren't the primary lock for these commands.

15. WHERE Clause vs HAVING Clause

    In which SQL statements can the WHERE clause be used, compared to HAVING?

    1. WHERE can be used only with SELECT; HAVING can be used with UPDATE and INSERT.
    2. HAVING can be used with any DML command; WHERE is only for SELECT.
    3. WHERE can be used with SELECT, UPDATE, and DELETE; HAVING can be used only with SELECT.
    4. Both WHERE and HAVING can be used with all DML commands.

    Explanation: The WHERE clause filters rows in SELECT, UPDATE, and DELETE statements, while HAVING is explicitly for filtering groups in SELECT statements with GROUP BY. It's incorrect to say HAVING applies to all DML or that both apply everywhere. HAVING is not for UPDATE or INSERT.