1Z0-882 Online Practice Questions and Answers

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;

A table (t1) contains 1000 random integer values in the first column (col1).The random values are in the range of 0-1000.

Examine this query: SELECT col1 FROM t1 WHERE col1 <=100 UNION SELECT col1 FROM t1 WHERE col1 >=900 ORDER BY col1 DESC

What is the expected output?

A. A list of all values, including duplicates, sorted in descending order in the ranges of 0-100 and 9001000

B. A list of all random unsorted values, including duplicates, in the range of 0-100 followed by the list of all values, including in the range of 900-1000 sorted in descending order

C. A list of unique random values in the range of 0-100 followed by the list of unique values in the range of 900-1000 sorted in descending order

D. A list of all unique values sorted in descending order within the ranges of 0-100 and 900

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.

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

Which statement is true when working with PHP and prepared statement?

A. The mysql extension supports only client-side emulation.

B. The mysql1 extension support client-side emulation.

C. The PDO_MySQL extension supports both client side and server side emulation.

D. The PDO_MySQL extension supports only server side emulation.

Which two queries return a value of NULL?

A. SELECT NULL =NULL

B. SELECT NULL is NULL

C. SELECT NULL <= > NULL

D. SELECT 1 > NULL

E. SELECT COUNT (NULL);

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

Examine this table that contains over two million rows of data:

CREATE TABLE `news_feed' (

.id'bigint (20) NOT NULL AUTO _INCREMENT,

.news _sources_id'varchar (11) NOT NULL,

.dataline' datetime NOT NULL,

.headline' varchar (256) NOT NULL,

.story' text NOT NULL,.tag varchar (32768) DEFAULT NULL, PRIMARY KEY (`id')

KEY `dateline' ( `dateline')

)

Examine this query that returns 332 rows of date:

SELECT *

FROM news_feed

WHERE DATE(dateline)= `2013-01-01'

Which change would show the greatest improvement in the response time of the query?

A. Use the LIKE operator: SELECT . . .WHERE dateline LIKE `2013-10-01and'

B. USE the DATEDIFF function: SELECT . . . WHERE DATEDIFF (dateline, `2013-01-01') = 0

C. Use numeric equivalents for comparing the two dates: SELECT. . .WHERE MOD(UNIX_TIMESTAMP (dateline), 86400 =UNIX_TIMESTAMP (`2013-01-01')

D. Use a date range comparison: SELECT . . . WHERE dateline >= `2013-01' and dateline < `2013-01-02'

You have two lists of values to correlate.

Which query lists all names in colors1 and how many total matches are there in colors2?

A. SELECT colors1 .name.count (colors2.name) FROM colors1. Colors2 WHERE Colors1. Name = (SELECT DISTINCT name FROM colors2 WHERE colors1.name=colors2.name) GROUP BY colorse1.name,

B. SELECT colors1.name, count(colorse2. Name) FROM colorse1 .name =colors2.name WHERE colors1. Name =colors2.name GROUP BY colors1.name,

C. SELECT colors1. Name count (colors2.name) FROM colors1 INNER JOIN colors2 on colors1. Name =colors2. Name GROUP BY colors1 .name;

D. SELECT colors1.name, count (colors2.name) FROM JOIN colors2 on colors1 .name =colors2.name GROUP BY colors1.name;

E. SELECT colors1.name, count (colors2.name) FROM colors1 RIGHT JOIN colors1 on colors1 .name =colors2.name GROUP BY colors1.name;

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