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 of the following supports the XML data type?

  1. A unique index

  2. .A composite index

  3. A check constraint

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

In SQL Server, XML data type columns can be indexed. While XML columns require special index types (PRIMARY XML index and secondary XML indexes), the question tests basic knowledge that XML data type supports indexing operations. A unique index is a valid operation on XML columns, though with specific syntax and constraints.

Multiple choice technology databases

The DEPT table has the following data

  1. The INSERT statement is invalid; a valid table name is missing.

  2. 50 is not a valid DEPTNO value, since the subquery limits DEPTNO to 10.

  3. The statement will work without error.

  4. A subquery and a VALUES clause cannot appear together.
Reveal answer Fill a bubble to check yourself
B Correct answer
Multiple choice technology databases

The following statements are executed: create sequence my_seq; select my_seq.nextval from dual; select my_seq.nextval from dual; rollback; select my_seq.nextval from dual;

  1. 0

  2. 1

  3. 2

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

Sequences in Oracle are outside transaction control. After creating a sequence, first nextval returns 1, second returns 2. ROLLBACK does not affect sequence values, so the third nextval call returns 3, not 1.

Multiple choice technology

Method to compare data in two tables

  1. Lookup

  2. Minus

  3. Either a or b is sufficient

  4. Both a and b are needed
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Both Lookup transformation and SQL MINUS operator can effectively compare data between two tables. Lookup joins sources for row-by-row comparison, while MINUS finds set differences. Either approach is sufficient for comparison tasks.

Multiple choice technology

If we need to create multiple occurances of a specific row we use the

  1. Normaliser

  2. Joiner

  3. Either a or b

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

The Normalizer transformation in Informatica is specifically designed to create multiple occurrences from a single input row. It denormalizes data by expanding columns into multiple rows, which is exactly what the question describes. Joiner transformations are for combining data from different sources, not for creating multiple occurrences.

Multiple choice technology

In a joiner transformation the following helps in performance

  1. Sorted input option

  2. Using the table with more rows as master

  3. Both a and b

  4. Either a or b
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The 'Sorted input' option in a Joiner transformation significantly improves performance because it allows Informatica to use a more efficient merge join operation instead of a nested loop join. The smaller table should be used as master, so option B is incorrect.

Multiple choice technology programming languages

Which of the following statements selects from a data set only those observations for which the value of the variable Style is RANCH, SPLIT, or TWOSTORY?

  1. a.where style='RANCH' or 'SPLIT' or 'TWOSTORY';

  2. a.where style in 'RANCH' or 'SPLIT' or 'TWOSTORY';

  3. a.where style in (RANCH, SPLIT, TWOSTORY);

  4. a.where style in ('RANCH','SPLIT','TWOSTORY');
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SAS, the IN operator compares a variable to a list of target values. The list of character values must be enclosed in parentheses, separated by commas or spaces, and each string must be quoted individually. Other options either use invalid syntax or omit necessary quotes.

Multiple choice technology mainframe

What is the table name to see IBS ordering days

  1. IBS_ORDR_ST_DTL

  2. LOB_ORDR_TYP_DMN

  3. IBS_ORDR_ST_DTL_H

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

IBS_ORDR_ST_DTL is the correct table to view IBS (Insurance Business System) ordering days. The other options represent different tables or may not be the primary table for this information.

Multiple choice technology mainframe

What is the table name to see IBS ordering days

  1. IBS_ORDR_ST_DTL

  2. LOB_ORDR_TYP_DMN

  3. IBS_ORDR_ST_DTL_H

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

IBS_ORDR_ST_DTL is the table name used to see IBS ordering days. This table stores order state details including the days information for IBS (Insurance Business System) ordering processes.

Multiple choice technology databases

COMPLETE REFRESH by default will _________________all rows in an mview before inserting new rows

  1. Create

  2. Delete

  3. Append

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

A complete refresh of a materialized view deletes all existing rows before inserting the new dataset. This differs from fast refresh (incremental) which only applies changes. The DELETE operation ensures the mview contains exactly the data from the refresh query, with no stale rows remaining.

Multiple choice technology databases

On a partitioned table which index is used mostly

  1. Local

  2. Global

  3. Both

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

Local indexes are preferred on partitioned tables because they are automatically maintained with partition operations and provide better partition pruning. Global indexes span all partitions and require more maintenance during partition operations like DROP or TRUNCATE. Local indexes align with the table's partitioning strategy.

Multiple choice technology databases

Which object in PL/SQL provides encapsulation features ?

  1. Procedure

  2. Package

  3. Functions

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

Packages provide encapsulation by bundling related procedures, functions, variables, and cursors together, with public/private specifications controlling access. Procedures and functions are individual program units, but only packages offer true encapsulation through the package specification and body structure. Packages also support session-specific state persistence.