Question 1

You must write a statement that combines the first_name and last_name columns from the

employees table as "last_name, first_name."

Which two statements will do this?

Options :

A : SELECT last_name + ', ' + first_name FROM employees;

B : SELECT CONCAT_WS(', ',last_name,first_name) FROM employees;

C : SELECT GROUP_CONCAT(last_name, first_name) FROM employees;

D : SELECT last_name, ‘ , ',first_name FROM employees;

E : SELECT CONCAT(last name,', ',first_name) FROM employees;

Answer: A,B

Question 2

Examine this statement:

DECLARE not_found CONDITION FOR SQLSTATE '02000';

In which two statements can not found be used?

Options :

A : in a leave statement to exit a loop

B : in an if statement

C : in a while loop

D : in a handler declaration

E : in a signal statement

Answer: A,C

Question 3

Examine these statement which execute successfully:

Now, examine this desired output:

Which two queries will produce the out?

Options :

A :

B :

C :

D :

E :

Answer: B,C

Question 4

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

Options :

A :

DELIMITER //

CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW. age < 0

THEN

SET NEW.age =0; END IF;//

DELIMITER ;

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 : ALTER TABLE people ADD CONSTRAINT check_age CHECK (ABS(age)>=0);

Answer: D

Question 5

Examine this statement which has executed successfully:

Options :

A : Execution performance can be improved by using like instead of RLIKE.

B : The statement takes advantage of index description_idx.

C : Execution performance can be improved by, using a composite index with column description as the leftmost prefix column description.

D : No index will improve statement performance.

E : Execution performance can be improved by adding an index on column description.

Answer: A

Higher Test Marks with Free Online 1Z0-909 Exam Practice

Buying Options: