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

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: CD

QUESTION 2
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 3
You have a view person age which contains these rows:
 clip_image001[1]
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 4
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_uscity did not previously exist?

A.    The view v_uscity would be created.
B.    An error would be issued that the view v_uscity does not exist.
C.    A warning would be issued that the view v_uscity 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 5
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 = ‘FIN1;
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 6
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 7
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 8
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: BCD

QUESTION 9
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: ABD

QUESTION 10
mysql> SELECT * FROM friends;
 clip_image001[3]
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
Passing your Oracle 1Z0-872 Exam by using the latest 1Z0-872 Exam Dump Full Version: http://www.braindump2go.com/1z0-872.html