Database and SQL

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.

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.

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.

In COBOL, level 66 is specifically reserved for the RENAMES clause, which regroups previously defined data items. Level 77 is used for independent elementary items, level 88 is for condition names, and level 90 is not a standard level number.

COBOL uses level numbers to define data structure, and level 66 is specifically designated for the RENAMES clause. This clause allows you to group existing data items under an alternative name, providing a way to treat a subset of data as a single logical unit without redefining the underlying data structure.

MAX is a valid aggregate function that returns the maximum value in a group. Group By is not a function but a clause that defines how data is grouped. Is Null is a conditional function, not an aggregate function. Other aggregate functions include SUM, COUNT, AVG, MIN, etc.

RCP in Datastage stands for Runtime Column Propagation. This feature automatically propagates column metadata at runtime without needing to define all columns in schema. The other options (Random, Remove, Real-time) are incorrect.

ON UPDATE CASCADE is a referential integrity constraint that automatically updates dependent rows when a referenced primary key is updated. This ensures data consistency across related tables, preventing orphaned records and maintaining the integrity of relationships. Options A and C describe different database concepts (normalization and materialized views).

When a NULL value appears in a NOT IN subquery or list, the entire condition evaluates to UNKNOWN for all rows because comparing any value to NULL (except with IS NULL) returns UNKNOWN, which falsifies the AND condition. This causes the query to return zero rows, not any rows as the statement suggests.