SQL and Database Transformations Quiz

The IS NULL operator specifically checks for NULL values. NULL IS NULL evaluates to TRUE in SQL. Since the WHERE condition is TRUE, the query returns 'Sql'. This is the correct way to test for NULL values - you must use IS NULL rather than = NULL.

The count(*) aggregate produces one row. HAVING 1=10 evaluates to FALSE, which eliminates this single aggregated row. When HAVING eliminates all groups, no rows are returned - not 0 or NULL, but literally no result rows. This is different from returning a count of 0.

The UNIQUE keyword is a valid Oracle SQL synonym for DISTINCT. Both SELECT UNIQUE sal and SELECT DISTINCT sal return each salary value only once, removing duplicates. The query executes successfully and returns distinct salary values from the emp table.

In Oracle, when you GROUP BY ROWNUM, it groups by the rownum values. With 100 rows, you get rownum values 1 through 100. The HAVING clause filters to show only the group where rownum equals 69, so it returns a single row with value 69. ROWNUM is a pseudocolumn assigned as rows are retrieved.

All three DELETE statements are syntactically valid in Oracle. 'DELETE FROM emp;' is the standard syntax. 'DELETE emp;' is a valid shorthand (FROM is optional). 'DELETE FROM emp WHERE 1=1;' is valid but the WHERE clause is redundant since 1=1 is always true. All three would delete all 10 rows successfully.

In Oracle, ROWNUM is assigned to rows as they are retrieved, starting from 1. A query like 'WHERE ROWNUM=8' tries to find the 8th row, but ROWNUM is assigned incrementally. By the time any row could potentially be the 8th row, ROWNUM values 1-7 haven't been satisfied, so the condition never matches. The query returns no rows.

Unconnected lookup transformations in Informatica can return only one row per lookup operation. This is a fundamental limitation - when you configure an unconnected lookup, it returns the first matching row based on the lookup condition. If multiple rows match, only one is returned. Connected lookups differ as they process data row-by-row within the data flow.

When sources come from homogeneous databases (same database type), the Source Qualifier transformation can join them at the source level using SQL. The Source Qualifier represents the data rows from a single source and can combine multiple sources from the same database connection into one SQL query. Joiner and Lookup are used when sources come from heterogeneous databases or when joining downstream in the pipeline.

The Sorter transformation is classified as an Active transformation in Informatica. Active transformations are those that can change the number of rows passing through them. The Sorter performs this by sorting data, which can potentially rearrange rows and affect data distribution. Connected vs Unconnected refers to how transformations participate in the data flow, not whether they're Active or Passive.

Lookup transformation is used to check if a record exists in the target table. It queries a cached version of the target table and returns matching rows based on the lookup condition. This is commonly used for incremental loading (checking for duplicates before insert) or slowly changing dimensions (determining if a row exists before update). Joiner combines two sources in the pipeline, and Source Qualifier operates at the source database level.

The Update Strategy transformation is the only one with a Data Driven option. This option allows you to control how rows are treated (insert, update, delete, or reject) based on expression logic that evaluates the data content. With Data Driven enabled, you can use the DD_UPDATE, DD_INSERT, DD_DELETE, or DD_REJECT functions to dynamically determine row treatment. Normalizer pivots columns, Sequence Generator creates sequential keys, and Union combines sources - none have data-driven row treatment.

Router transformation can filter based on multiple conditions simultaneously. You define multiple groups with different filter conditions, and Router routes each row to matching groups based on those conditions. This allows you to send rows to different downstream paths based on multiple criteria. Filter transformation only handles one filter condition per transformation instance. Lookup retrieves reference data, and 'All' is incorrect.

To join 4 sources, you need 3 Joiner transformations. The Joiner transformation only accepts two inputs at a time. The strategy is to join the first two sources (Joiner #1), then join that result with the third source (Joiner #2), and finally join that result with the fourth source (Joiner #3). For N sources, you need N-1 Joiner transformations arranged in a cascading fashion.

Stored Procedure transformation can be either Connected or Unconnected, and it's Passive in nature. As Connected, it participates in the data flow and receives input from the pipeline. As Unconnected, it's called from an expression and returns values. It's Passive because it doesn't change the number of rows passing through - it executes a database procedure but doesn't transform row count. Therefore 'All' is correct.

Aggregator transformation specifically benefits from sorted input. When you enable 'Sorted Input' option in Aggregator, it assumes data is already grouped by the specified keys. This improves performance because the aggregator can optimize memory usage and processing - it doesn't need to cache all rows before grouping. Normalizer pivots columns to rows (no sorting benefit), Sequence Generator creates sequential IDs (no sorting needed), and Sorter produces sorted output (it doesn't consume it).

Lookup transformation provides the SQL Override option, allowing you to customize the default query generated by Informatica. With SQL Override, you can add filters, modify the SELECT statement, or change the lookup condition SQL. This is useful for performance tuning or implementing complex lookup logic. Filter and Router use filter conditions (not SQL), and Expression uses expression logic - none have SQL Override capability.

A database view is a virtual table representing the result of a pre-defined SQL query. It does not store data physically but can be queried using standard SQL commands just like a normal table. Distractors are incorrect because views can be accessed via SQL, unlike tables which physically store data.