Oracle 1z0-071 Exam With Confidence Using Practice Dumps

The statement true regarding the CREATE TABLE command:

C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match: The SQL command tries to create a table with columns prod_id, cust_id, and order_date, but the SELECT statement specifies columns prod_id, cust_id, and time_id. The mismatch in column names and the number of columns specified will prevent the table from being created.

Incorrect options:

A: It is possible to specify a DEFAULT value in the column definition when creating a table with the CREATE TABLE AS SELECT syntax.

B: Not all NOT NULL constraints (or any other constraints, for that matter) are automatically passed to the new table unless explicitly stated in the CREATE TABLE statement.

D: FOREIGN KEY constraints are not automatically included when creating a table using the CREATE TABLE AS SELECT syntax; they would need to be added explicitly afterwards.

The LISTAGG function is used in Oracle to aggregate strings from data in a group specified by the GROUP BY clause, producing a single row of concatenated values. The correct syntax also specifies an ORDER BY clause within the WITHIN GROUP parenthesis to sort the values in the concatenated list.

The correct query is:

SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

This statement will return a comma-separated list of employee names (ename) in alphabetical order for each department (deptno) in the EMP table.

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.