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

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

by Joe Celko
Released January 2012
Publisher(s): Morgan Kaufmann
ISBN: 9780123877567

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

Table of contents

  1. Cover image
  2. Table of Contents
  3. Front matter
  4. Copyright
  5. Dedication
  6. Introduction
  7. Chapter 1. Graphs, Trees, and Hierarchies
  8. 1.1. Defining Tree and Hierarchies
  9. 1.2. Network Databases
  10. 1.3. Modeling a Graph in a Program
  11. 1.4. The Great Debate
  12. 1.5. Note on Recursion
  13. Chapter 2. Adjacency List Model
  14. 2.1. The Simple Adjacency List Model
  15. 2.2. The Simple Adjacency List Model Is Not Normalized
  16. 2.3. Fixing the Adjacency List Model
  17. 2.4. Navigation in Adjacency List Model
  18. 2.5. Inserting Nodes in the Adjacency List Model
  19. 2.6. Deleting Nodes in the Adjacency List Model
  20. 2.7. Leveled Adjacency List Model
  21. Chapter 3. Path Enumeration Models
  22. 3.1. Finding the Depth of the Tree
  23. 3.2. Searching for Subordinates
  24. 3.3. Searching for Superiors
  25. 3.4. Deleting a Subtree
  26. 3.5. Deleting a Single Node
  27. 3.6. Inserting a New Node
  28. 3.7. Splitting up a Path String
  29. 3.8. Microsoft SQL Server's HIERARCHYID
  30. 3.9. Edge Enumeration Model
  31. 3.10. XPath and XML
  32. Chapter 4. Nested Sets Model of Hierarchies
  33. 4.1. Finding Root and Leaf Nodes
  34. 4.2. Finding Subtrees
  35. 4.3. Finding Levels and Paths in a Tree
  36. 4.4. Functions in the Nested Sets Model
  37. 4.5. Deleting Nodes and Subtrees
  38. 4.6. Closing Gaps in the Tree
  39. 4.7. Summary Functions on Trees
  40. 4.8. Inserting and Updating Trees
  41. 4.9. Converting Nested Sets Model to Adjacency List Model
  42. 4.10. Converting Adjacency List Model to Nested Sets Model
  43. 4.11. Separation of Edges and Nodes
  44. 4.12. Comparing Nodes and Structure
  45. 4.13. Nested Sets Code in Other Languages
  46. Chapter 5. Frequent Insertion Trees
  47. 5.1. The Data Type of (lft, rgt)
  48. 5.2. Computing the Spread to Use
  49. 5.3. Total Reorganization
  50. 5.4. Rational Numbers and Nested Intervals Model
  51. 5.5. Egyptian Fractions
  52. Chapter 6. Linear Version of the Nested Sets Model
  53. 6.1. Insertion and Deletion
  54. 6.2. Finding Paths
  55. 6.3. Finding Levels
  56. 6.4. Cash Register Tape Problem
  57. Chapter 7. Binary Trees
  58. 7.1. Binary Tree Traversals
  59. 7.2. Binary Tree Queries
  60. 7.3. Deletion from a Binary Tree
  61. 7.4. Insertion into a Binary Tree
  62. 7.5. Heaps
  63. 7.6. Binary Tree Representation of Multiway Trees
  64. 7.7. Stern–Brocot Numbers
  65. Chapter 8. Other Models for Trees
  66. 8.1. Adjacency List with Self-References
  67. 8.2. Subordinate Adjacency List
  68. 8.3. Hybrid Models
  69. 8.4. Path Enumeration Using Prime Number Products
  70. Chapter 9. Proprietary Extensions for Trees
  71. 9.1. Oracle Tree Extensions
  72. 9.2. DB2 and the WITH Operator
  73. 9.3. Date's EXPLODE Operator
  74. 9.4. Tillquist and Kuo's Proposals
  75. 9.5. Microsoft Extensions
  76. 9.6. Other Methods
  77. Chapter 10. Hierarchies in Data Modeling
  78. 10.1. Types of Hierarchies
  79. 10.2. Data Definition Language Constraints
  80. Chapter 11. Hierarchical Encoding Schemes
  81. 11.1. ZIP Codes
  82. 11.2. Dewey Decimal Classification
  83. 11.3. Strength and Weaknesses
  84. 11.4. Shop Categories
  85. 11.5. Statistical Tools for Decision Trees
  86. Chapter 12. Graphs in SQL
  87. 12.1. Adjacency List Model Graphs
  88. Chapter 13. Petri Nets
  89. 13.1. Data Definition Language for Petri Nets
  90. Chapter 14. State Transition Graphs
  91. 14.1. The Temporal Side of Changes
  92. Chapter 15. Hierarchical Database Systems (IMS)
  93. 15.1. Types of Databases
  94. 15.2. Database History
  95. 15.3. Sample Hierarchical Database
  96. 15.4. Summary
  97. Index

Product information

  • Title: Joe Celko's Trees and Hierarchies in SQL for Smarties, 2nd Edition
  • Author(s): Joe Celko
  • Release date: January 2012
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780123877567