1Z0-882 Online Practice Questions and Answers

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.

Cormcctor/Net supports a decoupled database connection mode, where a database Connection is

established only when needed.

'

Choose the three parts that are involved when working with decoupled data.

A. OpenConnect.MySql, DataTTiread.MySql, CommandExecutor.MySql

B. MySqlQueryBuIld, MySqlQuerytxer. MySq I Query Results

C. DecoupODBCConn, DecoupMyScjlruiiimanrt, DecoupMySqlRetum

D. DataSet, MySqlDataAdapter, MySql Command Builder

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;

Inspect the query:

Mysql> SELECT count (emp_no) FROM titles WHERE title = `senior staff';

How can this query be optimized?

A. The query need an index on the emp-no column.

B. The query cannot be optimized as an index is already used.

C. The query needs an index that includes the title column.

D. The query cannot be optimized as count () must read all rows.

Consider the CREATE FUNCTION statement:

CREATE FUNCTION countrycount ()

BEGIN

DECLARE count INT;

SELECT COUNT (*) INTO count FROM country;

RETURN count ;

END

What is the outcome when you try to create the function?

A. An error results as the SELECT must assign the return values to a user variable.

B. An error results as the count variable is not initialized with a value.

C. An error result as the function must be defined with the CONTAINS SQL clause.

D. An error result as the variable type returned by the function must be defined with a RETURNS clause.

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)

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 B

C. Option C

D. Option D

You have a database `dev' that contains 15 tables, all of which use the CHARACTER SET `utfs' and the COLLATION `utfs_general_ci'.

You perform the command:

ALTER DATABASE `dev' CHARACTER SET ='latin' COLLATION='latin1'_swedish_ci'

What is the result?

A. You get an error because database are not allowed to have CHARACTER SET or COLLATION attributes.

B. You get an error because the settings for CHARACTER SET and COLLATION attributes do not match the settings for the tables inside the database.

C. You get an error while trying to change from a more inclusive CHARACTER SET like `utfs to a less' inclusive CHARACTER SET like `latin'.

D. You get an error because changes to the CHARACTER SET or COLLATION attribute can happen only for empty databases.

E. The statement succeeds and new tables created in this database use the new settings as their default values.

F. The statement succeeds and all of the tables inside the database are converted to user the new settings.

You wish to create a trigger on the country table. It will populate two session variables based on the row that is deleted: @old _countryNames with the value of the Name field @old _countryName with the value of the code field

You may assume that only one row is ever deleted at a time. CREATE TRIGGER Country_ad AFTER DELETE ON Country FOR EACH ROW SET

@old _CountryName= NEW.Name,

@

old _CountryCode=NEW.Code;

What is the outcome of the CREATE TRIGGER statement?

A.

The trigger will be created successfully.

B.

An error results because the NEW keyword cannot be used in a DELETE trigger.

C.

An error results because FOR EACH ROW is invalid syntax.

D.

An error results because a BEGIN. . .END block is required.

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