Question 1

Database programming concept check: does a function return exactly one value and use only output parameters (with no inputs)? Clarify what is typically allowed for function parameters across SQL and procedural languages.

Accepted Answer

Introduction / Context: This item checks your understanding of what a “function” is in database programming and general programming practice. Many learners confuse a function’s return value with its parameters and assume that functions should have only outputs. In reality, functions are typically designed to take input parameters, perform computation, and return exactly one value (or a single structured value) as their result. The claim here says a function “returns one value and has only output parameters,” which conflates roles and is misleading. Given Data / Assumptions: A function conventionally returns a single value via the function’s return mechanism. Parameters are usually inputs that influence the computation. Some languages support OUT or IN OUT parameters, but that is not the primary or exclusive model of a function. In many SQL dialects, functions that are callable from SQL must have only IN parameters; OUT parameters are disallowed in that context. Concept / Approach: In procedural thinking, a function is invoked with inputs and produces one return value. Procedures (or stored procedures) are the constructs that more commonly use OUT or IN OUT parameters to pass multiple results. While some platforms permit OUT parameters in functions at the procedural level, the dominant practice—especially when the function is used inside SQL—requires IN parameters only. Therefore, saying a function “has only output parameters” is incorrect; functions primarily accept inputs and return a value. Step-by-Step Solution: Identify what defines a function: one return value delivered via RETURN.Recognize parameter roles: typically IN; OUT parameters are uncommon for functions and may be disallowed in SQL-callable contexts.Contrast with procedures, which often use OUT/IN OUT to return multiple values.Conclude the statement is inaccurate because it claims “only output parameters.” Verification / Alternative check: Review platform rules: many DBMSs allow functions in SELECT/WHERE only if all parameters are IN. This reinforces the idea that functions are not “only outputs.” Why Other Options Are Wrong: Correct: Would imply functions chiefly expose outputs as parameters, which contradicts common definitions and SQL-callable rules. Only true in PL-SQL / T-SQL / when called from SQL: These carve-outs are not accurate; in fact, the opposite is usually enforced (IN only for SQL invocation). Common Pitfalls: Confusing procedures with functions; assuming OUT parameters define a function’s identity; forgetting that SQL-invoked functions must behave deterministically with IN parameters. Final Answer: Incorrect

Question 2

Transaction behavior: when AUTOCOMMIT is ON, are changes committed automatically at the end of each SQL statement?

Accepted Answer

Introduction / Context: AUTOCOMMIT controls how your session ends transactions. Understanding this setting prevents surprises such as unintended commits or uncommitted work. The question asks whether, with AUTOCOMMIT enabled, each statement is committed automatically when it finishes. Given Data / Assumptions: A data modification statement (INSERT, UPDATE, DELETE, certain DDL) changes data or schema. A transaction commits when changes are made durable and visible to other sessions. AUTOCOMMIT setting is per-session behavior in many tools and drivers. Concept / Approach: With AUTOCOMMIT ON, each successful statement is its own transaction: the platform issues an implicit COMMIT after that statement completes successfully. If the statement fails, changes are implicitly rolled back. With AUTOCOMMIT OFF, multiple statements can be grouped into a single transaction until an explicit COMMIT or ROLLBACK is issued. Step-by-Step Solution: Check AUTOCOMMIT state: ON means commit-per-statement.Run a DML statement; upon success, the system auto-commits.Run another statement; it executes in a new transaction and auto-commits again.Conclude that AUTOCOMMIT ON commits after each statement completes successfully. Verification / Alternative check: Open two sessions: in one, with AUTOCOMMIT ON, perform an UPDATE; in the second session, immediately SELECT the affected rows—changes are visible without an explicit COMMIT in the first session. Why Other Options Are Wrong: Incorrect: Misunderstands AUTOCOMMIT semantics. Only for SELECT: SELECT does not modify data; AUTOCOMMIT concerns transactions around DML/DDL. Only after SAVEPOINT / session end: These refer to manual control or session lifecycle, not AUTOCOMMIT behavior. Common Pitfalls: Forgetting AUTOCOMMIT is ON and making irreversible changes; assuming a tool’s UI default equals the server’s default. Final Answer: Correct

Question 3

UNION rules: must each SELECT in a UNION return the same number of columns with union-compatible data types?

Accepted Answer

Introduction / Context: The UNION family combines multiple query results into a single result set. To merge rows correctly, SQL needs the same column structure across all branches. This question checks whether each SELECT in a UNION must match in column count and have union-compatible data types. Given Data / Assumptions: UNION and UNION ALL stack rows vertically. Corresponding positions (1st column, 2nd column, etc.) must align. Type compatibility means values can be coerced to a common supertype. Concept / Approach: SQL aligns columns by ordinal position, not by name. Therefore, each SELECT must project the same number of columns. Their data types must be compatible so the DBMS can determine a common type for the final column. UNION removes duplicates; UNION ALL preserves all rows, but both require structural compatibility. Step-by-Step Solution: Ensure every SELECT lists the same count of columns.Align types in each ordinal position (for example, text with text-like, number with number-like).Use casts if needed to enforce compatibility.Choose UNION or UNION ALL based on whether duplicates should be removed. Verification / Alternative check: Try a UNION where one SELECT has an extra column; the DBMS raises an error. Try incompatible types without casting; the DBMS rejects or implicitly converts within its rules. Why Other Options Are Wrong: Incorrect: Contradicts the core UNION rule. Only for UNION ALL / ORDER BY / numeric columns: The requirement applies to UNION and UNION ALL alike, regardless of ORDER BY or specific data types. Common Pitfalls: Assuming column names matter for alignment; forgetting explicit CAST to harmonize types; mismatched column counts. Final Answer: Correct

Question 4

Join basics: is the most common operation that combines rows from related tables into one result an equality-based join (equi-join)?

Accepted Answer

Introduction / Context: Joins are the workhorse of relational querying. Among join predicates, equality conditions (matching keys or columns) dominate day-to-day SQL, commonly referred to as equi-joins. This question asks whether the most frequently used relational operation that merges tables is an equi-join. Given Data / Assumptions: Tables are commonly related by primary key to foreign key relationships. Most relationships are enforced via equality conditions between matching columns. Other join types exist (non-equi, theta joins), but are less common. Concept / Approach: In normalized schemas, foreign keys reference primary keys or unique keys, and queries recombine data using equality predicates like T1.pk = T2.fk. Whether expressed with JOIN ... ON or WHERE, the core predicate is equality. Non-equi joins (for example, range joins) are important in specific domains but are not the everyday default. Step-by-Step Solution: Identify typical relationship columns (PK and FK).Write a JOIN using equality: SELECT ... FROM A JOIN B ON A.id = B.a_id.Note that this equi-join composes the majority of relational queries.Conclude that equi-joins are indeed the most frequently used operation to merge related tables. Verification / Alternative check: Examine application SQL logs or BI tools’ generated queries; nearly all relationship joins are equality-based. Why Other Options Are Wrong: Incorrect: Dismisses the predominant role of equality predicates. Outer-join / non-key / removing duplicate columns: These are separate concerns from the predicate type or represent narrower scenarios. Common Pitfalls: Confusing equi-join with natural join; thinking outer joins imply inequality predicates; forgetting that join logic and column projection are separate. Final Answer: Correct

Question 5

Trigger behavior: are database triggers stored programs that must be called explicitly in order to run, or do they fire automatically when their event occurs?

Accepted Answer

Introduction / Context: Triggers are a core database feature used to enforce rules, log changes, or maintain derived data. A common misunderstanding is that triggers are like stored procedures that you must call directly. In reality, triggers are bound to table or view events and execute automatically when those events occur. Given Data / Assumptions: Triggers are defined on data change events such as INSERT, UPDATE, or DELETE. They can be row-level or statement-level depending on the DBMS and definition. They reside in the database catalog and are invoked by the server engine. Concept / Approach: A trigger is a declarative hook: once created, it fires automatically before or after the specified DML event on the target object. Unlike procedures or functions, which are invoked via CALL or SELECT, a trigger runs because the triggering event happened, not because a user explicitly invoked it. This makes triggers suitable for auditing, enforcing complex constraints, and propagating changes. Step-by-Step Solution: Define a trigger on a table for INSERT.Execute an INSERT on that table.Observe that the trigger executes without an explicit CALL.Conclude that triggers do not need manual invocation; they fire automatically. Verification / Alternative check: Temporarily disable the trigger; perform the DML; note that its logic no longer runs—confirming that the engine, not user code, invokes it in response to events. Why Other Options Are Wrong: Correct: Would imply triggers require explicit calls; not true. Only true for statement/row-level or legacy: Trigger auto-invocation applies broadly, regardless of granularity or modernity. Common Pitfalls: Doing business logic exclusively in triggers leading to opaque side effects; creating recursive trigger behavior. Final Answer: Incorrect

Question 6

Transaction control: does ROLLBACK WORK instruct SQL to “empty the log file,” or does it simply undo uncommitted changes in the current transaction?

Accepted Answer

Introduction / Context: ROLLBACK WORK (or simply ROLLBACK) is fundamental in transactional SQL. Misunderstanding what it does can put data integrity at risk. The statement claims that ROLLBACK “asks SQL to empty the log file,” which is not what rollback does. Given Data / Assumptions: The log (write-ahead log, transaction log, redo/undo) is an internal engine structure for durability and recovery. ROLLBACK reverts uncommitted changes in the active transaction. DBMS engines manage log truncation/archiving independently of a user’s rollback. Concept / Approach: A rollback walks back uncommitted changes so the database returns to the last consistent state (the start of the transaction or a SAVEPOINT). It does not perform log maintenance. Log space management (checkpointing, truncation, archiving) is handled by the engine based on recovery settings, not by the ROLLBACK command itself. Step-by-Step Solution: Begin a transaction and perform DML (INSERT/UPDATE/DELETE).Issue ROLLBACK; the uncommitted changes are undone.Observe data state: it matches the state before the transaction began.Note that the transaction log persists per engine policy; rollback does not “empty” it. Verification / Alternative check: Inspect log file growth and retention policies; they are governed by checkpoints, backups, and engine settings, independent of a single rollback. Why Other Options Are Wrong: Correct: Would misstate rollback’s purpose. Isolation level, savepoints, or DDL caveats do not turn rollback into a log-emptying command. Common Pitfalls: Confusing engine recovery mechanics with user transaction control; assuming rollback reclaims disk immediately. Final Answer: Incorrect

Question 7

Set operations: does the UNION clause combine the outputs of multiple queries into a single result set?

Accepted Answer

Introduction / Context: UNION is one of SQL’s set operations (UNION, UNION ALL, INTERSECT, EXCEPT/MINUS). It vertically concatenates the results of multiple SELECT statements into a single result set. This question asks if that description is accurate. Given Data / Assumptions: Each SELECT in a UNION must have the same number of columns and union-compatible types. UNION removes duplicates; UNION ALL keeps all rows. Column names are taken from the first SELECT unless aliased. Concept / Approach: Conceptually, UNION stacks result sets. After stacking, UNION applies a duplicate elimination step, while UNION ALL does not. Ordering is optional and, when used, applies to the final combined set via a trailing ORDER BY clause. Step-by-Step Solution: Prepare two SELECT statements with aligned columns.Combine using SELECT ... UNION SELECT ...Observe the combined rows in one result set.Add ORDER BY at the end if a specific ordering is desired. Verification / Alternative check: Compare UNION vs UNION ALL on datasets with duplicates to see the effect on row counts. Why Other Options Are Wrong: Incorrect: Misrepresents UNION’s role. Requiring identical column names / ORDER BY / UNION ALL is unnecessary; structural compatibility is what matters. Common Pitfalls: Mismatched column counts; forgetting that ORDER BY must reference the combined output; assuming UNION preserves internal ordering of each branch. Final Answer: Correct

Question 8

Subquery depth: can SQL subqueries be nested multiple levels deep (subject to practical and vendor limits)?

Accepted Answer

Introduction / Context: Subqueries are SELECT statements embedded inside another SQL statement. They are used for filtering, deriving values, and correlating results. This question asks whether subqueries can be nested multiple times. Given Data / Assumptions: SQL supports subqueries in clauses such as SELECT, FROM, WHERE, and HAVING (vendor-specific variations apply). Vendors may impose recursion or depth limits, but these are generally generous. Readability and performance, not syntax, are the usual constraints. Concept / Approach: Nesting subqueries allows stepwise problem solving: a subquery computes an intermediate set, which another query further filters or aggregates. Correlated subqueries reference columns from the outer query. While nesting is allowed, many problems are clearer and faster using JOINs or Common Table Expressions (CTEs). Step-by-Step Solution: Write a subquery in WHERE to filter with IN (SELECT ...).Place another subquery inside that subquery if needed (for example, to pre-filter).Optionally use scalar subqueries in SELECT for derived values.Confirm that multiple nesting levels parse and execute, subject to vendor limits and optimization. Verification / Alternative check: Convert deep nesting to CTEs for readability; execution plans should be functionally equivalent. Why Other Options Are Wrong: Incorrect / “Only one/two levels”: SQL syntax permits more than one level; limits are practical. “Only allowed in WHERE”: Scalar and derived table subqueries are valid in other clauses. Common Pitfalls: Overusing correlated subqueries causing performance issues; ignoring indexes; not analyzing execution plans. Final Answer: Correct

Question 9

Join types: is an outer join the same as an equi-join except that one duplicate column is omitted in the result?

Accepted Answer

Introduction / Context: SQL offers several join flavors: inner, outer (LEFT/RIGHT/FULL), natural, and equi-joins. Confusion often arises between outer joins and natural joins. This statement claims an outer join is the same as an equi-join but with one duplicate column removed, which is not correct. Given Data / Assumptions: Equi-join: join condition uses equality between columns (A.id = B.id). Outer join: preserves unmatched rows from one or both sides, filling missing columns with NULLs. Natural join: automatically joins by columns with the same names and outputs only one copy of those columns. Concept / Approach: The defining feature of outer joins is row preservation, not column projection. Removing duplicate columns is a characteristic of NATURAL JOIN (and sometimes USING syntax), not of outer joins per se. An outer join can be equi-based in its predicate, but it is distinct because it keeps non-matching rows from the preserved side(s). Step-by-Step Solution: Write an INNER JOIN A.id = B.id: only matching rows appear.Write a LEFT OUTER JOIN A.id = B.id: all A rows appear; unmatched B columns are NULL.Observe that both A.id and B.id remain unless explicitly omitted; duplicates are not auto-removed.Note that NATURAL JOIN removes duplicate same-named columns, which is a different concept. Verification / Alternative check: Test with sample data containing non-matching keys; only outer joins preserve those rows; natural join behavior on columns is unrelated to row preservation. Why Other Options Are Wrong: Correct / LEFT OUTER JOIN / primary key: None of these make an outer join intrinsically remove duplicate columns. Only true for NATURAL JOIN: This option names the feature (column de-duplication) correctly but confuses it with outer joins; natural joins can be inner or outer but the de-dup relates to column naming, not “outer-ness.” Common Pitfalls: Thinking USING/NATURAL removes columns for all join types; forgetting that outer joins are about including unmatched rows. Final Answer: Incorrect

Advanced SQL Questions