Q1. Examine this table that contains over two million rows of data:

CREATE TABLE ‘news_feed’ (

.id’bigint (20) NOT NULL AUTO _INCREMENT,

.news _sources_id’varchar (11) NOT NULL,

.dataline’ datetime NOT NULL,

.headline’ varchar (256) NOT NULL,

.story’ text NOT NULL,.tag varchar (32768) DEFAULT NULL, PRIMARY KEY (‘id’)

KEY ‘dateline’ ( ‘dateline’)

)

Examine this query that returns 332 rows of date:

SELECT *

FROM news_feed

WHERE DATE(dateline)= ‘2013-01-01’

Which change would show the greatest improvement in the response time of the query?

A. Use the LIKE operator:

SELECT . . .WHERE dateline LIKE ‘2013-10-01&’

B. USE the DATEDIFF function:

SELECT . . . WHERE DATEDIFF (dateline, ‘2013-01-01’) = 0

C. Use numeric equivalents for comparing the two dates:

SELECT. . .WHERE MOD(UNIX_TIMESTAMP (dateline), 86400 =UNIX_TIMESTAMP (‘2013-01-01’)

D. Use a date range comparison:

SELECT . . . WHERE dateline >= ‘2013-01’ and dateline < ‘2013-01-02’

View Answer

Q2. You are connected to a MySQL server and using a prepared statement. You accidentally exit your session.

What will happen if you log back in to use your prepared statement?

A. The statement exists, but will need to be deallocated and re-created.

B. The statement exists, but the user variables need to be redefined.

C. The statement can be used, if the MySQL server hasn’t been restarted.

D. The statement no longer exists.

View Answer

Q3. 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

View Answer

Q4. 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)

) ENGINE=MyISAM

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?

A. The ‘warehouseitem’ table must be managed by the MySAm storage engine.

B. The ‘warehouse-table must be managed by the InnoDB storage engine.

C. The foreign key clause must be reversed: FOREIGN KEY warehouse(1)REFERENCES (warehouse-id).

D. The data types of the ‘warehouse’.’id’ and ‘ warehouseitem.warehouse_is columns must match.

E. The warehouse_id’ column must be renamed ‘id’ to match the definition on the ‘warehouse’ table.

F. A UNIQUE key must be defined for the columns (‘item_id’,’warehouse_id’).

View Answer

Q5. You want to load data directly from a file into MYSQL by using the SOURCE command. Which types of data can the file contains to perform this?

A. SQL commands

B. Comma-delimited data

C. Tab-delimited data

D. MyISAM or InnoDB data files

View Answer

Q6. 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

View Answer

Q7. 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

View Answer

Q8. Which two code samples demonstrate valid methods for working with loops?

A. DECLARE I INT DEFAULT 0;

Test_loop: LOOP SET i =i +1;

IF i> =5 THEN

LEAVE test_loop; END IF;

END LOOP test_loop;

B. DECLARE i INT DEFAULT 0; WHILE I < 5ITERATE

SET i = i +1; END WHILE;

C. DECLARE i INT DEFAULT 0; WHILE i < 5 Do

SET i = i + 1; END WHILE;

D. DECLARE i INT DEFAULT 0;

Test _loop; LOOP SET i =i +1;

IF i >=5 THEN LEAVE; END IF;

END LOOP test_loop;

View Answer

Q9. 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 1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Connection 1> START TRANSACTION;

Connection 1> SELECT * FROM t1 WHERE id =1; Connection 2> TRUNCATE TABLE t1;

What happens to the TRUNCATE TABLE command in connection 2?

A. It immediately proceeds and causes an implicit commit of the transaction in connection1.

B. It runs concurrently with the transaction in connection 1 as each connection has its own view of the data in the t1 table.

C. It blocks waiting for a metadata lock until the transaction in connection 1 ends.

D. It blocks waiting for a table lock until the transaction in connection 1 ends.

View Answer

Q10. 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. An error message

B. One row with 5 columns

C. One row with 10 columns

D. One row with 15 columns

View Answer