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

What is the necessary condition for the Automatic Transaction Control in Oracle PL/SQL?

  1. The DML commands will be executed automatically by using this control.

  2. SET AUTOCOMMIT; is used to create AUTOCOMMIT environment variable.

  3. To split a long transaction into smaller units by setting some check points.

  4. It is used to manage the changes in the database.

  5. It is used to make the transaction safe.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To execute a COMMIT automatically an INSERT, UPDATE or DELETE command is executed,

Multiple choice

Which of the following scenarios is not possible for User-defined Exception in P/L SQL?

  1. This exception must be raised explicitly.

  2. DBMS_STANDARD.RAISE_APPLICATION_ERROR procedure is used to raise this exception.

  3. This exception cannot be raised automatically when a program violates database rules or exceeds system-dependent limits.

  4. This exception caused the memory problems in the database.

  5. These exceptions are created according to user's requirement.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

User defined exception does not cause any memory specific problems in the database.

Multiple choice

What is the necessary condition to execute a Standalone Procedure?

  1. By using the EXECUTE keyword, one can execute these procedures.

  2. By calling the method of the procedure from a PL/SQL block.

  3. By providing external command.

  4. By triggering the procedure.

  5. By synchronising the procedure.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This is a correct statement. EXECUTE is a basic command for that.

Multiple choice

Which of the following is incorrect about Stored Procedure in P/L SQL?

  1. It is a procedure to manage one or more tasks at a time.

  2. EXECUTE [or EXEC] procedure_name; can be used to execute the procedure.

  3. procedure_name; can be used to execute the procedure.

  4. One can pass parameters to procedures in three ways.

  5. The header consists of the name of the procedure and the parameters declaration section of the procedure.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The header does not have declaration section of procedure but the body section have it.

Multiple choice

A declared cursor variable is not responsible for _______ in Oracle P/L SQL.

  1. REF CURSOR type stores the RECORD type of cursor variables.

  2. The compiler does not allow the weak type of cursor variables for queries.

  3. Strong REF CURSOR types are less error-prone than weak types but less flexible.

  4. The compiler allows only the weak types cursor variables for queries.

  5. The compiler provides the specification of all the variables.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The PL/SQL compiler does not allow to use the strongly typed cursor variables for queries that return rows that are not of the return type.

Multiple choice

Which of the following is correct about IN OUT parameter in P/L SQL?

  1. This parameter does not return an updated value each time for the subprogram.

  2. This parameter returns a value to the calling program.

  3. It can be assigned a value and its value can be read.

  4. The actual parameter corresponding to an IN OUT formal parameter can be a constant.

  5. The parameter values cannot be changed.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

This is correct and IN OUT can be updated and read for the assigned value.

Multiple choice

Which of the following assumptions that the DBA can apply during changing process, triggers in Oracle P/L SQL?

  1. In order to change a trigger, use either the SQL Developer tool Edit or the DDL statement.

  2. The triggers state cannot be changed for the list of schema object types.

  3. Triggers cannot be changed in the list of triggers.

  4. Trigger cannot be used for managing the database operations in a frequent manner.

  5. Triggers can be used for data synchronisation.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQL Developer tools are mostly used in changing triggers. DDL statements CREATE TRIGGER with the OR REPLACE clause are also used.

Multiple choice

Identify the correct statement about Triggers in Oracle P/L SQL?

  1. A system trigger cannot be defined on a schema or the database.

  2. It is fired for each event associated with the owner of the schema (the current user).

  3. It is fired before the triggering event executes.

  4. It is fired after each row that the event affects.

  5. Triggers are fired when storage of data is performed.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This is correct as the current user manages the transaction by own procedure.

Multiple choice

Which of the following is correct about SUBSCRIPT_BEYOND_COUNT exception in Oracle P/L SQL?

  1. When subscript exceeds the number of elements in a collection.

  2. When a subscript is outside the allowed range.

  3. When a subscript is null or not convertible to the key type.

  4. When the user tries to operate on an atomically null collection.

  5. When the user tries to check the authenticated value for the particular type.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This is correct about SUBSCRIPT_BEYOND_COUNT exception.

Multiple choice

Which of the following statements is correct?

  1. TRUNCATE is a DDL statement, while DELETE is a DCL statement.

  2. TRUNCATE is a DML statement, while DELETE is a DDL statement.

  3. TRUNCATE is a DML statement, while DELETE is a DCL statement.

  4. TRUNCATE is a DDL statement, while DELETE is a DML statement.

  5. TRUNCATE is a DCL statement, while DELETE is a DDL statement.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

TRUNCATE is a DDL (data definition language) statement, while DELETE is a DML (data manipulation language) statement.

Multiple choice

Which of the following is not true for a primary key in DBMS?

  1. It uniquely identifies a row in a table.

  2. Its attributes cannot be duplicated in a row.

  3. It cannot be null.

  4. It is mandatory to provide input for the primary key attributes.

  5. It can be a composite key.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Primary key is chosen by a designer to uniquely identify rows in the table. It is always not null and unique. A null value can be used to represent an unknown data.

Multiple choice

Which of the following statements is correct about data control language (DCL)?

  1. It is used to define the end of a transaction.

  2. It defines change and drops the structure of a table.

  3. It is used to modify and delete data from a table.

  4. It has SELECT as a SQL keyword.

  5. It can remove all rows from a table.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Data control line (DCL) is a type of SQL statement. It is used to provide control over the data in a database and to define the end of a transaction.