Brian Cobb bcobb

SQL Tricks

Purpose

This purpose of this is to document all the tricks that I've learned over the last couple of decades (good grief, has it really been that long?) from interacting with and developing for databases.

I have organized this as an FAQ of sorts rather than by technical concept, e.g., scalar subuery, chasm trap, etc., because when you don't know how to express something in code, you can't know what the solution type is called or named. My other endeavor is to capture the more thornier types of queries that can come up as requirements, instead of just itemizing trivial, common examples.

Nota bene

Problem Description

User Story 1

You work for a bank, which has recently purchased an ingenious machine to assist in reading letters and faxes sent in by branch offices. The machine scans the paper documents, and produces a file with a number of entries which each look like this:

  _  _     _  _  _  _  _
| _| _||_||_ |_ ||_||_|

Where I tripped

I did not create a spatially-enabled database. I had to do this manually on EC2, whereas I recall this happening automatically locally.
I was not using a postgis:// DATABASE_URL scheme. Caveat emptor: this apparently breaks pgbackups, though there is perhaps a better solution forthcoming

	const membershipMachine = Machine({
	id: "membership",
	initial: "idle",
	states: {
	idle: {
	on: {
	SIGN_UP: "renewing"
	}
	},
	renewing: {

	Auto Refills Card
	Recommended Manual-Refills
	Add Starter Set -> Considering Manual-Refills
	Recommended Auto-Refills
	Add Starter Set -> Considering Auto-Refills
	Considering Auto-Refills
	Add Auto-Refills -> Reviewing Auto-Refills
	I prefer to Refill Manually -> Reconsidering Manual-Refills
	Considering Manual-Refills
	Review Order -> Reviewing Manual-Refills

	Auto Refills Card
	Recommended Auto-Refills
	Add Starter Set -> Considering Auto-Refills
	Considering Auto-Refills
	Add Auto-Refills -> Reviewing Auto-Refills
	I prefer to Refill Manually -> Reconsidering Manual-Refills
	Considering Manual-Refills
	Review Order -> Reviewing Manual-Refills
	I prefer Auto-Refills -> Reconsidering Auto-Refills
	Reviewing Auto-Refills

	<!DOCTYPE html>
	<html>
	<head>
	<meta charset="UTF-8">
	<style type="text/css">
	/* Shared styles */
	body {
	font-family: HelveticaNeue, sans-serif;
	font-size: 16.0px;
	color: #34302e;

	function emptyList() {
	return function(which) {
	return which(undefined, emptyList(), true);
	};
	}

	function prepend(head, tail) {
	return function(which) {
	return which(head, tail, false);
	};

	enum ConsPosition {
	case Left, Right
	}

	func cons<T>(a: T, b: T) -> (ConsPosition -> T) {
	func innerCons(i: ConsPosition) -> T {
	if i == .Left {
	return a;
	} else {
	return b;

	require 'thread'

	module ThreadPool

	class Executor

	def initialize(queue)
	@queue = queue
	@thread = nil
	end