Oracle 1z0-071 Real Exam Questions and Answers FREE [Q107-Q127] : Actual Test Materials : http://blog.actualtests4sure.com

Q107. Examine the structure of the MARKStable:

Which two statements would execute successfully? (Choose two.)
SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)

FROM marks
WHERE subject1 > subject2;
SELECT student_name subject1

FROM marks
WHERE subject1 > AVG(subject1);
SELECT SUM(subject1+subject2+subject3)

FROM marks
WHERE student_name IS NULL;
SELECT student_name,SUM(subject1)

FROM marks
WHERE student_name LIKE ‘R%’;

Q108. View the exhibit and examine the ORDERStable.

The ORDERStable contains data and all orders have been assigned a customer ID. Which statement would add a NOTNULLconstraint to the CUSTOMER_IDcolumn?
ALTER TABLE orders

MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
ALTER TABLE orders

ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
ALTER TABLE orders

MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
ALTER TABLE orders

ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Q109. View the Exhibit and examine the structure of the PORDUCT_INFORMATION table.
(Choose the best answer.)
PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

SELECT product_id, UPPER(product_name)FROM product_informationWHERE
UPPER(product_name) = ‘LASERPRO’ OR list_price > 1000;

SELECT UPPER(product_name)FROM product_information;

SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;

SELECT product_idFROM product_informationWHERE UPPER(product_name) IN (‘LASERPRO’, ‘CABLE’);

Q110. Which two statements are true about transactions in the Oracle Database server? (Choose two.)

If a session has an uncommitted transaction, then a DDL statement issues a COMMITbefore starting a new transaction.

An uncommitted transaction commits automatically if the user exists SQL*Plus.

Data Manipulation Language (DML) statements always start a new transaction.

A user can always see uncommitted updates made by the same user in a different session.

A Data Definition Language (DDL) statement does a COMMITautomatically only for the data dictionary updates caused by the DDL.

A session can always see uncommitted updates made by itself.

Q111. View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.

Evaluate the following MERGEstatement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.

The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.

The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.

The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.

Q112. View the Exhibit and examine the structure of the PORDUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

SELECT product_id, UPPER(product_name)FROM product_informationWHERE
UPPER(product_name) = ‘LASERPRO’ OR list_price > 1000;

SELECT UPPER(product_name)FROM product_information;

SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;

SELECT product_idFROM product_informationWHERE UPPER(product_name) IN (‘LASERPRO’,
‘CABLE’);

Q113. View the Exhibit and examine the structure of the ORDER_ITEMStable.

Examine the following SQL statement:
SELECT order_id, product_id, unit_price
FROM order_items
WHERE unit_price
(SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.What correction should be made in the above SQL statement to achieve this?

Remove the GROUP BY clause from the subquery and place it in the main query

Replace = with the >ANY operator

Replace = with the >ALL operator

Replace = with the IN operator

Q114. Examine the commands used to create department details and course details:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table. You execute the SQL statement:

What is the outcome?

it fails because the Join type used is Incorrect

It executes successfully and displays the required list.

It executes successfully but displays an incorrect list.

It fails because the ON clause condition is not valid

Q115. View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Q116. Which two are true about multiple table INSERT statements?

They always use subqueries.

They can transform a row from a source table into multiple rows in a target table.

The conditional INSERT FIRST statement always inserts a row into a single table.

The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.

The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.

Q117. View the exhibit and examine the description of the PRODUCT_INFORMATIONtable.

Which SQL statement would retrieve from the table the number of products having LIST_PRICEas NULL?

SELECT COUNT (DISTINCT list_price)
FROM product_information
WHERE list_price is NULL

SELECT COUNT (NVL(list_price, 0))
FROM product_information
WHERE list_price is NULL

SELECT COUNT (list_price)
FROM product_information
WHERE list_price i= NULL

SELECT COUNT (list_price)
FROM product_information
WHERE list_price is NULL

Q118. Which two are the minimal requirements for a self-join? (Choose two.)

Only equijoin conditions may be used in the query.

Outer joins must not be used in the query.

There must be a condition on which the self-join is performed.

No other condition except the self-join may be specified.

The table used for the self-join must have two different alias names in the query.

Q119. Examine the structure of the EMPLOYEEStable.

There is a parent/child relationship between EMPLOYEE_IDand MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_IDis 123.
Which query provides the correct output?

SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.manager_id = m.employee_id)
AND e.employee_id = 123;

SELECT e.last_name, m.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;

SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123;

SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123;

Q120. View the Exhibits and examine PRODUCTSand SALES tables.
Exhibit 1

Exhibit 2

You issue the following query to display product name the number of times the product has been sold:

What happens when the above statement is executed?

The statement executes successfully and produces the required output.

The statement produces an error because a subquery in the FROMclause and outer-joins cannot be used together.

The statement produces an error because the GROUP BYclause cannot be used in a subquery in the FROM clause.

The statement produces an error because ITEM_CNTcannot be displayed in the outer query.

Q121. View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total);
What would be the outcome of the above statement?

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

Q122. Examine this SQL statement:

Identify three order by clauses, any one of which can complete the query successfully.

ORDER BY CUST_NO

ORDER BY 2, 1

ORDER BY 2, cust_id

ORDER BY “Last Name”

ORDER BY “CUST NO”

Q123. Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES (‘ADV112’ , ‘Adventures of Tom Sawyer’, NULL, NULL);
SAVEPOINT a;
DELETE from books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true?

The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

The second ROLLBACK command does nothing.

The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

The second ROLLBACK command replays the delete.

The second ROLLBACK command undoes the insert.

Q124. Which two statements are true regarding constraints? (Choose two.)

All constraints can be defined at the table or column level.

A constraint can be disabled even if the constrained column contains data.

A column with a UNIQUEconstraint can contain a NULL value.

A column with a FOREIGN KEYconstraint can never contain a NULL value.

Constraints are enforced only during INSERToperations.

Q125. Which three statements are true about external tables?

They can be used In queries containing sorts.

They can be used in queries containing Joins.

They can be indexed.

DML statements can modify them.

They can be temporary tables.

Their metadata Is stored In the database.

Q126. Which two statements are true?

AIl conditions evaluated using DECODE can also be evaluated using CASE.

All conditions evaluated using CASE can also be evaluated using DECODE.

CASE is a function and DECODE is not.

DECODE is a function and CASE is not.

Neither CASE nor DECODE is a function.

Both CASE and DECODE are functions.

Q127. Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? (Choose three.)

Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCTkeyword.

Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEYand the PRIMARY KEYcolumns are not referenced in the defining query of the view.

Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

The WITH CHECKclause has no effect when deleting rows from the underlying table through the view.

Insert statements can always be done on a table through a view.

Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

Oracle 1z0-071 Real Exam Questions and Answers FREE [Q107-Q127]

Career opportunities after getting ORACLE 1Z0-071 Certification