Pop quiz: what would be the results of the following queries?

Here’s a quiz for you. The real query I witnessed was very complicated. I’ve simplified it, but kept a confusing alias. Can you answer the following three questions? Are they even valid?

Given the following countries table data:

+----+------+--------------------------+---------------+------------+
| id | Code | Name                     | Continent     | Population |
+----+------+--------------------------+---------------+------------+
|  1 | ANT  | Netherlands Antilles     | North America |     217000 |
|  2 | MNP  | Northern Mariana Islands | Oceania       |      78000 |
|  3 | NAM  | Namibia                  | Africa        |    1726000 |
|  4 | NCL  | New Caledonia            | Oceania       |     214000 |
|  5 | NER  | Niger                    | Africa        |   10730000 |
|  6 | NFK  | Norfolk Island           | Oceania       |       2000 |
|  7 | NGA  | Nigeria                  | Africa        |  111506000 |
|  8 | NIC  | Nicaragua                | North America |    5074000 |
|  9 | NIU  | Niue                     | Oceania       |       2000 |
| 10 | NLD  | Netherlands              | Europe        |   15864000 |
| 11 | NOR  | Norway                   | Europe        |    4478500 |
| 12 | NPL  | Nepal                    | Asia          |   23930000 |
| 13 | NRU  | Nauru                    | Oceania       |      12000 |
| 14 | NZL  | New Zealand              | Oceania       |    3862000 |
| 15 | PRK  | North Korea              | Asia          |   24039000 |
+----+------+--------------------------+---------------+------------+

Question #1

What is the result of:

SELECT
  id,
  Name AS 'n',
  Continent AS 'c'
FROM
  countries
ORDER BY
  c
;

Question #2

What is the result of:

SELECT
  MIN(Name) AS 'n',
  Continent AS 'c'
FROM
  countries
GROUP BY
  c
;

Question #3

What is the result of:

SELECT
  MIN(Name) AS 'n',
  Continent AS 'c'
FROM
  countries
GROUP BY
  'c'
;

Answers and explanations to be followed shortly in a separate post.

4 thoughts on “Pop quiz: what would be the results of the following queries?

  1. I’d expect the first to return the results order by continent first, then essentially some undefined order afterwards.

    The second I would expect the country that comes first alphabetically to return with each continent (Namibia with Africa, Nepal with Asia etc.).

    The third, I’d expect one row with Namibia and some undefined continent – because you are grouping by the literal string ‘c’.

  2. Is this some trickeration involving the ordering of ENUM values? Or am I barking up the wrong tree?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.