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;

What Does This Query Really Do?

Computers are dumb. And they will do exactly what you ask them to do.  The trick often is to think as dumb as the computer. Sadly it is all to easy to assume that the computer is 'thinking' like you are and blunder into a head scratching puzzle.  Recently there was a post on MySQL Community Space Groundbreakers Developer Community site that shows that sometimes what is intended is not what you want but you are getting exactly what you asked. 

Quiz -- What happens if you run the following query?

SELECT concat('CREATE TABLE if does not exists sakila1.', 
    TABLE_NAME, 
    ' like sakila.', 
    TABLE_NAME,  ';') 
FROM information_schema.`TABLES` 
WHERE TABLE_SCHEMA = 'sakila'

A) You will create copies of the tables in the sakila schema in sakila1 schema.

B) You will create the SQL queries to create copies of the tables in the sakila schema in the sakila1 schema.

C) This will fail as you have to create sakila1 before you can run this query.

D) This query has a syntax error

Your answer??

To help you take this quiz, here is the output of the query.  And yes, I have sakila schema but not a sakila1 schema. 

The author of this query was expecting the query to create copies of all the tables in the sakila schema in the sakila1 schema. 

Since the query ran, we can eliminate D as an answer.  

And since it ran without the sakila1 schema already created, we can eliminate C.

A simple SHOW SCHEMAS will show that A is not the answer.  Yes, even if you create the new schema before running the query.

The answer is that this query creates the query to do the work but does not perform the actual work.  So the query does exactly as asked but not as intended.

So what if you want this new schema?

Well, you could save the output from the query in a file and then execute that file.

Or put the query above in a CTE or subquery and use the output to do the intended work.