Consider the structures of the country and countryLanguage tables.
mysql > DESCRIBE Country;
Which of the following queries will give you the list of all European countries where German is spoken?
A.
SELECT Code AS c. Name FROM Country
WHERE Continent = ‘Europe’ AND EXISTS (SELECT * FROM CountryLanguage WHERE CountryCode = c AND Language = ‘German’)
B.
SELECT Code AS c. Name FROM Country
WHERE Continent = ‘Europe’ AND EXISTS IN (SELECT * FROM CountryLanguage WHERE CountryCode = c AND Language = ‘German’)
C.
SELECT Code AS c, Name FROM Country
WHERE Continent = ‘Europe’ AND EXISTS (SELECT Language, CountryCode FROM CountryLanguage WHERE CountryCode = c AND Language = ‘German’)
D.
SELECT Code AS c. Name FROM Country
WHERE Continent = ‘Europe’ AND NOT EXISTS (SELECT * FROM CountryLanguage WHERE CountryCode = c AND Language = ‘German’)
Explanation:
It doesn’t matter what is brought back with IF EXISTS, just that it can answer TRUE, something retured. B EXISTS IN (bogus)
— B (EXISTS IN) is incorrect. D (NOT EXISTS) brings back all countries where German is not spoken — 34 rows.
— Note question refers to CountryCode = c (column alias) but column alias is not possible in WHERE clause so have replaced c with code.