Oracle PL/SQL and Database Concepts

Skewness measures data distribution unevenness. Grade column with 1 and 999 rows is extremely skewed - 99.9% of rows have one value. Deptno (200 each) and Location (300,400,300) have much more even distributions. Higher skewness means more uneven distribution, which affects query optimization and statistics collection.

PL/SQL blocks have a size limit of 64K. This is a database-specific constraint on the maximum size of code that can be contained in a single PL/SQL block. The limit applies to the compiled code size, not the source text length. This constraint is important for developers writing complex stored procedures or large anonymous blocks.

All four statements correctly identify key differences between SQL and PL/SQL. SQL is declarative and statement-based, while PL/SQL is procedural and block-based. SQL handles queries and data definition, while PL/SQL handles program logic structures. The embedding relationship is one-directional: SQL can be embedded within PL/SQL, but not vice versa. These distinctions are fundamental for Oracle developers.

Stored functions cannot be called in table constraints in most database systems including Oracle. Constraints must be deterministic and cannot rely on user-defined functions that may change or have side effects. This restriction ensures data integrity and prevents constraint violations from function changes. Check constraints can only use built-in functions and deterministic expressions.

Cardinality refers to the number of unique values in a table column relative to the total rows. Scenario 3 has the highest ratio of unique values (30 distinct values out of 100 rows) compared to Scenario 1 (10%) and Scenario 2 (20%).

In AFTER triggers, the insert operation has already completed and the data is written to the table. The inserted values cannot be modified because the transaction is past the point where changes are allowed. To modify inserted values, you would need an INSTEAD OF trigger or modify the data before insertion using a BEFORE trigger (in databases that support it). AFTER triggers are for post-insert operations like auditing or cascading changes.

Queries using ROWID in the WHERE clause run faster because ROWID represents the fastest possible access path to a row - it's the logical (pseudo-column) address that points directly to the physical location. When you query by ROWID, Oracle doesn't need to use indexes or scan tables; it goes straight to the specific data block and row slot. ROWID is not a physical column stored in the table, but a pseudo-column that exists in every row automatically. The speed improvement comes from direct physical addressing.

Procedures, functions, and packages in PL/SQL (or similar database languages) include multiple programming constructs: variables and constants for data storage, cursors for querying data row-by-row, and exceptions for error handling. All of these (option D) are valid constructs.

In Informatica PowerCenter, a trigger can contain only one statement. The trigger body is limited to a single SQL statement or PL/SQL block. This is a fundamental constraint of trigger design in the tool. Options suggesting multiple statements (Two, Three, Four) are incorrect.

In SQL Server, the maximum nesting level for triggers is 32. If a trigger chain exceeds 32 levels (trigger A fires trigger B, which fires C, and so on), the execution stops and an error is raised. This prevents infinite recursive trigger scenarios that could crash the database server. The limit applies to total trigger depth, not the number of triggers defined.

In distributed database queries, the @ symbol precedes database object names to identify them as remote objects. For example, in a query like SELECT * FROM @remote_server.database.schema.table, the @ indicates the table is located on a different/remote database server. This syntax varies by database system, but @ is commonly used for remote object references.

SYSDATE in PL/SQL is a pseudo-column that returns the current date and time from the database server. It is implemented as a function call that gets evaluated each time it is referenced, not a stored variable value. This is why SYSDATE always returns the current time when called.

In SQL optimization, you can prevent index usage (avoid indexes) by: forcing a full table scan via the FULL hint, forcing a different index with INDEX or AND-EQUAL hints, using expressions on index columns (e.g. col + 0 or to_char(col)) which disables index usage, or disabling/dropping the index.

Actual parameters are the values or expressions passed in a subprogram call. They are the real data being supplied to the subroutine when it's invoked. This contrasts with formal parameters, which are the variable declarations in the subprogram's specification that define what parameters it accepts.

Formal parameters are the variables declared in a subprogram's header or specification. They serve as placeholders for the values that will be passed when the subprogram is called. These are referenced within the subprogram body to work with the data provided through actual parameters.

In Oracle, if you have EXECUTE privilege on a procedure in another user's schema, you CAN execute that procedure even without direct privileges on the tables it accesses. This works because of definer's rights - the procedure runs with the privileges of its owner (the schema that owns it), not the privileges of the user calling it.

A cursor FOR loop automatically handles: declaring a record type variable, opening the cursor, parsing SQL, fetching records, and closing the cursor when done. The only thing it does NOT require is an explicit exit condition - the loop terminates automatically when all rows are fetched. This makes Option D the correct answer.

SQL Server's default port is 1433, but administrators can configure custom ports. The SQL Server Browser service (port 1434) helps clients find named instances. Since port configuration is customizable, Option D is correct - you need to know what port your specific instance uses, not assume a fixed port.