Question: [Joe Celko’s Equal Sets Puzzle]Set theory has two symbols for subsets. One is a “horseshoe” on its side, which means that set A is contained within set B and is sometimes called a proper subset. The other is the same symbol with a horizontal bar under it, which means
Question: [Joe Celko’s Dataflow Diagrams Puzzle]Tom Bragg posted a version of this problem on the CASE Forum on CompuServe. You have a table of dataflow diagrams (DFDs), which has the name of the diagram, the names of the bubbles in each diagram and the labels on the flow lines. It
Question: [Joe Celko’s Milestones Puzzle]This puzzle, in a little different form, came from Brian Young. His system tracks a series of dates (milestones) for each particular type of service (tos) that they sell. These dates constitute the schedule for the delivery of the service and vary with the type of
Question: We are putting together a book with contributions from many Anthologies (identified by their contrnum). We want to find all Anthologies thathave articles in exactly two out of three categories in the book for a specified set of three categories that we put into the query as parameters. CREATE
Question: How would I change the following statement to yieldonly unique values? select * FROM WEBDBASE where ST = ‘#State#’ AND #SPECIALTY# = ‘x’; Answer: You can’t get unique values when using SELECT *. However, you can specify the DISTINCT keyword immediately before the column name of the column that
Question: How can I use SQL to specify a query like the example below: select * from mytable where column like ‘a[bc]’This should match both “ab” and “ac” but not “ad.” Answer: SQL does not have strong pattern matching facilities. For the example you’ve provided, you’ll have to construct the
Question: How do I arrange to sort within a GROUP BY clauserather than over the complete result set, e.g. L0 PDV …… L90 PDV …… L180 PDV …… L270 PDV …… L0 PPE …… L90 PPE ……I want to force col1 to be sorted within each group. Answer: If you
Question: [Joe Celko’s Employment Agency Puzzle]You are running an employment agency that has requests for jobs and applications from candidates. Both of these forms include a list of the skills required or offered, and whether this is a primary or secondary skill. The tables look like this: CREATE TABLE CandidateSkills
Question: [Joe Celko’s Personnel Problem Puzzle]Daren Race was trying to aggregate the results from an aggregate result set using Gupta’s SQLBase and could not think of any way other than using a temporary table or a VIEW. This is an example of what he was doing: Personnel: name deptid ===============