Exam Number: 1Z0-882
Exam Title: MySQL 5.6 Developer
Associated Certification Paths Oracle Certified Professional, MySQL 5.6 Developer
Duration: 150 minutes
Number of Questions: 86
Passing Score: 61%
Validated Against: Exams are based on General Availability (GA) versions of MySQL 5.6
Format: Multiple Choice
Complete Recommended Training
MySQL for Developers
Additional Preparation and Information
MySQL Architecture
Use MySQL client programs to interface with the MySQL Server interactively and in batch
Describe SQL Modes and their impact on behavior of MySQL
Identify characteristics which have session scope
General MySQL Syntax
Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words
Identify MySQL data type properties and appropriate usage
Recognize and use common functions and expressions for all MySQL data types
Identify and use comment syntax
Describe and utilize prepared statements
Describe transactions and transaction isolation levels and the impact they have on database behavior
Creation and Design of MySQL Schema Objects
Design and create normalized databases
Create and modify tables using appropriate data types and indexing
Describe and create table constraints enforcing data integrity
Creating and modifying views
Identify and use various methods to obtain metadata for MySQL database objects
Creation, Design and Use of MySQL Stored Programs
Describe and use triggers
Create and execute stored procedures
Create and use stored functions
Implement error handling within stored procedures
Querying for Data
Execute a basic SELECT statement
Limit rows returned by a SELECT statement
Limit columns returned by a SELECT statement
Apply sorting to SELECT statement results
Execute SELECT statements which aggregate and group data
Modifying Data
Describe and execute INSERT statements
Describe and execute REPLACE statements
Describe and execute UPDATE statements
Describe and execute TRUNCATE statements
Describe and execute LOAD DATA statements
Describe and execute DELETE statements
Joins, subqueries and UNION
Identify, describe and use JOINs in MySQL commands
Describe and utilize subqueries in MySQL commands
Perform operations using UNION clause
MySQL Application Development
Identify key characteristics, features and options for PHP, Java and .NET development using MySQL standard drivers
Write a basic Java application that uses MySQL
Write a basic PHP application that uses MySQL
Write a basic .NET application that uses MySQL
Interpret MySQL error messages
Collect available diagnostic information
Describe and use NoSQL and memcached API
Basic Optimizations
Identify statements requiring optimization
Recognize and create optimal indexes for query optimization
Recognize and fix sub-optimal SQL commands
Identify appropriate optimization strategies for InnoDB usage
Optimize performance through data normalization
QUESTION 1
The application logs contain many entries of the following:
ERROR 1153 (OSSO1): Got a packet bigger than ‘max_allowed_packet’ bytes
With two scenarios can (Hibernate this error message?
A. The application tried to INSERTa row that exceeded max_allowed_packet.
B. The network caused an error Inducing the max_allowed_packet error.
C. The application did not use the COMPRESS ()function for a large result set.
D. The application tried to SELECT many rows together that exceeded max__allowed_packet.
E. The application tried to SELECT a row that exceeded max_allowed_packet.
F. The operating system caused an error inducing the max_allowed_packet error.
Answer: D,F
Explanation:
QUESTION 2
Which two Functions can be used in a C program to retrieve information about warning?
A. mysql_info
B. mysql_error
C. mysql_warning_count
D. mysql_errno
Answer: A,B
Explanation:
QUESTION 3
You wish to create a trigger on the ‘city’ table that will check the value of the ‘District’ field before
any INSERT. The trigger needs to change it to” Unknown” for an empty string or NULL.
CREATE TRIGGER City_bi
BEFORE INSERT ON CITY
FOR EACH ROW
BEGIN
IF OLD. District IS NULL OR OLD.District= . .
THEN
SET NEW.District=’Unknown’;
END IF :
END;
Does the CREATE TRIGGER statement accomplish this goal?
A. Yes; the trigger works correctly.
B. No; FOR EACHROWis invalid syntax.
C. No; the syntax should be CREATETRIGGERcity-bi ON city BEFOREINSERT….
D. No; the OLD keyword cannot be used in an INSERT trigger.
Answer: A
Explanation:
QUESTION 4
The tab-delimited file”/tmp/people,txt contains:
1636 Carsten Pederson Denmark
4672 Kai Voigt Germany
4628 Max Mether France
This is the structure of the people table:
Mysq1> DESCRIBE people;
Which statement will load the first and last names into the Names column and the country into the
country column?
A. LOADDATAINFILE ‘/tmp/people.txt’INTOTABLEPEOPLE@First=$2.@Last=$3@Country=$4
(CONCAT (@First, ‘‘,@Last) ,@ Country)
B. LOADDATAINFILE‘/tmp/people.txt ‘ INTOTABLEPeople
@Skip=$1 , @ First=$2,@Last=$3,@ Country=4,
(CONCAT (@First, ‘‘ .@ Last) ,@ Country)
C. LOADDATAINFILE ‘/tmp/people.txt ‘INTOTABLEPeople
(@ Skip, @First , @Last, @Country
SETName=CONCAT (@First,‘‘,@Last)
D. LOADDATAINFILE‘/tmp/people,txt, INTOTABLEPeople.
(@Skip.@First, @Last,@Country)
Country=@country
E. It is not possible to load the data from the file/tmp/people.txt into the people table,as shown.
Answer: C
Explanation:
QUESTION 5
What are two ways in which normalizing your tables helps improve performance In MySQL?
A. Smaller table sizes and row lengths improve sorting operations.
B. Separate tables allow indexing more columns.
C. Fewer nullable column improve index usage.
D. Normalizing Improves the performance of innodb_file_per _table.
Answer: D
Explanation:
Exam Title: MySQL 5.6 Developer
Associated Certification Paths Oracle Certified Professional, MySQL 5.6 Developer
Duration: 150 minutes
Number of Questions: 86
Passing Score: 61%
Validated Against: Exams are based on General Availability (GA) versions of MySQL 5.6
Format: Multiple Choice
Complete Recommended Training
MySQL for Developers
Additional Preparation and Information
MySQL Architecture
Use MySQL client programs to interface with the MySQL Server interactively and in batch
Describe SQL Modes and their impact on behavior of MySQL
Identify characteristics which have session scope
General MySQL Syntax
Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words
Identify MySQL data type properties and appropriate usage
Recognize and use common functions and expressions for all MySQL data types
Identify and use comment syntax
Describe and utilize prepared statements
Describe transactions and transaction isolation levels and the impact they have on database behavior
Creation and Design of MySQL Schema Objects
Design and create normalized databases
Create and modify tables using appropriate data types and indexing
Describe and create table constraints enforcing data integrity
Creating and modifying views
Identify and use various methods to obtain metadata for MySQL database objects
Creation, Design and Use of MySQL Stored Programs
Describe and use triggers
Create and execute stored procedures
Create and use stored functions
Implement error handling within stored procedures
Querying for Data
Execute a basic SELECT statement
Limit rows returned by a SELECT statement
Limit columns returned by a SELECT statement
Apply sorting to SELECT statement results
Execute SELECT statements which aggregate and group data
Modifying Data
Describe and execute INSERT statements
Describe and execute REPLACE statements
Describe and execute UPDATE statements
Describe and execute TRUNCATE statements
Describe and execute LOAD DATA statements
Describe and execute DELETE statements
Joins, subqueries and UNION
Identify, describe and use JOINs in MySQL commands
Describe and utilize subqueries in MySQL commands
Perform operations using UNION clause
MySQL Application Development
Identify key characteristics, features and options for PHP, Java and .NET development using MySQL standard drivers
Write a basic Java application that uses MySQL
Write a basic PHP application that uses MySQL
Write a basic .NET application that uses MySQL
Interpret MySQL error messages
Collect available diagnostic information
Describe and use NoSQL and memcached API
Basic Optimizations
Identify statements requiring optimization
Recognize and create optimal indexes for query optimization
Recognize and fix sub-optimal SQL commands
Identify appropriate optimization strategies for InnoDB usage
Optimize performance through data normalization
QUESTION 1
The application logs contain many entries of the following:
ERROR 1153 (OSSO1): Got a packet bigger than ‘max_allowed_packet’ bytes
With two scenarios can (Hibernate this error message?
A. The application tried to INSERTa row that exceeded max_allowed_packet.
B. The network caused an error Inducing the max_allowed_packet error.
C. The application did not use the COMPRESS ()function for a large result set.
D. The application tried to SELECT many rows together that exceeded max__allowed_packet.
E. The application tried to SELECT a row that exceeded max_allowed_packet.
F. The operating system caused an error inducing the max_allowed_packet error.
Answer: D,F
Explanation:
QUESTION 2
Which two Functions can be used in a C program to retrieve information about warning?
A. mysql_info
B. mysql_error
C. mysql_warning_count
D. mysql_errno
Answer: A,B
Explanation:
QUESTION 3
You wish to create a trigger on the ‘city’ table that will check the value of the ‘District’ field before
any INSERT. The trigger needs to change it to” Unknown” for an empty string or NULL.
CREATE TRIGGER City_bi
BEFORE INSERT ON CITY
FOR EACH ROW
BEGIN
IF OLD. District IS NULL OR OLD.District= . .
THEN
SET NEW.District=’Unknown’;
END IF :
END;
Does the CREATE TRIGGER statement accomplish this goal?
A. Yes; the trigger works correctly.
B. No; FOR EACHROWis invalid syntax.
C. No; the syntax should be CREATETRIGGERcity-bi ON city BEFOREINSERT….
D. No; the OLD keyword cannot be used in an INSERT trigger.
Answer: A
Explanation:
QUESTION 4
The tab-delimited file”/tmp/people,txt contains:
1636 Carsten Pederson Denmark
4672 Kai Voigt Germany
4628 Max Mether France
This is the structure of the people table:
Mysq1> DESCRIBE people;
Which statement will load the first and last names into the Names column and the country into the
country column?
A. LOADDATAINFILE ‘/tmp/people.txt’INTOTABLEPEOPLE@First=$2.@Last=$3@Country=$4
(CONCAT (@First, ‘‘,@Last) ,@ Country)
B. LOADDATAINFILE‘/tmp/people.txt ‘ INTOTABLEPeople
@Skip=$1 , @ First=$2,@Last=$3,@ Country=4,
(CONCAT (@First, ‘‘ .@ Last) ,@ Country)
C. LOADDATAINFILE ‘/tmp/people.txt ‘INTOTABLEPeople
(@ Skip, @First , @Last, @Country
SETName=CONCAT (@First,‘‘,@Last)
D. LOADDATAINFILE‘/tmp/people,txt, INTOTABLEPeople.
(@Skip.@First, @Last,@Country)
Country=@country
E. It is not possible to load the data from the file/tmp/people.txt into the people table,as shown.
Answer: C
Explanation:
QUESTION 5
What are two ways in which normalizing your tables helps improve performance In MySQL?
A. Smaller table sizes and row lengths improve sorting operations.
B. Separate tables allow indexing more columns.
C. Fewer nullable column improve index usage.
D. Normalizing Improves the performance of innodb_file_per _table.
Answer: D
Explanation:
