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

Query to view installed Oracle version information ?
  1. 1. select banner from v$version;
  2. 2. select version from v$instance;
  3. 3. select version from v$version;

  4. Both 1 and 2

  5. Both 2 and 3
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To view Oracle version: (1) SELECT banner FROM v$version; displays version banners for all components. (2) SELECT version FROM v$instance; shows instance version. Option 3 is invalid - v$version doesn't have a 'version' column (it has 'banner'). So 'Both 1 and 2' is correct, not 'Both 2 and 3'.

Multiple choice technology databases

Which procedure can be used to create a customized error message?

  1. RAISE_ERROR

  2. SQLERRM

  3. RAISE_APPLICATION_ERROR

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

To solve this question, the user needs to have knowledge of error handling in SQL.

Now, let's go through each option and explain why it is right or wrong:

A. RAISE_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_ERROR is not a standard Oracle SQL function.

B. SQLERRM: This option is not a procedure to create a custom error message, but it is a function that returns the error message associated with the most recently raised error exception. It is not used to raise custom exceptions.

C. RAISE_APPLICATION_ERROR: This option is correct. RAISE_APPLICATION_ERROR is a procedure used to create a customized error message in Oracle SQL. It allows an application to raise an error with a user-defined error message and error number.

D. RAISE_SERVER_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_SERVER_ERROR is not a standard Oracle SQL function.

Therefore, the correct answer is: C. RAISE_APPLICATION_ERROR

Multiple choice technology databases

What happens when rows are found using a FETCH statement ?

  1. It causes the cursor to close

  2. It loads the current row values into variables

  3. It causes the cursor to open

  4. It creates the variables to load the values
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When rows are found using a FETCH statement in SQL, it loads the current row values into variables.

Option B is the correct answer. The FETCH statement is used to retrieve rows from a cursor, and each time it is executed, it fetches the next row from the cursor result set and assigns the values to the specified variables. This allows us to work with the data retrieved from the cursor.

Here is a breakdown of the other options:

A. It causes the cursor to close: This option is incorrect. The FETCH statement does not close the cursor; it only retrieves the rows. The cursor can be closed explicitly using the CLOSE statement.

C. It causes the cursor to open: This option is incorrect. The FETCH statement is used after the cursor has been declared and opened. It does not open the cursor itself.

D. It creates the variables to load the values: This option is incorrect. The variables to hold the row values need to be declared before the FETCH statement is executed. The FETCH statement does not create the variables, but rather assigns the retrieved values to the existing variables.

Therefore, the correct answer is B. It loads the current row values into variables.

Multiple choice technology databases

Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS v_salary NUMBER; v_raise NUMBER(8,2); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; v_raise := p_raise_amt * v_salary; RETURN v_raise; END; Which statement is true?

  1. a) This statement creates a stored procedure named get_sal.

  2. b) This statement returns a raise amount based on an employee id.

  3. c) This statement creates a stored function named get_sal with a status of invalid.

  4. d) This statement creates a stored function named get_sal.

  5. e) This statement fails.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The function declaration fails to compile because the variable v_email_home (or v_raise in the body) is mismatched or contains syntax errors, and the SQL query contains invalid syntax like [email protected] . instead of standard string literals.

Multiple choice technology databases

When using a packaged function in a query, what is true?

  1. a) The COMMIT and ROLLBACK commands are allowed in the packaged function.

  2. b) You can not use packaged functions in a query statement.

  3. c) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

  4. d) The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.

  5. e) The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A packaged function called from a SQL query cannot execute DML statements (INSERT, UPDATE, DELETE) against the table being queried, as this violates read-consistency rules and causes a runtime error.

Multiple choice technology mainframe

SQLCABC contains value of SQLCA.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQLCABC (SQL Communication Area Byte Count) contains the length of the SQLCA structure. It exists to verify SQLCA size compatibility between different program and DB2 versions, making the statement essentially correct.

Multiple choice technology mainframe

88 level used for

  1. condition names

  2. RENAMES clause

  3. All the above

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

In COBOL, level 88 is used exclusively for condition names - boolean flags that can be set true/false based on specific data conditions. Level 88 entries define conditional status flags associated with their parent elementary item, used for program logic flow control, not for RENAMES or regular data storage.

Multiple choice technology mainframe

SEARCH command used for

  1. binary search & the table must be sorted

  2. serial search

  3. binary search

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

The SEARCH verb in COBOL performs a serial (sequential) search by default, examining table elements one-by-one until a match is found. The table need not be sorted. For binary search on sorted tables, COBOL provides SEARCH ALL, not SEARCH. The question specifically asks about SEARCH command without ALL.