Oracle synonyms are used to simplify the referencing of complex schema objects:

Option A: Incorrect. A synonym cannot have another synonym; it directly references the base object.

Option B: Incorrect. A synonym does not have an object number as it is merely an alias for another object.

Option C: Correct. Any user with sufficient privileges can create a public synonym, which is accessible to all users in the database.

Option D: Incorrect. All private synonym names must be unique within a schema but not across the entire database.

Option E: Correct. Synonyms can be created for objects within packages, such as procedures or functions, simplifying the referencing of these objects without needing to specify the full package name.

A. The constraint commission < salary implies that COMMISSION must be less than SALARY, so it can have a negative value if the salary is greater than zero, making this statement true.

B. The constraint named CR_MGR is listed as a Referential (R) constraint on the MANAGER column, which implies it is a foreign key that would reference the EMPNO column, making this statement true.

C. The SALARY column is part of a Check (C) constraint CC_COMM that requires the SALARY to be greater than 100, which means it must have a value and cannot be NULL, making this statement true.

D. The presence of a foreign key constraint does not automatically create an index on the foreign key column. While Oracle may implicitly create indexes for primary keys, it does not do so for foreign keys.

E. The constraint deptno > 9 prevents DEPTNO from containing values less than or equal to 9, so it cannot contain the value 1, making this statement false.

F. There is no NOT NULL constraint listed on the DEPTNO column in the constraints image, so it can contain NULL values, making this statement false.

G. There is no indication of a unique constraint on the DNAME column in the constraints image; the constraint DNAME IS NOT NULL only implies that the column cannot contain NULL values, making this statement false.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Constraints"

Oracle Database Concepts, 12c Release 1 (12.1): "Indexes and Index-Organized Tables"

In Oracle Database 12c, the SYSDATE function returns a date value in the current session's date format. The SUBSTR function is used to extract a substring from a string. However, SYSDATE is a DATE data type and not a string, so you cannot directly use SUBSTR on it. Therefore, SYSDATE must first be converted to a character data type using the TO_CHAR function before applying the SUBSTR function.

Therefore, the correct form of the query should be:

SELECT SUBSTR(TO_CHAR(SYSDATE), 1, 5) AS "Result" FROM DUAL;

This converts the current date and time to a string using the default date format and then extracts the first 5 characters from it.

A: This statement is false. A table can only have one primary key, although the primary key can consist of multiple columns (composite key).

B: This statement is true. A table can have multiple foreign keys referencing the primary keys of other tables or the same table.

C: This statement is false. A NUMBER column without data is NULL, not zero.

D: This statement is false. A column definition must specify exactly one data type.

E: This statement is true. A VARCHAR2 column without data defaults to NULL, not an empty string.

In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.

A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.

The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.

A. The USER SYNONYMS view can provide information about private synonyms: This view contains information about private synonyms that have been created by the user. It lists all synonyms that are accessible to the user in their schema.

E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access: This view lists the names of all data dictionary views available to the user, providing a directory of useful database metadata.

Incorrect options:

B: The SYSTEM user does not own all base tables of the data dictionary; some are owned by SYS.

C: Not all users have access to dynamic performance views prefixed with V$; access is restricted based on privileges.

D: The USER_OBJECTS view lists all objects owned by the user, not just tables and views but also other types of schema objects like procedures and functions.

B. True. CURRENT_TIMESTAMP returns the current date and time in the session time zone, which is determined by the SESSIONTIMEZONE setting.

E. True. CURRENT_TIMESTAMP returns a value of the data type TIMESTAMP WITH TIME ZONE.

A and D are incorrect because CURRENT_TIMESTAMP reflects the session time zone, not DBTIMEZONE. C is incorrect because CURRENT_DATE returns the current date with no time component, whereas CURRENT_TIMESTAMP includes both date and time. F is incorrect because SYSTIMESTAMP returns the current date and time from the system clock in the time zone of the database server, which can differ from the session's time zone affecting CURRENT_TIMESTAMP.