Click here to monitor SSC
A service from Red Gate

in
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Phil Factor's Phrenetic Phough... > A SQL Limerick

Phil Factor's Phrenetic Phoughts

Simple-Talk columnist
The wilder shores of Transact SQL    Phil on Twitter   Phil on SQL Server Central  Phil on BOS

A SQL Limerick

Published Friday, January 13, 2006 8:15 AM

It wasn't my idea at all. However someone set me the challenge of writing executable code that rhymed and scanned in the form of a limerick. Obviously, the easy trick is to just to write the limerick, assign it to a string and then do text substitution on that striing to convert it into SQL code before executing it as a string. Sorry, cheating. You could also make sure your tables had entries that were defined once you'd written the limerick. Hmm. A bit unsporting. No. Straight to the north face of the Eiger for me. SQL in a directly executable form that executes against the sample PUBS database. After several spectacular failures, the best I could come up with was the following ('>' has to be read as 'greater than'.')  All I have done, I think, is to illustrate that it is possible.
 
Select advance,  title, case when
price = 5000 then
'good' else 'low' end
from titles WeSend
where pub_id > 10
 
So can anyone do better? I believe that Red-Gate are willing to offer a prize for anyone who can do it. In which case, I am cheered by the thought that, if this blog entry is met by stunned silence in the comments, I shall claim the prize myself.
 
by Phil Factor

Comments

 

Robert Tomlin said:

update Employees set
FirstName='Brett'
where EmployeeId=2
and Birthdate='2/19/1952'

January 16, 2006 3:16 PM
 

Doug Stewart said:

USE Pubs; --limerick title

Update Pubs.dbo.pub_info
Set logo = NULL where pr_info
IS NULL; SELECT state, city,
pub_id, pub_name, country
from publishers as unaffected_info
January 16, 2006 9:05 PM
 

Doug Stewart said:

Select Pub_name from Publishers PubLess
Join Titles as twoBooks --priced for less
on twoBooks.pub_ID
= PubLess.pub_id
Where ISNULL(CAST(price as CHAR),'YES') = 'YES'
January 16, 2006 10:13 PM
 

doug stewart said:

Insert into Stores (stor_ID)
values( 303 / 3 )
Update Stores set stor_name
= 'simple math is our fame'
Where stor_id = (78 + 23)
January 16, 2006 10:24 PM
 

Doug Stewart said:

SELECT SUM(CAST(SUBSTRING(TWO,1,1) AS INT)) PHEW
from (SELECT CAST(LEN(stor_name) AS CHAR) TWO
from stores where LEN(State)
< (3+5)) EIGHT
WHERE (EIGHT.TWO - 2) > 2
January 16, 2006 11:03 PM
 

Robert Tomlin said:

Select Substring(Phone,1,3)
From authors as "Area For Me"
Where Contract<>0
and City='Palo Alto'
Order by Contract,City
January 17, 2006 1:09 AM
 

Andrew said:


-I know I'm cheating but..

Declare @Command Varchar(255)
Select @Command=Replace(Replace(replace(replace(replace('
------------------------------------------

A stressed and hard-worked DBA
got his wife in the family way
it is true his job wrecks
all enjoyment of sex
but at last we now know he aint gay

-------------------------------------------
','A stressed and hard-worked DBA','Select title, Stor_ID, Ord_Num, Qty, Ord_Date')
,'got his wife in the family way','from titles left outer join sales')
,'it is true his job wrecks','on titles.title_ID')
,'all enjoyment of sex','=sales.Title_ID')
,'but at last we now know he aint gay','where Qty is null')
execute (@Command)
January 17, 2006 12:58 PM
 

Tim Graham said:

CREATE TABLE [Dumb!] (ID int IDENTITY)
SELECT * FROM sysconstraints WHERE OBJECTPROPERTY
( constid , 'IsPrimaryKey') = 1
DROP TABLE [Dumb!]
CREATE TABLE Smart (Natural int CONSTRAINT [PK] PRIMARY KEY )
January 20, 2006 12:26 AM
 

Phil Factor said:

A distinguished panel of Judges at Red-Gate have decided that the limerick by Robert Tomlin ...

Select Substring(Phone,1,3)
From authors as "Area For Me"
Where Contract<>0
and City='Palo Alto'
Order by Contract,City

..is the winner.
As well as a prize of a book, he gets the Phil Factor 'Crimes Against Poetry' award.
A special 'Determination and Persistance' award goes to Doug Stewart.

January 23, 2006 4:51 PM
 

Tim Black said:

Select Notes,convert (real,price ) to_Yen
From titles given_out_by, sales men
where payterms not in ('question')
and exists ( select 'Suggestions'
from publishers,authors and_ken )

or in english ( almost )
Select notes “convert real price to Yen” , from titles by salesmen, where payterm’s not in question and exists select suggestions from publishers,authors and Ken


January 27, 2006 2:03 PM
You need to sign in to comment on this blog
<January 2006>
SuMoTuWeThFrSa
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234
How to Kill a Company in One Step or Save it in Three
 The majority of companies that suffer a major data loss subsequently go out of business. Wesley David... Read more...

Migrating from OCS 2007 R2 to Lync: Part 4
 Having migrated the rest of our users and legacy resources across and started getting ready to... Read more...

Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL... Read more...

Seth Godin: Big in the IT Business
 Seth Godin has transformed our understanding of marketing in IT. He invented the concept of 'permission... Read more...

Using SQL Test Database Unit Testing with TeamCity Continuous Integration
 With database applications, the process of test and integration can be frustratingly slow because so... Read more...