1Z0-882 Online Practice Questions and Answers

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

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

CALL sum_t1();

What is the result of the CALL statement?

A. The procedure completes, and 15 is returned

B. The procedure's IF condition is not satisfied, and 0 is returned.

C. The procedure's loop is not entered, and 1 is returned.

D. An infinite loop will be running until the command is killed.

You try to add a foreign key to the InnoDB table employees:

Mysq1> ALTER TABLE employees ADD FOREIGN KEY (Department_ID) REFERENCES departments (Department_ID); ERROR 1215 (HY000): cannot add foreign key constraint

Which command will provide additional information about the error?

A. SHOW ERRORS

B. Error 1215

C. SHOW ENGINE INNODB STATUS

D. SELECT FROM information_schema.INNODB_SYS_FOREIGN

The data from t1 table is:

Assuming You want to see this output: Which query achieves the preceding result?

A. SELECT name FROM t1 WHERE name LIKE ,_e%

B. SELECT name FROM t1 WHERE name LIKE,e%.;

C. SELECT name FROM t1 GROUP BY name ORDER by name LIMIT 1,1;

D. SELECT name FROM t1 GROUP BY name HAVING sun ( marks)=176 ORDER BY name;

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.

Your application is running slow.

Which two features provide information that help to identify problems?

A. The MYSQL error log

B. The slow query log

C. The performance schema

D. The GET DIAGNOSTICS statement

A complex query consists of eight populated tables that are all connected via INNER JOIN operands as shown:

You modify the query and replace the SELECT operand with SELECT STRAIGHT JOIN. What is the effect of adding STRAIGHT JOINs to the query?

A. The optimizer processes only the JOINs in the sequence listed in the query.

B. The optimizer will only JOIN the tables by using their PRIMARY KEYS or UNIQUE constraints.

C. The optimizer will only JOIN the tables in sequence from smallest to largest.

D. The optimizer ignores all terms in the WHERE clause until all JOINs have been completed.

Which there statements describe valid reasons why queries that use "SELECT" construct are discouraged?

A. SELECT * may cause more data than you need to be read from disk if your application needs only some columns.

B. SELECT * causes more data than you need to be sent via the client/server protocol if your application needs only some columns.

C. SELECT * prevents the use of indexes, so a full table scan for every query.

D. SELECT *causes your application to depend on the columns present when you wrote it , so your application could break if the table structure changes.

E. SELECT * causes the statements to return all rows from the table.

Consider the my_table table with two integer columns, a and b, and the contents as shown; Mysql > SELECT a, b FROM my_table;

1 row in set result of this query?

SELECT a--b

FROM my_table;

A. 0

B. 2

C. 4

D. An error message

Consider the structures of the country and countrylanguage tables.

mysql >DESCRIBE country;

mysql> DESCRIBE countrylanguage;

Which query will give you the list of all European countries where German is spoken?

A. SELECT Code AS c, Name FROM Country WHERE Continent = `Europe' AND EXISTS ( SELECT * FROM CountryLanguage WHERE CountryCode = Code And Language= `German' )

B. SELECT Code AS c, Name FROM Country WHERE Continent = `Europe' AND Name IN ( SELECT * FROM CountryLanguage WHERE CountryCode = Code AND Language ='German' )

C. SELECT Code AS c, Name FROM Country WHERE Continent = ` Europe' AND EXIST ANY ( SELECT Language, CountryCode FROM CountryLanguage WHERE CountryCode =Code AND Language = `German' )

D. SELECT Code AS c, Name FROM Country WHERE Continent = `Europe' AND ( SELECT * FROM CountryLanguage WHERE CountryCode =Code AND Language ='German' )

Examine the structure and content of the MemberLocation table:

You want to have the field location returned in all letters (example: BERLIN). Which query would you use?

A. SELECT UPPER (Location) as location FROM MemberLocation

B. SELECT UPPER (BINARY location) as location FROM MemberLocation

C. SELECT UPPER (location AS UPPER ) as location FROM Memberlocation

D. SELECT CONVERT (Location AS UPPER ) as location FROM memberlocation

When working with stored routines, these details are available:

The affected rows count

The number of conditions that occurred

The condition information, such as the error code and message

Where can you find these default?

A. In the Handler area, defined in the DECLARE handler_action HANDLER block in a stored routine

B. In the Signal area, which is set with the help of the SIGNAL statement in a stored routine

C. In the Diagnostics area, part, of which can be stored in user-defined or routine variables

D. In the Error area, which can be accessed with the help of the SHOW ERRORS statement

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 (warehouseid).

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

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?

A. You can do this one command: ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_german_ci

B. You can only do this with two separate commands: ALTER DATABASE `test' CHARACTER SET latin1 ALTER DATABASE `test' COLLATE latin_german1_ci

C. You cannot change the CHARACTER set or COLLATION value on an existing database.

D. Databases do not have CHARACTER SET or COLLATION attributes.

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