content_image/703977.jpg content_image/703978.jpg
ORDER BY dates.date; 

The output looks like this: 

<img src='content_image/703977.jpg'>

Some points to note: 

•Are the queries inefficient, particularly the one with the MAX()  subquery executed for each row in the recursive SELECT ? EXPLAIN  shows that the subquery containing MAX()  is evaluated only once and the result is cached. 

•The use of COALESCE()  avoids displaying NULL  in the sum_price  column on days for which no sales data occur in the sales  table. 

## Hierarchical Data Traversal 

Recursive common table expressions are useful for traversing data that forms a hierarchy. Consider these statements that create a small data set that shows, for each employee in a company, the employee name and ID number, and the ID of the employee’s manager. The top-level employee (the CEO), has a manager ID of NULL  (no manager). 

CREATE TABLE employees ( id INT PRIMARY KEY NOT NULL, name VARCHAR(100) NOT NULL, manager_id INT NULL, INDEX (manager_id), FOREIGN KEY (manager_id) REFERENCES employees (id) ); INSERT INTO employees VALUES (333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL) (198, "John", 333), # John has ID 198 and reports to 333 (Yasmina) (692, "Tarek", 333), (29, "Pedro", 198), (4610, "Sarah", 29), (72, "Pierre", 29), (123, "Adil", 692); 

The resulting data set looks like this: 

mysql> SELECT * FROM employees ORDER BY id; 

<img src='content_image/703978.jpg'>

