Oracle Certified Professional, MySQL 5.6 Developer
Question No: 1
Which two Functions can be used in a C program to retrieve information about warning?
Question No: 2
Which condition must be true in order that a view is considered updateable?
The user must have the UPDATE or DELETE privilege for the underlying table.
There must be a subquery in the WHERE clause that refers to a table in the FROM clause.
There must be a one-to-one relationship between the rows in the view and the rows in the underlying table.
The view must only refer to literal values.
Reference: http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html (first para)
Question No: 3
You create a new,empty database called ‘test’. You want to change the database ‘s CHARACTER SET to “latin1” and the database ‘sCOLLATION to ‘latin_german_ci’.
Which statement is true?
You can do this one command:
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_german_ci
You can only do this with two separate commands: ALTER DATABASE ‘test’ CHARACTER SET latin1 ALTER DATABASE ‘test’ COLLATE latin_german1_ci
You cannot change the CHARACTER set or COLLATION value on an existing database.
Databases do not have CHARACTER SET or COLLATION attributes.
Question No: 4
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 (1,4)
B. (2,8) and (1,4)
C. (7,1) and (10,8)
D. (2,8) and (10,8)
Question No: 5
Assume the user has just connected to the MySQL server.
What is the result of the query SELECT @ a?
An error that @ a is undefined
A single NULL
An empty string
The value of GLOBAL variable @ a
Question No: 6
You attempt to create two new tables: CREATE TABLE ‘warehouse’ (
‘id’ int (11) NOT NULL AUTO_INCREMENT,
‘name’ varchar (20) NOT NULL, ‘phone’ varchar (20) NOT NULL, PRIMARY KEY (‘ id)
CREATE TABLE ‘warehouseitem’ ( ‘warehouse_id’ bigint (11) NOT NULL, ‘item_id’ int (11) NOT NULL,
‘count’ int(11) NOT NULL DEFAULT ‘0’,
KEY “warehouse_id’ (‘warehouse-id) ,
FOREIGN KEY (warehouse_id) REFFERENCES warehouse (id)
) ENGINE= InnoDB
You get this error :
ERROR 1215 ( HYooo): cannot add foreign key constraint
Which two changes are required to permit these statements to execute without any error?
The ‘warehouseitem’ table must be managed by the MySAm storage engine.
The ‘warehouse-table must be managed by the InnoDB storage engine.
The foreign key clause must be reversed: FOREIGN KEY warehouse(1)REFERENCES (warehouse-id).
The data types of the ‘warehouse’.’id’ and ‘ warehouseitem.warehouse_is columns must match.
The warehouse_id’ column must be renamed ‘id’ to match the definition on the ‘warehouse’ table.
A UNIQUE key must be defined for the columns (‘item_id’,’warehouse_id’).
Question No: 7
Your application is running slow.
Which two features provide information that help to identify problems?
The MYSQL error log
The slow query log
The performance schema
The GET DIAGNOSTICS statement
Question No: 8
You create a table and a stored procedure: CREATE TABLE t1 (f1 int);
INSERT INTO t1 VALUES (1), (2) , (3), (4), (5);
CREATE PROCEDURE sum_t1() BEGIN
DECLARE done INT DEFAULT 0; DECLARE va1 INT;
DECLARE result CURSOR FOR SELECT f1 FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur; REPEAT
FETCH cur INTO va1; IF NOT done THEN
SET result = result va1; END IF:
UNTIL done END REPEAT;
SELECT result; END
What is the result of the CALL statement?
The procedure completes, and 15 is returned
The procedure’s IF condition is not satisfied, and 0 is returned.
The procedure’s loop is not entered, and 1 is returned.
An infinite loop will be running until the command is killed.
Question No: 9
Which two code samples demonstrate valid methods for working with loops?
DECLARE I INT DEFAULT 0; Test_loop: LOOP
SET i =i 1; IF igt; =5 THEN
LEAVE test_loop; END IF;
END LOOP test_loop;
DECLARE i INT DEFAULT 0;
WHILE I lt; 5ITERATE SET i = i 1;
DECLARE i INT DEFAULT 0; WHILE i lt; 5 Do
SET i = i 1; END WHILE;
DECLARE i INT DEFAULT 0; Test _loop; LOOP
SET i =i 1;
IF i gt;=5 THEN LEAVE; END IF;
END LOOP test_loop;
Question No: 10
In MYSQL 5.6 you have the table t1: CREATE TABLE t1 (
id int unsigned NOT NULL PRIMARY key) ENGINE = InnoDB; There are two connections to the server. They execute in this order:
Connection 1gt; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Connection 1gt; START TRANSACTION; Connection 1gt; SELECT * FROM t1 WHERE id =1;
Connection 2gt; TRUNCATE TABLE t1;
What happens to the TRUNCATE TABLE command in connection 2?
It immediately proceeds and causes an implicit commit of the transaction in connection1.
It runs concurrently with the transaction in connection 1 as each connection has its own view of the data in the t1 table.
It blocks waiting for a metadata lock until the transaction in connection 1 ends.
It blocks waiting for a table lock until the transaction in connection 1 ends.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|