Examine the description of the SALES table: Examine this statement: CREATE TABLE mysales (prod_id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales…

Question

Exhibit

Options

• AMYSALES is created with no rows.
• BMYSALES will have no constraints defined regardless of which constraints might be de-fined on
• CMYSALES has NOT NULL constraints on any selected columns which had that constraint in the
• DMYSALES is created with 2 rows.
• EMYSALES is created with 1 row.

• A
  87% (39)
• B
  4% (2)
• D
  7% (3)
• E
  2% (1)

Explanation

Option A is correct because WHERE 1 = 2 is always false - the subquery returns zero rows, so the table is created with its structure intact but completely empty.

Option C is correct because in Oracle's CREATE TABLE AS SELECT (CTAS), NOT NULL constraints are the only constraints inherited from the source table. Any column in SALES with a NOT NULL constraint will retain that constraint in MYSALES.

Why the distractors fail:

• B is wrong precisely because CTAS does carry over NOT NULL constraints - it just drops other constraint types (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY).
• D and E are wrong because WHERE 1 = 2 can never be true, so zero rows are ever inserted - not 1 or 2.

Memory tip: Think of CTAS as a "structure copy with selective constraints" - the NOT NULL constraint is baked into the column definition itself (it's a column-level property), while integrity constraints like PRIMARY KEY are separate objects that don't travel with the column. The WHERE 1 = 2 trick is a classic Oracle pattern for cloning a table's structure without any data.

No community discussion yet for this question.