Exit demo 1Z0-071 Oracle Database SQL PDF format · free preview

Oracle 1Z0-071 - Questions & Answers

Free preview · every answer includes a full explanation

Product page: https://prepkeys.com/1z0-071.html

Question 1
Multiple choice

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

A.

Only the primary key can be defined at the column and table level.

B.

The foreign key columns and parent table primary key columns must have the same names.

C.

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

D.

A table can have only one primary key but multiple foreign keys.

E.

Primary key and foreign key constraints can be defined at both the column and table level.

F.

A table can have only one primary key and one foreign key.

G.

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

Question 2
Multiple choice

Which three statements are true about GLOBAL TEMPORARY TABLES?

A.

GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

B.

A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

C.

A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

D.

A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.

E.

Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

F.

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

Question 3
Multiple choice

Examine the description of the PRODUCTS table Which contains data:

Which two are true?

A.

The PROD_NAME column cannot have a DEFAULT clause added to it.

B.

The EXPIRY_DATE column cannot be dropped.

C.

The EXPIRY_DATE column data type can be changed to TIMESTAMP.

D.

The PROD_ID column can be renamed.

E.

The PROD_ID column data type can be changed to VARCHAR2(2).

Question 4
Multiple choice

Which three statements are true about a self join?

A.

It must be an inner join.

B.

It can be an outer join.

C.

The ON clause must be used.

D.

It must be an equation.

E.

The query must use two different aliases for the table.

F.

The ON clause can be used.

Question 5
Multiple choice

Which two statements are true about Oracle synonyms?

A.

A synonym can have a synonym.

B.

A synonym has an object number.

C.

Any user can create a public synonym.

D.

All private synonym names must be unique in the database.

E.

A synonym can be created on an object in a package.

Question 6
Single choice

Which statement is true about aggregate functions?

A.

The AVG function implicitly converts NULLS to zero

B.

The MAX and MIN functions can be used on columns with character data types

C.

Aggregate functions can be used in any clause of a SELECT statement

D.

Aggregate functions can be nested to any number of levels

Question 7
Multiple choice

Which two statements are true about the COUNT function?

A.

It can only be used for NUMBER data types.

B.

COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column

C.

COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

D.

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

E.

COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

Question 8
Multiple choice

In Which three situations does a new transaction always start?

A.

When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement
was issued in the same session

B.

When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session

C.

When issuing a TRUNCATE statement after a SELECT statement was issued in the same session

D.

When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

E.

When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

F.

When issuing a DML statement after a DML statement filed in the same session.

Question 9
Multiple choice

You execute these commands:

CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));
INSERT INTO customers VALUES (1`Custmoer1 `);

SAVEPOINT post insert;

INSERT INTO customers VALUES (2, 'Customer2 `);
<TODO>

SELECTCOUNT (*) FROM customers;

Which two, used independently, can replace <TODO> so the query retums 1?

A.

ROLLBACK;

B.

COMMIT;

C.

ROLIBACK TO SAVEPOINT post_ insert;

D.

CONOIT TO SAVEPOINT post_ insert;

E.

ROLLEBACK TO post_ insert;

Question 10
Multiple choice

Examine this business rule:

Each student can work on multiple projects and each project can have multiple students.

You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format:

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK

Which two statements are true? (Choose two.)

A.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

B.

STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.

C.

An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the students and projects entities.

D.

The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.

E.

The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

Showing 10 of 415 questions · Unlock the full set