Category: Technical

Straight quotes can be used like backticks around aliases IN SOME CASES (as of 5.0.15, at least). Observe:

CREATE TABLE myTable (id int, someData int);
INSERT INTO myTable VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2);

These three queries produce identical output:

SELECT * FROM myTable GROUP BY id;
SELECT * FROM myTable GROUP BY `id`;
SELECT * FROM myTable GROUP BY 'id';
(more…)

Here are a couple table definitions:

CREATE TABLE `users1` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);

CREATE TABLE `users2` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);

After inserting some data, we get the following row counts:

SELECT COUNT(*) FROM users1;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+

SELECT COUNT(*) FROM users2;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+


Now, for this query:

SELECT * FROM users1 UNION SELECT * FROM users2;

How many rows will be in the result set? (Choose the best answer)

a) Exactly 27
b) At least 12
c) At least 15
d) Between 1 and 27
e) Zero or more

d) Between 1 and 27. Default behavior for UNION is DISTINCT. If all 27 rows in the two tables are identical, there will be only 1 row in the result. If all are unique, there will be 27 rows.

CREATE TABLE myTable (
 id INT,
 string varchar(100) DEFAULT NULL
);
INSERT INTO myTable (id, string) VALUES (1, "First"), (2, NULL);
UPDATE myTable SET string = CONCAT(id, " has the value ", string);
SELECT * FROM myTable;

What is the output?

+------+-----------------------+
| id   | string                |
+------+-----------------------+
|    1 | 1 has the value First |
|    2 | NULL                  |
+------+-----------------------+

What would result from the following?

CREATE TABLE my_table (
   id INT
);
INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4);
SELECT * FROM my_table AS m WHERE my_table.id > 3;

Answer:

ERROR 1109 (42S02): Unknown table 'my_table' in where clause.


(because it uses an alias for my_table, we should have said “WHERE m.id > 3"

Assume the following has completed correctly:

CREATE TABLE test (
   charField varchar(5)
);

What is the end difference between the following two statements?

INSERT INTO test (charField) VALUES ("123456");
INSERT INTO test (charField) VALUES ("12345 ");

Answer: In both cases, the inserted value is trimmed to ‘12345’. For the first, a warning is issued. For the second, only whitespace is trimmed, so no warning.

What would result from the following?

CREATE TABLE my_table (
id INT
);
INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4);
SELECT * FROM my_table AS m WHERE my_table.id > 3;

Answer:
ERROR 1109 (42S02): Unknown table 'my_table' in where clause.
(because it uses an alias for my_table, we should have said WHERE m.id > 3

Assume the following have completed:

GRANT ALL ON test1.* TO 'johnQ'@'localhost' IDENTIFIED BY 'pass1';
GRANT ALL ON test2.* TO 'johnQ'@'localhost' IDENTIFIED BY 'pass2';

User johnQ has a query that joins tables from test1 to tables from test2. How should he log in?

a) Using ‘pass1’.
b) Using ‘pass2’.
c) Using ‘pass1’ and ‘pass2’, separated by a colon.
d) It can’t be done; he must change the two passwords to match.
e) Other (explain)

Answer: b) Using ‘pass2’. Passwords are specific to the user/host combination, not to the table.

SELECT COUNT(DISTINCT someField) FROM someTable;
SELECT DISTINCT someField FROM someTable;

T/F: The value returned by the first will always equal the number of rows returned by the second.

Answer: False. COUNT() doesn’t count NULL, but SELECT DISTINCT will return a NULL. So the query using COUNT() could return one less row than the number of rows found with DISTINCT.