# What’s in a name? – “Brittany” edition

How do you spell “Brittany”? The picture above has four well-known women with four different spellings of the name. It turns out there are nearly 100 different ways that Americans have spelled it. The US Social Security Administration names data lets us tease out all the spellings and find out which ones are most popular – and when.

# What’s in a name? or rather, in the SSA Names data

One of the amazing things about being a DBA/developer in 2016 is the sheer amount of freely available, downloadable data to play with. One fun publicly available data sets is the American Social Security Administration names data. It contains all names for which SSNs were issued for each year, with the number of occurrences (although names with <5 occurrences are not included to protect individual privacy).

# SQL vs. Excel – Subgroup medians

Recently I ran across this post on how to do subgroup medians in Excel 2010. First you need to create a pivot table, then "do some copying and pasting and use a formula to make it happen". In SQL you can do this with one command.

# Roman numerals to decimal in SQL

Earlier this week I got tangled up doing a Roman Numeral conversion in my head. So of course my second thought, right after “Doh!”, was “I bet I can write a SQL statement to do this for me next time.”

# Running totals in SQL

One of the many useful things analytic SQL makes it easy to do is a running total - or running average, or other calculation. This post is a quick look at the syntax.

# LAG and analytic COUNT

Just a quick post today to play around with some analytic functions.

# What you can’t do with NEXTVAL

I wrote a quiz for the PL/SQL challenge on sequence pseudocolumns, and I noticed that a couple of the answers tricked a lot of people who took the quiz. Time for a blog post on what you can’t do with NEXTVAL!

If you want to try the quiz, do it now before reading on:

# A little fun with global temporary tables

Here’s a little bit of fun I thought up as part of a SQL game I was working on at Oracle. It’s a simple use of a trigger and a global temporary table to create a “combination lock” that any logged-in session can try to “open”.

To use it:

SQL> INSERT INTO combinationlock (combination) VALUES ('Test');

1 ROW created.

SQL> SELECT * FROM combinationlock;

COMBI LOCK_STATE
----- --------------------------------------------------
Test  Closed

SQL> UPDATE combinationlock SET combination='99999';

1 ROW updated.

SQL> SELECT * FROM combinationlock;

COMBI LOCK_STATE
----- --------------------------------------------------
99999 Closed

SQL> SELECT * FROM combinationlock;

COMBI LOCK_STATE
----- --------------------------------------------------
12345 OPEN Sesame

To create:

DROP TABLE combinationlock;
CREATE global TEMPORARY TABLE combinationlock (combination varchar2(5), lock_state varchar2(50))
ON commit preserve ROWS;

DROP TRIGGER combinationlock_trig ;
CREATE TRIGGER combinationlock_trig
BEFORE INSERT OR UPDATE ON combinationlock
FOR each ROW
BEGIN
IF :NEW.combination = '12345'
THEN :NEW.lock_state := 'Open Sesame';
ELSE :NEW.lock_state := 'Closed';
END IF;
exception
WHEN others THEN
:NEW.lock_state := 'Closed';
raise;
END;
/

The nice thing about this is that, since it’s using a global temporary table, any user can try their guesses at the combination and it affects only their session.

# Floating down the infinite river

Sayan Malakshinov (@ora_sql)’s quiz inspired this post. Spoiler: if you’d like to take his quiz, do it before reading this post!