Oracle 1Z0-071 Exam Dumps

Based on the business rule, the true statements regarding the Entity Relationship (ER) model are:

A . An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities. This is true because, in a many-to-many relationship, an associative (junction) table is used to maintain the associations between two entities.

D . The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. This is correct because many-to-many relationships are resolved by introducing an associative table that breaks it down into two 1-to-many relationships.

Options B, C, and E are incorrect:

B is incorrect because PROJECT_ID should not be a foreign key in the STUDENTS entity in a many-to-many relationship.

C is incorrect as the rule indicates a many-to-many relationship, not 1-to-many.

E is incorrect because STUDENT_ID should not be a foreign key in the PROJECTS entity in a many-to-many relationship.

In Oracle Database 12c, the following are true about privileges and roles:

Option B: PUBLIC acts as a default role granted to every user in a database.

The PUBLIC role is a special role that is automatically granted to all users. It can be used to provide privileges to every user.

Option C: A user has all object privileges for every object in their schema by default.

By default, a user has all privileges on the objects they own, i.e., the objects in their schema.

Option D: A role can contain a combination of several privileges and roles.

Roles are designed to group together privileges and other roles, making it easier to manage and assign security settings to users.

Options A, E, F, and G are incorrect because:

Option A: System privileges can be set for specific schemas or objects, not always for the entire database.

Option E: Roles are not 'owned' by users in the traditional sense of ownership; they are managed by the database and can be granted to any user.

Option F: Not all roles are owned by the SYS schema; roles are a database security concept and do not have an 'owner'.

Option G: The PUBLIC role is not something that can be revoked from a user; it is a default role that exists for all users.

Single-row functions operate on single rows and return one result per row. Let's look at each option in the context of Oracle 12c SQL:

A . CONCAT: This function can only combine two values at a time. If you need to concatenate more than two values, you have to nest CONCAT functions or use the || operator.

B . MOD: The MOD function returns the remainder of a division operation, not the quotient.

C . CEIL: This function returns the smallest integer that is greater than or equal to a specified number. It works with both positive and negative numbers.

D . FLOOR: It returns the largest integer that is less than or equal to the specified number, not greater than or equal to.

E . TRUNC: This function can indeed be used with both NUMBER and DATE values to truncate them to a specified number of decimal places or to a particular component of a date.

Oracle Database SQL Language Reference 12c Release 1 (12.1), Functions

Oracle Database SQL Language Reference 12c Release 1 (12.1), CONCAT Function

Oracle Database SQL Language Reference 12c Release 1 (12.1), MOD Function

Oracle Database SQL Language Reference 12c Release 1 (12.1), CEIL and FLOOR Functions

Oracle Database SQL Language Reference 12c Release 1 (12.1), TRUNC Function

A . True, a single table column can be included in both a unique index and a non-unique index, provided they are different indexes.

D . True, an invisible index is maintained during DML operations, which means it is updated when insert, update, or delete operations are performed on the table. However, it is not used by the optimizer by default for query execution unless explicitly hinted or the session is altered to consider invisible indexes.

E . True, an index can be specified in the CREATE TABLE statement using the INDEX clause to create an index on a specific column at the time of table creation.

B, C, and F are not correct: B. A descending index is simply an index that is sorted in descending order, not a type of function-based index. C. A DROP INDEX

In a CROSS JOIN (also known as a Cartesian join), each row from the first table (emp) is joined to every row of the second table (dept). When we apply the filter for job = 'MANAGER' and dept.deptno IN (10, 20), we are restricting the results to managers in departments 10 and 20.

From the given data:

There are 2 managers in department 10 (CLARK and KING).

There is 1 manager in department 20 (JONES).

With a cross join, each of these emp records will join with each dept record where deptno is 10 or 20. Since there are two departments (10 and 20) in the dept table, each employee will match with two departments.

Therefore, the result set will be:

CLARK with accounting (dept 10) and research (dept 20)

KING with accounting (dept 10) and research (dept 20)

JONES with accounting (dept 10) and research (dept 20)

That gives us a total of 6 rows, which means the correct answer is B: 6.