Worksheet Two

Start by ensuring that the world database used in the first lab is correctly installed.

Using at most one SQL query per question, find the answers to the following questions and submit them, one answer per line, in a text file that you submit using cssubmit.

Your query does not have to produce the answer and only the answer - for example, you can write a query that produces a table that contains the answer and then read it off. However you should avoid using one query to find one thing (say a CountryCode) and then using that information in a second query.

All questions are to be answered just according to the data in the database, regardless of how out-of-date it is!

Some (but not all) of the questions will require more than one table, so you should use an appropriate JOIN. The first thing to do with each question is to decide which tables contain the information you need - thus if the question is asking about capital cities, then this information is only in Country. Use only the tables you need and no more!

One final hint: remember the SQL mantra of construct (construct the candidate rows as an appropriate Cartesian product), then filter (extract only the rows that make sense and contain the information you want) and finally project (extract only the columns you want).

• What is the largest country by surface area?
• What SQL query will list all countries by increasing GNP per capita ?
• In how many countries is English listed as one of the spoken
  languages (even if the percentage is 0.0)?
• In how many countries is English an official language?
• What SQL query will list the names of all the French-speaking countries ? (i.e., countries where French is an official language)?
• What SQL query will produce a two column table listing the name of each country followed by one of its official languages (countries with more than one official language will appear in one row for each language).

 +----------------------+------------+
| Name                 | Language   |
+----------------------+------------+
| Aruba                | Dutch      |
| Afghanistan          | Dari       |
| Afghanistan          | Pashto     |
| Anguilla             | English    |

• What SQL command will produce a two-column table listing each city together with the region it lies in?

+----------------+---------------------------+
| Name           | Region                    |
+----------------+---------------------------+
| Kabul          | Southern and Central Asia |
| Qandahar       | Southern and Central Asia |
| Herat          | Southern and Central Asia |
| Mazar-e-Sharif | Southern and Central Asia |
| Amsterdam      | Western Europe            |
| Rotterdam      | Western Europe

• How many cities lie in French-speaking countries (i.e. where French is an official language)?
• How many cities lie in French-speaking countries outside Western Europe? [Hint: the symbol for not-equals in SQL is <>]
• How many countries have both English and French as official languages? [Slightly Cryptic Hint: The information is all in CountryLanguage but you will still need to do a join of more than one table]

Written with StackEdit.