Not quite pop quiz format, but if you enjoyed the ones I published some time ago (almost 2 years ago now… how time flies), you’ll probably be interested to know that “plogi” and “urs” (whoever they are) have picked up on the idea and started on their own set of questions and answers.

Do pay them a visit. You might learn something!

Yet another quiz inspired by Freenode#mysql IRC discussions. If you haven’t been around on the channel lately, you should have a peek. Lots of interesting discussions going on!

mysql> SELECT c1, c2 FROM t WHERE c2 LIKE 'a%';
+------+------+
| c1   | c2   |
+------+------+
| a    | abc  |
+------+------+

Is the following construct for the LIKE clause legal?

SELECT c1, c2 FROM t WHERE c2 LIKE CONCAT('a', '%');

And how about this one?

SELECT c1, c2 FROM t WHERE c2 LIKE CONCAT(c1, '%');

Bonus question: Suppose the table has many rows, several more columns and there’s an index on (c2, c1). Could that index be utilized when running the legal versions of the query — and if so, how?

[hint]Hint: Perhaps some parts of the statement are optimized away before evaluation? [/hint]

[answer]Answer:Both the constructs shown are legal. …

Don’t forget to send in your suggestions for new quizzes!

This quiz originated on FreeNode#mysql, where someone asked how to count the number of newline-separated “fields” in a TEXT column. For the purposes of the quiz, I’ve changed the idea slightly but you should be able to appreciate the usefulness of this method for any x-separated data that you have to deal with.

Given the following data…

mysql> SELECT * FROM t;
+-----------+
| s         |
+-----------+
| aba       |
| abacad    |
| abacadaea |
+-----------+

…create a query which counts the number of occurrences of the character ‘a’ in each line, e.g.

mysql> SELECT s, <something> AS count_a
    -> FROM t;
+-----------+---------+
| s         | count_a |
+-----------+---------+
| aba       |       2 |
| abacad    |       3 |
| abacadaea |       5 |
+-----------+---------+

[hint]Hint: You need …

[Update: A couple of people have told me they really like "your game". I am not the creator of this game; I merely point to it. To my knowledge, I don't know any of the people involved in creating it.]

“The ATTACK query is an undocumented feature of the development branch of MySQL…”

With the popularity of MySQL I guess it was just a question of time before someone came up with a game not just based on MySQL, but also with the theme of MySQL. In the words of the website…

Are you tired of browser-based games that are thinly veiled interfaces for databases? Finally, there’s a game that just is a database!

THRILL as you insert your very own row in the “rows” table!

With careful selection of SQL queries, you will soon have three or even four-digit numbers in some of the fields in your row! Other queries may allow you to use those numbers to …

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This wonderful quiz from Vladimir Kolesnikov is one of those that should make you stop and think for a moment…

Given this table and data:

mysql> SELECT * FROM t;
+------+------+
| i1   | i2   |
+------+------+
|    1 |    2 |
|    2 |    1 |
|    4 |    3 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

What is the result of the following three statements?

SELECT * FROM t ORDER BY 1

SELECT * FROM t ORDER BY 2

SELECT * FROM t ORDER BY 1+1

(more…)

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

Today’s quiz question, which subsequently became three, comes courtesy of Janek Bogucki.

Since questions 2 and 3 contain hints on the other questions, they are embedded within the answers to previous questions.

Question 1: What happens if you throw the following statement at your MySQL server?

CREATE TABLE log(
name CHAR(20) NOT NULL,
count INT UNSIGNED NOT NULL
)

(more…)

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This quiz on INFORMATION_SCHEMA is stolen (with permission!), from Roland:

• Specify a minimal set of columns of the information_schema.TABLE_CONSTRAINTS table that is sufficient to reliably identify a single row in the information_schema.TABLE_CONSTRAINTS table.
• Argue why these columns are necessary and sufficient to identify a row, and why a smaller set of columns does not exist

(more…)

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

What happens if you try to enter the following command into MySQL?

CREATE TABLE t1 (
i INT,
INDEX i (i)
);

(more…)

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

Here’s something I came across several months back. Watch in wonder as we create a PRIMARY KEY which is already there, then drop it again, only and to see that it’s still present in the table…:

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
mysql> ALTER TABLE t1 ADD PRIMARY KEY(i);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| …

Thanks to Marcos Besteiro and Andrew Dashin, the MySQL Pop Quizzes are now available in Spanish and Russian!

Thanks for the hard work, guys! It’s great to see that people are finding this worthwhile. I will try to get around to linking up the individual quizzes as soon as I find some time.

If you wish to translate the quizzes into your own language, please contact me at carsten (at) bitbybit (dot) dk before you start so we can sort out the details.