1Z0-882 Online Practice Questions and Answers

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.

Which two PHP modules provide APIs for developing MYSQL applications?

A. Mysqli

B. Mysqlnd

C. PDO

D. PDO_mysql

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)

What is true about the contents of the INFORMATION_SCHEMATA table?

A. It contains information about the table structure for all databases.

B. It contains information about all the tables, triggers, and views for all databases.

C. It contains information such as name, character set, and collation for all the databases on the server.

D. It contains information including tables, trigger, stored routines, and views for all databases

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 have been tasked to create a database that will store a list of all managers and the employees who report directly to them. The following is stipulated:

No manage is managing more than three people.

No employee can work for more than one manage.

Which of these designs represents a normalized schema that meets the project requirements?

A. CREATE TABLE `manager' `manager' varchar (50) DEFAULT NULL, `employee2' varchar (50) DEFAULT NULL, `employee' varchar (50) DEFAULT NULL, UNIQUE ( `manager `, `employee1', `employee2, `employee3') )

B. CREATE TABLE `managers' ( "id' int(11) NOT NULL AUTO_INCREMENT, `manager' varchar (50) DEFAULT NULL , PRIMARY KEY (`id') ) CREATE TABLE "employees' ( `id' int(11) NOT NULL AUTO _INCREMENT, `manager_id' int(11) DEFAULT NULL, `employee varchar (25) DEFAULT NULL, PRIMARY KEY (`id') )

C. CREATE TABLE `manager' ( `manager' varchar (50) DEFAULT NULL, `employee_list'varchar (150) DEFAULT NULL, )

D. CREATE TABLE `message' ( `id' int(11) NOT NULL AUTO_INCREMENT, `manager' varchar(50) DEFAULT NULL, PRIMARY KEY ("id') ) CREATE TABLE `employees' ( `id int (11) NOT NULL AUTO _INCREMENT, ` employees' varchar(25) DEFAULT NULL, )

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.

You have created your connector/Net object to connect to MySQL. What are three valid database operations you can call?

A. ExecuteReader, ExecuteNonQuery, ExecuteScalar

B. PreformReadonly, performNonQuery,perforIndexRead

C. Query, Execute.MySql, Read. Execute. MySQl, Execute,Mysql

D. Insert Mysql, UpdateMysql,DeleteMysql

E. Query .Apply ,Mysql.Delete.Mysql,Query. Update .Mysql

When executing a stored routine, how is the SQL_MODE determined?

A. By the default SQL_MODE of the server

B. By the current SQL _MODE of the session

C. By the SQL_MODE that was set when the routine was defined

D. By using TRADITTIONAL regardless of any other settings

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. Results in an error

B. Updates every row in the specified table(s)

C. Results in - -safe-updates being enabled automatically

D. Causes a syntax error