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!

A column defined as CHAR(x) or VARCHAR(x) will allow you to store up to x characters within each field of that column.

What’s the minimum size of x?

What’s the maximum size of x?

(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!

The maximum row size in a MySQL table in 64kb (65,535 bytes).

A LONGTEXT or LONGBLOB column may be up to 4,294,967,296 (4G) in size.

Explain how a LONGTEXT or LONGBLOB column fits into a MySQL table.

(more…)

Today’s question comes courtesy of Diego Medina.

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!

When using a 1-byte character set like Latin-1, a VARCHAR(250) field requires 251 bytes of storage. A VARCHAR(260) field requires 262 bytes of storage. Why?
(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 entry comes from Rudy Limeback

A user searching your website is moving through categories, and you want to display descriptions of these categories as the user progresses. Your basic SELECT looks like this:

SELECT id, description
FROM descriptions
WHERE id IN (23, 6, 9, 37)

You want the descriptions (records) to appear in the same order as the IDs listed in the WHERE clause.

How do you do that?

Show answer

Answer: Tag on an ORDER BY clause with the FIELD function like the following:

ORDER BY FIELD(id, 23, 6, 9, 37)

Very handy. I only wish Rudy had told me this 6 months ago when I was trying to do something exactly …

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!

In mysql>, you already know that hitting return on this:

SELECT 1+1, 2+2;

returns this:

+-----+-----+
| 1+1 | 2+2 |
+-----+-----+
|   2 |   4 |
+-----+-----+

What happens if you instead hit return after the following line?

SELECT 1+1, 2+2\\g

or this one?

SELECT 1+1, 2+2\\G

Show hint

Hint: Fire up an instance of mysql> and read the messages at the start of the session. (and while there, also type in the examples. They won’t do any damage).

Show answer

Answer: \g works just the same as ; .

\G does the same thing, too, but the output is displayed “vertically”, i.e. with one column name/field value per line. This is …

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!

As the MySQL root user user, I grant access to a new employee in the organization:

GRANT SELECT ON abc.* TO "user_a"@"192.168.1.%";

Some time later, the new colleague becomes a former colleague, and being not only lazy but also unsure exactly what privileges user_a has been granted over time, I do

REVOKE ALL PRIVILEGES ON *.* FROM "user_a"@"192.168.1.%";

in order to remove all of user_a’s access to the database system.

Questions:

1. Has the original SELECT privilege granted in the GRANT statement been revoked for user_a by the REVOKE statement?
2. Suppose all privileges on all databases and tables have been revoked for user_a. Will user_a be able to log on to the MySQL server?

Show answer

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!

Short questions with somewhat involved answers. In terms of MySQL (and databases in general)…

1. What is a character set?
2. What is a collation?
3. How do they interact?
4. Is this something you even need to worry about?

(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!

Question: Where can you find a really cool Pop Quiz — like question on the syntax (and pitfalls) of the CASE statement?

(more…)

This quiz is based on an email I got from a somewhat annoyed Jesper Krogh.

btw, 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!

Given:

SELECT * FROM car_parts:
+---------+---------+-------+--------+
| part_id |  car_id | price | weight |
+---------+---------+-------+--------+
|       1 |       1 |  2.20 |   3.00 |
|       2 |       1 |  3.30 |   5.00 |
|       3 |       1 |  4.40 |   7.00 |
|       4 |       2 |  2.20 |   9.00 |
|       5 |       2 |  3.30 |  11.00 |
|       6 |       2 |  4.40 |  13.00 |
+---------+---------+-------+--------+

We want to know the least expensive part of each car, and how much that part weighs:

SELECT car_id, MIN(price), weight
FROM parts
GROUP BY car_id;
+---------+------------+--------+
|  car_id | MIN(price) | weight | …

Another quiz based on comments from aka fenixshadow:

Two identical tables with FOREIGN KEY constraints within the tables are created:

CREATE TABLE t (
  id INT NOT NULL PRIMARY KEY,
  type INT,
  pid INT,
  FOREIGN KEY (pid) REFERENCES t (id)
) ENGINE=INNODB;

CREATE TABLE t1 (
  id INT NOT NULL PRIMARY KEY,
  type INT,
  pid INT,
  FOREIGN KEY (pid) REFERENCES t1 (id)
) ENGINE=INNODB;

The tables are created, and then the following updates are done in table t:

INSERT INTO t VALUES (1,1, NULL);
INSERT INTO t VALUES (2,1, NULL);
UPDATE t SET pid  = 2 WHERE id = 1;

Satisfied that all the updates have completed successfully, we attempt to add the data in t to t1:

INSERT INTO t1 SELECT * from t;

But are greeted with:

ERROR 1452 : Cannot add or update a child row: a foreign key
constraint fails

Question 1: Why did …