1Z0-909 Actual Questions Answers PDF 100% Cover Real Exam Questions [Q24-Q43]

4.3/5 - (3 votes)

1Z0-909 Actual Questions Answers PDF 100% Cover Real Exam Questions

1Z0-909 Exam questions and answers

Oracle 1Z0-909 Exam Syllabus Topics:

Topic	Details
Topic 1	Understand locking mechanisms within MySQL Control transactions in applications
Topic 2	Create and execute stored routines MySQL Schema Objects and Data
Topic 3	Use MySQL Shell to access document stores Retrieve data from the database by using a connector
Topic 4	Create and access a document store JSON and Document Store Store and process numeric data
Topic 5	Rewrite queries for optimization Store and process string data
Topic 6	Demonstrate connector use, management and configuration Secure credentials and connections

NEW QUESTION 24
Which two are true about indexes?

Secondary index access will always be faster than a table scan.

Indexing all of a table’s columns improves performance.

Indexes contain rows sorted by key values.

Indexes reduce disk space used.

Indexes are used to enforce unique constraints.

NEW QUESTION 25
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

NEW QUESTION 26
You must write a statement that combines the first_name and last_name columns from the employees table as “last_name, first_name.” Which two statements will do this?

SELECT last_name + ‘, ‘ + first_name FROM employees;

SELECT CONCAT_WS(‘, ‘,last_name,first_name) FROM employees;

SELECT GROUP_CONCAT(last_name, first_name) FROM employees;

SELECT last_name, ‘ , ‘,first_name FROM employees;

SELECT CONCAT(last name,’, ‘,first_name) FROM employees;

NEW QUESTION 27
A server hosts MySQL Server and Apache Webserver supporting a PHP/PDO based application.
The application must be migrated from PHP to their Java application server on another host. The MySQL instance remains on the original host.
Examine the PDO connection string used in the existing application:
Mysql:host-localhost;dbname=sales;unix_socket=/var/run/mysql.sock
Which two prevent Java from using the Unix socket?

The X Dev API protocol must be enabled to use sockets in Connector/J driver.

The socket is not implemented in Connector/J driver.

The socket can only be accessed from the local host.

socket is a reserved word in Java.

Java treats the socket file as insecure.

NEW QUESTION 28
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.

NEW QUESTION 29
Examine these statement which execute successfully:

Now, examine the statements executed in the mysqi command-line client:

What is true?

NEW QUESTION 30
Examine these statements issued from Session 1 which execute successfully:

Now, examine these statements issued from Session 2 which execute successfully:
Session 2>
BEGIN;
UPDATE band SET song=CONCAT (“Here Comes the “, song) WHERE song LIKE ‘ %Sun ; Which two are true?

Session 1 takes a shared lock on all the rows in the band table.

Session 1 must commit before the update in Session 2 can complete.

Session 1 does not block Session 2.

Statements in Session 2 are committed.

Session 2 takes an exclusive lock on all the rows in the band table.

Session 2 does not start a transaction.

NEW QUESTION 31
The variables c and d are declared as integer types.
Examine these initialization statements with placeholder value <p1>, which execute successfully:
Now, examine this loop which executes successfully:

Which loop results in the same value of d for all valid values of <p1>?
A)

B)

C)

Option A

Option B

Option C

Option D

NEW QUESTION 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);

NEW QUESTION 33
Examine the layout of the my_values table.

Examine the data in the my_value3 table.

Examine this statement:

A)

B)

D)

Option A

Option B

Option C

Option D

NEW QUESTION 34
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?

start_time TIMESTAMP duration TIMESTAMP

start_time TIMESTAMP duration TIME

start_time DATETIME duration DATETIME

start_time TIME duration TIME

start__time DATETIME duration TIME

NEW QUESTION 35
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.

NEW QUESTION 36
Examine the output:

Which explain command will obtain the output?

EXPLAIN ANALYZE

EXPLAIN PARTITIONS

EXPLAIN FORMAT=TREE

EXPLAIN PORMAT^TRADITIONAL

EXPLAIN PORMAT=JSON

NEW QUESTION 37
Examine the employee table structure:

Which set of statements immediately returns empname for a given emp_id by using a parameterized prepare statement?
A)

D)

Option A

Option B

Option C

Option D

NEW QUESTION 38
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.

NEW QUESTION 39
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.

NEW QUESTION 40
Examine this bar graph based on columns from the players table:

Which two statements would generate this bar graph?

SELECT Name, Gender, Sport, REPEAT(‘# ‘Y GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;

SELECT Name, Gender, Sport, LENGTH (GPA*10, ‘# ‘) AS GPA_Graph FROM players ORDER BY GPA DESC;

SELECT Name, Gender, Sport, CHAR_LENGTH (‘# ‘ GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;

SELECT Name, Gender, Sport, RPAD (‘# ‘ GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;

SELECT Name, Gender, Sport, CONCAT (‘# ‘ GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;

NEW QUESTION 41
You must enforce data integrity for data Inserted in a JSON column.
Which statement successfully creates a constraint in a 3SON column?

CREATE TABLE fshop (product JSON CHECK (JSON_VALID(product) ) ) ;

CREATE TABLE fshop ( product JSON, f INT’ GENERATED ALWAYS AS (product->”S – id”) ) ;

CREATE TABLE fshop (id INT NOT NULL AUTOINCREMENT, product JSON, PRIMARY KEY (id)) ENGINE=InnoDB;

CREATE TABLE fshop (id INT NOT NULL AUTO_ INCREMENT, product JSON, CHECK (id>0) ) ENGXNE=InnoDB;

NEW QUESTION 42
Examine this statement and output:

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