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

Question No: 1

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: 2

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: 3

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: 4

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: 5

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: 6

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%';

Question No: 7

MultipleChoice

You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.

Which query will provide the required result?

Options

ASELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES;

BSELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

CSELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

DSELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY;

Question No: 8

MultipleChoice

Which two statements are true regarding roles? (Choose two.)

Options

AA role can be granted to itself.

BA role can be granted to PUBLIC.

CA user can be granted only one role at any point of time.

DThe REVOKE command can be used to remove privileges but not roles from other users.

ERoles are named groups of related privileges that can be granted to users or other roles.

Question No: 9

MultipleChoice

Which statement is true regarding external tables?

Options

AThe CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.

BThe data and metadata for an external table are stored outside the database.

CThe default REJECT LIMIT for external tables is UNLIMITED.

DORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Question No: 10

MultipleChoice

Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)

Options

AAn INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.

BAn UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.

CA DELETE FROM..... statement can remove rows based on only a single condition on a table.

DAn INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.

EA DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.

FAn UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.