QUESTION 91
You issued the following command to drop the PRODUCTS table:
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)

A.    All data along with the table structure is deleted.
B.    The pending transaction in the session is committed.
C.    All indexes on the table will remain but they are invalidated.
D.    All views and synonyms will remain but they are invalidated.
E.    All data in the table are deleted but the table structure will remain.

Answer: ABD

QUESTION 92
Which two statements are true regarding views? (Choose two.)

A.    A simple view in which column aliases have been used cannot be updated.
B.    Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
C.    Rows added through a view are deleted from the table automatically when the view is dropped.
D.    The OR REPLACE option is used to change the definition of an existing view without dropping and
re-creating it.
E.    The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns
displayed through the view.

Answer: BD

QUESTION 93
Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2)NOT NULL,
job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:
CREATE OR REPLACE VIEW sales_staff_vu
SELECT employee_id, last_name,job_id
FROM employees
WHERE job_id LIKE ‘SA_%’
WITH CHECK OPTION;
Which two statements are true regarding the above view? (Choose two.)

A.    It allows you to insert rows into the EMPLOYEES table .
B.    It allows you to delete details of the existing sales staff from the EMPLOYEES table.
C.    It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.
D.    It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in
multitable INSERT statements.

Answer: BD

QUESTION 94
View the Exhibit to examine the description for the SALES and PRODUCTS tables.
You want to create a SALE_PROD view by executing the following SQL statement:
CREATE VIEW sale_prod
AS SELECT p.prod_id, cust_id, SUM(quantity_sold) “Quantity” , SUM(prod_list_price) “Price”
FROM products p, sales s
WHERE p.prod_id=s.prod_id
GROUP BY p.prod_id, cust_id;
Which statement is true regarding the execution of the above statement?

A.    The view will be created and you can perform DML operations on the view.
B.    The view will be created but no DML operations will be allowed on the view.
C.    The view will not be created because the join statements are not allowed for creating a view.
D.    The view will not be created because the GROUP BY clause is not allowed for creating a view.

Answer: B
Explanation:
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view

QUESTION 95
Which two statements are true regarding views? (Choose two.)

A.    A subquery that defines a view cannot include the GROUP BY clause.
B.    A view that is created with the subquery having the DISTINCT keyword can be updated.
C.    A view that is created with the subquery having the pseudo column ROWNUM keyword cannot
be updated.
D.    A data manipulation language ( DML) operation can be performed on a view that is created with
the subquery having all the NOT NULL columns of a table.

Answer: CD
Explanation:
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view

QUESTION 96
Which three statements are true regarding views? (Choose three.)

A.    Views can be created only from tables.
B.    Views can be created from tables or other views.
C.    Only simple views can use indexes existing on the underlying tables.
D.    Both simple and complex views can use indexes existing on the underlying tables.
E.    Complex views can be created only on multiple tables that exist in the same schema.
F.    Complex views can be created on multiple tables that exist in the same or different schemas.

Answer: BDF

QUESTION 97
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

A.    1
B.    10
C.    100
D.    an error

Answer: A

QUESTION 98
View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, ’25-jan-2007′,101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?

A.    All the statements would execute successfully and the ORD_NO column would contain the value 2
for the CUST_ID 101.
B.    The CREATE SEQUENCE command would not execute because the minimum value and maximum
value for the sequence have not been specified.
C.    The CREATE SEQUENCE command would not execute because the starting value of the sequence
and the increment value have not been specified.
D.    All the statements would execute successfully and the ORD_NO column would have the value 20 for
the CUST_ID 101 because the default CACHE value is 20.

Answer: A

QUESTION 99
When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics. Which two statements describe the implications of this? (Choose two.)

A.    It produces less accurate results of the comparison analysis.
B.    It automatically calls the SQL Tuning Advisor for recommendations.
C.    It shortens the time of execution and reduces the impact on system resources.
D.    Only the changes in the execution plan, and not performance regression, are detected.

Answer: AC

QUESTION 100
Which statements are correct regarding indexes? (Choose all that apply.)

A.    When a table is dropped, the corresponding indexes are automatically dropped.
B.    A FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.
C.    A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a
unique index.
D.    For each data manipulation language (DML) operation performed, the corresponding indexes are
automatically updated.

Answer: ACD

If you want to pass Oracle 1Z0-051 exam successfully, donot missing to read latest lead2pass Oracle 1Z0-051 practice tests.
If you can master all lead2pass questions you will able to pass 100% guaranteed.

http://www.lead2pass.com/1z0-051.html