New (2023) Download free 1Z0-909 PDF for Oracle Practice Tests [Q24-Q44]

Rate this post

New (2023) Download free 1Z0-909 PDF for Oracle Practice Tests

100% Free 1Z0-909 Files For passing the exam Quickly

Oracle 1Z0-909 Exam Syllabus Topics:

Topic	Details
Topic 1	Use MySQL Shell to access document stores Retrieve data from the database by using a connector
Topic 2	Understand locking mechanisms within MySQL Control transactions in applications
Topic 3	Explain the programming constructs used in stored routines Choose between connectors for a given application
Topic 4	Analyze queries for optimization Aggregate and summarize data Data-driven Applications
Topic 5	Create and access a document store JSON and Document Store Store and process numeric data
Topic 6	Store and process spatial data Design, create, and alter views
Topic 7	Schedule database operations Store and process temporal data

NO.24 Examine this statement and output:

Which will provide the same level of detail when the error is encountered within a stored routine?

NO.25 Examine these commands which execute successfully:
mYsql> CREATE TABLE income (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER subtotal BEFORE INSERT ON income
FOR EACH ROW SET @subtotal = subtotal + NEW.amount;
Which is true for the income table?

The trigger activates after any row has been inserted into the table.

The trigger body set causes trigger activation.

The trigger activates after any row in the table has been updated.

Execution of an insert statement causes the trigger to activate.

NO.26 Examine these statements and output:

Now, examine this command:
Mysql> ROLLBACK;
What is true about the effect of the command?

It undoes the update command.

It returns an error because there is no active transaction.

It undoes the insert command.

It undoes both insert and update commands.

It has no effect.

NO.27 Which two are true about MySQL connectors?

Connectors must be installed on both the client and server hosts.

Connector/J is based on Connector/C.

Connector/ODBC is available on Unix, Windows, and MacOS X.

Connector/NET runs on the Windows platform only.

Connector/Python is released in precompiled binary and source code.

NO.28 Examine these statements:
SET collation_connection=utf8mb4_0900_as_cs;
SELECT STRCMPCAlice’, UCASE (‘Alice* )) ;
What is displayed?

ERROR: 1267 (HYOOO): Illegal mix of collations

-1

NULL

NO.29 Your program which uses a MySQL connector receives this error:
Client does not support authentication protocol request by server
The account running the program uses caching_sha2_password.
Which two resolve this conflict?

Disable TLS/SSL authentication.

Place this in the root directory of your shell account:
[mysqld] require__secure_transport=OFF

Use blank RSA or SSL certificates.

Upgrade the connector to a version that supports caching_sha2_password.

Change the user account to use mysql_native_password.

NO.30 Examine these commands which execute successfully in the sequence shown in Sessions S1 and S2:

Now, examine this statement that execute successfully in s1:
S1> SELECT * FROM emp;
Which is true about the result of the select statement?

The inserted row is returned because the transaction is auto committed in S2.

The inserted row is not returned because the isolation level is READ COMMITTED in S2.

The inserted row is not returned because the transaction still active in s2.

The inserted row is returned because the isolation level is RPEATABLE READ in S1.

NO.31 Which two statements are true about AUTO_INCREMENT?

AUTO_INCREMENT values allocated to a transaction that is rolled back are not reused.

A table can have multiple AUTO_INCREMENT columns.

A server restart always resets the AUTO_INCREMENT value to largest value in the AUTO_INCREMENT column plus 1.

The decimal data type supports AUTO_INCREMENT.

An AUTO_INCREMENT column must be indexed.

NO.32 Examine this statement which executes successfully:
CREATE TABLE ‘fshop’ (‘product’ JSON DEFAULT NULL ) ENGINE=InnoDB;
Now, examine a json value contained in the table:
{“name” : “orange”, “varieties” : [{“VarietyName”:”Clementine”, “Origin” : [“PA”, “BU”] }, {“VarietyName”: “tangerine”, “Origin” : [“CH”,”JP”]>]> Which will cause documents to be indexed over the ‘name’ key?

ALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product-
>* S .varieties. VarietyName ‘ ) VIRTUAL, ADD KEY idx_name (name.) ;

ALTER TABLE fshop ADD COLUMN name VARCHAR(100) AS (product->’ S – varieties’ ) VIRTUAL, ADD KEY idx_name (name) ;

ALTER TABLE fshop ADD COLUMN name VARCHAR(20) AS (product->’ S – name’ ) VIRTUAL, ADD KEY idx_name (name) ;

ALTER TABLE fshop ADD COLUMN name VARCHAR(20), ADD KEY idx_name (name) ;

ALTER TABLE fshop ADD name VARCHAR(20) AS (JSON_ONQUOTE (product->”S.varieties.VarietyName”)), ADD INDEX (name);

NO.33 Examine these lines of Python code:

You must add a line of code to complete the code to return data to the variable d. Which line will do this?

d = cursor.execute(query)

d = cursor.execute(query, (hire_start, hire_end) )

d = cursor.fetch(query % (hire_start, hire_end))

d = cursor.fetch(query, (hire_start, hire_end))

d = cursor . f etchall (query)

d = cursor.fetchall(query, (hire_start, hire_end))

NO.34 Examine these statements which execute successfully:

The statements executed without exception. Which two are true?

No transaction commits.

One row is inserted into band.

Two transactions commit.

No row is inserted into band.

The transaction is rolled back to the savepoint.

NO.35 Which two statements are true about aggregate functions?

SUM () returns o if there are no rows to aggregate.

MAX () returns null if there are no rows to aggregate.

COUNT (distinct) returns a count of the number of rows with different values including Null.

MIN () cannot use distinct when it executes as a Windows function.

AVG () does not allow use of the distinct option.

NO.36 Examine this statement and output:

Now, examine these desired output values:

Which statement updates the table data as require

UPDATE exam_result SET score=TRUNCATE

UPDATE examresult SET score=ROUND(CEIL(score

UPDATE exam_result SET score=CEIL(TRUNCATE(sco

UPDATE exam_result SET score=CEIL(ROUND(score,1));

UPDATE exam_result SET score=ROUND(score,1);

UPDATE exam_result SET score=TRUNCATE(score,1);

NO.37 Which select statement returns true?

SELECT NULL <> NULL;

SELECT NULL <=> NULL;

SELECT NULL = NULL;

SELECT NULL := NULL;

NO.38 Examine the contents of these tables:

Now examine the expected results for a user with privileges to access the table:

Which query returns the expected results?
A)

B)

C)

D)

Option A

Option B

Option C

Option D

NO.39 Which statement is true about the show errors command?

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

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

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

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

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

It displays similar diagnostics results as get diagnostics.

NO.40 Examine these statements issued from Session 1 which execute successfully:

Now, examine this statement issued from Session 2:

What is the outcome of the update statement in Session 2?

The row will be updated immediately.

The statement will wait for the transaction in Session 1 to finish.

A deadlock will occur.

The transaction in Session 1 will be rolled back automatically.

NO.41 Examine this statement that execute successfully in an interactive session:

The user running this session now goes to lunch for an hour.
Now, examine these statements executed independently in separate sessions while Session 0 is still active:

How many of them will complete while Session 0 is still active?

NO.42 The collection col contains all episodes for all seasons for a TV show.
Examine this document which has an example of the details for each episode:

Which query returns all expisode names from the first season?

SELECT doc->”$.name” FROM col WHERE doc->”$.season” = “1”;

SELECT “S.name” FROM col WHERE “S.season” = “1”;

SELECT doc->,$.name,, FROM col WHERE doc->”$ . season” = “1”;

SELECT name FROM col WHERE season = 1;

NO.43 Examine this statement which has executed successfully:

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

The statement takes advantage of index description_idx.

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

No index will improve statement performance.

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

NO.44 Examine these commands which execute successfully in the sequence shown in Sessions S1 and S2:

Now, examine this statement that execute successfully in s1:
S1> SELECT * FROM emp;
Which is true about the result of the select statement?