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

Question No: 21

MultipleChoice

Choose two

Examine the description of the PRODUCT DETALS table:

Options

APRODUCT_ID can be assigned the PEIMARY KEY constraint.

BEXPIRY_DATE cannot be used in arithmetic expressions.

CEXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

DPRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

EPRODUCT_PRICE contains the value zero by default if no value is assigned to it.

FPRODUCT_NAME cannot contain duplicate values.

Question No: 22

MultipleChoice

Which two statements are true about single-row functions? (Choose two.)

Options

ACEIL: can be used for positive and negative numbers

BFLOOR: returns the smallest integer greater than or equal to a specified number

CTRUNC: can be used with NUMBER and DATE values

DCONCAT: can be used to combine any number of values

EMOD: returns the quotient of a division operation

Question No: 23

MultipleChoice

Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

Options

AA sequence can issue duplicate values

BA sequence's unallocated cached value are lost if the instance shuts down

CSequences can always have gaps

DTwo or more tables cannot have keys generated from the same sequence

EA sequence can only be dropped by a DBA

FA sequence number that was allocated can be rolled back if a transaction fails

Question No: 24

MultipleChoice

Examine this statement:

What is returned upon execution?

Options

A1 row

Ban error

C0 rows

D2 rows

Question No: 25

MultipleChoice

Which three are true about system and object privileges? (Choose three.)

Options

AWITH GRANT OPTION can be used when granting an object privilege to both users and roles

BAdding a primary key constraint to an existing table in another schema requires a system privilege

CAdding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege

DRevoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect

ERevoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.

FWITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Question No: 26

MultipleChoice

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval.

Examine this SQL statements:

What will be the result?

Exhibit 1.

Exhibit 2.

Options

AIt gives an error because the GROUP BY clause is not valid

BIt executes successfully but does not give the required result

CIt executes successfully and gives the required result

DIt gives an error because the ALL keyword is not valid

Question No: 27

MultipleChoice

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

Options

AImplementing a relationship between two tables might require joining additional tables

BRelating data from a table with data from the same table is implemented with a self join

CRows from unrelated tables cannot be joined

DEvery relationship between the two tables must be implemented in a join condition

EAn inner join relates rows within the same table

Question No: 28

MultipleChoice

Which two statements are true about substitution variables? (Choose two.)

Options

AA substitution variable can be used with any clause in a SELECT statement

BA substitution variable used to prompt for a column name must be enclosed in a single quotation marks

CA substitution variable prefixed with & always prompts only once for a value in a session

DA substitution variable can be used only in a SELECT statement

EA substitution variable used to prompt for a column name must be enclosed in double quotation marks

FA substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Question No: 29

MultipleChoice

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

Options

ASELECT product_id, (expiry_date -- delivery_date) * 2 FROM products;

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

CSELECT product_id, unit_price, 5 ''Discount'', unit_price + surcharge -- discount FROM products;

DSELECT product_id, unit_price, unit_price + surcharge FROM products;

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

FSELECT product_id, expiry_date * 2 FROM products;

Question No: 30

MultipleChoice

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

Options

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

BViews 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.

CViews cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.

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

EThe WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

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