Free Oracle 1Z0-071 Exam Dumps and 1Z0-071 Exam Questions

Question No: 1

MultipleChoice

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

ORDER_ID is the primary key in the ORDERS table and the foreign key of the ORDER_ITEMS table, whose constraint is defined with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

Options

ADELETE orders o, order_items iWHERE o.order_id = i.order_id;

BDELETEFROM ordersWHERE (SELECT order_idFROM order_items);

CDELETE ordersWHERE order_total < 1000;

DDELETE order_idFROM ordersWHERE order_total < 1000;

Question No: 2

MultipleChoice

For each employee in department 90 you want to display:

1. their last name

2. the number of complete weeks they have been employed

The output must be sorted by the number of weeks, starting with the longest serving employee

first.Which statement will accomplish this?

Options

ASELECT last_name, TRUNC( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;

BSELECT last_name, ROUND( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;

CSELECT last_name, ROUND( (SYSDATE - hire_ date) 17) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure DESC;

DSELECT last_name, TRUNC ( (SYSDATE - - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_id = 90
ORDER BY tenure DESC;

Question No: 3

MultipleChoice

Which statement falls to execute successfully?

Options

ASELECT *
FROM employees e
JOIN department d
WHERE e.department_id=d.department_id
AND d.department_id=90;

BSELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id=90;

CSELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
AND d.department_id=90;

DSELECT *
FROM employees e
JOIN departments d
ON d.departments_id=90
WHERE e.department_id=d.department_id;

Question No: 4

MultipleChoice

Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.

You have a requirement from the supplies department to give a list containing PRODUCT _ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is lessthan five.

Which two SQL statements can accomplish the task? (Choose two)

Options

ASELECT product id, quantity on hand, supplier id
FROM product information
NATURAL JOIN inventories AND quantity .on hand < 5;

BSELECT i. product id, i. quantity .on hand, pi. supplier_id
FROM product_information pi JOIN inventories i
ON (pi. product. id=i. product id) AND quantity on hand < 5;

CSELECT i. product_id, i. quantity_on hand, pi. supplier id
FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;

DSELECT i.product id, i. quantity on hand, pi. supplier id
FROM product information pi JOIN inventories i
ON (pi.product id=i. product id)WHERE quantity on hand < 5;

Question No: 5

MultipleChoice

Refer to the exhibit.

Which two queries execute successfully?

Options

ASELECT prod_id, exp_date FROM products
UNION ALL
SELECT prod_id, NULL FROM new_products;

BSELECT prod_id, prod_name FROM products
INTERSECT
SELECT 100, prod_name FROM newproducts;

CSELECT * FROM products
UNION
SELECT * FROM new_products;

DSELECT k FROM products
MINUS
SELECT prod_id FROM new_products;

ESELECT prod_id FROM products
UNION ALL
SELECT prod_id, prod_name FROM new_products;

Question No: 6

MultipleChoice

View the Exhibit and examine the structure of CUSTOMERS table.

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message 'Not Available' displayed.

Which SQL statement would produce the required result?

Options

ASELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') 'NEW CREDIT'FROM customers;

BSELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') 'NEW CREDIT'FROM customers;

CSELECT NVL(cust_credit_limit * .15), 'Not Available') 'NEW CREDIT'FROM customers;

DSELECT NVL(cust_credit_limit), 'Not Available') 'NEW CREDIT'FROM customers;

Question No: 7

MultipleChoice

You issue the following command to drop the PRODUCTS table:

SQL > DROP TABLE products;

Which three statements are true about the implication of this command? (Choose three.)

Options

AAll data along with the table structure is deleted.

BA pending transaction in the session is committed.

CAll indexes on the table remain but they are invalidated.

DAll views and synonyms on the table remain but they are invalidated.

EAll data in the table is deleted but the table structure remains.

Question No: 8

MultipleChoice

You execute the following commands:

SQL > DEFINE hiredate = '01-APR-2011'

SQL >SELECT employee_id, first_name, salary

FROM employees

WHERE hire_date > '&hiredate'

AND manager_id > &mgr_id;

For which substitution variables are you prompted for the input?

Options

Anone, because no input required

Bboth the substitution variables ''hiredate' and 'mgr_id'.

Conly hiredate'

Donly 'mgr_id'

Question No: 9

MultipleChoice

Examine the structure of the MEMBERS table:

NameNull?Type

------------------ --------------- ------------------------------

MEMBER_IDNOT NULLVARCHAR2 (6)

FIRST_NAMEVARCHAR2 (50)

LAST_NAMENOT NULLVARCHAR2 (50)

ADDRESSVARCHAR2 (50)

You execute the SQL statement:

SQL > SELECT member_id, ' ' , first_name, ' ' , last_name 'ID FIRSTNAME LASTNAME ' FROM members;

What is the outcome?

Options

AIt fails because the alias name specified after the column names is invalid.

BIt fails because the space specified in single quotation marks after the first two column names is invalid.

CIt executes successfully and displays the column details in a single column with only the alias column heading.

DIt executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Question No: 10

MultipleChoice

Examine the structure of the MEMBERS table:

You want to display details of all members who reside in states starting with the letter A followed by exactly one character.

Which SQL statement must you execute?

Options

ASELECT * FROM MEMBERS WHERE state LIKE '%A_';

BSELECT * FROM MEMBERS WHERE state LIKE 'A_';

CSELECT * FROM MEMBERS WHERE state LIKE 'A_%';

DSELECT * FROM MEMBERS WHERE state LIKE 'A%';