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

The following methods can be used for handling slowly changing dimensions

  1. Inserting a new field in the table

  2. Inserting a new record in the table

  3. Overwrite the existing data

  4. all of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Slowly Changing Dimensions (SCD) have three standard handling approaches: Type 1 overwrites existing data; Type 2 inserts new records to preserve history; Type 3 adds new fields. 'All of the above' correctly covers all three approaches plus variations. Options A, B, and C represent individual valid techniques.

Multiple choice technology packaged enterprise solutions

Which of the following statements is true?

  1. Form applet data may come from many tables and columns

  2. Form applet data comes from only one table

  3. Form applet data comes from only one column in a table

  4. Form applet data may only come from many columns in a single table
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Form applets can aggregate data from multiple tables and columns through the business component layer, unlike list applets which typically show single-table data. This flexibility enables complex data presentation.

Multiple choice technology

You are optimizing a Mapping. Which of the following statements about expressions is true?

  1. String operations are faster than numeric operations

  2. You should trim Char and Varchar fields before performing comparisons

  3. The CONCAT function is faster than the || operator

  4. Using a variable is less efficient than using the equivalent code in several expressions
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In PowerCenter's expression engine, string operations are generally faster than numeric operations due to internal processing architecture. Trimming before comparisons is unnecessary (PowerCenter handles this), || is faster than CONCAT(), and variables improve efficiency by avoiding repeated calculations.

Multiple choice technology

How do you establish a primary key-foreign key relationship between two source definitions in PowerCenter when that relationship does not currently exist between the tables in the database?

  1. Use the Source Analyzer to create the primary key-foreign key relationship and save the repository.

  2. Use the Source Analyzer to create the primary key-foreign key relationship and run the appropriate SQL statement on the database tables.

  3. Use the Source Analyzer to create the primary key-foreign key relationship and save the repository. Open the SQL Editor in all Source Qualifier transformations that join data from these two source definitions and regenerate the join statement

  4. Use the Source Analyzer to create the primary key-foreign key relationship and save the repository. Open the SQL Editor in all Source Qualifier transformations that join data from these two source definitions, generate the join statement, and modify the j
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

You can create logical primary key-foreign key relationships in the repository metadata using Source Analyzer. These relationships exist only in PowerCenter's metadata and don't require database changes. They help with join optimization and data lineage tracking.

Multiple choice technology

A database field requires the hour that an order is shipped. The date is stored in a 24-hour clock format. Which of the following example will return the value 22 for an order shipped on June 4, 2007 at 10:14PM

  1. GET_DATE_PART( DATE_SHIPPED, 'HH' )

  2. GET_DATE_PART( DATE_SHIPPED, 'HH12' )

  3. GET_DATE_PART( DATE_SHIPPED, 'HH24' )

  4. Any of these would produce the desired output
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

For a 24-hour clock format, HH returns hours in 12-hour format (1-12), HH12 returns 12-hour format with AM/PM indicator, and HH24 returns 24-hour format (0-23). For 10:14 PM stored in 24-hour format (22:14), any of these would return 22 when the hour is 10 PM, because the stored value is already 22.

Multiple choice technology databases

You can commit inside a trigger

  1. Yes, there is a workaround, but not directly

  2. yes directly

  3. no

  4. I dont Know
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

You cannot execute a direct COMMIT statement inside a database trigger. However, you can commit changes by using a workaround like declaring the trigger as an autonomous transaction via PRAGMA AUTONOMOUS_TRANSACTION.

Multiple choice technology databases

Which is performance friendly

  1. VARRAY

  2. PL/SQL TABLE

  3. NESTED TABLE

  4. NONE OF THESE
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

VARRAY (Varying Array) is the most performance-friendly collection type in Oracle because it's stored inline with the table data, has a fixed maximum size, and uses contiguous memory allocation. PL/SQL tables (associative arrays) and nested tables have more memory overhead.

Multiple choice technology databases

Examine this database trigger CREATE OR REPLACE TRIGGER prevent_gross_modification {additional trigger information} BEGIN IF TO_CHAR(sysdate, DY) = MON THEN RAISE_APPLICATION_ERROR(-19999,'user Defined error'); END IF; END; This trigger will error out

  1. True

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

The trigger code has a syntax error: TO_CHAR(sysdate, DY) should be TO_CHAR(sysdate, 'DY') with quotes around the format mask. Additionally, the comparison should be TO_CHAR(sysdate, 'DY') = 'MON' with quotes around the day value. As written, this will cause a compilation error, so the statement that it 'will error out' is correct.

Multiple choice technology databases

In Teradata, indexes are maintained by the Senior Database Administrator

  1. True

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

In Teradata, indexes are automatically maintained by the system itself, not by database administrators. When data is inserted, updated, or deleted, Teradata automatically updates all affected indexes (both primary and secondary) as part of the transaction management. No manual intervention is required.

Multiple choice technology databases

Primary Index is mandatory for teradata table

  1. True

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

Teradata does NOT require a Primary Index for a table. Tables can be created without a Primary Index (NOPI tables). This is useful for certain scenarios like staging tables or when distribution control isn't needed. Option B correctly states that Primary Index is not mandatory.

Multiple choice technology databases

Primary index value can be changed

  1. At a cost of row redistribution

  2. At a cost of table recreation

  3. When the table is empty

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

Changing a Primary Index value in an existing row causes the row to be redistributed to a different AMP because the Primary Index determines data distribution. This requires physically moving the row, which has a performance cost. Teradata will delete the row from the original AMP and re-insert it at the new AMP. Option A correctly describes this cost.

Multiple choice technology databases

Reading the rows using Non-Unique Secondary index is

  1. One or Two AMP operation

  2. All AMP operation

  3. Many AMP operation

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

Non-Unique Secondary Index (NUSI) allows duplicate values, so a single NUSI value can point to rows on multiple AMPs. When querying via NUSI, Teradata must check all AMPs that might contain matching rows, making it an All-AMP operation. Unlike USI (which typically hits 1-2 AMPs), NUSI requires broader scanning. Option B correctly identifies this as an All-AMP operation.