Online Access Free 1Z0-909 Practice Test

How does InnoDB choose deadlock victims in MySQL?

• A.It chooses the transaction randomly.
• B.It chooses the transaction with the most modified rows.
• C.It chooses the transaction with the most accumulated CPU time.
• D.It chooses the transaction with the fewest modified rows.
• E.It chooses the transaction with the least accumulated CPU time.
• F.It chooses the transaction with the lowest transaction ID.

Examine the appointments table definition which contains one million rows:

Now, examine this statement which executes successfully:

Which statement will improve query performance?

• A.ALTER TABLE appointments add index IX_4<attendant_id, payment, credit)
• B.ALTER TABLE appointments add index IX_3(attendant_id, created_by)
• C.ALTER TABLE appointments add index IX_1(credit,payment)
• D.ALTER TABLE appointments add index IX_2(attendant_session_id, created_by)

Examine the contents of these tables:

Now examine the expected results for a user with privileges to access the table:

Which query returns the expected results?
A)

B)

C)

D)

• A.Option A
• B.Option B
• C.Option C
• D.Option D

You must reclaim memory used by a prepared statement named prep. Which two achieve this?

• A.DROP PREPARE prep;
• B.SET @a = ''; EXECUTE prep USING @a;
• C.DEALLOCATE PREPARE prep?
• D.PREPARE prep FROM '';
• E.DROP PROCEDURE prep;
• F.SET @prep = NULL;

Which change will prevent negative ages to be inserted into the people table?

• A.ALTER TABLE people ADD CONSTRAINT check_age CHECK (ABS(age)>=0);
• B.ALTER TABLE people ADD COLUMN valid_age=ABS(check_age) GENERATED ALWAYS;
• C.DELIMITER //
  CREATE TRIGGER age check AFTER INSERT ON people FOR EACH ROW IF NEW. age < 0 THEN SET NEW.age =0; END IF;// DELIMITER;
• D.DELIMITER //
  CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW. age < 0 THEN SET NEW.age =0; END IF;// DELIMITER ;