MySQL - the database with a sense of adventure

Interview questions for MySQL users. You ask them the SQL statement and get them to tell you what the database will do. At the end ask them why MySQL is a great database.

mysql> create table tmp (id integer auto_increment primary key, val
varchar(255)
);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tmp (id, val) values (0,'1');
Query OK, 1 row affected (0.07 sec)

mysql> select * from tmp;
+----+------+
| id | val  |
+----+------+
|  1 | 1    |
+----+------+
1 row in set (0.00 sec)

mysql> insert into tmp (id, val) values (0,'1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tmp;
+----+------+
| id | val  |
+----+------+
|  1 | 1    |
|  2 | 1    |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into tmp (id, val) values (4,'1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp;
+----+------+
| id | val  |
+----+------+
|  1 | 1    |
|  2 | 1    |
|  4 | 1    |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into tmp (id, val) values (0,'1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp;
+----+------+
| id | val  |
+----+------+
|  1 | 1    |
|  2 | 1    |
|  4 | 1    |
|  5 | 1    |
+----+------+
4 rows in set (0.00 sec)

mysql> insert into tmp (id, val) values (4,'1');
ERROR 1062 (23000): Duplicate entry '4' for key 1

Games with the query optimizer

Which of the following is fastest? Why ?


1: select distinct name from fphash where hash in (select distinct hash as h
from fphash where name = 'Nokia 6230i');

2: select distinct name from fphash where hash in (select hash from (select
distinct hash as h from fphash where name = 'Nokia 6230i') as hh);

Home Mythic Beasts, shell accounts, cvs
hosting, co-location, virtual servers