Skip to content

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

MySQL SQL May 12, 2011

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
4
Leave a Reply

3 Comment threads
1 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Mark Leith

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’.

Guest
kabel

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

Author
shlomi

@kabel
No tricks of this sort. This is an honest SQL question 🙂

Author
shlomi

@Mark: Douze Point! (You have to be a Eurovision watcher to understand this)

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.