Short and sweet:

CREATE TABLE parts (
   type CHAR(10) NOT NULL,
   id MEDIUMINT NOT NULL AUTO_INCREMENT,
   description CHAR(30) NOT NULL,
   PRIMARY KEY (type, id)
) ENGINE=MyISAM;

INSERT INTO parts (type, description) VALUES
 ('car part', 'brake'),
 ('car part', 'steering wheel'),
 ('furniture', 'table leg'),
 ('printer', 'toner cartridge'),
 ('furniture', 'shelf');

What would be returned by executing:

SELECT DISTINCT id FROM parts;

(more…)

Today’s quiz comes out of an e-mail conversation with fenixshadow, aka

On my system (5.0.45-Debian_1ubuntu3.1-log) , I run these commands:

CREATE TABLE t (
  ID INT NOT NULL PRIMARY KEY,
  PID INT,
  FOREIGN KEY (PID) REFERENCES t(ID)
) ENGINE=INNODB;

INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (2, NULL);

SELECT * FROM t yields the expected result:

+----+------+
| ID | PID  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+

I now do

UPDATE t SET PID  = 2 WHERE ID = 1

and observe the result of SELECT * FROM t again:

+----+------+
| ID | PID  |
+----+------+
|  2 | NULL |
|  1 |    2 |
+----+------+

Question 1: Can you easily explain why the rows are now in reverse order?

(more…)

Note to those of you that have written me with words of appreciation and encouragement as well as suggestions for new pop quizzes: First: Thanks for the kind words! Second: If you’re awaiting an answer, please rest assured that I do read your mail! I get mails relating to the pop quizzes daily, so I’m having to work on this in batch mode. You will get an answer from me in a few days.

Today’s question comes from Bertrand Gorge, who tells me he uses this as a test question during interviews. So if you’re looking to work for him, make sure you’ve understood this!

The following two queries do not return the same result. Why ?

SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id AND table2.val < 5

SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id WHERE table2.val …

This poser comes from Diego Madina.
Using a fairly recent MySQL 5.0 distribution, we see that…

mysql> SELECT 1.1E0, 2.2E0, 3.3E0;
+-------+-------+-------+
| 1.1E0 | 2.2E0 | 3.3E0 |
+-------+-------+-------+
|   1.1 |   2.2 |   3.3 |
+-------+-------+-------+
1 row in set (0.00 sec)

and…

mysql> select 1.1+2.2=3.3;
+-------------+
| 1.1+2.2=3.3 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

But…

mysql> select 1.1E0+2.2E0=3.3E0;
+-------------------+
| 1.1E0+2.2E0=3.3E0 |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

Why?

(more…)

In Quiz #5, we looked at the following CREATE TABLE and INSERT:

CREATE TABLE test (
  id varchar(1) NOT NULL,
  PRIMARY KEY  (id)
);
INSERT INTO test VALUES ('0'), ('1'), ('2'), ('a'), ('b');

The INSERT succeeds, and the table contains 5 rows.
The multi-row INSERT … VALUES (…), (…), … syntax is completely legal in MySQL. But…

1. is it portable to other Relational Database Management Systems such as Oracle, MS-SQL, DB2, Firebird, Postgresql, …?
2. is this standard SQL syntax?

(more…)

Today’s entry is from an anonymous contributor. Keep ‘em coming!

Suppose you issue the following list of commands:

CREATE TABLE fk_relations (
  key1 INT NOT NULL PRIMARY KEY,
  key2 INT NOT NULL UNIQUE
);
INSERT INTO fk_relations VALUES (1,1), (2,2);

The table now contains (1,1) and (2,2). We then do

REPLACE INTO fk_relations VALUES (1,3);

Question 1: The table now contains (1,3) and (2,2). Explain why.
We now do:

REPLACE INTO fk_relations VALUES (1,2);

Question 2: The table now contains the single row (1,2). Why?

Question 3: What is the value of Rows affected in your client after each of the two REPLACE statements?

(more…)

The following statement is completely valid and does what you expect:

SELECT DATE(creation_time) AS date,
       COUNT(*) AS num_signups
FROM signups
GROUP BY date
ORDER BY date;

In MySQL, you could leave out one clause of the above SELECT statement, yet still end up with the exact same result. Which clause is that?

(more…)

This thing is catching on … today’s question comes courtesy of Lasse Christiansen who is apparently residing in Japan (Lasse, correct me if I’m wrong).

Suppose you issue the following list of commands:

CREATE TABLE test (
  id varchar(1) NOT NULL,
  PRIMARY KEY  (id)
);
INSERT INTO test VALUES ('0'), ('1'), ('2'), ('a'), ('b');

The INSERT succeeds — nothing surprising here, the table now contains the values ‘0′, ‘1′, ‘2′, ‘a’ and ‘b’

Now you do:

DELETE FROM test WHERE id=0;

…and your client informs you that 3 rows were deleted.

Explain why.

(more…)

Which data type holds the greatest range of data? MEDIUMINT or INT?

Which data type may hold the largest amount of data? MEDIUMTEXT or TEXT?

(more…)

The last two Pop Quiz entries seem to have garnered some interest, so let’s do another one.

Today’s question is quite short: What is the maximum amount of data you can store in a MyISAM table?

(more…)