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

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ROWID access is fastest because ROWID contains the physical disk address of the row, enabling direct retrieval without traversing indexes. Primary key and unique index access require index traversal to find the ROWID first, then row access. Full table scan reads all blocks, making it slowest for single-row retrieval. ROWID is essentially a direct pointer to the row's location on disk.

Multiple choice technology databases

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 3

  3. 2

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

To join N tables without Cartesian products, you need N-1 join conditions. For 4 tables, you need 3 conditions. Each condition links two tables together. With 3 conditions for 4 tables, you create a chain: T1-T2, T2-T3, T3-T4. Fewer than 3 conditions would leave some tables disconnected, creating Cartesian products between disconnected sets. The minimum is n-1 conditions to connect n tables.

Multiple choice technology databases

CREATE PROCEDURE wise.Test123 (IN VAR1 INTEGER) P1: BEGIN DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR SELECT DISTINCT NO FROM (VALUES (1),(2),(3),(3),(2)) AS TEST (NO); SET VAR1= VAR1 * VAR1 +VAR1; END P1@ What happens when you call wise.Test123 (10)

  1. Return Status = 0

  2. 3 rows returned by result-set

  3. 5 rows returned by result-set

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

The procedure creates a cursor but never opens it, and the SET statement only modifies the input parameter. The procedure returns status 0 (success) but no result set rows because the cursor was never opened. The calculation 10*10+10=110 only affects the local parameter variable.

Multiple choice technology databases

Which of the following can be a user defined SQLSTATE

  1. 20000

  2. 35000

  3. 60000

  4. 71000
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

User-defined SQLSTATE values must be in the range 70000-99999 as per SQL standard. Option 71000 falls within this valid user-defined range, while the other options are either reserved for specific implementations or outside the valid range.

Multiple choice technology databases

A Simple stored procedure is created with Dynamic Result sets 3 However only 2 cursors with return to client are defined within the source code. What will happen when you call the SP ?

  1. Will executes successfully

  2. Fails as limit is 3 but only 2 opened

  3. Executes with warning

  4. Would not allow compiling the SP at the first place
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When DYNAMIC RESULT SETS is set to 3 but only 2 cursors are defined, the procedure executes successfully. The DYNAMIC RESULT SETS clause declares the maximum number of result sets that can be returned, and having fewer cursors than the maximum is perfectly valid.

Multiple choice technology databases

CREATE PROCEDURE p1() BEGIN CREATE TABLE T1 (C1 CHAR); INSERT INTO T1 VALUES ('A'); SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('B'); SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('C'); SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('D'); ROLLBACK TO SAVEPOINT SAVEPOINT3; ROLLBACK TO SAVEPOINT SAVEPOINT1; COMMIT WORK; INSERT INTO T1 VALUES ('E'); END@ Call p1() @ How many rows will be present in Table T1

  1. 0

  2. 1 (A)

  3. 5 (A,B,C,D,E)

  4. 2 (A,E)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The procedure inserts 'A', sets SAVEPOINT1, then inserts 'B', 'C', and 'D' with subsequent savepoints. Rolling back to SAVEPOINT1 removes 'B', 'C', and 'D', leaving only 'A'. After the COMMIT, 'E' is inserted, resulting in two rows: 'A' and 'E'.

Multiple choice technology databases

Which of the following SQL functions can operate on any datatype?

  1. TO_CHAR

  2. TO_DATE

  3. MAX

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

MAX is an aggregate function that works on any datatype - it finds the maximum value in a set regardless of whether the values are numbers, strings, or dates. TO_CHAR and TO_DATE are type conversion functions that require specific input types, while LPAD is a string manipulation function.

Multiple choice technology databases

On which data type indexes can not be created.

  1. NUMBER

  2. LONG

  3. VARCHAR2

  4. RAW
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Oracle, LONG is a legacy datatype with restrictions - you cannot create indexes on LONG columns. NUMBER, VARCHAR2, and RAW all support indexing. LONG has been deprecated in favor of CLOB. This is a well-known limitation of the LONG datatype.

Multiple choice technology web technology

What are the requirements of an effective dated table

  1. Define EFFDT

  2. Define EFF_STATUS

  3. Define EFFDT as key, in ascending order

  4. Define EFFDT as key, in descending order
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Effective dated tables require EFFDT as a key field in DESCENDING order. This ensures the most recent effective date (highest date value) appears first when querying, which is critical for time-based data retrieval. EFF_STATUS is often used but not strictly required. The key requirement is EFFDT in descending order as the first key field.