Test Yourself!

These ten questions run the length of the chapter, and each turns on behavior that catches working developers out. Every snippet ran on MySQL 9.7.2 524 with its default sql_mode and collation (utf8mb4_0900_ai_ci), against a fresh copy of the chapter's shop database for each question, through the mysql client with --force, so a failing statement reports its error and the next statement still runs. Run nothing: write down exactly what each statement returns or does, and why. The answers, with the reasoning and the section each comes from, are in Appendix H.

Questions

Questions 1-3: filtering, grouping and strict modeSQL

-- 1. Categories 2-5 have no children. Why do these two counts differ?
SELECT COUNT(*) AS not_in FROM categories
WHERE id NOT IN (SELECT parent_id FROM categories);
SELECT COUNT(*) AS not_exists FROM categories c
WHERE NOT EXISTS (SELECT 1 FROM categories k WHERE k.parent_id = c.id);
SELECT COUNT(*) AS fixed FROM categories
WHERE id NOT IN (SELECT parent_id FROM categories WHERE parent_id IS NOT NULL);
SELECT NULL IN (1, NULL) AS a, 1 IN (1, NULL) AS b, 2 NOT IN (1, NULL) AS c;
-- 2. One of these three fails. Which, why is the first legal, and what does the third show?
SELECT c.id, c.name, COUNT(*) AS titles FROM products p
JOIN categories c ON c.id = p.category_id GROUP BY c.id;
SELECT c.name, p.title, MAX(p.price) FROM products p
JOIN categories c ON c.id = p.category_id GROUP BY c.name;
SELECT c.name, ANY_VALUE(p.title) AS a_title, MAX(p.price) AS top FROM products p
JOIN categories c ON c.id = p.category_id GROUP BY c.name ORDER BY c.name LIMIT 1;
-- 3. How many rows does t3 hold at the end, what is in them, and what does SHOW WARNINGS say?
CREATE TABLE t3 (code CHAR(3), qty TINYINT UNSIGNED);
INSERT INTO t3 VALUES ('ABCD', 1);
INSERT IGNORE INTO t3 VALUES ('ABCD', 300), ('XY', -5);
SHOW WARNINGS;
SELECT * FROM t3;

Questions 4-6: collations, window frames and JSONSQL

-- 4. Five comparisons, then a lookup on a stored name of 'Ana Souza'.
SELECT 'café' = 'CAFE' AS accents, 'a' = 'a ' AS pad, 'ß' = 'ss' AS eszett,
  'café' = 'CAFE' COLLATE utf8mb4_0900_as_cs AS as_cs, 'ß' LIKE 'ss' AS like_ss;
SELECT COUNT(*) FROM customers WHERE name = 'ANA SOUZA';
-- 5. Four window columns, two with no explicit frame. Fill in every value.
SELECT sku, category_id AS cat, stock,
  SUM(stock) OVER (ORDER BY category_id) AS by_range,
  SUM(stock) OVER (ORDER BY category_id, sku ROWS UNBOUNDED PRECEDING) AS by_rows,
  LAST_VALUE(sku) OVER (ORDER BY category_id, sku) AS last_sku,
  COUNT(*) OVER (PARTITION BY category_id) AS in_cat
FROM products WHERE category_id IN (2, 3) ORDER BY category_id, sku;
-- 6. BK-SQL-02 has attributes {"format": "ebook", "pages": 188}. What are a to f?
SELECT attributes->'$.format' AS a, attributes->>'$.format' AS b,
  attributes->'$.format' = 'ebook' AS c, attributes->>'$.pages' + 1 AS d,
  attributes->>'$.isbn' IS NULL AS e, JSON_TYPE(attributes->'$.pages') AS f
FROM products WHERE sku = 'BK-SQL-02';
SELECT JSON_EXTRACT('{"n": 1}', '$.n') = JSON_EXTRACT('{"n": 1.0}', '$.n') AS same;

Questions 7-8: isolation and index useSQL

-- 7. Default isolation level. Product 1 starts with stock 25. Session B autocommits.
-- session A:
START TRANSACTION;
SELECT stock AS a1 FROM products WHERE id = 1;
-- session B:
UPDATE products SET stock = 99 WHERE id = 1;
-- session A again:
SELECT stock AS a2 FROM products WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT stock AS a3 FROM products WHERE id = 1;
COMMIT;
-- 8. Which of the four queries use the new index, and how?
CREATE INDEX idx_cat_price ON products (category_id, price);
EXPLAIN SELECT title FROM products WHERE category_id = 2 AND price > 40;
EXPLAIN SELECT title FROM products WHERE price > 40;
EXPLAIN SELECT title FROM products WHERE category_id + 0 = 2;
EXPLAIN SELECT title FROM products WHERE category_id = 2 ORDER BY price DESC LIMIT 1;

Questions 9-10: referential actions and removed functionsSQL

-- 9. Customer 8 has no orders; product 3 appears in order 2. What is left in wishlist,
--    and what does the last DELETE report?
CREATE TABLE wishlist (customer_id INT UNSIGNED NULL, product_id INT UNSIGNED NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE SET NULL);
INSERT INTO wishlist VALUES (7, 3), (8, 3), (8, 5);
DELETE FROM customers WHERE id = 8;
DELETE FROM products WHERE id = 3;
SELECT * FROM wishlist;
DELETE FROM customers WHERE id = 1;
-- 10. A PHP 5-era login check calls the first three. Which statements succeed on 9.7?
SELECT MD5('shop');
SELECT SHA1('shop');
SELECT LEFT(SHA2('shop', 256), 16) AS sha256, LENGTH(UNHEX(SHA2('shop', 256))) AS bytes;
SELECT PASSWORD('shop');
SET @u = '6ccd780c-baba-1026-9564-5b8c656024db';
SELECT LENGTH(UUID()) AS text_len, LENGTH(UUID_TO_BIN(UUID(), 1)) AS bin_len,
  UUID_TO_BIN(@u, 1) = UUID_TO_BIN(@u) AS same;

MENU

Questions