DB2 and Oracle Database Fundamentals

Database triggers are stored programs that execute automatically in response to specific events on a table or view. Option A is incorrect because Application Trigger is not a standard database trigger type. Option B is correct - database triggers CAN fire when an INSERT statement occurs (among other DML operations). Option C is incorrect - system event triggers fire for DDL or database events, not DML DELETE. Option D is incorrect - INSTEAD OF triggers fire on views, not directly on INSERT statements to tables.

DML triggers use timing keywords BEFORE, AFTER, and INSTEAD OF (for views). DURING is not valid SQL syntax. ON SHUTDOWN is a system event trigger, not DML. INSTEAD without OF is incomplete.

The CALL statement in Oracle triggers invokes stored procedures. It cannot call functions (which return values), packages (which are containers), or other triggers (to avoid recursive calls).

The standard SQL command to remove a trigger is DROP TRIGGER trigger_name. DELETE is for data rows, REMOVE is not valid SQL, and ALTER TRIGGER ... REMOVE is not valid syntax.

OLD and NEW qualifiers reference row values before and after the triggering DML operation. They are only available in row-level DML triggers because statement-level triggers have no specific row context. System and application triggers don't use row qualifiers.

Oracle uses ALTER TABLE table_name DISABLE ALL TRIGGERS to disable all triggers on a table. The other options are not valid Oracle syntax.

The query uses concatenation (||) to combine address1, a comma, address2, another comma, and address2 again into a single column named 'Adress'. Despite concatenating multiple values, the result is presented as ONE column with the alias 'Adress'. The duplicate use of address2 appears intentional or typographical but doesn't change column count.

ROWID is Oracle's physical row address. Access by ROWID is the fastest single-row retrieval method because it's a direct physical location lookup. Unique index requires index traversal first, full table scan reads all rows, and primary key access typically uses an index.

Oracle column names must start with a letter, cannot start with a number or special character (except underscore, $, # after the first character). 1966_Invoices starts with a number, #Invoices starts with #, Column is a reserved word. Catch_#22 is valid.

TRUNCATE removes all rows from a table and does so as a DDL operation that does not generate individual row delete logs, meaning it does not write to the rollback segment. DROP removes the table definition, and DELETE logs each row, while CASCADE is not a standalone command for this purpose.

The substr function in Oracle SQL extracts and returns a specific substring from a character string based on a starting position and optional length. substring is standard SQL but not native to Oracle SQL*Plus, and instr locates character positions.

To answer this question, the user needs to understand what a primary key and an index are, and how they are related.

A primary key is a column or set of columns that uniquely identifies each row in a table. An index is a data structure that stores a copy of selected columns of a table to improve the speed of data retrieval operations on that table.

Now, let's go through each option and determine whether or not it will use the associated index on EMPNO:

A. select * from EMP where nvl(EMPNO, '00000') = '59384';
This statement will not use the associated index on EMPNO because the NVL function applied to EMPNO will turn an indexed column into an unindexed one. Using functions on indexed columns can result in the optimizer not choosing to use the index.

B. select 1 from EMP where EMPNO = '59834';
This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

C. select EMPNO, LASTNAME from EMP where EMPNO = '59384'
This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

D. select * from EMP where EMPNO = '59384';
This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

Therefore, the statement that will not use the associated index on EMPNO is option A.

The Answer is: A

When no TABLESPACE clause is specified, Oracle stores the segment in the user's default tablespace (assigned at user creation). The SYSTEM tablespace is for Oracle data dictionary. Users tablespace is not a default concept.

In SQL*Plus, the hyphen character (-) placed at the end of a line acts as the continuation character, signaling to the command line processor that the SQL statement continues on the following line.

NEXT_DAY(sysdate, 'MONDAY') returns the next occurrence of Monday after the current date. Since today is Monday July 10, 2000, the next Monday is July 17, 2000. The to_char format 'DD-MON-RR' produces '17-JUL-00'. Options A and B show dates before or on the current day, while option C shows July 11 which is a Tuesday.

To prevent a Cartesian product when joining N tables, you need at least N-1 join conditions. For a four-table join, this means a minimum of 3 conditions are required in the WHERE clause to properly link all relations.

DB2 Express Edition supports up to 2 CPUs and is positioned as IBM's entry-level server offering for production environments. DB2 Everyplace targets mobile devices, while Workgroup and Enterprise Server Editions support more CPUs and cost more.

DB2 Personal Edition functions as a client that can connect to remote DB2 servers but cannot accept connections from other clients. The Workgroup, Enterprise, and Data Warehouse editions are full server products that can both initiate and accept remote connections.

The Solaris server needs a full DB2 server product to host the database (Enterprise Server Edition) plus DB2 Connect Enterprise Edition to enable connections from the mainframe (z/OS) client. DB2 Connect is specifically required for mainframe-to-distributed system connectivity.

For HP-UX specifically, DB2 Enterprise Server Edition is the only product listed that is available for this platform. DB2 Express-C is the free community edition (not a licensed product), DB2 Express targets Windows/Linux, and DB2 Personal is for Windows workstations only.