The STATE table has the following constraints (the constraint status is shown in parentheses):

Primary key                     pk_state (enabled)

Foreign key                      COUNTRY table: fk_state (enabled)

Check constraint               ck_cnt_code (disabled)

Check constraint               ck_st_code (enabled)

NOT NULL constraint      nn_st_name (enabled)

You execute the following SQL code:

CREATE TABLE STATE_NEW AS SELECT * FROM STATE;

How many constraints will there be in the new table?

A. 0
B. 1
C. 3
D. 5
E. 2

---

Explanation:

When you execute the SQL code CREATE TABLE STATE_NEW AS SELECT * FROM STATE;, you are creating a new table called STATE_NEW by copying the data from the existing STATE table. The question asks how many constraints will be present in the new table.

In this case, the new table will have only one constraint. The primary key constraint pk_state will be copied over to the new table. The primary key constraint ensures that each record in the table has a unique value for the specified column(s). In this case, the primary key constraint pk_state is enabled, so it will be copied to the new table.

The other constraints present in the original STATE table will not be copied to the new table. This includes the foreign key constraint fk_state referencing the COUNTRY table, the check constraint ck_cnt_code, the check constraint ck_st_code, and the NOT NULL constraint nn_st_name.

Therefore, the correct answer is B. There will be only one constraint (the primary key constraint pk_state) in the new table STATE_NEW.

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips