Today I wrote my longest SQL (SQLite) query ever. It was designed to query Microsoft’s popular Northwind example database for customer and employee phone numbers, and return content from orders where those phone numbers area codes were the same. To summarize the way this query works, I will move chronologically through the execution process as described on SQLBolt.

  1. FROM: Designate that I’m pulling info from the Order table, and joining OrderDetail, Employee, and Customer on their appropriate Id’s.
  2. WHERE: Use four nested REPLACE functions to remove dots, dashes, and parentheses from phone numbers for easier parsing. Use INSTR to get the index of the first space, which indicates the end of the area code. Feed that index into SUBSTR, along with another copy of the cleaned phone number, to get the final area code. Perform this operation for phone numbers in the Customer and Employee table, then see if they are equal.
  3. GROUP BY: Group all the data by order Id number, because without this, the query will return a list of items on orders instead of orders. This would not work without the SUM aggregate function after SELECT
  4. HAVING: not used in this query
  5. SELECT: Multiply the quantity of a product by its value, then by one minus the discount to yield the product value. Use the SUM function to add these values up by order in concert with GROUP BY
  6. not used in this query
  7. not used in this query
  8. not used in this query

Be sure to enlarge this image and check out what’s specifically going on. The text parsing bit gets a little hairy, so I might add comments and repost soon.