Oracle Certified Professional, MySQL 5.6 Developer

QUESTION NO: 1
Given the data from table t1:

This DELETE command is executed:
DELETE FROM t1 ORDER BY b.a DESC LIMIT 2;
Which set of rows will be deleted by the command?

A. (7,1) and (10,8) B. (2,8) and (10,8) C. (2,8) and (1,4) D. (7,1) and (1,4)

Correct Answer: D

QUESTION NO: 2
You execute this EXPLAIN statement for a SELECT statement on the table named comics.which contains 1183 rows:
Mysql> explain select comic_ title, publisher from comics where comic_title like '& Action&';

row in set (0.00 sec)
You create the following index:
CREATE INDEX cimic_title_idx ON comics (comic_title, publisher);
You run the same EXPLAIN statement again;
Mysql > explain select comic_title ,publisher from comics where comic_title like '& Action&';

1 row in set (0.00 sec)
Why did the second SELECT statement need to read all 1183 rows in the index comic_title_idx?

A. Because comic_title is not the primary key B. Because a LIKE statement always requires a full tables scan C. Because a wildcard character is at the beginning of the search word D. Because comic _title is part of acovering index

Correct Answer: D

QUESTION NO: 3
A MySQL command- line client is started with safe updates disabled.
Mysql - -safe - updates=0
What happens when you execute an UPDATE statement without a WHERE clause?

A. Causes a syntax error B. Results in an error C. Results in - -safe-updates being enabled automatically D. Updates every row in the specified table(s)

Correct Answer: D

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 4
Consider the my_table table with these contents:

You execute:
SELECT 1 FROM my_table;
What is the result?

A. A single value: 1 B. Two values: 1, 1 C. Two values: 11, 13 D. An error message E. Two values: 11, 12

Correct Answer: B

QUESTION NO: 5
You have two tables:
CREATE TABLE department (
Department_ID int unsigned NOT NULL auto_increment PRIMARY KEY,
Department _Name varchar(12) NOT NULL
) ENGINE=InnoDB
CREATE TABLE employee (
Employee_Number int unsigned NOT NULL PRIMARY KEY,
Employee_Name varchar(10) NOT NULL,
Department_ID int unsigned DEFAULT NULL,
FOREIGN KEY (Department ID) REFERENCES Department (Department_ID)
ON UPDATE SET NULL ON DELETE CASCADE
)ENGINE= InnoDB
The tables have the data:
Department

You execute the statement:
REPLACE INTO department (Department_ID, Department_Name) VALUES (1, 'Admin');
What data is in the employee table after the statement?

A. Option A B. Option C C. Option D D. Option B

Correct Answer: B

QUESTION NO: 6
A floating- point column defined as FLOAT(7,5)allows___________

A. 7 digits to the left of the decimal point and 5 digits to the right B. 5 digits to the left of the decimal point and 7 digits to the right C. 7 digits in total, of which 5 are to the left of the decimal point D. 7 digits in total, of which 5 are to the right of the decimal point

Correct Answer: D

QUESTION NO: 7
Consider the content of the class and student tables:
Class

Which three queries produce the same result?

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

Correct Answer: A,B,E

QUESTION NO: 8
You have two test tables:
Code_innodb as InnoDB engine
Code_ myisam as MYISAM engine
The tables have the same structure:

The tables have one row of data:

You execute an INSERT statement on both code_myisam tables and receive duplicate key errors:
mysql> INSERT INTO code_innodb VALUES (1, 'Alpha'), (2, 'Beta'), (3, 'charlie,),(4, 'Delta');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
Mysql> INSERT INTO code_myisam VALUES (1, 'Alpha'), (2, 'Beta'), (3, 'charlie'),
(4, 'Delta');
ERROR 1062 (23000); Duplicate entry '3' for key 'PRIMARY'
What is the expected output of the SELECT statements?

A. Option A B. Option C C. Option D D. Option B

Correct Answer: A

QUESTION NO: 9
Consider the table structure shown by this output:
Mysql> desc city:

5 rows in set (0.00 sec)
You execute this statement:
SELECT -,-, city. * FROM city LIMIT 1
What is returned?

A. One row with 10 columns B. One row with 5 columns C. One row with 15 columns D. An error message

Correct Answer: D

QUESTION NO: 10
Consider a table my_table , with contents shown:

You execute:
SELECT a b, b a
FROM my_table
WHERE a < s
ORDER BY b;
What does this statement return?

A. Option A B. Option C C. Option D D. Option B

Correct Answer: C

Oracle Certified Professional, MySQL 5.6 Developer - 1z0-882 Exam Questions