Oracle Certified Professional, MySQL 5.6 Developer v7.0 (1z0-882)

Page:    1 / 7   
Total 100 questions

As a developer, you inherit this table as part of a project:
CREATE TABLE exam (
Exam_id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
Examinee_id INTEGER UNSIGNED UNIQUE,

Score INTEGER UNSIGNED -
What change should you make to ensure that examinee_id is an integer value throughout the table?

  • A. The examinee_id column should be designated as PRIMARY KEY.
  • B. A NOT NULL qualifier should be moved from exam-id to examinee-id.
  • C. The PRIMARY KEY should be dropped and re-created as PRIMARY KEY (examinee-id, exam_id).
  • D. A NOT NULL qualifier should be added to examinee_id.


Answer : D

Which two can be used to obtain information stored in the Diagnostics Area?

  • A. SHOW WARNINGS
  • B. GET DIAGNOSTICS CONDITION 1 @errno=MYSQL_ERRNO, @msg =MESSAGE_TEXT; SELECT @errno, @msg;
  • C. SELECT ERRNO, MESSAGE_TEXT FROM INFORMATION_SCHEMA.DIAGNOSTICS_AREA WHERE CONDITION=1
  • D. SHOW GLOBAL STATUS LIKE ‘Diagnostics’


Answer : A,B

Reference: http://dev.mysql.com/doc/refman/5.6/en/diagnostics-area.html

You want to use the SHA -256 Authentication plugin with Connector/J.
Which two parameter settings achieve this?
A.
Authenticationplugins=com.mysql.jdbl.jdbc.authentication.sha256passwordplugin,com.mys ql.jdbc
B. Authenticationplugins=com,mysql,authentication,mysqlNativepasswordplugin
C. defaultAuthenticationplugin=com.mysql.jdbc.authentication.sha256passwordplugin
D. defaultAuthenticationplugin=com.mysql.jdbc.authentication.MysqlNativepasswordplugin



Answer : A

Given the data:


Expected output:

Which query produces the expected output?

  • A. SELECT colors2.name, colors1.name FROM colors2 OPTIONAL JOIN colors1 ON colors2.name, colors1.name
  • B. SELECT colors2.name, colors1.name FROM colors2 NATURAL JOIN colors1 ON colors2.name=colors1.name
  • C. SELECT colors2.name, colors1.name FROM colors2 STRAIGHT JOIN colors1 ON colors2.name, =colors1.name
  • D. SELECT colors2.name,colors1.name FROM colors2 LEFT JOIN colors1 ON colors2.name=colors1.name
  • E. SELECT colors2.name,colors1.name FROM colors2 RIGHT JOIN colors1 ON colors2.name=colors1.name


Answer : E

You have a transaction that queries a table at the beginning of the transaction and performs the same query later.
Which two transaction isolation levels guarantee that you get the same results both times?

  • A. Repeatable read
  • B. Read committed
  • C. Read uncommitted
  • D. Single user
  • E. serializable


Answer : A,E

Reference: http://dev.mysql.com/doc/refman/5.0/en/commit.html

Inspect the SELECT query:
Mysql> EXPLAIN SELECT employees. Emp_no, first_name, last_name FROM employees
JOIN title WHERE to_date > 2008-10-11;


2 rows in set (0.00 sec)
Which action will optimize the query?

  • A. Add an index to the employees. emp _no column.
  • B. Add the keyword STRAIGHT_JOIN.
  • C. Add an index on the to_ date column.
  • D. Add the FORCE INDEX (PRIMARY) optimizer hint for the employees table.


Answer : C

Consider the query and its output:
Mysql> SELECT Language As Lang FROM countrylanguage
->WHERE countrycode =GBR;


A user wants to have an output as shown:

What query would achieve this?

  • A. SELECT GROUP_IMPLODE (Language) As Lang FROM countrylanguage WHERE countrycode= GBR;
  • B. SELECT GROUP_CAT(Language) As Lang FROM countrylanguage WHERE countrycode=GBR;
  • C. SELECT GROUP_CSV(Language) As Lang FROM countrylanguage WHERE countrycode=GBR;
  • D. SELECT GROUP_CONCAT (Language) As Lang FROM countrylanguage WHERE countrycode=GBR;


Answer : D

Reference: http://dcsg.bcs.org/sites/default/files/mysql-certification-41addendum_0.pdf
(page 32, 1.9.)

The friends table has the columns and contents as shown:
Mysql> SELECT * FROM friends;


This statement was executed:

SELECT AVG (age) FROM friends -
What value is returned?

  • A. NULL
  • B. 10
  • C. 20
  • D. 0


Answer : C

Explanation: http://stackoverflow.com/questions/14020924/strange-mysql-avg-anomaly- null-values

Consider the statement:
CREATE TABLE t1 (a INT) PARTITION BY KEY
/*150611 ALGORITHM = 1*/
What does this statement do?

  • A. Create the t1 table partitioned by KEY with the default algorithm in all versions.
  • B. Create the t1 table partitioned by KEY using algorithm 1 only in MYSQL version 5.6.11 and the default algorithm in other versions.
  • C. Create the t1 table partitioned by KEY using algorithm 1 only in MYSQL versions 5.6.11 or newer and the default algorithm in older versions.
  • D. Create the t1 table partitioned by KEY using algorithm 1 only if the preceding statement returned error condition 50611.


Answer : C

Cormcctor/Net supports a decoupled database connection mode, where a database
Connection is established only when needed.
'
Choose the three parts that are involved when working with decoupled data.

  • A. OpenConnect.MySql, DataTTiread.MySql, CommandExecutor.MySql
  • B. MySqlQueryBuIld, MySqlQuerytxer. MySq I Query Results
  • C. DecoupODBCConn, DecoupMyScjlruiiimanrt, DecoupMySqlRetum
  • D. DataSet, MySqlDataAdapter, MySql Command Builder


Answer : D

Reference: http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-data- adapter.html

Which Three options describe benefits of using the InnoDB memcached API?

  • A. Provides a simple, well supported method for accessing and updating data.
  • B. Provides a total in –memory storage system that eliminates disk1/0 overhead.
  • C. Bypasses the SQL layer thus avoiding extra processing.
  • D. Implements a fast caching mechanism to replace the query cache.
  • E. Provides protection via InnoDB buffers and crash recovery.


Answer : C,D,E

Given the table City:

SELECT Name -

FROM City -
WHERE CountryCode = USA OR WHERE CountryCode= JPN
What does this statement procedure?

  • A. A single result set with one column that contains the names of cities from country codes USA and JPN.
  • B. Two result sets each containing a single column with the names of cities from country codes USA and JPN.
  • C. A single result set with two columns containing the names from country codes USA and JPN.
  • D. No result set is returned and an error message is given.


Answer : D

Which three connector/J connection strings can be used to connect to the MYSQL server?

  • A. Jdbc:mysq1://localhost/test?user =xxx&password=xxx
  • B. Jdbc :mysqli://localhost/test?user=xxx&password=xxx
  • C. Jdbc :mysql:replication://master,slave1.slave2. /test?user=xxx&password=xxx
  • D. Jdbc:mysql:proxy://localhost/test?user=xxx&password=xxx
  • E. Jdbc :mysql:loadbalance://master.slave1,slave2/test?user=xxx&password=xxx


Answer : A,C,E

Using the query:
SELECT Code FROM country WHERE Name = united states
Which statement executed in the mysql client would create a prepared statement?

  • A. PREPARE STATEMENT countrycode FROM SELECT code FROM counyry WHERE Name =?.;
  • B. PREPARE countrycode As ‘SELECT code FROM country WHERE Name =?.;
  • C. PREPARE countrycode FROM ‘SELECT code FROM country WHERE Name =?.;
  • D. PREPARE STATEMENT countrycode As SELECT code FROM country WHERE Name =?.;


Answer : C

Reference: http://www.timlin.net/csm/cis363/mysql18.html

Consider the content of the class and student tables:

Class -


Which three queries produce the same result?

  • A. SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id
  • B. SELECT * FROM JOIN student LEFT JOIN student ON class. Class.class_id=student.class_id
  • C. SELECT * FROM class INNER JOIN student WHERE NOT ISNULL (student.class_id)
  • D. SELECT * FROM JOIN student On class .class_id=student.class_id WHERE NOT ISNULL (student.class_id)
  • E. SELECT * FROM student RIGHT JOIN class ON class.class_id=student.class_id


Answer : B,D,E

Page:    1 / 7   
Total 100 questions