Quiz #1 Answers

Here are answers to Quiz #1 and please note there are several ways to solve just about any problem so your answer may be different.

QUIZ #1:   How would you modify the following query on the world database to find only the official languages of each country?  Bonus - list all the official languages for each country on one row.

select country.name,
       countrylanguage.Language
from country
join countrylanguage 

    on country.code = countrylanguage.CountryCode);

First step is to see what in the countrylanguage table provides us on the official language status.

 SQL > desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+

For those not used to dealing with enums they let enumerate a list of allowed values. Is this case IsOfficial can can either be 'T' or 'F'.  

I prefer to use alias on table names and below I use c for the country table and l for the countrylanguage table. Why? It improves readability and saves typing.    For extra karma points if you are a novice, rewrite the above query with alias.

The above query needs to qualify the query so that we receive only the rows where the column IsOfficial is equal to 'T'.  

select c.name,
       l.Language
from country c         
join countrylanguage l

     on  (c.code = l.CountryCode) 

where  l.IsOfficial = 'T';

Bonus

Listing all the official languages for each country on one row is a little more complex. First we have to GROUP BY each country.   If you simply add GROUP BY c.name you will find that you only get one official language in the cases there there  are many.    You also have to use GROUP_CONCAT() to provide all the possible official languages.  I also added aliases for the selected column names.

select c.name as 'Country',           

       GROUP_CONCAT(l.Language) as 'Official Languages'

from country c         

join countrylanguage l 

     on(c.code = l.CountryCode) 

where  l.IsOfficial = 'T' 

GROUP BY c.name;

The GROUP_CONCAT() adds the commas between each language automatically and you can change the separator as you desire.

But if you peer through the results there is one thing that may catch you eye that could be cleaned up for readability.  Take a look at the entry for South Africa where the languages are list but are not in alphabetical order.  Here is where you can add ORDER BY to the GROUP_CONCAT() to fix that issues.

Final code:

select c.name as 'Country',           

         GROUP_CONCAT(l.Language order by l.language) as 'Offical Langauges' 

from country c         

join countrylanguage l 

       on  (c.code = l.CountryCode) 

where  l.IsOfficial = 'T' 

GROUP BY c.name;