Master Oracle Database SQL 1Z0-071: Your Gateway to Oracle Excellence

Correct : A

To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.

A . This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.

B . SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.

C . There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.

D . There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

E . This is a repetition of option D with the same issues.

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

Correct : B, E

After the execution of the given SQL statements:

B . The primary key constraint will be enabled and IMMEDIATE. This is true. The last statement ENABLE CONSTRAINT emp_emp_no_pk; enables the primary key constraint immediately.

E . The foreign key constraint will be enabled and IMMEDIATE. This is correct as well. The foreign key constraint emp_mgr_fk was not explicitly disabled, so it remains enabled and in the immediate checking mode.

Options A, C, and D are incorrect:

A is incorrect because constraints are immediate by default; they are not deferred unless explicitly stated.

C is incorrect because the foreign key constraint emp_mgr_fk was never disabled with the given statements.

D is incorrect because there is no statement altering the foreign key constraint to a deferred state.

Correct : A

In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column.

Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause.

Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon.

Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery.

Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery.

For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM():

Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause

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

Correct : A, B, F

A . Schema owners indeed can grant privileges on objects in their schema to other users or roles, making this statement true.

B . Roles in Oracle can be granted both object and system privileges, making this statement true as well.

C . Not all types of schema objects have associated object privileges. For example, synonyms do not have object privileges because they are just aliases for other objects.

D . The DBA role is a powerful role, but creating roles can be done by any user granted the necessary privileges, not just users with the DBA role.

E . Object privileges on a table do not automatically apply to all synonyms for that table. Synonyms are separate objects that must have privileges granted explicitly.

F . The privilege to grant system privileges is controlled by the GRANT ANY PRIVILEGE system privilege, making this statement true.

Oracle Database SQL Language Reference, 12c Release 1 (12.1): 'Privileges'

Oracle Database Security Guide, 12c Release 1 (12.1): 'Administering User Privileges, Roles, and Profiles'

Correct : A, D

A . True. This statement will work if customer_id is a character data type in the database. Oracle will implicitly convert the numeric literal 0001 to a string to compare with customer_id.

D . True. If the insert_date is of type DATE and the NLS_DATE_FORMAT matches 'DD-MON-YY', Oracle will implicitly convert the string literal '01-JAN-19' to a date type to compare with insert_date.

B is incorrect because if customer_id is a numeric data type, there is no need for implicit conversion. C is incorrect because using the DATE literal DATE '2019-01-01' is an explicit conversion. E is incorrect because TO_CHAR(customer_id) is an explicit conversion from a numeric to a string data type.