{"id":3445,"date":"2011-05-12T10:53:11","date_gmt":"2011-05-12T08:53:11","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3445"},"modified":"2011-05-12T10:53:11","modified_gmt":"2011-05-12T08:53:11","slug":"pop-quiz-what-would-be-the-results-of-the-following-queries","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/pop-quiz-what-would-be-the-results-of-the-following-queries","title":{"rendered":"Pop quiz: what would be the results of the following queries?"},"content":{"rendered":"<p>Here&#8217;s a quiz for you. The real query I witnessed was very complicated. I&#8217;ve simplified it, but kept a confusing alias. Can you answer the following three questions? Are they even valid?<\/p>\n<p>Given the following <strong>countries<\/strong> table data:<!--more--><\/p>\n<blockquote>\n<pre>+----+------+--------------------------+---------------+------------+\r\n| id | Code | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Continent\u00a0\u00a0\u00a0\u00a0 | Population |\r\n+----+------+--------------------------+---------------+------------+\r\n|\u00a0 1 | ANT\u00a0 | Netherlands Antilles\u00a0\u00a0\u00a0\u00a0 | North America |\u00a0\u00a0\u00a0\u00a0 217000 |\r\n|\u00a0 2 | MNP\u00a0 | Northern Mariana Islands | Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 78000 |\r\n|\u00a0 3 | NAM\u00a0 | Namibia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 1726000 |\r\n|\u00a0 4 | NCL\u00a0 | New Caledonia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 214000 |\r\n|\u00a0 5 | NER\u00a0 | Niger\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 10730000 |\r\n|\u00a0 6 | NFK\u00a0 | Norfolk Island\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2000 |\r\n|\u00a0 7 | NGA\u00a0 | Nigeria\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 111506000 |\r\n|\u00a0 8 | NIC\u00a0 | Nicaragua\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | North America |\u00a0\u00a0\u00a0 5074000 |\r\n|\u00a0 9 | NIU\u00a0 | Niue\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2000 |\r\n| 10 | NLD\u00a0 | Netherlands\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 15864000 |\r\n| 11 | NOR\u00a0 | Norway\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 4478500 |\r\n| 12 | NPL\u00a0 | Nepal\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 23930000 |\r\n| 13 | NRU\u00a0 | Nauru\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 12000 |\r\n| 14 | NZL\u00a0 | New Zealand\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 3862000 |\r\n| 15 | PRK\u00a0 | North Korea\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 24039000 |\r\n+----+------+--------------------------+---------------+------------+<\/pre>\n<\/blockquote>\n<h4>Question #1<\/h4>\n<p>What is the result of:<\/p>\n<blockquote>\n<pre>SELECT\r\n  id,\r\n  Name AS 'n',\r\n  Continent AS 'c'\r\nFROM\r\n  countries\r\nORDER BY\r\n  c\r\n;<\/pre>\n<\/blockquote>\n<h4>Question #2<\/h4>\n<p>What is the result of:<\/p>\n<blockquote>\n<pre>SELECT\r\n  MIN(Name) AS 'n',\r\n  Continent AS 'c'\r\nFROM\r\n  countries\r\nGROUP BY\r\n  c\r\n;<\/pre>\n<\/blockquote>\n<h4>Question #3<\/h4>\n<p>What is the result of:<\/p>\n<blockquote>\n<pre>SELECT\r\n  MIN(Name) AS 'n',\r\n  Continent AS 'c'\r\nFROM\r\n  countries\r\nGROUP BY\r\n  'c'\r\n;<\/pre>\n<\/blockquote>\n<p>Answers and explanations to be followed shortly in a separate post.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Here&#8217;s a quiz for you. The real query I witnessed was very complicated. I&#8217;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:<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[21],"class_list":["post-3445","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Tz","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3445"}],"version-history":[{"count":13,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3445\/revisions"}],"predecessor-version":[{"id":3623,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3445\/revisions\/3623"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}