SQL Puzzlers, by Joe Celko
Joe Celko is an independent consultant in Austin, Texas, and the author of SQL Puzzles and Answers (2006), Joe Celko's SQL for Smarties: Advanced SQL Programming (2005), and Joe Celko's Trees and Hierarchies in SQL for Smarties (2004). Celko's Theater-Seat-Assignment SQL Puzzle In many situations, auxiliary tables are faster and more appropriate than SQL with computations (a topic I address at length this week in "Celko on SQL: Auxiliary Tables vs. Declarative Coding"). To illustrate, consider a classic problem. You have a theater and a bunch of seats you wish to sell for a performance (or think of seats for an airline flight). The seats have a sequential serial number from 1 to (n) for inventory. But in the theater building and on the tickets, the seats are arranged in rows of (k) seats and referenced by the pair (row_nbr, seat_within_row_nbr). You could construct an auxiliary table like this: >>Continue reading "Celko's Theater-Seat-Assignment SQL Puzzle" Posted Monday, November 5, 2007 7:51 AM >>Comments And the 'Email Validation' SQL Puzzle Winner Is... The winner of last week's 'Email Address Validation' SQL puzzle is "Guest" (see comments), because he/she bothered to do the research and come up with an answer that is generic enough to port to any SQL dialect with a SIMILAR TO or a regexp() function. So, "Guest" please email me with your snail mail address (and some attempt to validate your SQL mastery/identity) and I'll send you one of my books. My answer to last week's puzzle is as follows: >>Continue reading "And the 'Email Validation' SQL Puzzle Winner Is..." Posted Thursday, September 27, 2007 8:59 AM >>Comments Celko's Email-Address-Validation SQL Puzzle You've probably noticed that many Web sites use the customer's email addresses as an identifier — an email address has a very easy validation. We have all received messages something like this: "Thanks for signing up for 'The Leech Farmer's Monthly' email newsletter! We are sending you a confirmation at your email address with your temporary password." How many ways can you write CHECK() column constraint to validate an email address? Call the column "email" just so all entries look alike (yes, you really should use " >>Continue reading "Celko's Email-Address-Validation SQL Puzzle" Posted Monday, September 17, 2007 7:26 AM >>Comments Celko's 'Can't Be Your Own Supervisor' SQL Puzzle For this month's puzzler, consider a posting in a Newsgroup by Patrick L. Nolan at Stanford University. He has a small database with the following business rules: 1) Every person in the database is uniquely defined by a single key, their user_id. 2) Everyone is assigned a job category, call them 'A', 'B' and 'X'. 3) Everyone in job category 'X' has a supervisor who must be in either job category 'A' or job category 'B'. 4) Nobody can be their own supervisor. One proposal was to divide job category 'X' into two, call them 'XA' and 'XB' respectively. All the 'XA' people would have 'A' supervisors, and all the 'XB' people would have 'B' supervisors. Nolan immediately noticed that there is redundancy and the possibility of inconsistency. Suppose somebody in job category 'XA' somehow gets assigned to a supervisor in job category 'B', contrary to the definition of 'XA'. Can you think of a way to do this in pure DDL? >>Continue reading "Celko's 'Can't Be Your Own Supervisor' SQL Puzzle" Posted Monday, August 20, 2007 11:35 AM >>Comments Cooking an old puzzle I just got an email about Puzzle #11 in my old book SQL PUZZLES & ANSWER from Rainer Gemulla at TU Dresden, Fak. Informatik, Institut SyA, in Dresden, Germany. It is a very nice cook and it is embarassing to see how needlessly complex the other answers were: >>Continue reading "Cooking an old puzzle" Posted Monday, April 25, 2005 9:51 AM >>Comments More on streaming databases There is a good article on streaming databases with lots of product names and stuff suitable for googling inthe current issue of INTELLIGENT ENTERPRISE. >>Continue reading "More on streaming databases" Posted Saturday, April 16, 2005 10:27 AM >>Comments More on streaming databases There is a good article on streaming databases with lots of product names and stuff suitable for googling inthe current issue of INTELLIGENT ENTERPRISE. >>Continue reading "More on streaming databases" Posted Saturday, April 16, 2005 10:27 AM >>Comments An Old Celko Puzzle If you go over to http://www.dbdebunk.com/page/page/666711.htm, you will find a letter to the editor from someone named PV about an old column of mine. Here is the jist of it: Back in June of 1996, Jack Wells submitted this SQL problem to my SQL FOR SMARTIES column. >>Continue reading "An Old Celko Puzzle" Posted Friday, March 25, 2005 8:13 PM >>Comments Hello everyone! INTELLIGENT ENTERPRISE once more changed format at the end of last year and my long-time column went away. However CMP asked me if I would like to do a blog on SQL, databases and the other things that interests me. >>Continue reading "Hello everyone!" Posted Tuesday, March 15, 2005 2:09 PM >>Comments
|
Blog Channels
Cindi Howson on Business Intelligence The Brain Food Blogger Tony Byrne on Content Management SQL Puzzlers by Joe Celko Rajan Chandras on IT & Information Management Seth Grimes on Analytics In Context by Doug Henschen Phil Kemelor on Web Analytics Sandy Kemsley's Column Two Nelson King on Enterprise App Development David Linthicum on Software as a Service Natural Insight, By Mark Madsen Alan Pelz-Sharpe on Content Management Mark Smith on Performance Management Neil Raden on Business Intelligence Bruce Silver on Business Process Management Product Maven Subscribe to RSS Archives
|
|