Question

Using the world_x database you installed in Module 1, list the countries and the capitals of each country. Modify your query to limit the list to those countries where less than 30% of the population speaks English. Be sure to identify the literary sources you used to look up any SQL syntax you used to formulate your query. Submit your query and query results Word file.

Answer 1

Answer:

SELECT country.Name, city.Name

FROM country

JOIN countrylanguage ON country.Code = countrylanguage.CountryCode

JOIN city ON country.Capital = city.ID

WHERE countrylanguage.Language = 'English'

AND countrylanguage.Percentage < 30;

Explanation:

SELECT is used to query the database and get back the specified fields.

City.Name is an attribute of city table.

country.Name is an attribute of country table.

FROM is used to query the database and get back the preferred information by specifying the table name.

country , countrylanguage and city are the table names.

country and countrylanguage are joined based ON country.Code = countrylanguage.CountryCode

countrylanguage and city are joined based ON country.Capital = city.ID

WHERE is used to specify a condition based on which the data is to be retrieved. The conditions are as follows:

countrylanguage.Language = 'English'

countrylanguage.Percentage < 30;