nerdexam
Oracle

1Z0-060 · Question #157

You issue the following command to alter the country column in the departments table: Which statement is true?

The correct answer is B. It executes successfully and all the rows that have a null value for the country column will be updated. In Oracle Database, altering an existing column to add a DEFAULT value executes successfully and updates all existing rows that currently hold NULL in that column with the new default.

Upgrading to Oracle Database 12c

Question

You issue the following command to alter the country column in the departments table:

Which statement is true?

Options

  • AIt produces an error because column definitions cannot be altered to add default values.
  • BIt executes successfully and all the rows that have a null value for the country column will be updated
  • CIt executes successfully. The modification to add the default value takes effect only from subsequent
  • DIt produces an error because the data type for the column is not specified.

How the community answered

(40 responses)
  • A
    5% (2)
  • B
    90% (36)
  • C
    3% (1)
  • D
    3% (1)

Why each option

In Oracle Database, altering an existing column to add a DEFAULT value executes successfully and updates all existing rows that currently hold NULL in that column with the new default.

AIt produces an error because column definitions cannot be altered to add default values.

Oracle's ALTER TABLE MODIFY syntax fully supports adding or changing a DEFAULT clause on a column; no error is raised because modifying default values is a legal DDL operation.

BIt executes successfully and all the rows that have a null value for the country column will be updatedCorrect

Oracle's ALTER TABLE MODIFY command executes without error when adding a DEFAULT clause, and existing rows that contain NULL for the modified column are updated to the new default value at the time the statement runs, in addition to the default being applied for future inserts where the column value is omitted.

CIt executes successfully. The modification to add the default value takes effect only from subsequent

The new default does not apply only to rows inserted after the ALTER statement; Oracle also applies the default retroactively to existing rows that currently have NULL in the column.

DIt produces an error because the data type for the column is not specified.

When modifying an existing column, the data type does not need to be re-specified in the MODIFY clause; Oracle retains the column's existing data type if it is omitted.

Concept tested: ALTER TABLE MODIFY column DEFAULT value effect on existing rows

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html

Topics

#ALTER TABLE#DEFAULT constraint#column modification#DDL behavior

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-060 Practice