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).

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.