A numbers table is a versatile tool that can be used to synthesize ranges of various kinds of data. For instance, you can write queries against a numbers table to identify gaps or breaks in a sequence. We’ll see this…
Sometimes it’s useful to be able to generate an HTML table based on the data produced by a query. I’ve worked with clients who have needed this done as part of an internal automated notification procedure, for example. The technique…
Query result ordering is not guaranteed. If your query does not include an ORDER BY clause, the order of your results is completely arbitrary. This is because tables are sets, which themselves have no order. For example, in SQL Server…
The first edition. Thanks for reading. 1. NULL Values in SQL Queries Handling NULL values in SQL is tricky to say the least. An interesting tour of ternary logic. 2. pg_ctl Tips and Tricks pg_ctl Tips and Tricks Learn lesser…
What if you need to create a temporary table, but you don’t want or need to create a table variable or temporary table? One option is to ‘synthesize’ a table using constants — also known as a row constructor. Not…
If all you care about is the version alone, then all you need is… In my work, I find I also sometimes need to know more information about the underlying system. Which produces:
1. Create a new user defined table type (UDTT). A table type is conceptually like a temporary table, but you can pass this dude into a stored procedure as a parameter. This is handy if you need to move data…
The following list of sites give users the chance to practice writing SQL through a series of progressive exercises. These are the sites I’ve found through initial research, but is not necessarily an exhaustive list. My plan is to add…
The SQL sandboxes listed here are a great way to practice your SQL skills and practice querying against a live database. The interactivity is the killer feature. The sits listed here present guided exercises along with a window to execute…