Computer Knowledge

Database and SQL

4,213 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice

Which of the following is/are incorrect about 'raise_application_error' in PL/SQL?

  1. It defines the user-defined error message.

  2. The syntax for this procedure is raise_application_error(error_number, message).

  3. By using this procedure, all the uncommitted transactions within the PL/SQL block are rolled back automatically.

  4. RAISE_APPLICATION_ERROR raises an exception but does not handle it.

  5. Both (2) and (4)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The correct syntax is: raise_application_error(error_number, message[, {TRUE | FALSE}]); where error_number is a negative integer in the range -20000 -20999  and message is a character string up to 2048 bytes long.

Multiple choice

Which of the following statements is/are incorrect about % ROWCOUNT function in PL/SQL cursor?

  1. It returns the invalid cursor error in case of cursor is not declared.

  2. It returns the number of rows fetched.

  3. An iteration gives the count of rows for the defined cursor.

  4. It returns TRUE if a successful fetch has been executed.

  5. If no row is returned, then this function returns an error.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This is false as it does not return this value in case of successful fetched of row.

Multiple choice

What information does DBMS_UTILITY.FORMAT_ERROR_ function have in exception trapping functions?

  1. It returns the error number for the latest occurred exception in the PL/SQL block.

  2. It returns the error message associated with the later error number.

  3. It provides details about the error message.

  4. It provides details on the line containing the error.

  5. It retunes the error details about the DML statements.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

This is correct about this function.

Multiple choice

Which of the following conditions is not responsible for invalid cursor error in PL/SQL?

  1. FETCH cursor before opening the cursor.

  2. CLOSE cursor before opening the cursor.

  3. FETCH cursor after closing the cursor.

  4. It is raised when a database call is issued without being connected to the database.

  5. None of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

This condition is not responsible for invalid cursor error in PL/SQL. It is raised when attempts are made to make a cursor operation that is not allowed.

Multiple choice

Which of the following statements is incorrect regarding '%FOUND' cursor attribute in PL/SQL?

  1. Returns INVALID_CURSOR if cursor is declared, but not open or if cursor has been closed.

  2. Returns NULL if cursor is open, but fetch has not been executed.

  3. Returns TRUE if a successful fetch has been executed.

  4. Returns FALSE if no row was returned.

  5. Returns the number of rows fetched.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, it is correct as it does not return the number of rows to be fetched.

Multiple choice

Which of the following statements is/are correct about 'WRITE clause' in PL/SQL COMMIT statement?

  1. It is used to specify the priority that the redo information for the committed transaction is to be written to the redo log.

  2. It is used to force the commit of a transaction that may be corrupt or in doubt.

  3. It is used to specify a comment to be associated with current transaction.

  4. This clause is optional.

  5. Both (1) and (4)
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

These are correct statements about 'WRITE clause' in PL/SQL COMMIT statement.

Multiple choice

Which of the following statements is/are correct about 'IN OUT Parameter' in PL/SQL procedure?

  1. These types of parameters are used to send values and get values from stored procedures.

  2. This parameter is used if the value of the parameter will be changed in the procedure.

  3. This is a write-only parameter, i.e. one cannot pass values to OUT paramters while executing the stored procedure.

  4. This parameters is used to send only the OUTPUT from a procedure or a function.

  5. Both (1) and (2)
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Both are correct statements about ' IN OUT Parameter' in PL/SQL procedure.

Multiple choice

What is the significance of '[OF col_name]' clause in PL/SQL trigger?

  1. This clause is used with update triggers.

  2. It creates a trigger with the given name or overwrites an existing trigger with the same name.

  3. This clause is used to reference the old and new values of the data being changed.

  4. This clause is used to determine whether a trigger must fire when each row gets affected.

  5. This clause is valid only for row level triggers.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This clause is used when one wants to trigger an event only when a specific column is updated.

Multiple choice

What is 'ISOLATION LEVEL READ COMMITTED' parameter in SET TRANSACTION statement in PL/SQL?

  1. If a transaction attempts to update a resource that has been updated by another transaction and uncommitted, the transaction will fail.

  2. It is used to release the lock for the multiple transactions allocated for the same resource.

  3. It assigns a name to the transaction identified by 'transaction_name'.

  4. It assigns a name to the transaction identified by 'transaction_name'.

  5. None of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This is called 'ISOLATION LEVEL READ COMMITTED ' clause and if a transaction requires row locks held by another transaction, the transaction will wait until the row locks are released.

Multiple choice

ANT means

  1. active name table

  2. active normal table

  3. action name table

  4. none of these
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

ANT stands for Active Name Table, which stores information about the names of files currently active in the system.