QUESTION NO:  56 
From SQL*Plus, you issue this SELECT statement:
SELECT*
From orders;
You use this statement to retrieve data from a data table for __________. (Choose all
that apply)
A. Updating
B. Viewing
C. Deleting
D. Inserting
E. Truncating
Answer: B, D
Explanation:
You can use SELECT statement to display and to insert data into different table.
Incorrect Answers
A: You cannot update data with SELECT statement. Update command is used for this
purpose.
C: You cannot delete data with SELECT statement. Delete command is used for this purpose.
E: You cannot truncate data with SELECT statement. Truncate command is used for this
purpose.

QUESTION NO:  99 

Which three are true regarding the use of outer joins? (Choose three.)

A. You cannot use IN operator in a condition that involves an outerjoin.
B. You use (+) on both sides of the WHERE condition to perform an outerjoin.
C. You use (*) on both sides of the WHERE condition to perform an outerjoin.
D. You use an outerjoin to see only the rows that do not meet the join condition.
E. In the WHERE condition, you use (+) following the name of the column in the table
without matching rows, to perform an outerjoin.
F. You cannot link a condition that is involved in an outerjoin to another condition by
using the OR operator.

Answer: D, E, F

Explanation:
You can use an outerjoin to see only the rows that do not meet the join condition. In the
WHERE condition, you use (+) following the name of the column in the table without
matching rows, to perform an outerjoin. You cannot link a condition that is involved in an
outerjoin to another condition by using the OR operator.

Incorrect Answers
A: You can use IN operator in a condition that involves an outerjoin.
B: You use (+) following the name of the column in the table without matching rows, but not
on both sides of the WHERE condition to perform an outerjoin.
C: You don’t use (*) to define outerjoin operation.

QUESTION NO:  134 

You need to create a table named ORDERS that contains four columns:
 1 . an ORDER_ID column of number data type
 2 .- a CUSTOMER_ID column of number data type
 3 . an ORDER_STATUS column that contains a character data type
 4 . a DATE_ORDERED column to contain the date the order was placed

When a row is inserted into the table, if no value is provided for the status of the order,
the value PENDING should be used instead.

Which statement accomplishes this?

A. CREATE TABLE orders (
order_id NUMBER( 10 ),
customer_id NUMBER ( 8 ),
order_status VARCHAR2 ( 10 ),
date_ordered DATE = SYSDATE);

B. CREATE TABLE orders (
order_id NUMBER ( 10 ),
customer_id NUMBER ( 8 ),
order_status VARCHAR2 ( 10 ),
date_ordered DATE DEFAULT SYSDATE)

C. CREATE OR REPLACE TABLE orders (
order_id NUMBER ( 10 ),
customer_id NUMBER ( 8 ),
order_status VARCHAR2 ( 10 ),
date_ordered DATE = SYSDATE);

D. CREATE TABLE orders (
order_id NUMBER ( 10 ),
customer_id NUMBER ( 8 )
order_status NUMBER ( 10 ),
date_ordered DATE DEFAULT SYSDATE);

Answer: B

QUESTION NO:  144 

In which four clauses can a subquery be used? (Choose four.)

A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
E. in the SET clause of an UPDATE statement
F. in the VALUES clause of an INSERT statement

Answer: B, D, E, F

Explanation : subquery can be use in the FROM clause of a SELECT statement,
in the WHERE clause of a SELECT statement, in the SET clauses of an UPDATE statement,
in the VALUES clause of an INSERT statement.

Incorrect answer :
A subquery cannot be used
C subquery cannot be used

QUESTION NO:  170 
Evaluate the SQL statement:

TRUNCATE TABLE DEPT;

Which three are true about the SQL statement? (Choose three.)

A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement
executes will display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to
truncate the DEPT table

Answer: B, D, F

Explanation : does not release the storage space used by the table,
Can not rollback the deletion of rows after the statement executes,
You must be the owner of the table or have DELETE ANY TABLE
system privilege to truncate the DEPT table.

Incorrect answer :
A is not true
C is not true
E is not true

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release  10 . 2 . 0 . 1 . 0  - Prod
PL/SQL Release  10 . 2 . 0 . 1 . 0  - Production
CORE     10 . 2 . 0 . 1 . 0       Production
TNS for  32 -bit Windows: Version  10 . 2 . 0 . 1 . 0  - Production
NLSRTL Version  10 . 2 . 0 . 1 . 0  - Production

SQL> desc aaa;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 A                                                  VARCHAR2( 5 )
 B                                                  NUMBER( 5 )
 C                                                  NUMBER( 7 , 2 )

SQL> select * from aaa;

no rows selected

SQL> insert into (select * from aaa)
   2   values ('test',  1 ,  2 . 3 )
   3   ;

 1  row created.

SQL> select * from aaa;

A              B          C
----- ---------- ----------
test            1          2 . 3 

SQL> 
SQL> update (select * from aaa)
   2   set c =  10 ;

 1  row updated.

SQL> 
SQL> select * from aaa;

A              B          C
----- ---------- ----------
test            1           10 

SQL> 
SQL> delete (select * from aaa);

 1  row deleted.

SQL> 
SQL> select * from aaa;

no rows selected