1z0-071 Exam Dumps - Oracle Database 12c SQL

To find customer names where the last name starts with Mc or MC, the correct queries are:

A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%'This query converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'.

E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation.

Options B, C, and D are incorrect:

B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.

C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action.

D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.

A. True, arithmetic operators of equal precedence are evaluated from left to right within an expression, according to the standard SQL operator precedence. B. True, multiple parentheses can be used in an expression to change the order of operations and override the default precedence of operators.

C, D, and E are not correct because: C. The + binary operator does not have the highest precedence; multiplication and division have higher precedence in SQL. D. NULLS do not influence the precedence of operators in an expression; they may affect the result of an operation but not the order in which operators are evaluated. E. The concatenation operator (||) has lower precedence than arithmetic operators in SQL expressions.

References:

Oracle documentation on operator precedence: Oracle Database SQL Language Reference

Regarding the SESSION_PRIVS dictionary view:

C. It contains the current system privileges available in the user session: This view displays the system privileges granted to the current user, making it a tool for understanding which actions the user is allowed to perform in the current session.

Incorrect options:

A and B: SESSION_PRIVS does not contain information about privileges granted to or by other users; it specifically lists the privileges available to the current session.

D: It does not contain object privileges; these are contained in other views such as USER_TAB_PRIVS.

A: This statement is correct. It uses the TO_DATE function with a proper date string, format mask, and NLS_DATE_LANGUAGE setting.

B: This statement will not execute successfully because the syntax of the TO_CHAR function is incorrect. The date string should be a DATE data type when used with TO_CHAR, and the format mask and NLS parameter are incorrectly specified.

C: This statement will not execute successfully because it is redundant to use TO_CHAR and then immediately convert it back to a date with TO_DATE without specifying a proper format mask.

D: This statement is correct. It converts a string to a DATE using TO_DATE and then back to a string with TO_CHAR, without specifying a format which defaults to the session’s NLS_DATE_FORMAT.

E: This statement will not execute successfully because TO_CHAR is used incorrectly; the first argument must be of DATE data type when you're using a date format mask.

References for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.

C: True. You can add a new NOT NULL column to an existing table, provided you specify a DEFAULT value for the column. This ensures that existing rows in the table can be updated with a default value, thus maintaining the NOT NULL constraint integrity.

E: True. It is permissible to increase the width of a numeric column, provided the change does not truncate existing data. This change is generally safe as it allows for greater values or more precision without affecting existing data.

F: True. The default value of a column can be changed. This action affects subsequent insert operations but does not alter the values of existing rows. Changing the default is useful for adapting tables to evolving business rules without impacting existing data.

Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database users.

A. The term "GLOBAL" is not used in the creation of synonyms in Oracle.

B. The statement without the keyword PUBLIC will create a private synonym that is only accessible to the user creating the synonym, not all users.

C. The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this option incorrect.

D. You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects.

E. This is the correct syntax to create a public synonym, which makes the underlying object accessible to all users.

References:

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

A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. (Incorrect)

Primary key columns that are referenced by foreign keys cannot be dropped as it would violate referential integrity. The CASCADE option does not apply in this context.

B. A DROP COLUMN command can be rolled back. (Correct)

Dropping a column from a table is a transactional operation. If it's not committed, it can be rolled back.