Free Practice Questions for Oracle 1Z0-909 Exam

Question 1

You are using buffered queries with PHP mysqli in a browser-based web application. Which three are true?

AAdditional queries on the same session are blocked until the result set is released.

BResults are sent from the server to the browser for buffering.

CBuffered queries are enabled by default.

DBuffered queries should be used on large tables when the result size is unknown.

EResults are sent to the calling PHP process for buffering.

FBuffered queries must be explicitly enabled using mysqliuseresult.

GLarge results can have a negative impact on performance.

Correct Answer: D. Buffered queries should be used on large tables when the result size is unknown.; F. Buffered queries must be explicitly enabled using mysqliuseresult.; G. Large results can have a negative impact on performance.

Question 2

Which statement is true about the show errors command?

AIt displays the total number of errors, warnings, and notes since the beginning of the current session.

BIt displays the total number of errors, warnings, and notes since the server last restarted.

CIt cannot display information for more than max_errot_count server system variable setting.

DIt displays errors messages only, since the start time of the current session.

EIt displays errors messages only, since the server last restarted.

FIt displays similar diagnostics results as get diagnostics.

Correct Answer: A. It displays the total number of errors, warnings, and notes since the beginning of the current session.

Question 3

Examine these statements and output:

Now, examine this command:

Mysql> ROLLBACK;

What is true about the effect of the command?

AIt undoes the update command.

BIt returns an error because there is no active transaction.

CIt undoes the insert command.

DIt undoes both insert and update commands.

EIt has no effect.

Correct Answer: C. It undoes the insert command.

Question 4

Examine this statement and output:

You execute this statement:

SELECT JSON_SEARCH(product,'one','IT') FROM fshop ;

What is the output?

Aproduct->''$.varieties [4]. origin[1];;

B''$.varieties[4]. origin[1]''

Cproduct->varieties[3]. origin[0]''

D''$.varieties [3]. origin[0]''

Correct Answer: A. product->''$.varieties [4]. origin[1];;

Question 5

Examine this statement which has executed successfully:

AExecution performance can be improved by using like instead of RLIKE.

BThe statement takes advantage of index description_idx.

CExecution performance can be improved by, using a composite index with column description as the leftmost prefix column description.

DNo index will improve statement performance.

EExecution performance can be improved by adding an index on column description.

Correct Answer: A. Execution performance can be improved by using like instead of RLIKE.

Question 6

You must reclaim memory used by a prepared statement named prep. Which two achieve this?

ASET @a = ''; EXECUTE prep USING @a;

BDEALLOCATE PREPARE prep?

CDROP PROCEDURE prep;

DSET @prep = NULL;

EDROP PREPARE prep;

FPREPARE prep FROM '';

Correct Answer: C. DROP PROCEDURE prep;; D. SET @prep = NULL;

Question 7

Examine these statements:

SET collation_connection=utf8mb4_0900_as_cs;

SELECT STRCMPCAlice', UCASE ('Alice* )) ;

What is displayed?

A0

BERROR: 1267 (HYOOO): Illegal mix of collations

C-1

DNULL

E1

Correct Answer: C. -1

Question 8

Examine these commands and output:

Which is true?

AExisting emp_vuL is dropped and a new emp_vu1 created with the new definition.

BA new view is created because the previous was dropped on execution of the drop table statement.

CIt returns an error because the CREATE TABLE statement automatically recreated the view.

DIt returns an error because the DROP TABLE statement did not drop the view.

Correct Answer: D. It returns an error because the DROP TABLE statement did not drop the view.

Question 9

The meeting table stores meeting schedules with participants from five continents. The participants' details are stored in another table.

You need to adjust the start_time and duration columns for optimal storage. What datatype changes would achieve this?

Astart_time TIMESTAMP duration TIMESTAMP

Bstart_time TIMESTAMP duration TIME

Cstart_time DATETIME duration DATETIME

Dstart_time TIME duration TIME

Estart__time DATETIME duration TIME

Correct Answer: C. start_time DATETIME duration DATETIME

Question 10

Examine this statement and output:

CREATE TABLE geom (g GEOMETRY NOT NOLL, SPATIAL INDEX(g) ) ; Query OK, 0 rows affected, 1 warning (0.01 sec)

An attempt is made to add an SRID attribute to the column using the statement:

ALTER TABLE geom MODIFY COLUMN g geometry NOT NULL SRID 0;

Which is true?

AAn error is generated because srid o is an invalid identifier value.

BExecution succeeds with a warning.

CAn error is generated because the index prevents changes to the column.

DExecution succeeds and allows the use of the index by the optimizer.

Correct Answer: C. An error is generated because the index prevents changes to the column.