Last updated: 27 March 2011
SQL Poetry and Other Fancy SQL Stuff
 
SQL code should be clear, efficient and sufficiently commented. Obviously.
But it can also be funny, fancy or just plain strange, written for other purposes than the functionality it implements. Below are some examples.

The message here: did you put any art in your SQL today? Spice it up!

For more inspiration how to bend SQL syntax in ways that were not intended check out the quiz question where this all started.

If you have a great (or funny, or fancy, or weird, or rhyming) piece of SQL, let me know and I'll be glad to add it to this page.
To be included, your SQL must actually be able to run (no matter how botched the setup), so please provide some setup steps if needed. Also, please indicate which Sybase database & version are required to run it (doesn't have to be ASE).


 
Last updated: 27 March 2011
First, here's a little tribute to Jim Morrison:
select 
"this" = the.[end](beautiful.friend())
(I'm assuming you're familiar with that particular Doors song...)


To actually make this run, use the following setup (this runs in ASE 12.5.1 or later):
sp_addlogin the, 'doesntmatter'
go
sp_addlogin beautiful, 'doesntmatter'
go
sp_adduser the
go
sp_adduser beautiful
go
setuser 'the'
go
create function [end] (@c varchar(200)) returns varchar(100)
as
   declare @v varchar(100), @n int set @n = 1
   while @n <= len(@c) begin set @v = @v + 
   strtobin(substring(@c,@n,2)) set @n = 
   @n + 2 end return @v
go
setuser 'beautiful'
go
create function friend returns varchar(200)
as
 return "43616e20796f7520706963747572652077686" + 
        "1742077696c6c2062650a736f206c696d6974" + 
        "6c65737320616e642066726565200a6465737" +
        "065726174656c7920696e206e656564200a6f" + 
        "6620736f6d6520737472616e6765722773206" + 
        "8616e642e2e2e"
go
setuser
go



 
Last updated: 27 March 2011
This artful piece of SQL code was submitted by Trevor Moore, and as you'd expect from him, it runs on IQ (any version).
If you think you're good with SQL, just see if you can determine the result of this SQL code, but without running it... (let's just say this is as close as you'll get to making the IQ parser fail with syntax nausea)

Note: when running this code, make sure you run it in 'dbisql' or 'sqsh', since the terminating semicolon should really execute the preceding statement. If you want to run it in 'isql', make sure you put a 'go' after every statement, otherwise you'll get a syntax error).
SET TEMPORARY OPTION NON_KEYWORDS = 'TRUNCATE, INDEX, DATE, INT, EXEC, 
NUMERIC, BIT, GOTO';
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'TRUNCATE' ) 
THEN DROP TABLE TRUNCATE END IF;
CREATE TABLE TRUNCATE ( DATE  VARCHAR(10) NULL, TRUNCATE CHAR(10) NULL, 
NUMERIC VARCHAR(20) NULL, GOTO VARCHAR(50) NULL ) ON SYSTEM;
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'INT' ) 
THEN DROP TABLE INT END IF;
CREATE TABLE INT ( BIT VARCHAR(10) NULL ) ON SYSTEM;
INSERT TRUNCATE (DATE,TRUNCATE,NUMERIC) SELECT 'HAVING','GROUP BY','HAVING';
INSERT INT (BIT) SELECT 'HAVING';
INSERT TRUNCATE (DATE,TRUNCATE,NUMERIC)SELECT BIT,'FROM','GROUP BY' 
FROM INT WHERE BIT IN (SELECT NUMERIC FROM TRUNCATE WHERE NUMERIC = DATE);
DELETE TRUNCATE WHERE NUMERIC = DATE;
UPDATE TRUNCATE SET NUMERIC='EXEC', TRUNCATE='GROUP BY',GOTO = 'DECLARE' 
WHERE TRUNCATE = 'FROM';
SELECT INDEX = SUBSTRING(DATE,CHAR_LENGTH(NUMERIC)
, SOUNDEX(TRUNCATE)/SOUNDEX(TRUNCATE)) +
+ SUBSTRING(TRUNCATE,CHAR_LENGTH(DATE), CHAR_LENGTH(GOTO)
- CHAR_LENGTH(BIT)) 
+ SUBSTRING(BIT,CHAR_LENGTH(NUMERIC)+SOUNDEX(NUMERIC)/SOUNDEX(NUMERIC)
, CHAR_LENGTH(GOTO) - CHAR_LENGTH(DATE)) 
+ REVERSE(REPEAT(SUBSTRING(NUMERIC,CHAR_LENGTH(NUMERIC) 
- ( CHAR_LENGTH(GOTO) - CHAR_LENGTH(NUMERIC))
, SOUNDEX(GOTO)/ SOUNDEX(GOTO)), CHAR_LENGTH(TRUNCATE) /CHAR_LENGTH(NUMERIC)))
+ LEFT(GOTO,CHAR_LENGTH(GOTO) - CHAR_LENGTH(BIT))
+ SUBSTRING(TRUNCATE,CHAR_LENGTH(DATE),SOUNDEX(BIT)/SOUNDEX(BIT))
+ SUBSTRING(BIT,CHAR_LENGTH(TRUNCATE) /CHAR_LENGTH(NUMERIC) ,
  SIMILAR(BIT,DATE)/ SIMILAR(BIT,DATE)) 
+ SPACE(LENGTH(RIGHT(NUMERIC,SOUNDEX(TRUNCATE)/SOUNDEX(TRUNCATE))))
+ SUBSTRING(GOTO,LENGTH(NUMERIC),CHAR_LENGTH(GOTO) - CHAR_LENGTH(DATE))
+ SUBSTRING(DATE,CHAR_LENGTH(NUMERIC),SOUNDEX(GOTO)/SOUNDEX(GOTO))
+ CHAR (ASCII(LEFT(TRUNCATE,CHAR_LENGTH(GOTO) - CHAR_LENGTH(DATE))) 
- SOUNDEX(BIT)/SOUNDEX(BIT) )
+ CHAR (ASCII(SUBSTRING(BIT,CHAR_LENGTH(BIT),SIMILAR(BIT,DATE)
/ SIMILAR(BIT,DATE))) - LENGTH(BIT) + LENGTH(NUMERIC) )
FROM TRUNCATE JOIN INT ON (BIT=DATE);
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'TRUNCATE' ) 
THEN DROP TABLE TRUNCATE END IF;
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'INT' ) 
THEN DROP TABLE INT END IF;
SET TEMPORARY OPTION NON_KEYWORDS = '';
(thanks for sharing that Trevor; actually, I think I need the same as you do)


 
Last updated: 27 March 2011
These two come from Alexander Blyakher. it's worth running these SQL statements to see the result (runs on ASE, IQ and SQL Anywhere):
select life from death
       where stars=shining
       and mood=sad
go
and:
select essence from jazz
       where sax=alt
       and drums=heartbeat
       and bass=rhythm
go


Setup:
create table death(life char(50), shining int, stars int, mood int, sad int)
go
insert death values(reverse("denodnaba ylno ,dehsinif reven si meop A"),1,1,1,1)
insert death values(reverse("yrelaV luaP                        "),1,1,1,1)
go
create table jazz(essence char(70), sax int, rhythm int, alt int, bass int, 
                  drums int, heartbeat int)
go				 
insert jazz values (reverse(" eb nac taht gnihtemos ton si zzaJ") + 
       reverse(".stnemurtsni tnulb hguorht denifed"),1,1,1,1,1,1)
insert jazz values(reverse(".taht naht citeop erom hcum si tI"),1,1,1,1,1,1)
insert jazz values(reverse("ynehteM taP    "),1,1,1,1,1,1)
go



 
Last updated: 27 March 2011
Here are two fine examples of SQL Poetry on external web sites:

 
 This document is located at www.sypron.nl/fancy.html