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 CountryLanguage.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 CountryLanguage GROUP BY Language
Explanation:
A can use MERGE algorithm as updatable view (1 to 1 relationship between rows and no aggregate functions in construct.
— B uses COUNT(*) – agggregate function, C uses DISCTINCT, and D uses MIN and GROUP BY, so all these must use temptable algorithms (not updatable)