1z0-071 sample questions

Question 1

View the Exhibit and examine the structure of the SALES table.

The query below is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 items.

Which statement is true regarding this SQL statement?

Accepted Answer

It produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.

Answer

It executes successfully and generates the required result.

Answer

It produces an error because COUNT (*) should be specified in the SELECT clause also.

Answer

It executes successfully but produces no result because COUNT(prod_ID) should be used instead of COUNT(*).

Question 2

Evaluate the query below. What would be the outcome of the above query?

Accepted Answer

It executes successfully and introduces an's at the end of each promo_name in the output.

Answer

It produces an error because the data types are not matching.

Answer

It executes successfully and displays the literal" {'s start date was \> " for each row in the output.

Answer

It produces an error because flower braces have been used.

Question 3

Examine the structure of the PROMOTIONS table below. Management requires a report of unique promotion costs in each promotion category. Which query would satisfy this requirement?

Accepted Answer

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Answer

SELECT promo_category, DISTINCT promo_cost FROM promotions;

Answer

SELECT DISTINCT promo_cost, promo_category FROM promotions;

Answer

SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Question 4

You need to display the date 11 -oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

Accepted Answer

SELECT TO_CHAR(TO_DATE(’11-oct-2007’), ‘fmDdthsp “o f Month, Year’) FROM DUAL;

Answer

SELECT TO_CHAR(‘ 11 -oct-2007’, ‘fmDdspth “of’ Month, Year’) FROM DUAL;

Answer

SELECT TO_CHAR(TO_DATE(‘11 -oct-2007’), 'fmDdspth of month, year’) FROM DUAL;

Answer

SELECT TO_DATE(TO_CHAR(’11-oct-2007’), ‘fmDdspth “of’ Month, Year’)) FROM DUAL;

Question 5

An international logistics company, GlobalShip, needs to consolidate its regional shipment data. The SHIPMENTS table contains all current shipment records. A staging table, INBOUND_SHIPMENTS, is loaded daily with new records and updates from regional offices. The SHIPMENT_ID column uniquely identifies each shipment.

A database developer must write a single, atomic SQL statement to synchronize the SHIPMENTS table. The logic must be as follows:

For SHIPMENT_IDs present in both tables, the STATUS and EXPECTED_DELIVERY date in the SHIPMENTS table must be updated to match the staging table's values.
For SHIPMENT_IDs present in the staging table but not in the main SHIPMENTS table, a new record must be inserted.
The statement must be efficient and handle large volumes of data without requiring multiple table scans.

Below is a diagram of the tables involved.
Which SQL query correctly implements this synchronization logic?

erDiagram SHIPMENTS { INTEGER SHIPMENT_ID PK VARCHAR STATUS DATE EXPECTED_DELIVERY VARCHAR ORIGIN VARCHAR DESTINATION } INBOUND_SHIPMENTS { INTEGER SHIPMENT_ID PK VARCHAR STATUS DATE EXPECTED_DELIVERY VARCHAR ORIGIN VARCHAR DESTINATION } SHIPMENTS ||--|{ INBOUND_SHIPMENTS : "synchronizes with"

Accepted Answer

MERGE INTO shipments s
USING inbound_shipments i ON (s.shipment_id = i.shipment_id)
WHEN MATCHED THEN
UPDATE SET s.status = i.status, s.expected_delivery = i.expected_delivery
WHEN NOT MATCHED THEN
INSERT (shipment_id, status, expected_delivery, origin, destination)
VALUES (i.shipment_id, i.status, i.expected_delivery, i.origin, i.destination);

Answer

UPDATE shipments s SET (status, expected_delivery) = (SELECT i.status, i.expected_delivery FROM inbound_shipments i WHERE i.shipment_id = s.shipment_id);
INSERT INTO shipments SELECT * FROM inbound_shipments WHERE shipment_id NOT IN (SELECT shipment_id FROM shipments);

Answer

INSERT ALL
WHEN shipment_id IN (SELECT shipment_id FROM shipments) THEN
INTO shipments (status, expected_delivery) VALUES (status, expected_delivery)
ELSE
INTO shipments (shipment_id, status, expected_delivery, origin, destination) VALUES (shipment_id, status, expected_delivery, origin, destination)
SELECT * FROM inbound_shipments;

Answer

BEGIN
FOR rec IN (SELECT * FROM inbound_shipments) LOOP
UPDATE shipments SET status = rec.status, expected_delivery = rec.expected_delivery WHERE shipment_id = rec.shipment_id;
IF SQL%NOTFOUND THEN
INSERT INTO shipments VALUES (rec.shipment_id, rec.status, rec.expected_delivery, rec.origin, rec.destination);
END IF;
END LOOP;
END;

Question 6

Examine the structure of the MEMBERS table below.

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;

What would be the result execution? (Choose the best answer.)

Accepted Answer

It displays all cities in ascending order, within which the last names are further sorted in descending order.

Answer

It displays all cities in descending order, within which the last names are further sorted in descending order.

Answer

It fails because a column alias cannot be used in the ORDER BY clause.

Answer

It fails because a column number and a column alias cannot be used together in the ORDER BY clause.

Question 7

View the exhibit and examine the structure of the CUSTOMERS table. Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

Accepted Answer

finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

Accepted Answer

listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Answer

finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

Answer

listing of customers who do not have a credit limit and were born before 1980

Answer

finding the number of customers, in each city, whose marital status is 'married'.

Question 8

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

Accepted Answer

During execution, the Oracle server may read data from storage if the required data is not already in memory.

Answer

During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.

Answer

During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

Answer

During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.