MySQL

Slides for Join-Fu: The Art of SQL (I and II)

Below are the Open Office Impress and PDF versions of the slide decks I will be giving tonight. Utah PLUG readers, these are those intermediate slides that have taken so long for me to clean up!

Join-fu: The Art of SQL – Part I

Open Office Impress slides

PDF slides

Topics include:

  • Schema optimization and vertical partitioning
  • SQL Coding style guidelines
  • Set-wise problem solving in SQL (and why it kicks for-loop-fu’s ass.)
    • Rewriting correlated subqueries into joins
    • Using derived tables or subqueries in the FROM clause
  • Working with N:M or “mapping” tables
    • Dealing with OR conditions
    • Dealing with AND conditions
    • Combatting filesorts using Cross-join-fu
  • Understanding Left-join-fu

Join-fu: The Art of SQL – Part II

Open Office Impress slides

PDF slides

Topics include:

  • Handling Heirarchical Data
    • Adjacency Lists
    • Nested Sets
  • Doing GIS calculations in SQL
    • Without using user variables, the spatial extensions or stored functions
    • Calculating distance between two points
    • Doing radius searches in spherical geometries
  • Reporting techniques in SQL
    • Calculating running sums and aggregates
    • Calculating rank of results