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 two types of query results cannot be stored in the query result cache? (Choose two.)

  1. subquery results

  2. results of a query having the SYSDATE function

  3. results of a query having the GROUP BY clause

  4. results of a query having the DATE data type in the WHERE clause
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Query result cache cannot store results from queries with non-deterministic functions like SYSDATE (which changes each execution). Subquery results are also excluded as they represent intermediate query constructs rather than final result sets.

Multiple choice technology databases

Which script creates the CHAINED_ROWS table

  1. catproc.sql

  2. utlchain.sql

  3. catchain.sql

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

In Oracle databases, the utlchain.sql script creates the CHAINED_ROWS table, which stores information about chained and migrated rows. This table is used with the ANALYZE command to identify rows that are chained or migrated across multiple blocks, helping optimize database performance.

Multiple choice technology platforms and products

There is a class group A-B-C and a class A-B-C-D belongs to it Class group A-B-C instance will be in database table pc_work How about class A-B-C-D’s instance?

  1. pr4_base

  2. pc_work

  3. pc_other

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

In Pega, work classes within a class group typically map to the class group table, which defaults to pc_work. However, unless explicitly mapped otherwise, standard behavior for concrete classes not designated as part of a work pool is to fall back to the default pr4_base table.

Multiple choice technology

Suppose you have imported table from database as a source and you have used that source in multiple mappings. If there is any change in the source table structure in the database then how will it be reflected in the mapping?

  1. the changes in the source table in the database will not be reflected in the source that has been imported

  2. N/A

  3. do the changes in the source table , close the session and then re-import the data

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

When you import a source table in Informatica, it creates a static definition of that table structure at the time of import. Changes made later in the actual database are not automatically reflected in the imported source definition or mappings that use it. You must manually re-import or synchronize the source definition to pick up database changes.

Multiple choice technology

can we filter records with look up transformation?

  1. Yes

  2. no

  3. look up is not for filtering so it throws error

  4. when performing look up we need to write a stored procedure
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Lookup transformations can effectively filter records by using the lookup condition - records that match the condition are returned, while non-matching records can be dropped or processed differently. This is a common filtering technique in mappings.

Multiple choice technology databases

The __________ attribute is used to declare variables based on definitions of columns in table.

  1. %ROWTYPE

  2. %TYPE

  3. AS_COLUMN

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

The %TYPE attribute in PL/SQL declares a variable with the same data type as a specified column in a table. %ROWTYPE declares a record type matching an entire row. AS_COLUMN doesn't exist in PL/SQL. %TYPE is useful for maintaining type compatibility without hardcoding specific types.

Multiple choice technology databases

SQL has facility for programmed handling of errors that arise during the manipulation of data.

  1. True

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

The statement claims SQL has NO error handling facility, which is False. SQL DOES have programmed error handling: PL/SQL uses EXCEPTION blocks, T-SQL uses TRY/CATCH, MySQL uses DECLARE handlers. Modern SQL implementations support robust error handling mechanisms for data manipulation errors.

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 BEGIN and END are mandatory. The DECLARE section (for declarations) and EXCEPTION section (for error handling) are both optional components that can be omitted if not needed.

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

A local subprogram within ORDERTOTAL is ideal because it's scoped only to that unit (avoiding global scope pollution) while being reusable from multiple places within ORDERTOTAL. Stored procedures or standalone code would be overly broad for this requirement.