Oracle PL 1z0-071 Reddit Questions

To determine if customer details have been entered more than once using a different custno, the following methods can be used:

• SUBQUERY: A subquery can be used to find duplicate names by grouping the names and having a count greater than one.

SELECT custname FROM customers GROUP BY custname HAVING COUNT(custname) > 1;

• Self Join: A self join can compare rows within the same table to find duplicates in the custname column, excluding matches with the same custno.

SELECT c1.custname FROM customers c1 JOIN customers c2 ON c1.custname = c2.custname AND c1.custno != c2.custno;

These methods allow us to find duplicates in the custname column regardless of the custno. Options A, B, and E are not applicable methods for finding duplicates in this context.

References:

• Oracle Documentation on Group By: Group By
• Oracle Documentation on Joins: Joins

A. Incorrect. There is no such restriction in Oracle that a global temporary table cannot have a public synonym. B. Correct. Global temporary tables can have more than one index, just like permanent tables. C. Correct. Global temporary tables can be referenced in the defining query of a view. However, any data selected by the view will be session-specific if it comes from the global temporary table. D. Correct. DML operations on global temporary tables do not generate redo log entries for the data changes; however, undo information for these tables is stored in the undo tablespace and can generate redo entries. E. Incorrect. As stated in B, global temporary tables can have more than one index. F. Correct. Triggers can be created on global temporary tables.

These properties are documented in the Oracle Database SQL Language Reference and Oracle Database Concepts Guide.

In Oracle Database 12c, it is important to understand the behavior and properties of constraints.

A. This statement is false. A FOREIGN KEY column in a child table does not need to have the same name as the referenced PRIMARY KEY column in the parent table. What is required is that the data type is the same and that the values in the FOREIGN KEY column correspond to values in the PRIMARY KEY column of the parent table.

B. This statement is false. A table cannot have multiple PRIMARY KEY constraints. By definition, a PRIMARY KEY is a unique identifier for a row in a table, and there can only be one such identifier.

C. This statement is false for the same reasons as B; a table can have only one PRIMARY KEY. However, it can have multiple FOREIGN KEY constraints that reference PRIMARY KEYS in other tables.

D. This is true. PRIMARY KEY and FOREIGN KEY constraints can indeed be specified at the column level with the column definition or at the table level with the ALTER TABLE statement.

E. This is true. A table can have only one PRIMARY KEY constraint because it defines a unique row identifier. However, it can have multiple FOREIGN KEY constraints referencing keys in other tables, allowing for complex relational structures.

F. This statement is false. NOT NULL constraints are always defined at the column level, as they apply to individual columns. They cannot be specified at the table level.

References:

• Oracle Documentation on Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271
• Oracle Documentation on NOT NULL Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52162

• A: No implicit conversion is done here because TO_CHAR is an explicit conversion from a numeric to a string data type.
• B: Implicit conversion happens here because the '0001' string will be automatically converted to a numeric data type to match the CUSTOMER_ID field.
• C: No implicit conversion is needed because 0001 is already a numeric literal.
• D: Implicit conversion occurs here because the string '01-JAN-19' will be converted to a date type to compare with INSERT_DATE.
• E: No implicit conversion; DATE is explicitly specified using the DATE keyword.
• F: Incorrect syntax, it has typographical errors and also TO_DATE is an explicit function, not an implicit conversion.

References for SQL functions, conversions, and the CROSS JOIN behavior can be found in the Oracle Database SQL Language Reference 12c documentation.