1Which component of a database programming block is used to trap and handle runtime errors?
A.Declaration Section
B.Execution Section
C.Exception Handling Section
D.Header Section
Correct Answer: Exception Handling Section
Explanation:The Exception Handling Section is specifically designed to catch and manage runtime errors (exceptions) that occur during the execution of the block, preventing abrupt program termination.
Incorrect! Try again.
2In PL/SQL and T-SQL, which keyword is used to define a conditional control structure that executes a sequence of statements only if a condition is true?
A.LOOP
B.IF
C.CASE
D.WHILE
Correct Answer: IF
Explanation:The IF statement is the fundamental control structure used for conditional execution. It follows the logic: IF condition THEN statements END IF.
Incorrect! Try again.
3Which of the following statements regarding Stored Procedures is FALSE?
A.They can reduce network traffic.
B.They are compiled and stored in the database.
C.They must always return a value to the caller.
D.They can improve security by encapsulating logic.
Correct Answer: They must always return a value to the caller.
Explanation:Stored procedures are not required to return a value. They perform an action. This distinguishes them from Functions, which must return a value.
Incorrect! Try again.
4What is the primary purpose of a Cursor in database programming?
A.To trigger an event automatically.
B.To handle row-by-row processing of a result set.
C.To define the structure of a table.
D.To store a compiled SQL statement permanently.
Correct Answer: To handle row-by-row processing of a result set.
Explanation:SQL operates on sets of rows. A Cursor allows a program to process these rows one at a time (tuple-at-a-time processing).
Incorrect! Try again.
5Which parameter mode allows a value to be passed into a stored procedure and also allows the procedure to change that value and pass it back to the calling environment?
A.IN
B.OUT
C.INOUT (or IN OUT)
D.RETURN
Correct Answer: INOUT (or IN OUT)
Explanation:INOUT parameters act as both input and output parameters. The caller passes an initial value, the procedure can modify it, and the new value is returned to the caller.
Incorrect! Try again.
6When using a cursor, which command is used to retrieve the current row from the result set and advance the cursor pointer to the next row?
A.OPEN
B.SELECT
C.FETCH
D.GET
Correct Answer: FETCH
Explanation:The FETCH statement retrieves the data from the current row of the cursor's active set and stores it in variables, then advances the pointer.
Incorrect! Try again.
7Which type of Trigger is executed once for every row affected by a DML statement (INSERT, UPDATE, DELETE)?
A.Statement-level Trigger
B.Row-level Trigger
C.System Trigger
D.INSTEAD OF Trigger
Correct Answer: Row-level Trigger
Explanation:A Row-level Trigger fires once for each individual row modified. If an UPDATE statement affects 10 rows, a row-level trigger fires 10 times.
Incorrect! Try again.
8What does the cursor attribute %%NOTFOUND return?
A.It returns TRUE if the last FETCH returned a row.
B.It returns the number of rows fetched so far.
C.It returns TRUE if the last FETCH failed to return a row.
D.It returns the text of the SQL statement.
Correct Answer: It returns TRUE if the last FETCH failed to return a row.
Explanation:%%NOTFOUND (often written as %NOTFOUND in PL/SQL) is a boolean attribute that evaluates to TRUE when a fetch operation fails to retrieve data (usually indicating the end of the result set).
Incorrect! Try again.
9Which programming construct is conceptually similar to a 'view' that allows parameters?
A.Stored Procedure
B.Trigger
C.Scalar Function
D.Table-Valued Function
Correct Answer: Table-Valued Function
Explanation:A Table-Valued Function returns a table data type and can be used in the FROM clause of a query, similar to a view, but it accepts parameters to filter or calculate data dynamically.
Incorrect! Try again.
10In the context of exception handling, what does the RAISE statement do?
A.It increases the priority of the transaction.
B.It explicitly generates an exception or error.
C.It recovers the database from a crash.
D.It creates a new trigger.
Correct Answer: It explicitly generates an exception or error.
Explanation:The RAISE statement is used to explicitly stop normal execution and transfer control to the exception handling block, often used to enforce business rules.
Incorrect! Try again.
11Which of the following is NOT a valid cursor attribute?
A.%FOUND
B.%ISOPEN
C.%ROWCOUNT
D.%TYPE
Correct Answer: %TYPE
Explanation:%FOUND, %ISOPEN, and %ROWCOUNT are cursor attributes describing the state of the cursor. %TYPE is a data type attribute used to declare a variable with the same type as a table column, but it is not a cursor attribute.
Incorrect! Try again.
12What is the difference between a Function and a Stored Procedure regarding SQL statements?
A.Functions can only contain DDL statements.
B.Procedures can be called from within a SELECT statement (in most DBMS), whereas Functions cannot.
C.Functions are generally called from within SQL statements (like SELECT), whereas Procedures are called using CALL or EXECUTE.
D.There is no difference.
Correct Answer: Functions are generally called from within SQL statements (like SELECT), whereas Procedures are called using CALL or EXECUTE.
Explanation:Functions are designed to return a value and are typically used in expressions within SQL queries. Procedures are invoked as standalone executable blocks.
Incorrect! Try again.
13An INSTEAD OF trigger is most commonly used on which database object?
A.Tables
B.Views
C.Indexes
D.Sequences
Correct Answer: Views
Explanation:INSTEAD OF triggers are used on Views (specifically non-updatable views) to define logic that should occur in place of the DML operation, effectively making the view updatable.
Incorrect! Try again.
14Which loop structure repeats a sequence of statements as long as a specified condition is true?
A.FOR loop
B.WHILE loop
C.BASIC loop
D.CURSOR loop
Correct Answer: WHILE loop
Explanation:The WHILE loop checks a condition before each iteration. If the condition is true, the loop body executes; otherwise, the loop terminates.
Incorrect! Try again.
15In a trigger, what do the pseudo-records NEW and OLD represent?
A.They represent the first and last rows of a table.
B.They represent the state of the row before and after the DML operation.
C.They represent the previous and next transaction IDs.
D.They are used to create new tables.
Correct Answer: They represent the state of the row before and after the DML operation.
Explanation:OLD references the values of the row columns before an update or delete. NEW references the values that will be inserted or updated.
Incorrect! Try again.
16Which control statement evaluates a single expression and compares it against several potential values to determine which branch to execute?
A.IF-THEN-ELSE
B.WHILE
C.CASE
D.GOTO
Correct Answer: CASE
Explanation:The CASE statement (specifically the simple CASE) evaluates an expression once and compares it against a list of WHEN clauses to find a match.
Incorrect! Try again.
17A stored procedure calls stored procedure . If an unhandled exception occurs in , what generally happens?
A.The database shuts down.
B.The exception is ignored and returns successfully.
C.The exception propagates up to the calling procedure .
D.Procedure is immediately deleted.
Correct Answer: The exception propagates up to the calling procedure .
Explanation:Exceptions propagate up the call stack. If does not handle it, the control and the exception pass to . If doesn't handle it, it goes to the host environment.
Incorrect! Try again.
18Which of the following is a benefit of using Implicit Cursors?
A.They allow precise control over when to fetch rows.
B.They are automatically managed by the DBMS for single-row DML and SELECT operations.
C.They are used for fetching large result sets manually.
D.They allow the use of WHERE CURRENT OF clauses.
Correct Answer: They are automatically managed by the DBMS for single-row DML and SELECT operations.
Explanation:Implicit cursors are created automatically by the system for SQL statements like INSERT, UPDATE, DELETE, and SELECT INTO, reducing the coding burden for simple operations.
Incorrect! Try again.
19What is the purpose of the CLOSE statement in cursor management?
A.It deletes the cursor definition from the code.
B.It releases the memory and resources associated with the result set.
C.It executes the query associated with the cursor.
D.It commits the transaction.
Correct Answer: It releases the memory and resources associated with the result set.
Explanation:The CLOSE statement disables the cursor, releases the context area, and frees resources. The cursor cannot be fetched from again unless it is reopened.
Incorrect! Try again.
20Which type of trigger would you use to prevent a row from being deleted if it violates a specific complex business rule?
A.AFTER DELETE
B.BEFORE DELETE
C.AFTER INSERT
D.BEFORE UPDATE
Correct Answer: BEFORE DELETE
Explanation:A BEFORE DELETE trigger fires before the actual deletion occurs. You can raise an error inside this trigger to cancel the operation and prevent the deletion.
Incorrect! Try again.
21Consider the following syntax: What is the purpose of WHEN OTHERS?
A.To handle only arithmetic errors.
B.To handle specific named exceptions.
C.To handle any exception not explicitly handled by previous handlers.
D.To restart the block.
Correct Answer: To handle any exception not explicitly handled by previous handlers.
Explanation:WHEN OTHERS is a catch-all handler in the exception section. It traps any error that was not matched by specific WHEN [Exception_Name] clauses.
Incorrect! Try again.
22Which variable is often used inside an exception handler to retrieve the error message associated with the exception?
A.SQLCODE
B.SQLERRM
C.%ROWCOUNT
D.NO_DATA_FOUND
Correct Answer: SQLERRM
Explanation:SQLERRM (SQL Error Message) is a function that returns the error message text associated with the current error code.
Incorrect! Try again.
23What is a Mutating Table error in the context of triggers?
A.An error that occurs when a table's structure is changed during a transaction.
B.An error occurring when a row-level trigger attempts to query or modify the table that triggered the event.
C.An error caused by a trigger calling itself recursively indefinitely.
D.An error when a table is dropped while a trigger is firing.
Correct Answer: An error occurring when a row-level trigger attempts to query or modify the table that triggered the event.
Explanation:A mutating table is a table that is currently being modified by an update, delete, or insert statement. Row-level triggers generally cannot query the mutating table because the data state is inconsistent.
Incorrect! Try again.
24In a FOR loop defined as FOR i IN 1..10 LOOP, what happens to the counter variable i?
A.It must be explicitly declared in the declaration section.
B.It is implicitly declared as an integer by the loop construct.
C.It must be manually incremented inside the loop.
D.It retains its value after the loop ends.
Correct Answer: It is implicitly declared as an integer by the loop construct.
Explanation:In most PL/SQL dialects, the loop index variable in a FOR loop is implicitly declared, its scope is local to the loop, and it is automatically incremented.
Incorrect! Try again.
25What is the primary difference between User-defined Exceptions and Predefined Exceptions?
B.Predefined exceptions are raised automatically by the system; user-defined must be declared and raised explicitly.
C.User-defined exceptions cannot have error messages.
D.There is no difference.
Correct Answer: Predefined exceptions are raised automatically by the system; user-defined must be declared and raised explicitly.
Explanation:Predefined exceptions (like NO_DATA_FOUND) are defined by the DBMS and raised automatically upon specific error conditions. User-defined exceptions are defined by the programmer to enforce business logic.
Incorrect! Try again.
26Which statement allows a programmer to exit a loop immediately, bypassing the remaining statements in the loop body?
A.CONTINUE
B.EXIT (or BREAK)
C.PASS
D.NEXT
Correct Answer: EXIT (or BREAK)
Explanation:The EXIT (PL/SQL) or BREAK (T-SQL) statement immediately terminates the loop and transfers control to the statement following the loop.
Incorrect! Try again.
27To execute a Stored Procedure named Calculate_Tax with a parameter 1000, which syntax is generally used?
A.SELECT Calculate_Tax(1000);
B.EXEC Calculate_Tax(1000); or CALL Calculate_Tax(1000);
C.RUN Calculate_Tax(1000);
D.TRIGGER Calculate_Tax(1000);
Correct Answer: EXEC Calculate_Tax(1000); or CALL Calculate_Tax(1000);
Explanation:While syntax varies slightly by DBMS (Oracle uses EXEC/CALL, MySQL uses CALL), CALL or EXEC is the standard method to invoke a procedure. SELECT is used for functions.
Incorrect! Try again.
28What does the PRAGMA EXCEPTION_INIT instruction do?
A.It creates a new exception type.
B.It associates a user-defined exception name with a specific backend error code.
C.It ignores all exceptions in a block.
D.It initializes the exception handling section.
Correct Answer: It associates a user-defined exception name with a specific backend error code.
Explanation:This pragma tells the compiler to associate a specific database error number (e.g., -20001) with a declared exception variable, allowing the programmer to handle that specific error by name.
Incorrect! Try again.
29Which construct allows a cursor to be defined with arguments, making it dynamic?
A.Implicit Cursor
B.Parameterized Cursor
C.Reference Cursor
D.Trigger
Correct Answer: Parameterized Cursor
Explanation:A Parameterized Cursor allows values to be passed into the cursor query's WHERE clause when the cursor is opened, allowing the same cursor logic to be used for different data sets.
Incorrect! Try again.
30When declaring a variable in a database program, the NOT NULL constraint implies:
A.The variable cannot be changed after initialization.
B.The variable must be initialized with a value and cannot be assigned NULL later.
C.The variable is a primary key.
D.The variable is visible globally.
Correct Answer: The variable must be initialized with a value and cannot be assigned NULL later.
Explanation:If a variable is declared NOT NULL, the PL/SQL engine prevents null values from being assigned to it, and it usually requires an initial value at declaration.
Incorrect! Try again.
31Which of the following is an example of a Control Flow statement?
A.INSERT INTO
B.CREATE TABLE
C.IF...THEN...ELSE
D.COMMIT
Correct Answer: IF...THEN...ELSE
Explanation:Control flow statements determine the order in which instructions are executed. IF...THEN...ELSE directs the flow based on conditions.
Incorrect! Try again.
32If a Trigger is defined as BEFORE UPDATE OF salary ON Employees, when does it fire?
A.Before any column in the Employees table is updated.
B.Before the 'salary' column specifically is updated.
C.After the 'salary' column is updated.
D.Before a new employee is inserted.
Correct Answer: Before the 'salary' column specifically is updated.
Explanation:The OF column_name syntax restricts the trigger to fire only when the specific column mentioned is modified in the UPDATE statement.
Incorrect! Try again.
33What is a Compound Trigger?
A.A trigger that fires on multiple tables simultaneously.
B.A single trigger that can define actions for multiple timing points (Before Statement, Before Row, After Row, After Statement).
C.A trigger that calls a stored procedure.
D.A trigger that creates another trigger.
Correct Answer: A single trigger that can define actions for multiple timing points (Before Statement, Before Row, After Row, After Statement).
Explanation:A compound trigger combines the logic of several different trigger types (timing points) into one code block, allowing them to share state variables.
Incorrect! Try again.
34Which exception is typically raised when a SELECT INTO statement returns more than one row?
A.NO_DATA_FOUND
B.TOO_MANY_ROWS
C.ZERO_DIVIDE
D.VALUE_ERROR
Correct Answer: TOO_MANY_ROWS
Explanation:A standard SELECT INTO expects exactly one row. If the query result contains multiple rows, the database engine raises the TOO_MANY_ROWS exception.
Incorrect! Try again.
35Which statement is used to remove a stored procedure from the database?
A.DELETE PROCEDURE
B.REMOVE PROCEDURE
C.DROP PROCEDURE
D.ALTER PROCEDURE
Correct Answer: DROP PROCEDURE
Explanation:The DROP command is the standard DDL command for removing database objects, including procedures (DROP PROCEDURE proc_name).
Incorrect! Try again.
36What is the result of the expression: in most database programming languages?
A.5
B.
C.NULL
D.Error
Correct Answer: NULL
Explanation:In SQL and database programming, NULL represents an unknown value. Any arithmetic operation involving NULL results in NULL (Unknown + 5 = Unknown).
Incorrect! Try again.
37The WHERE CURRENT OF cursor_name clause is used in UPDATE or DELETE statements to:
A.Update or delete the most recently fetched row by the cursor.
B.Update or delete all rows in the cursor.
C.Update or delete rows that match a WHERE condition in the table.
D.Close the cursor.
Correct Answer: Update or delete the most recently fetched row by the cursor.
Explanation:This clause allows the user to modify the specific row currently pointed to by the cursor without repeating the selection criteria in the WHERE clause.
Incorrect! Try again.
38Which of the following is TRUE about Deterministic Functions?
A.They return different results for the same input values.
B.They always return the same result given the same input values.
C.They use random number generators.
D.They cannot be used in function-based indexes.
Correct Answer: They always return the same result given the same input values.
Explanation:Deterministic functions guarantee consistency. For a specific set of inputs, they will always output the same result, which allows the database to optimize calls or index the results.
Incorrect! Try again.
39In the context of Loops, what does the CONTINUE statement do?
A.Terminates the loop entirely.
B.Skips the remainder of the current iteration and proceeds to the next iteration.
C.Pauses the loop execution.
D.Restarts the loop from index 1.
Correct Answer: Skips the remainder of the current iteration and proceeds to the next iteration.
Explanation:CONTINUE stops the current pass through the loop and immediately jumps to the loop control (condition check or increment), effectively skipping any code below it for that iteration.
Incorrect! Try again.
40Which parameter type is the default if no mode is specified in a stored procedure declaration?
A.OUT
B.INOUT
C.IN
D.REF
Correct Answer: IN
Explanation:If the parameter mode is omitted, the database assumes the parameter is an IN parameter (read-only inside the procedure).
Incorrect! Try again.
41What is the primary benefit of using exception handling in database scripts?
A.To make the code run faster.
B.To prevent the application from crashing and to manage transactions gracefully (e.g., Rollback).
C.To automatically correct data errors.
D.To increase database storage size.
Correct Answer: To prevent the application from crashing and to manage transactions gracefully (e.g., Rollback).
Explanation:Exception handling separates error processing code from normal logic, ensuring that if an error occurs, the program can clean up resources, roll back partial transactions, and log errors rather than just stopping.
Incorrect! Try again.
42Which cursor attribute returns the number of rows fetched so far (for explicit cursors) or rows affected (for implicit cursors)?
A.%ROWCOUNT
B.%COUNT
C.%NUMROWS
D.%FOUND
Correct Answer: %ROWCOUNT
Explanation:%ROWCOUNT provides an integer value representing the number of rows processed by the most recent SQL statement or fetched from a cursor.
Incorrect! Try again.
43What is the 'Execution Order' of a database block?
Explanation:The standard block structure begins with declarations (variables), followed by the execution logic (begin...end), and finally exception handling if errors occur during execution.
Incorrect! Try again.
44Which statement allows you to pass a cursor variable (REF CURSOR) as a parameter to a stored procedure?
A.It is not possible to pass cursors.
B.By declaring the parameter type as SYS_REFCURSOR or a generic cursor type.
C.By converting the cursor to a string.
D.By using an array.
Correct Answer: By declaring the parameter type as SYS_REFCURSOR or a generic cursor type.
Explanation:Ref Cursors (Reference Cursors) are pointers to result sets that can be passed between procedures and client applications.
Incorrect! Try again.
45Which of the following is an example of a Searched CASE statement?
A.CASE selector WHEN value THEN result END
B.CASE WHEN grade > 90 THEN 'A' WHEN grade > 80 THEN 'B' END
C.IF grade > 90 THEN 'A' END IF
D.SWITCH(grade)
Correct Answer: CASE WHEN grade > 90 THEN 'A' WHEN grade > 80 THEN 'B' END
Explanation:A searched CASE statement does not have a selector expression immediately after the keyword CASE. Instead, each WHEN clause contains an independent boolean condition.
Incorrect! Try again.
46Why are Triggers generally avoided for returning data to the user?
A.They are too slow.
B.They are event-driven and run in the background; they are not designed to return result sets.
C.They cannot access tables.
D.They do not support SQL.
Correct Answer: They are event-driven and run in the background; they are not designed to return result sets.
Explanation:Triggers are invoked implicitly by database events. There is no direct connection to the user interface to 'return' data in the way a SELECT query or function does.
Incorrect! Try again.
47Can a Stored Procedure call itself recursively?
A.No, recursion is not supported in SQL.
B.Yes, provided the recursion depth limit is not exceeded.
C.Only if it is a function, not a procedure.
D.Yes, but only once.
Correct Answer: Yes, provided the recursion depth limit is not exceeded.
Explanation:Most modern DBMS (like Oracle, SQL Server, PostgreSQL) support recursion in stored procedures and functions, though there is usually a configuration limit (stack depth) to prevent infinite loops.
Incorrect! Try again.
48What is the status of a cursor immediately after it is OPENed but before the first FETCH?
A.It points to the first row.
B.It points before the first row.
C.It points to the last row.
D.It is closed.
Correct Answer: It points before the first row.
Explanation:When opened, the cursor executes the query and identifies the result set, but the pointer is positioned before the first row. The first FETCH moves it to the first row.
Incorrect! Try again.
49In exception handling, what happens if an exception is raised inside an exception handler?
A.The program crashes immediately.
B.The exception is handled by the same handler recursively.
C.The exception propagates to the enclosing block (or calling environment).
D.The transaction commits automatically.
Correct Answer: The exception propagates to the enclosing block (or calling environment).
Explanation:An exception handler cannot handle exceptions raised within itself. Such exceptions are propagated outward to the enclosing block or the calling environment.
Incorrect! Try again.
50Which keyword is used to start the execution block in a PL/SQL or standard SQL procedure?
A.START
B.GO
C.BEGIN
D.RUN
Correct Answer: BEGIN
Explanation:The keyword BEGIN marks the start of the executable section of the code block, following the declaration section.
Incorrect! Try again.
Give Feedback
Help us improve by sharing your thoughts or reporting issues.