1Which of the following statements about Stored Procedures is FALSE?
A.They are precompiled and stored in the database.
B.They can return multiple values using OUT parameters.
C.They must always return a value using the RETURN clause.
D.They reduce network traffic by executing on the server side.
Correct Answer: They must always return a value using the RETURN clause.
Explanation:Stored procedures are not required to return a value using a RETURN clause; they typically perform an action. Functions, however, must return a value.
Incorrect! Try again.
2In PL/SQL or T-SQL, which control structure is used to select one of many code blocks to execute based on a specific value?
A.WHILE loop
B.CASE statement
C.FOR loop
D.LOOP...EXIT
Correct Answer: CASE statement
Explanation:The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions or executes a specific block of code.
Incorrect! Try again.
3What is a Cursor in the context of database programming?
A.A lock held on a table during a transaction.
B.A pointer to a memory area that stores the result set of a query.
C.A type of trigger that fires before an update.
D.A function that returns a boolean value.
Correct Answer: A pointer to a memory area that stores the result set of a query.
Explanation:A cursor is a database object used to retrieve, manipulate, and traverse data row by row from a result set defined by a SELECT statement.
Incorrect! Try again.
4Which of the following states the correct order of operations for using an Explicit Cursor?
A.OPEN, DECLARE, FETCH, CLOSE
B.DECLARE, FETCH, OPEN, CLOSE
C.DECLARE, OPEN, FETCH, CLOSE
D.OPEN, FETCH, CLOSE, DEALLOCATE
Correct Answer: DECLARE, OPEN, FETCH, CLOSE
Explanation:To use an explicit cursor, you must first Declare it (define the query), Open it (execute query), Fetch rows (retrieve data), and finally Close it (release memory).
Incorrect! Try again.
5Which cursor attribute returns the number of rows fetched so far?
A.%FOUND
B.%NOTFOUND
C.%ISOPEN
D.%ROWCOUNT
Correct Answer: %ROWCOUNT
Explanation:%ROWCOUNT is a cursor attribute that returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or the number of rows fetched from a cursor.
Incorrect! Try again.
6What distinguishes a Row-Level Trigger from a Statement-Level Trigger?
A.Row-level triggers fire once per transaction, Statement-level fire once per row.
B.Row-level triggers fire for each row affected by the operation, Statement-level fire once per SQL statement.
C.Row-level triggers cannot access OLD and NEW values.
D.Statement-level triggers are used only for SELECT statements.
Correct Answer: Row-level triggers fire for each row affected by the operation, Statement-level fire once per SQL statement.
Explanation:A row-level trigger executes once for every single row modified by the triggering event, whereas a statement-level trigger fires only once regardless of how many rows are affected.
Incorrect! Try again.
7In a trigger, what do the qualifiers :OLD and :NEW refer to?
A.The previous and next transaction IDs.
B.The values of columns before and after the data modification.
C.The timestamp of the previous and current login.
D.The old and new schema definitions.
Correct Answer: The values of columns before and after the data modification.
Explanation:In row-level triggers, :OLD references the value of a column before the update/delete, and :NEW references the value that will be inserted or updated.
Incorrect! Try again.
8Which block is used to handle runtime errors in PL/SQL?
A.CATCH block
B.EXCEPTION block
C.ERROR block
D.HANDLER block
Correct Answer: EXCEPTION block
Explanation:The EXCEPTION block in PL/SQL is dedicated to trapping and handling errors (exceptions) that occur during the execution of the code.
Incorrect! Try again.
9What is the primary purpose of the RAISE statement in exception handling?
A.To increase the salary in a table.
B.To promote a transaction to a higher priority.
C.To explicitly generate a user-defined or standard exception.
D.To restart the database server.
Correct Answer: To explicitly generate a user-defined or standard exception.
Explanation:The RAISE statement is used to explicitly stop normal execution and transfer control to the exception handling block, often used for business logic validation.
Incorrect! Try again.
10Which of the following is NOT a property of a database Transaction (ACID)?
A.Atomicity
B.Concurrency
C.Isolation
D.Durability
Correct Answer: Concurrency
Explanation:ACID stands for Atomicity, Consistency, Isolation, and Durability. Concurrency is a concept of execution, not one of the specific ACID properties.
Incorrect! Try again.
11The property of Atomicity guarantees that:
A.Transactions are executed in isolation.
B.The database remains consistent before and after execution.
C.All operations of the transaction are reflected in the database, or none are.
D.Updates persist even after a system crash.
Correct Answer: All operations of the transaction are reflected in the database, or none are.
Explanation:Atomicity ensures the 'all-or-nothing' rule; a transaction is treated as a single indivisible unit of work.
Incorrect! Try again.
12Which state does a transaction enter immediately after it starts execution?
A.Active
B.Partially Committed
C.Committed
D.Failed
Correct Answer: Active
Explanation:A transaction enters the Active state immediately after it begins execution and remains there while it is executing read/write operations.
Incorrect! Try again.
13A transaction reaches the Committed state when:
A.The first instruction is executed.
B.The final statement has been executed, but not written to disk.
C.All operations are successfully completed and changes are permanently saved.
D.A hardware failure occurs.
Correct Answer: All operations are successfully completed and changes are permanently saved.
Explanation:A transaction is Committed only after it successfully completes all operations and the system ensures the changes are durable.
Incorrect! Try again.
14What is the Consistency property in ACID?
A.Transactions must happen sequentially.
B.Execution of a transaction must take the database from one consistent state to another.
C.Changes must be written to the log immediately.
D.Transactions must not be interrupted by OS interrupts.
Correct Answer: Execution of a transaction must take the database from one consistent state to another.
Explanation:Consistency ensures that a transaction brings the database from one valid state to another, maintaining all defined integrity constraints.
Incorrect! Try again.
15Consider two transactions and . If is updating a row and reads that row before commits, which isolation problem might occur?
A.Dirty Read
B.Phantom Read
C.Unrepeatable Read
D.Lost Update
Correct Answer: Dirty Read
Explanation:A Dirty Read occurs when a transaction reads data written by a concurrent uncommitted transaction. If the other transaction rolls back, the read data is invalid.
Incorrect! Try again.
16What is a Schedule in the context of transaction management?
A.A timetable for database backups.
B.A sequence of instructions that specify the chronological order in which instructions of concurrent transactions are executed.
C.The list of users allowed to access the database.
D.The priority list of processes in the CPU.
Correct Answer: A sequence of instructions that specify the chronological order in which instructions of concurrent transactions are executed.
Explanation:A schedule represents the execution sequence of operations (Read, Write, Commit, Abort) from multiple transactions.
Incorrect! Try again.
17A schedule is called Serial if:
A.Transactions are executed concurrently (interleaved).
B.Transactions are executed one after another, with no interleaving.
C.It contains a cycle in the precedence graph.
D.It results in a database inconsistency.
Correct Answer: Transactions are executed one after another, with no interleaving.
Explanation:In a serial schedule, the operations of a transaction are executed consecutively without any operations from other transactions interleaved between them.
Incorrect! Try again.
18Two operations in a schedule are said to be in Conflict if:
A.They belong to the same transaction.
B.They access different data items.
C.They belong to different transactions, access the same data item, and at least one is a write operation.
D.They are both read operations.
Correct Answer: They belong to different transactions, access the same data item, and at least one is a write operation.
Explanation:Conflicting operations dictate the order of execution. If two transactions access the same data and one writes, the order matters.
Incorrect! Try again.
19A schedule is Conflict Serializable if:
A.It is view equivalent to a serial schedule.
B.It is conflict equivalent to a serial schedule.
C.It contains blind writes.
D.It provides higher throughput than any serial schedule.
Correct Answer: It is conflict equivalent to a serial schedule.
Explanation:Conflict serializability ensures that the schedule produces the same final state as some serial schedule by checking the ordering of conflicting operations.
Incorrect! Try again.
20In a Precedence Graph (Serialization Graph), a cycle indicates that:
A.The schedule is Conflict Serializable.
B.The schedule is NOT Conflict Serializable.
C.The system is in a deadlock.
D.The schedule is recoverable.
Correct Answer: The schedule is NOT Conflict Serializable.
Explanation:If the precedence graph contains a cycle, dependencies exist that prevent the transactions from being ordered serially; hence, it is not conflict serializable.
Incorrect! Try again.
21Which of the following is a condition for View Serializability but not necessarily Conflict Serializability?
A.Handling Blind Writes.
B.Ensuring no cascading rollbacks.
C.Ensuring ACID properties.
D.Locking all data items.
Correct Answer: Handling Blind Writes.
Explanation:View Serializability is a weaker condition than Conflict Serializability. It can allow schedules with blind writes (writing without reading) to be serializable where Conflict Serializability would reject them.
Incorrect! Try again.
22What is a Cascading Rollback?
A.When the database crashes and restarts.
B.When a single transaction failure leads to the rollback of a series of dependent transactions.
C.When a transaction is rolled back due to a deadlock.
D.When the log file fills up.
Correct Answer: When a single transaction failure leads to the rollback of a series of dependent transactions.
Explanation:This occurs in non-recoverable or recoverable (but not cascadeless) schedules where Transaction A reads data written by uncommitted Transaction B. If B aborts, A must also abort.
Incorrect! Try again.
23A schedule is Recoverable if:
A.For every pair of transactions and , if reads a data item written by , then commits before commits.
B.No deadlocks occur.
C.The precedence graph is acyclic.
D.It is conflict serializable.
Correct Answer: For every pair of transactions and , if reads a data item written by , then commits before commits.
Explanation:Recoverability ensures that if a transaction reads dirty data, the transaction that created the dirty data must commit first; otherwise, durability cannot be guaranteed.
Incorrect! Try again.
24What is a Shared Lock (S-Lock)?
A.A lock that allows both reading and writing.
B.A lock that allows only writing.
C.A lock that allows read access but prevents write access by other transactions.
D.A lock that prevents both read and write access.
Correct Answer: A lock that allows read access but prevents write access by other transactions.
Explanation:Shared locks allow multiple transactions to read a resource simultaneously but prevent any transaction from modifying it until the locks are released.
Incorrect! Try again.
25In Two-Phase Locking (2PL) protocol, what happens in the Growing Phase?
A.The transaction releases locks but cannot acquire new ones.
B.The transaction acquires all necessary locks but cannot release any.
C.The transaction performs rollback operations.
D.The transaction writes to the log file.
Correct Answer: The transaction acquires all necessary locks but cannot release any.
Explanation:2PL has two phases: Growing (acquiring locks) and Shrinking (releasing locks). In Growing, a transaction cannot release a lock once it starts acquiring them.
Incorrect! Try again.
26The primary disadvantage of basic Two-Phase Locking (2PL) is:
A.It does not ensure serializability.
B.It can lead to Deadlocks.
C.It allows Dirty Reads.
D.It consumes too much memory.
Correct Answer: It can lead to Deadlocks.
Explanation:While 2PL ensures conflict serializability, it is prone to deadlocks because transactions may hold locks and wait for others cyclically.
Incorrect! Try again.
27Which concurrency control protocol uses system time or a logical counter to order transactions?
A.Two-Phase Locking
B.Timestamp Ordering Protocol
C.Graph-Based Protocol
D.Validation-Based Protocol
Correct Answer: Timestamp Ordering Protocol
Explanation:Timestamp Ordering assigns a unique timestamp to each transaction and ensures that conflicting operations are executed in timestamp order.
Incorrect! Try again.
28In the Wait-Die scheme for deadlock prevention (based on timestamps):
A.If an older transaction requests a resource held by a younger one, it waits. If younger requests from older, it dies.
B.If an older transaction requests a resource held by a younger one, the younger one is killed.
C.All transactions wait indefinitely.
D.Deadlocks are detected using a Wait-For Graph.
Correct Answer: If an older transaction requests a resource held by a younger one, it waits. If younger requests from older, it dies.
Explanation:Wait-Die is a non-preemptive strategy. Older transactions wait for younger ones (Wait), but younger transactions requesting resources from older ones are aborted (Die).
Incorrect! Try again.
29What is the Wound-Wait scheme?
A.A preemptive deadlock prevention scheme where older transactions force younger ones to abort (wound).
B.A scheme where all transactions wait for locks.
C.A scheme where younger transactions wait for older ones, and older ones die.
D.A method to handle log recovery.
Correct Answer: A preemptive deadlock prevention scheme where older transactions force younger ones to abort (wound).
Explanation:In Wound-Wait, if an older transaction requests a lock held by a younger one, the younger one is aborted (Wounded). If a younger requests from older, it Waits.
Incorrect! Try again.
30The Phantom Read problem occurs when:
A.A transaction reads a value that is later updated by another transaction.
B.A transaction reads a row that has been deleted by another transaction.
C.A transaction executes a query returning a set of rows, and a concurrent transaction inserts a new row that matches the query criteria.
D.A transaction updates a row that was never read.
Correct Answer: A transaction executes a query returning a set of rows, and a concurrent transaction inserts a new row that matches the query criteria.
Explanation:Phantom reads happen when the 'collection' of rows satisfying a condition changes (via insert/delete) between two reads in the same transaction.
Incorrect! Try again.
31Which of the following is true about Strict Two-Phase Locking (Strict 2PL)?
A.It releases shared locks in the shrinking phase but holds exclusive locks until commit.
B.It holds all locks (shared and exclusive) until the transaction commits.
C.It does not guarantee recoverability.
D.It allows cascading rollbacks.
Correct Answer: It releases shared locks in the shrinking phase but holds exclusive locks until commit.
Explanation:Strict 2PL holds all Exclusive (Write) locks until the transaction commits/aborts to prevent cascading rollbacks. Rigorous 2PL holds all locks until commit.
Incorrect! Try again.
32The Lost Update problem occurs when:
A.Two transactions read the same data and update it based on the read value, overwriting each other's changes.
B.A system crash deletes the log file.
C.An update is made to a temporary table.
D.A transaction reads uncommitted data.
Correct Answer: Two transactions read the same data and update it based on the read value, overwriting each other's changes.
Explanation:If and read , then writes , and then writes , the update by is lost.
Incorrect! Try again.
33What is the Thomas Write Rule used for?
A.To prevent deadlocks in 2PL.
B.To modify Timestamp Ordering to allow ignoring obsolete write operations.
C.To enforce strict serializability.
D.To manage cursor memory allocation.
Correct Answer: To modify Timestamp Ordering to allow ignoring obsolete write operations.
Explanation:The Thomas Write Rule optimizes Timestamp Ordering. If a transaction attempts to write a value but finds a newer timestamp already on the data, the write is ignored (viewed as obsolete) rather than aborted.
Incorrect! Try again.
34A Casacadeless Schedule ensures that:
A.No transaction reads data written by an uncommitted transaction.
B.Transactions never abort.
C.Throughput is maximized.
D.Deadlocks are impossible.
Correct Answer: No transaction reads data written by an uncommitted transaction.
Explanation:Cascadelessness prevents cascading rollbacks by ensuring that if reads written by , must have committed before the read.
Incorrect! Try again.
35Which parameter mode in a stored procedure allows a value to be passed in and an updated value to be returned?
A.IN
B.OUT
C.IN OUT
D.RETURN
Correct Answer: IN OUT
Explanation:The IN OUT parameter mode allows the procedure to read the initial value of the variable and modify it, returning the new value to the caller.
Incorrect! Try again.
36In the context of database recovery, what is WAL?
A.Write-After-Log
B.Write-Ahead Logging
C.Wait-And-Lock
D.Write-Access-Level
Correct Answer: Write-Ahead Logging
Explanation:Write-Ahead Logging ensures that log records representing changes are written to stable storage before the actual data pages are updated on the disk.
Incorrect! Try again.
37Which PL/SQL loop structure is guaranteed to execute at least once?
A.WHILE loop
B.FOR loop
C.REPEAT...UNTIL (or basic LOOP with exit at bottom)
D.None of the above
Correct Answer: REPEAT...UNTIL (or basic LOOP with exit at bottom)
Explanation:A loop with the exit condition checked at the end (like REPEAT UNTIL or a generic LOOP with EXIT WHEN at the bottom) ensures the body runs at least once. WHILE and FOR check conditions at the start.
Incorrect! Try again.
38What is the function of the INSTEAD OF trigger?
A.It runs after the standard action.
B.It is used primarily on Views to handle updates/inserts that are not inherently supported.
C.It is used to replace the database engine.
D.It runs before the transaction starts.
Correct Answer: It is used primarily on Views to handle updates/inserts that are not inherently supported.
Explanation:INSTEAD OF triggers are usually defined on views. They execute instead of the triggering statement, allowing logic to update underlying tables of a complex view.
Incorrect! Try again.
39Which of the following creates a user-defined function?
A.CREATE PROCEDURE
B.CREATE TRIGGER
C.CREATE FUNCTION
D.DECLARE FUNCTION
Correct Answer: CREATE FUNCTION
Explanation:The SQL command to define a stored function is CREATE FUNCTION.
Incorrect! Try again.
40Consider the precedence graph with nodes . If edges exist: and and , then:
A.The schedule is serializable.
B.The schedule is not conflict serializable.
C. must run first.
D.There are no conflicts.
Correct Answer: The schedule is not conflict serializable.
Explanation:The edges form a cycle (). The existence of a cycle in the precedence graph implies the schedule is not conflict serializable.
Incorrect! Try again.
41Which level of isolation offers the highest data consistency but the lowest concurrency?
A.Read Uncommitted
B.Read Committed
C.Repeatable Read
D.Serializable
Correct Answer: Serializable
Explanation:Serializable isolation simulates serial execution, offering the highest consistency (no dirty, non-repeatable, or phantom reads) but requires strict locking/validation, reducing concurrency.
Incorrect! Try again.
42The Read Committed isolation level prevents:
A.Dirty Reads
B.Non-repeatable Reads
C.Phantom Reads
D.All of the above
Correct Answer: Dirty Reads
Explanation:Read Committed ensures a transaction only reads data that has been committed, preventing Dirty Reads. However, it does not prevent Non-repeatable Reads or Phantoms.
Incorrect! Try again.
43In a cursor FOR loop (PL/SQL), which actions are performed automatically?
A.Only Fetching.
B.Only Opening and Closing.
C.Open, Fetch, and Close.
D.None, everything must be manual.
Correct Answer: Open, Fetch, and Close.
Explanation:A Cursor FOR loop implicitly opens the cursor, fetches rows one by one into the loop index, and closes the cursor when the loop terminates.
Incorrect! Try again.
44What is Granularity in the context of locking?
A.The size of the data item being locked (e.g., row, page, table).
B.The duration of the lock.
C.The number of locks a transaction can hold.
D.The strength of the encryption.
Correct Answer: The size of the data item being locked (e.g., row, page, table).
Explanation:Granularity refers to the hierarchy of data items. Fine granularity (row) increases concurrency but increases overhead; coarse granularity (table) reduces overhead but decreases concurrency.
Incorrect! Try again.
45Which logic is used to detect a deadlock in a system using locks?
A.Wait-For Graph
B.Precedence Graph
C.Dependency Graph
D.ER Diagram
Correct Answer: Wait-For Graph
Explanation:A Wait-For Graph tracks which transactions are waiting for which others. A cycle in the Wait-For Graph indicates a deadlock.
Incorrect! Try again.
46If a transaction fails, the process of undoing its actions is called:
A.Commit
B.Rollback
C.Redo
D.Savepoint
Correct Answer: Rollback
Explanation:Rollback is the operation that restores the database to the state it was in before the transaction began.
Incorrect! Try again.
47A Savepoint allows:
A.Rolling back the entire transaction only.
B.Committing the transaction partially.
C.Rolling back part of a transaction to a specific marker.
D.Saving the transaction to a separate file.
Correct Answer: Rolling back part of a transaction to a specific marker.
Explanation:Savepoints act as markers within a transaction. You can rollback to a specific savepoint without aborting the entire transaction.
Incorrect! Try again.
48Which concurrency control problem is described by: ?
A.Dirty Read
B.Unrepeatable Read
C.Overwriting Uncommitted Data
D.Blind Write
Correct Answer: Unrepeatable Read
Explanation:Actually, strictly speaking, this looks like a Lost Update or Overwrite depending on interpretation, but in standard definitions: reads A, then writes A and commits. If were to read A again, it would see a different value (Unrepeatable Read).
Incorrect! Try again.
49In PL/SQL, what does the %TYPE attribute do?
A.Defines a new data type.
B.Declares a variable with the same data type as a database column or another variable.
C.Checks the type of a variable at runtime.
D.Converts a string to a number.
Correct Answer: Declares a variable with the same data type as a database column or another variable.
Explanation:%TYPE anchors a variable's data type to an underlying table column, ensuring type compatibility if the schema changes.
Incorrect! Try again.
50Which statement regarding Implicit Cursors is true?
A.They must be explicitly declared by the user.
B.They are created automatically by the database engine for DML statements (INSERT, UPDATE, DELETE).
C.They are used for queries returning multiple rows.
D.They require an OPEN and CLOSE statement.
Correct Answer: They are created automatically by the database engine for DML statements (INSERT, UPDATE, DELETE).
Explanation:Implicit cursors are automatically generated by the system for single-row SELECTs and DML operations. The user does not open or close them.