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

Question No: 11

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

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

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

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.

Question No: 15

MultipleChoice

Evaluate the following SQL statement:

SQL> select cust_id, cust_last_name 'Last name'

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30

Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)

Options

AORDER BY 'Last name'

BORDER BY 2, cust_id

CORDER BY CUST_NO

DORDER BY 2,1

EORDER BY 'CUST_NO'

Question No: 16

MultipleChoice

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?

Options

AThe WHERE clause can be used to exclude rows after dividing them into groups

BWHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

CThe HAVING clause can be used with aggregating functions in subqueries.

DAggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.

EThe WHERE clause can be used to exclude rows before dividing them into groups.

Question No: 17

MultipleChoice

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.

Which two are true about data type conversions involving these columns in query expressions?

Options

Ainvoice_date> '01-02-2019': uses implicit conversion

Bqty_sold ='05549821 ': requires explicit conversion

CCONCAT(qty_sold, invoice_date): requires explicit conversion

Dqty_sold BETWEEN '101' AND '110': uses implicit conversion

Einvoice_date = '15-march-2019': uses implicit conversion

Question No: 18

MultipleChoice

Examine the description of the PRODUCTS table:

Which three queries use valid expressions?

Options

ASELECT produet_id, unit_pricer, 5 'Discount',unit_price+surcharge-discount FROM products;

BSELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

CSELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;

DSPLECT product_id, expiry_date * 2 FROM products;

ESELEGT product_id, unit_price, unit_price + surcharge FROM products;

FSELECT product_id,unit_price || 'Discount', unit_price + surcharge-discount FROM products;

Question No: 19

MultipleChoice

Examine the structure of the two tables.

Which two queries execute successfully? (Choose two.)

Options

AOption A

BOption B

COption C

DOption D

EOption E

Question No: 20

MultipleChoice

Which three actions can you perform on an existing table containing data? (Choose three.)

Options

AAdd a new NOT NULL column with a DEFAULT value

BChange the default value of a column

CChange a DATE column containing data to a NUMBER data type

DAdd a new column as the table's first column

EDefine a default value that is automatically inserted into a column containing nulls

FIncrease the width of a numeric column