Database and SQL

To find employee numbers appearing more than twice, we need to: 1) Group by empno to aggregate occurrences, 2) Count occurrences with count(*), 3) Filter groups having count > 2 using HAVING clause. Option D correctly implements all three steps. Option A lacks the empno column in SELECT. Option B lacks GROUP BY. Option C incorrectly uses WHERE instead of HAVING for aggregate filtering.

In relational database terminology, a row represents a single complete record of data. Columns are called attributes or fields. The entire table represents a relation. Therefore, 'Record' is the correct term for a row in database context.

Single-value functions (also called scalar functions) operate on individual values and return a single value. SQRT() is a classic example - it takes one number and returns its square root. AVG() and COUNT() are aggregate functions that operate on multiple rows. Option D 'All the Above' is incorrect because it mixes scalar and aggregate functions.

Joining two tables without specifying a join condition in the WHERE clause (or an ON clause) performs a Cartesian product (CROSS JOIN), resulting in the multiplication of their rows (100 * 50 = 5000 rows).

SQL override in Source Qualifier transformation allows you to write custom SQL queries to filter data during extraction from the source. While it can also join tables from the same homogeneous source, joining heterogeneous sources (different database types) is typically done through joiner transformations, not SQL override alone.

Tables should first be imported or replaced in the Source Analyzer, and then copied to the Target Designer. This maintains proper metadata relationships and object hierarchy.

Transaction Control transformation is strictly an active/connected transformation - it must be connected to the data flow and cannot be used unconnected. It generates transaction control events (commit, rollback) based on conditions and changes the transaction boundary. In contrast, Lookup can be connected or unconnected, Expression is passive and connected, Stored Procedure can be connected or unconnected.

Comprehensive API validation requires checking all aspects: the return value verifies the output is correct, database updates confirm data persistence works properly, and exception handling ensures the API responds appropriately to errors. Each validation type catches different issues - return values check business logic, database updates verify data integrity, and exception testing confirms error handling.

Database unit tests in VSTT support multiple test conditions: Scalar Value checks verify specific return values from functions or procedures, Row count validates that queries return the expected number of rows, and Empty/not-empty result set conditions confirm whether a query returned any data. Each condition type serves different validation needs - scalar values test computed results, row counts verify data volume, and result set checks confirm data presence.

Third Normal Form (3NF) requires that all non-key attributes are functionally dependent only on the primary key, eliminating transitive dependencies where a non-key attribute depends on another non-key attribute. 2NF only addresses partial dependencies, while 5NF deals with join dependencies.

In SQL Server, a table without a clustered index is called a heap. Data in a heap is stored without any organized order based on key values, unlike tables with clustered indexes where rows are physically sorted. The other options (AOL, BOL, DOL) are not SQL Server storage terminology.

Domain integrity ensures that data values fall within defined valid domains for each column. Check constraints enforce domain integrity by validating column values against specific conditions (e.g., age > 0, price BETWEEN 0 AND 1000). Primary key and unique constraints enforce entity integrity (row uniqueness), while foreign key enforces referential integrity between related tables.

SQL subqueries can be nested within all major Data Manipulation Language statements. They provide powerful query capabilities in SELECT statements for filtering and projection, and can also be used in UPDATE, DELETE, and INSERT statements to determine which rows are affected or what values are inserted.

The SQL LEFT function extracts a specified number of characters starting from the left of the string. Passing 4 as the second parameter to 'Have a nice day.' returns the first four characters, which is 'Have'.