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 technology databases

In your database server, the parameter PLSQL_OPTIMIZE_LEVEL has been set to 2. What would this setting achieve?

  1. It degrades the run time and compiler performance

  2. It provides better run time and compiler performance

  3. It provides better optimization of the statement during parse time.

  4. It forces the rule based optimizer to be used for statement optimization.

  5. It provides better run time performance, but slightly degraded compiler performance.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

PLSQL_OPTIMIZE_LEVEL=2 enables aggressive optimization that significantly improves runtime performance by applying more compilation time for optimization. This includes transformations like subprogram inlining and better code generation. Level 1 is for minimal optimization (faster compile), level 0 disables optimization. It does not force rule-based optimizer.

Multiple choice technology programming languages

What is SQL code -818?

  1. Authentication failure

  2. SELECT statement has resulted in retrieval of more than one row

  3. Generated when the consistency tokens in the DBRM and the load module are different

  4. Resource Unavailable
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To understand this question, the user needs to know what SQL is and its error codes. SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. SQL error codes are used to indicate any errors or problems that occur during SQL processing.

Option A: Authentication failure. This error occurs when a user provides incorrect login credentials while trying to access the database. However, SQL code -818 is not related to authentication failure.

Option B: SELECT statement has resulted in retrieval of more than one row. This error occurs when a SELECT statement is expected to return only one row, but it returns more than one row. However, SQL code -818 is not related to this error.

Option C: Generated when the consistency tokens in the DBRM and the load module are different. This error occurs when there is a mismatch between the consistency tokens in the DBRM (Database Request Module) and the load module. Consistency tokens are used to ensure that the DBRM and the load module are in sync. SQL code -818 is related to this error.

Option D: Resource Unavailable. This error occurs when the required resources are not available in the system. However, SQL code -818 is not related to this error.

Therefore, the correct answer is:

The Answer is: C. Generated when the consistency tokens in the DBRM and the load module are different.

Multiple choice technology databases

In a PL/SQL block structure, which parts are optional?

  1. DELCARE and BEGIN

  2. DECALRE and EXCEPTION

  3. EXCEPTION and END

  4. BEGIN and END
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In a PL/SQL block, only the BEGIN and END sections are mandatory. The DECLARE section (where variables, cursors, and types are defined) is optional if you don't need to declare anything. The EXCEPTION section (error handling) is also optional if you don't need specific exception handlers. A minimal block can be just BEGIN-END.

Multiple choice technology databases

You want to create a PL/SQL block of code that calculates discounts on customer orders. This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?

  1. A stored procedure on the server.

  2. A block of code in a PL/SQL library.

  3. A standalone procedure on the client machine.

  4. A block of code in the body of the program unit ORDERTOTAL.

  5. A local subprogram defined within the program unit ORDERTOTAL.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Since the discount calculation code will only be used within the program unit ORDERTOTAL and invoked from multiple places within that same unit, a local subprogram defined within ORDERTOTAL is the most appropriate choice. This encapsulates the logic, maintains scope within ORDERTOTAL, and avoids unnecessary global scope. A stored procedure (A) would be unnecessarily global, a PL/SQL library (B) is for broader reuse, and a standalone procedure on the client (C) is not a standard PL/SQL construct. Simply placing code inline (D) doesn't support the stated requirement of invocation from several places.

Multiple choice technology databases

Implicit cursors are declared for

  1. Some DML,PL/SQL statements

  2. All DML,PL/SQL statements

  3. Only PL/SQL statements

  4. None of the Above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle PL/SQL implicitly creates cursors for all DML statements (INSERT, UPDATE, DELETE, MERGE) and SELECT statements (which are PL/SQL statements). These implicit cursors handle the SQL processing automatically - you declare variables to receive the data, but the cursor itself is created implicitly. Option A is incorrect because it's not just 'some' but all statements. Option C is incorrect because implicit cursors are also created for DML, not just PL/SQL. Option D is incorrect because implicit cursors are indeed declared.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID EXCEPTION WHEN STATS_EXIST_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table); END; What prevents this procedure from being created successfully?

  1. A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. The STATS_EXIST_EXCEPTION has not been declared as a number.

  3. The STATS_EXIST_EXCEPTION has not been declared as an exception.

  4. None
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The procedure references a user-defined exception called STATS_EXIST_EXCEPTION in the EXCEPTION block, but this exception is never declared in the declaration section. In PL/SQL, user-defined exceptions must be explicitly declared before they can be used. This missing declaration is what prevents the procedure from being created successfully. Option A is incorrect because commas after exception names are not required syntax. Option B is incorrect because exceptions are not declared as numbers - they're declared as EXCEPTION type. Option D is incorrect because there is a clear error preventing creation.

Multiple choice technology databases

Under which two circumstances do you design database triggers? (Choose two)

  1. To duplicate the functionality of other triggers.

  2. To replicate built-in constraints in the Oracle server such as primary key and foreign key.

  3. To guarantee that when a specific operation is performed, related actions are performed.

  4. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

Database triggers should be designed to guarantee that when a specific operation is performed, related actions are executed, and for centralized, global operations that must fire regardless of the user or application. Replicating built-in constraints or duplicating other triggers are bad design practices.

Multiple choice technology databases

What does “REPLACE” indicate in creating procedures?

  1. An existing will be dropped and it will be replaced by the new version.

  2. An existing will be dropped and it will not be replaced by the new version.

  3. No change will occur

  4. There is no such option available in creating procedures.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The REPLACE keyword in CREATE OR REPLACE PROCEDURE performs two actions: if a procedure with the same name already exists, it drops the existing procedure first, then creates the new version. This allows you to update procedure definitions without manually dropping them first. Option A correctly describes this behavior. Option B is incorrect because REPLACE does replace with the new version, not leave it missing. Option C is incorrect because a change does occur - the old version is dropped. Option D is incorrect because REPLACE is a valid and commonly used option.

Multiple choice technology databases

This statement fails when executed: CREATE OR REPLACE TRI GGER CALC_TEAM_AVG AFTER I NSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT ( PLAYER_I D, SEASON_YEAR, AT_BATS, HI TS) VALUES ( : NEW. I D, 1 997, 0, 0) ; END; To which type must you convert the trigger to correct the error?

  1. Row

  2. Statement

  3. ORACLE FORM trigger

  4. Before
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The trigger fails because it references :NEW.ID, which is only available in row-level triggers. A statement-level trigger (default) cannot access :NEW or :OLD values since it fires once per statement, not once per affected row. Converting to a row-level trigger (FOR EACH ROW) allows access to :NEW values for each inserted row.

Multiple choice technology databases

What is true about stored procedures?

  1. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.

  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.

  3. A stored procedure must have at least one executable statement in the procedure body.

  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A stored procedure must have at least one executable statement in its body - it cannot be empty (just NULL is executable). Option A is wrong because it's DECLARE (not DELCLARE) and DECLARE is used in the body for local variables, not formal parameters. Option B is wrong because procedures don't require parameters - parameterless procedures are valid. Option D is wrong because DECLARE is for local variables in the body, not formal parameters in the specification.

Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

  1. EXECUTE UPD_BAT_STAT;

  2. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  3. EXECUTE UPD_BAT_STAT(31, 'FOUR', 'TWO');

  4. UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  5. RUN UPD_BAT_STAT;
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

The procedure has two parameters with DEFAULT values (V_ID=10, V_AB=4), so it can be called with no arguments - option A works. Option B uses named notation correctly and overrides defaults. Option C is wrong because it passes 3 arguments but only 2 exist, and 'FOUR'/'TWO' are strings where numbers expected. Option D is wrong because EXECUTE keyword is required in SQL*Plus. Option E is wrong because RUN is a SQL*Plus command for scripts, not procedures.

Multiple choice technology databases

Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS v_email_name VARCHAR2(19); BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || [email protected] .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?

  1. g_comm has a value of 15 at 9:06am for Smith.

  2. g_comm has a value of 15 at 9:06am for Jones.

  3. g_comm has a value of 20 at 9:06am for both Jones and Smith.

  4. g_comm has a value of 15 at 9:03 am for both Jones and Smith.

  5. g_comm has a value of 10 at 9:06am for both Jones and Smith.

  6. g_comm has a value of 10 at 9:03am for both Jones and Smith.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Package variables are instantiated per-session. Jones's change to g_comm affects only Jones's session, so Jones sees 15, while Smith's session remains independent.