OCP-047 insert into values(defualt)

67. View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and
ORDER_TOTAL have the default values 'direct' and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
A. INSERT INTO orders VALUES (1, '09mar2007','online','',1000);
B. INSERT INTO orders  (order_id,order_date,order_mode,customer_id,order_total)
VALUES(1,TO_DATE(NULL), 'online', 101, NULL);
C. INSERT INTO  (SELECT order_id,order_date,customer_id FROM orders)  VALUES (1,'09mar2007', 101);
D. INSERT INTO orders VALUES (1,'09mar2007', DEFAULT, 101, DEFAULT);
E. INSERT INTO orders (order_id,order_date,order_mode,order_total)  VALUES (1,'10mar2007','online',1000);

Answer: CD

Syntax

insert::=

Description of the illustration insert.gif

(single_table_insert ::=, multi_table_insert ::=)

single_table_insert ::=

Description of the illustration single_table_insert.gif

(insert_into_clause ::=, values_clause ::=, returning_clause ::=, subquery::=, error_logging_clause ::=)

insert_into_clause ::=

Description of the illustration insert_into_clause.gif

(DML_table_expression_clause::=)

values_clause ::=

Description of the illustration values_clause.gif

returning_clause ::=

Description of the illustration returning_clause.gif

multi_table_insert ::=

Description of the illustration multi_table_insert.gif

(insert_into_clause ::=, values_clause ::=, conditional_insert_clause ::=, subquery::=, error_logging_clause ::=)

conditional_insert_clause ::=

Description of the illustration conditional_insert_clause.gif

(insert_into_clause ::=, values_clause ::=)

DML_table_expression_clause::=

Description of the illustration DML_table_expression_clause.gif

(subquery::=--part of SELECT, subquery_restriction_clause::=, table_collection_expression ::=)

subquery_restriction_clause::=

Description of the illustration subquery_restriction_clause.gif

table_collection_expression ::=

Description of the illustration table_collection_expression.gif

error_logging_clause ::=

Description of the illustration error_logging_clause.gif

If the departments table had been created with a default value of 121 for the manager_id column, then you could issue the same statement as follows:

INSERT INTO departments
   VALUES (280, 'Recreation', DEFAULT, 1700);

Restrictions on Inserted Values The value are subject to the following restrictions:

• You cannot insert a BFILE value until you have initialized the BFILE locator to null or to a directory name and filename.

  See Also:

  • BFILENAME for information on initializing BFILE values and for an example of inserting into a BFILE

  • Oracle Call Interface Programmer's Guide and Oracle Database Application Developer's Guide - Fundamentals for information on initializing BFILE locators

• When inserting into a list-partitioned table, you cannot insert a value into the partitioning key column that does not already exist in the partition_valuelist of one of the partitions.

• You cannot specify DEFAULT when inserting into a view.

• If you insert string literals into a RAW column, then during subsequent queries Oracle Database will perform a full table scan rather than using any index that might exist on the RAW column.