book

Joe Celko's Trees and Hierarchies in SQL for Smarties, 2nd Edition

by Joe Celko

January 2012

Intermediate to advanced

296 pages

5h 4m

English

Morgan Kaufmann

Read now

Unlock full access

1.1. Defining Tree and Hierarchies1.2. Network Databases1.3. Modeling a Graph in a Program1.4. The Great Debate1.5. Note on Recursion
2.1. The Simple Adjacency List Model2.2. The Simple Adjacency List Model Is Not Normalized2.3. Fixing the Adjacency List Model2.4. Navigation in Adjacency List Model2.5. Inserting Nodes in the Adjacency List Model2.6. Deleting Nodes in the Adjacency List Model2.7. Leveled Adjacency List Model
3.1. Finding the Depth of the Tree3.2. Searching for Subordinates3.3. Searching for Superiors3.4. Deleting a Subtree3.5. Deleting a Single Node3.6. Inserting a New Node3.7. Splitting up a Path String3.8. Microsoft SQL Server's HIERARCHYID3.9. Edge Enumeration Model3.10. XPath and XML
4.1. Finding Root and Leaf Nodes4.2. Finding Subtrees4.3. Finding Levels and Paths in a Tree4.4. Functions in the Nested Sets Model4.5. Deleting Nodes and Subtrees4.6. Closing Gaps in the Tree4.7. Summary Functions on Trees4.8. Inserting and Updating Trees4.9. Converting Nested Sets Model to Adjacency List Model4.10. Converting Adjacency List Model to Nested Sets Model4.11. Separation of Edges and Nodes4.12. Comparing Nodes and Structure4.13. Nested Sets Code in Other Languages
5.1. The Data Type of (lft, rgt)5.2. Computing the Spread to Use5.3. Total Reorganization5.4. Rational Numbers and Nested Intervals Model5.5. Egyptian Fractions
6.1. Insertion and Deletion6.2. Finding Paths6.3. Finding Levels6.4. Cash Register Tape Problem
7.1. Binary Tree Traversals7.2. Binary Tree Queries7.3. Deletion from a Binary Tree7.4. Insertion into a Binary Tree7.5. Heaps7.6. Binary Tree Representation of Multiway Trees7.7. Stern–Brocot Numbers
8.1. Adjacency List with Self-References8.2. Subordinate Adjacency List8.3. Hybrid Models8.4. Path Enumeration Using Prime Number Products
9.1. Oracle Tree Extensions9.2. DB2 and the WITH Operator9.3. Date's EXPLODE Operator9.4. Tillquist and Kuo's Proposals9.5. Microsoft Extensions9.6. Other Methods
10.1. Types of Hierarchies10.2. Data Definition Language Constraints
11.1. ZIP Codes11.2. Dewey Decimal Classification11.3. Strength and Weaknesses11.4. Shop Categories11.5. Statistical Tools for Decision Trees
12.1. Adjacency List Model Graphs
13.1. Data Definition Language for Petri Nets
14.1. The Temporal Side of Changes
15.1. Types of Databases15.2. Database History15.3. Sample Hierarchical Database15.4. Summary

Overview

The demand for SQL information and training continues to grow with the need for a database behind every website capable of offering web-based information queries. SQL is the de facto standard for database retrieval, and if you need to access, update, or utilize data in a modern database management system, you will need SQL to do it. The Second Edition of Joe Celko's Trees and Hierarchies in SQL for Smarties covers two new sets of extensions over three entirely new chapters and expounds upon the changes that have occurred in SQL standards since the previous edition's publication. Benefit from mastering the challenging aspects of these database applications in SQL as taught by Joe Celko, one of the most-read SQL authors in the world.

Expert advice from a noted SQL authority and award-winning columnist who has given 10 years of service to the ANSI SQL standards committee
Teaches scores of advanced techniques that can be used with any product, in any SQL environment
Offers graph theory and programming techniques for working around deficiencies and gives insight into real-world challenges