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

Question No: 21

MultipleChoice

Choose the best answer.

Examine the description of the EMPLOYEES table:

Which query is valid?

Options

ASELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

BSELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

CSELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

DSELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

Question No: 22

MultipleChoice

Which three are true about the CREATE TABLE command?

Options

AIt can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.

BThe owner of the table should have space quota available on the tablespace where the table is defined.

CIt implicitly executes a commit.

DIt implicitly rolls back any pending transactions.

EA user must have the CREATE ANY TABLE privilege to create tables.

FThe owner of the table must have the UNLIMITED TABLESPACE system privilege.

Question No: 23

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

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

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

MultipleChoice

Examine this statement:

What is returned upon execution?

Options

A1 row

Ban error

C0 rows

D2 rows

Question No: 27

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

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

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

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