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.
Read more
Category: SQL
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
Got anyone who needs April Fooling?
Do you have a sql*plus user who really needs an April Fool’s joke played on them? With a little editing to their glogin.sql, every sql*plus session will exit with what appears to be a pseudo-random TNS error.
Read more
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.”
Read more
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.
Read more
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:
Pseudocolumns: Sequence Pseudocolumns from “Weekly SQL Quiz: 31 January – 6 February 2015”.
Read more
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.