Oracle Practice Test - 1

To avoid a cartesian product when joining n tables, you need n-1 join conditions. For 3 tables, that's 2 join conditions. Each condition links one new table to the existing result set. Without sufficient join conditions, every row from every table matches every other row - an explosive cartesian product.

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is NOT NULL, and expr3 if expr1 IS NULL. Since the first argument is NULL, the function returns the third argument which is NULL. Option B would be returned if the first argument were NOT NULL.

In Oracle Forms, when you need to change the source table associated with an existing data block, you use the Data Block wizard after selecting the items. The Layout Wizard (option A) is for visual arrangement, not changing data sources. Option C is incorrect because table sources can be changed.

Data files in Oracle contain the actual user data and system data. Control files contain metadata about the database structure. Redo log files (online and offline) contain transaction records for recovery, not the actual data.

Record groups in Oracle Forms are programmatic data structures that can store data in memory and are not visible to users. They are ideal for storing authorization data that needs to be accessed programmatically but hidden from the user interface. List items and lists of values are visible UI elements.

A default role in Oracle is automatically enabled for a user when they log on to the database. It does not require a password (that would be a password-protected role). Default roles are set via the ALTER USER DEFAULT ROLE command, not automatically assigned when user is created.

Oracle Shared Server is the evolution of the multithreaded server architecture, improving resource utilization through connection pooling and dispatcher processes. Options B and C describe different concepts - B describes basic connection pooling without mentioning dispatcher processes, while C describes Real Application Clusters (RAC). Option D refers to RAC load balancing, not Shared Server.

PMON (Process Monitor) performs process monitoring and recovery, but it does NOT perform ALL system monitoring functions. That statement is an overstatement - other background processes like SMON (System Monitor), CKPT (Checkpoint), and archiving processes handle different aspects of system monitoring. PMON specifically monitors failed processes, cleans up their resources, and rolls back their transactions.

Index-organized tables (IOTs) store rows in B-tree index structure sorted by primary key, providing fast key-based access for both exact matches and range searches. Regular tables store rows in heap format, clustered tables group related tables together physically, and partitioned tables divide data into segments but don't optimize key-based access specifically.

PRAGMA EXCEPTION_INIT is a compiler directive that associates a user-defined error message with a specific Oracle error number. The other options are not valid PL/SQL constructs - ORA_EXCEPTION and ORA_USER_EXCEPTION don't exist, and 'Pragma Exception' is incomplete syntax.

In Oracle 10g, DROP TABLE by default moves tables to the recyclebin. The PURGE option drops the table permanently without sending it to the recyclebin, making it equivalent to pre-10g DROP TABLE behavior. DROP TABLE NO PURGE is not valid syntax, and DROP TABLE...TO BEFORE DROP is used for flashback recovery, not permanent deletion.

Oracle Advanced Security Option provides authentication adapters that support various methods including token cards, biometrics, and Kerberos. Heterogeneous Services is for non-Oracle data access, Firewalls are network security devices, and Oracle Connection Manager handles connection pooling and multiplexing but not advanced authentication.

The hierarchical query syntax is correct: CONNECT BY PRIOR employee_id = manager_id builds the tree from parent to child, and START WITH manager_id IS NULL correctly identifies Mr. King as the root. The query structure (1, level, last_name, NULL, to_char(employee_id)) properly defines tree depth, label, and data attributes. Option D's suggestion to reverse PRIOR would produce incorrect hierarchy.

Oracle Connection Manager provides connection concentration and multiplexing, allowing hundreds of clients to share fewer server connections. Dedicated Server would require one server process per client (not scalable), Directory Naming is for service resolution, and Heterogeneous Services is for non-Oracle database access.

Multiple Oracle instances can run on the same machine, each accessing separate physical databases - this is how Oracle Real Application Clusters (RAC) and standalone instances coexist. Option A is incorrect because redo log buffer IS part of SGA. Option C is incorrect because instances consist of memory structures and background processes, not user processes. Option D is incorrect because shared server environments have one SGA but multiple PGAs (one per server process).

To find the highest GPA per semester, you need MAX(gpa) with GROUP BY semester_end. Option C has the correct clause order: SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end. Options B, D, and E have incorrect SQL syntax - GROUP BY must come after WHERE but before HAVING, and SELECT cannot appear inside parentheses.

Oracle column names must start with a letter, can contain letters, digits, underscore (), dollar sign ($), and pound sign (#). Option C (Catch#22) is valid because it starts with a letter and only contains allowed characters. Option A has a trailing space, Option B starts with a digit, and Option D starts with # instead of a letter.

In the expression 2+34/2-8, multiplication and division have higher precedence than addition and subtraction. Among * and / with equal precedence, evaluation is left-to-right: 34=12 first, then 12/2=6, making * the first operator evaluated.

When the inner query returns NULL, NOT IN (1, 2, NULL) evaluates to UNKNOWN for every comparison. A WHERE condition of UNKNOWN returns no rows, so the outer query selects nothing. This is a classic SQL NULL pitfall.

With PCTINCREASE=20, each subsequent extent grows by 20%. Extent 1: 100, Extent 2: 100. Extent 3: 1001.2=120. Extent 4: 1201.2=144. The percentage applies to the previous extent's size.