Oracle Updated 1z0-071 Exam Questions and Answers by leena

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 SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.

A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.

B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.

C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.

D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.

E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).

References:

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

A: LAST_DAY does not only return the last day of the current month; it returns the last day of the month based on the date argument passed to it, which may not necessarily be the current month. Thus, statement A is incorrect.

B: CEIL requires a numeric argument and returns the smallest integer greater than or equal to that number. Thus, statement B is incorrect.

C: ADD_MONTHS function adds a specified number of calendar months to a date. This statement is correct as per the Oracle documentation.

D: ADD_MONTHS can work with a character string if the string can be implicitly converted to a DATE, according to Oracle SQL data type conversion rules. Therefore, statement D is correct.

E: LAST_DAY does not specifically return the last day of the previous month; it returns the last day of the month for any given date. Thus, statement E is incorrect.

F: CEIL returns the smallest integer greater than or equal to the specified number, not the largest integer less than or equal to it. Hence, statement F is incorrect.

G: LAST_DAY returns the last day of the month for the date argument passed to the function, which aligns with the definition in Oracle's SQL reference. Therefore, statement G is correct.

To achieve the desired output for the query from the MEMBERS table:

C. ORDER BY 1, LNAME DESC: This clause correctly sorts the results first by the first column (city) in ascending order by default, and then by the alias LNAME (last_name) in descending order.

E. WHERE city LIKE '%AN%': This clause correctly filters the rows to include only those cities containing the string 'AN' anywhere in the city name, using the LIKE operator which is suitable for pattern matching.

Incorrect options:

A: This would order both columns in ascending order, which does not meet the requirement for last_name to be in descending order.

B: This misplaces the order priorities and uses explicit column names that contradict the sorting requirement specified.

D: Incorrect syntax for a LIKE clause; equality operator cannot be used with wildcards.

F: The IN operator is incorrectly used here with a pattern, which is not valid syntax.