Sun Certified MySQL 5.0 Developer Part II

QUESTION NO: 1

When executing multi-row operations, what should be the first thing you look for to see if anything unexpected happened?

A. Warning/Error Count.

b. Warning/Error Codes.

C. Warning/Error messages.

Answer: A

QUESTION NO: 2

Which of the following is a valid method to restrict the amount of records returned from SHOW WARNINGS?

A. SHOW WARNINGS LIMIT

B. SHOW WARNINGS LIMIT

C. limit can not be used with show warnings

Answer: A, B

QUESTION NO: 3

Consider the following statement: show COUNT ( *) warnings what would be the result of executing this statement?

A. An error would be returned, as this is not valid syntax.

B. The number of current warnings would be returned.

C. The total number of warnings server-wide would be returned

Answer: B

QUESTION NO: 4

Which of the following describe situations when MySQL generates warnings?

A. When it is not able to completely fulfill a request.

B. When a system-level error occurs.

C. When an action has possible unintended side-effects.

Answer: A, C

QUESTION NO: 5

Which of the following statements are true?

A. The MylSAM storage engine should be preferred when you expect many reads and few updates to occur

B. The MylSAM storage engine should be preferred when you expect a mix of many reads and many updates to occur

C. The InnoDB storage engine should be preferred when you expect many reads and few updates to occur

D. The InnoDB storage engine should be preferred when you expect a mix of many reads and many updates to occur

Answer: A, D

QUESTION NO: 6

For maximum efficiency in join operations of one column from each of two tables, which of the following statements regarding indexes are true?

A. In a LEFT JOIN such as table1 LEFT JOIN table2 ON table1 .a = table2.b, the column b in table2 should always be indexed

B. A LEFT JOIN would not benefit from either of the participating columns being indexed

C. In an INNER JOIN, either, but not both, columns participating in the join should be indexed

D. In an INNER JOIN, both columns participating in the join should be indexed

E. An INNER JOIN would not benefit from either of the participating columns being indexed

F. In a LEFT JOIN such as table 1 LEFT JOIN table2 ON table 1 .a = table2.b, the column a in table 1should always be indexed

Answer: A, D

QUESTION NO: 7

Which of the following are valid optimization techniques?

A. Rewrite queries to use indexes

B. Use EXPLAIN

C. Use ORDER BY

Answer: A, B

QUESTION NO: 8

Which of the following best describes the major trade-offs for MylSAM fixed-length types vs. variable-length types, respectively?

A. Fixed-length are slower, but require less disk space, as variable-length are faster, but require more disk space.

B. Fixed-length are faster and require less disk space, as variable-length are slower and require more disk space.

C. Fixed-length are faster, but require more disk space, as variable-length are slower, but require less disk space.

D. Fixed-length are slower and require more disk space, as variable-length are faster and require less disk space.

Answer: C

QUESTION NO: 9

Which of the following best describes how a column prefix index works and why its an advantage?

A. A Column prefix allows for a shorter index, making it read less from disk,

B. A Column prefix allows for a shorter index name, making less code to be written.

C. A Column prefix is another name for a left-most prefix, which is usefully with multi-column indexes.

Answer: A

QUESTION NO: 10

Using column prefix indexing helps reduce

A. The amount of time to write a query

B. The number of results returned

C. The amount of disk I/O needed

D. The amount of disk space needed

Answer: C, D

QUESTION NO: 11

Assuming you want to add an index on the isofficial column, which of the following statements are true?

A. A SELECT query with WHERE lsOfficial=’T’ will perform much faster with the index because there aren't many rows with that value.

B. A SELECT query with WHERE IsOfficial-‘F ’ will perform much faster with the index because there are many rows with that value.

C. A SELECT query with a WHERE condition on the IsOfficial column won't perform much faster because there are only few distinct values.

D. Each UPDATE or INSERT statement will take longer because the additional index needs to be maintained.

E. You cannot add an index on ENUM columns.

Answer: C, D

QUESTION NO: 12

When performing a select for a value that is in an index

A. the row may be used exclusively to return the data

B. the index may be used exclusively to return the data

C. both the row and the index are always used to return the data

Answer: B

QUESTION NO: 13

You have a view person age which contains these rows:

If the view definition includes where age >= 17 with check option what will happen when the following statement is executed?

UPDATE person_age SET age = 16

A. No rows will be changed because with check option prevents changing rows to values less than 17.

B. The age in all rows will be changed to 16 and with check option will cause all rows to become invisible within the view because all now have an age less than 17.

C. The age in all rows will be changed to 16 and with check option will keep all rows visible in the view because they started in it.

Answer: A

QUESTION NO: 14

Consider the following statement:

TER VIEW v_USCity AS

SELECT Name FROM City WHERE CountryCode = 'USA'

What would be the result if the above statement was issued and the view v_usc i ty did not previously exist?

A. The view v_usc i ty would be created.

B. An error would be issued that the view v_usc i ty does not exist.

C. A warning would be issued that the view v_usc i ty does not exist, then it would create it.

D. The query would appear to have executed, but no action on that view would occur.

Answer: B

QUESTION NO: 15

Consider the following statement:

ALTER VIEW v_city AS SELECT Name FROM City WHERE CountryCode

Which of the following statements are equivalent to the above example?

A. mysql> DROP VIEW v_city;

mysql> CREATE VIEW v_city AS

-> SELECT Name FROM City WHERE CountryCode = 'FIN';

B. mysql> CHANGE VIEW v_city AS

-> SELECT Name FROM City WHERE CountryCode = 'FIN 1 ;

C. mysql> DROP VIEW v_city;

mysql> ALTER VIEW v_city AS

-> SELECT Name FROM City WHERE CountryCode = 'FIN';

D. mysql> DROP VIEW IF EXISTS v_city; mysql> CREATE VIEW v_city AS -> SELECT Name FROM City WHERE CountryCode = 'FIN';

Answer: A

QUESTION NO: 16

Consider the following statement:

DROP VIEW CityPop, CountryCodes, StatePop

All three views exist at the time the statement is executed. Which of the following results should be expected when executing the statement?

A. An error message will be issued for trying to drop more than one view in a single statement.

B. A warning message is issued for trying to drop more than one view in a single statement.

C. All the views will be successfully destroyed.

D. The first view will be destroyed if it is not dependent on the following two views.

Answer: C

QUESTION NO: 17

Is the following statement true or false?

"All tables referenced by the underlying SELECT of a view must exist at its creation time."

A. true

B. false

Answer: A

QUESTION NO: 18

For which of the following create view statements will MySQL elect to use the temptable algorithm? Assume each select is syntactically correct.

A. CREATE VIEW spanishCountries AS SELECT Country.Name FROM Country, CountryLanguage

WHERE Country.Code = CountryLanguage.CountryCode AND C o unt r yL anguage.Language = 'Spanish'

B. CREATE VIEW spanishCount AS SELECT COUNT(* ) as count FROM CountryLanguage WHERE Language = 'Spanish'

C.CREATE VIEW officialLanguages AS SELECT DISTINCT Language FROM CountryLanguage WHERE IsOfficial = 'T'

D. CREATE VIEW lowestLanguages AS

SELECT Language, MIN(Percentage) as lowest FROM Country Language GROUP BY Language

Answer: B, C, D

QUESTION NO: 19

Which of the following statements are true?

A. A view created with the temptable algorithm is not updatable.

B. A view containing a group BY clause is not updatable.

C. A view containing a where clause is not updatable.

D. A view containing a having clause is not updatable.

Answer: A, B, D

QUESTION NO: 20

mysql> SELECT * FROM friends;

The following select statement will return the id, username, friend's name and birthdate of any user that is also a friend whose birthday falls in the current month.

SELECT

users.id, users.name, friends.name, friends.birthdate FROM users JOIN friends ON users.id = friends.userid WHERE MONTH{friends.birthdate} = MONTH{CURDATE}}

Will the following create view statement successfully create a view of this select? If not, why?

CREATE VIEW currentBirthdays AS SELECT

users.id, users.name, friends.name, friends.birthdate

WHERE MONTH(friends.birthdate J = MONTH(CURDATE()}

A. Yes.

B. No; all column names within a view must be unique.

C. No; the syntax is incorrect.

D. No; functions with variable output like curdate may not be used with views.

Answer: B

QUESTION NO: 21

Which query will produce a list of all countries with no capital?

A. SELECT Country.name FROM Country, City WHERE Country.Capital=City.id AND ISNULL{City.Name}

B. SELECT Country.name FROM Country, City ON Country.Capital=City.id WHERE ISNULL(Citv.name)

C. SELECT Country.name FROM Country LEFT JOIN City ON Country.Capital=City.id WHERE ISNULL(City.name)

D. It's not possible to get this list in only one query. Answer:

Answer: C

QUESTION NO: 22

Consider the numbers of rows for the city and country tables.

mysql> SELECT COUNTO FROM City;

How many rows will the following query produce?

SELECT * FROM City, Country

A. 0

b. 1

C . 239

D. 4079

E. 4318 (the sum of 239 and 4079)

F. 974881 (the product of 239 and 4079)

G. None of the above; the query will produce a syntax error.

Answer: F

QUESTION NO: 23

Which of the following statements are true?

A. Rows are always returned from all inner joined tables that match the join condition(s).

B. Rows are always returned from all outer joined tables that match the join condition(s).

C. Rows may be returned from an inner joined table even if the other table doesn't match the join condition(s)

D. Rows may be returned from an outer joined table even if the other table doesn't match the join condition

Answer: A, B, D

QUESTION NO: 24

Consider the numbers of row in the country and city tables:

mysql> SELECT COUNT(*J FROM Country;

Which of the following queries is most efficient for listing the names and capitals of each country?

A. SELECT Country.name, City.name FROM Country, City WHERE Country.Capitai=City.id

B. SELECT Country.name, City.name FROM City, Country WHERE Country.Capitai=City.id

C. The queries are equally efficient.

Answer: C

QUESTION NO: 25

Consider the personnel table that stores information about you and your coworkers.

mysql> DESCRIBE personnel;

The id column holds the unique identifier of each employee and the boss column references the boss of each employee. Which query employees' names along with the name of their bosses?

A. . SELECT * FROM personnel, personnel WHERE personnel.id=personnel.boss

B. SELECT pi. name, p2.name FROM personnel AS pi SELF JOIN personnel AS p2 ON pl.id=p2.boss

C. SELECT name, name FROM personnel LEFT JOIN personnel ON id=boss

D. SELECT pi.name, p2.name FROM personnel AS pi LEFT JOIN personnel AS p2 ON pl.id=p2.boss

E. You cannot produce this list with only one query.

Answer: D

QUESTION NO: 26

Given one inner join query and one outer join query that both produce the same results, which of the following statements is true?

A. Inner joins are usually faster, use less memory and should be used instead of outer joins.

B. Outer joins are usually faster, use less memory and should be used instead of inner joins.

C. Inner and outer joins are running at the same speed and take the same amount of memory.

Answer: A

QUESTION NO: 27

All but one of the following queries produces the same results. Which one produces a different result or produces an error?

A. SELECT * FROM class JOIN student

ON class.class_id=student.class_id

B. SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id

C. SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id

D SELECT * FROM class JOIN student

ON class.class id=student.class id

387: C

E. SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id WHE RE NOT ISNULL(student.class_idJ

F. SELECT * FROM class LEFT JOIN student ON class.class_id=student.class_id WHERE NOT ISNULL(student.class id)

Answer: C

QUESTION NO: 28

Note: The countrycode field in the city table references the Code field in the country table. Which of the following delete statements all of the cities in the country "Spain" from the city table?

A. DELETE City FROM Country JOIN City

ON Country.Code=City.CountryCode WHERE Country.Name = 'Spain'

B. DELETE FROM City

USING Country, City

WHERE Countrv.Code=Citv.CountrvCode

tun i> <~ <j un u l y . w cuiic— o p ctxn delete

C. DELETE FROM City USING Country JOIN City

ON Country.Code=City.CountryCode WHERE Country.Name='Spain'

D. DELETE FROM City USING Country

WHERE Country.Code=City.CountryCode AND Country.Name='Spain'

Answer: A, B, C

QUESTION NO: 29

Consider the contents of the coiorsi and coiors2 tables.

mysql> SELECT * FROM colors1;

How many rows will be returned by the following query?

SELECT * FROM colorsl, colors2 WHERE colors2.name='blue'

A.0

b. 1

C. 2

D. 4

E. 8

F. 16

G. The query will produce a syntax error because it contains no join condition.

Answer: E

QUESTION NO: 30

Consider the city and country tables are given by the following DESCRIBE output extracts.

mysql> DESC Country;

Note: The CountryCode field in the city table references the code field in the country table. Which of the following delete statements the country "Spain" along with all of it's cities?

A. DELETE Country, City FROM Country, City

WHERE Country.Code=City.CountryCode AND Country.Name='Spain'

B. DELETE FROM Country JOIN City ON Country.Code=City.CountryCode WHERE Country.Name = 'Spain'

C. DELETE Country, City

USING Country.Code=City.CountryCode

WHERE Country.Name='Spain'

D. DELETE FROM Country, City

WHERE Country.Code=City.CountryCode AND Country.Name='Spain'

Answer: A

QUESTION NO: 31

The city table has the following structure:

Consider the following statement:

PREPARE CountryBylD FROM 'SELECT Country FROM City WHERE ID=?

An error will be issued when the above statement is executed because there is no country city table, and the prepared statement will not be successfully defined.

What will occur if a prepared statement named CountryBylD already exists and the above statement is executed?

A. An error will be issued because a prepared statement with the same name already exists.

B. An error will be issued because the new prepared statement contains an error, and the old prepared statement definition will remain in effect.

C. An error will be issued because the new prepared statement contains an error, and no prepared statement named CountryBylD will exist.

Answer: C

QUESTION NO: 32

Consider the following query:

SELECT Code FROM Country WHERE Name = 'United States'

Which of the following is the correct way to create a prepared statement using this statement in the mysql client?

A . mysql> PREPARE STATEMENT CountryCode FROM 'SELECT Code FROM Country WHERE Name = ? ';

B. mysql> PREPARE CountryCode AS 'SELECT Code FROM Country WHERE Name = ? ';

C. mysql> PREPARE CountryCode FROM 'SELECT Code FROM Country WHERE Name = ? ';

D. mysql> PREPARE STATEMENT CountryCode AS 'SELECT Code FROM Country WHERE Name = ? ';

Answer: C

QUESTION NO: 33

Which of the following are true with regards to prepared statements? Select all that apply. When executing a prepared statement

A. exactly one value must be given for each parameter.

B. any number of parameters may be assigned a value; undefined parameters are assigned a default value.

C. undefined parameters are assigned a value of NULL.

D. undefined parameters are assigned an empty string.

Answer: A

QUESTION NO: 34

Which of the following statements best describe why prepared statements are useful:

A. They often reduce the amount of code you have to write.

B. They often improve transaction support.

C. They often improve performance when using with stored procedures.

D. They often reduce parsing time when performing several similar queries

Answer: D

QUESTION NO: 35

Consider the following two statements:

LOAD DATA INFILE SELECT ... INTO OUTFILE

Which of the following are respective interfaces to the above statements?

A. mysqlload

B. mysqlimport

C. mysqlexport

D. mysqldiunp

Answer: D

Site Search:

Close

Close
Download Free Demo of VCE
Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.


Simply submit your e-mail address below to get started with our interactive software demo of your free trial.


Enter Your Email Address

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.