We leave it to the readers to provide the answer! He saw it as a tribute to MS providing upward compatibility in their 4GL dialect and his skill. I appreciate the sentiment, but I would consider the fact of my old T-SQL still running as a problem rather than a brag. In those days, we used triggers where we now have DRI actions. We nested subqueries where we now have an OVER clause. The ISO naming conventions did not exist.
|Published (Last):||12 January 2010|
|PDF File Size:||12.48 Mb|
|ePub File Size:||9.93 Mb|
|Price:||Free* [*Free Regsitration Required]|
You want to write a query to produce a list of the available seats in the restaurant, set up in blocks by their starting and ending seat numbers. Oh yes, the gimmick is that the database resides on a personal digital assistant and not a mainframe computer.
As part of the exercise, you must do this with the smallest amount of storage possible. Assume each seat number is an integer. The available seating query would be based on the flag. This would be 1, rows of one integer and one character for the whole restaurant and would work pretty well, but we have that minimal storage requirement. Answer 1 The flag can be represented by a plus or minus on the seat number itself to save the extra column, but this is very bad relational practice; two attributes are being collapsed into one column.
But it does keep us at 1, rows. Answer 2 The second thought is to create a second table with a single column of occupied seating and to move numbers between the occupied and available tables. That would require a total of 1, rows in both tables, which is a little weird, but it leads to the next answer.
They act as sentries on the edge of the real seating to make the code easier. Delete each seat from the table as it is occupied and insert it back when it is free again. The Restaurant table can get as small as the two dummy rows if all the seating is taken, but no bigger than 1, rows 2, bytes if the house is empty.
This could be useful if the restaurant is broken into sections in some way. He was working on a pension fund problem. The longest period could be 60 years, with 1 month in each year. Some people might work four years and not the fifth, and thus not qualify for a pension at all. HINT: For each employee in each year, insert a row even in the years in which the employee did not work.
It not only makes the query easier, but you also have a record to update when you get in new information. Answer 1 This query will get me the starting and ending years of consecutive periods where 1 the employee worked i. I would guess that the plan estimation is being fooled by the three-way self-joins, which are usually very expensive. For me the biggest problem was to get sum of earnings in the first position.
An employee works for 10 full years starting in January of the first year and six months in the final, 11th year. The most recent 60 months start in the middle of a year, in July. There is no data available in the database to show the salary earned during the first six months of the month period.
An average monthly salary for that year could be used, but that would not properly account for a pay raise that occurred in July of the first year used for the calculation.
This issue will occur any time the number of months needed to make 60 is less than the number of months worked in the earliest year that is used to build the month period. The problem is worse for hourly workers who work different numbers of hours in different months. However, he could not avoid using a subquery to find the most recent of those month blocks.
Eventually I hit on the same idea that Paul used joining the table to itself a couple of times, one to form the start of the range, another to form the end of the range, and a third to aggregate across the range.
Naturally I ran into the same problem as Paul mentions in his post: that you wind up with several potential ranges of consecutive years that have at least 60 months, and you only want the last one.
See why the MIN works? I like to use NULLs for missing values, but you could use a string constant instead. The reason for this solution is that the original problem was given for a situation in Oracle and Oracle lacks certain SQL features.
That is, those columns should be blank after the first line. You are destroying First Normal Form 1NF and doing something that should be done in the front end and not the database. You want to print out a report with one line per employee that gives both numbers, and shows a NULL if either or both numbers are missing. Married personnel could share the same fax or home telephones, and a single line could be both voice and fax services.
Answer 1 You can do a lot of things wrong with this query. The first thought is to construct the home telephone information as a query in its own right. Answer 3 Kishore Ganji, a database analyst at Liz Claiborne Cosmetics, proposed a solution that avoids the two views altogether.
This is pretty easy if you can put each salary in one row in the result set and let the host program format it. That is the first programming problem for the reader, in fact. Oh, I forgot to mention that the application programmers are a bunch of lazy bums who want to have both the current and previous salary information on one row for each employee.
This will let them write a very simple cursor statement and print out the report without any real work on their part.
Jack spoke with Fabian Pascal, who runs the www. Pascal replied that this query could not be done. Oh, I forgot to mention an additional constraint on the query; Jack is working in Oracle.
This product was not up to SQL standards at the time i. The other approaches build all the target output rows first and then find the ones we want. This query finds the raw rows first and puts them together last. The table is used only once, no self-joins, but a hidden sort will be required for the RANK function. This is probably not a problem in SQL engines that use contiguous storage or have indexing that will group the employee names together. The conversion of the legacy database was done by making each Paradox table into a Watcom SQL table, without any thought of normalization or integrity rules—just copy the column names and data types.
The system tracks teams of personnel to work on jobs. Each job has a slot for a single primary mechanic and a slot for a single optional assistant mechanic. Do not worry about normalization or the other tables for this problem. The same person can be assigned as both a primary and assistant mechanic on the same job. You could do the report with views for primary and assistant mechanics, and then put them together, but you can avoid all of this mess with the following query: SELECT Jobs.
The double quotation marks make the word into an identifier. When the same word is in single quotation marks, it is treated as a character string. If you have two or more primary mechanics or two or more assistant mechanics on a job, then you will get an error, as you should. That gives you the outer joins you wanted.
On the face of it, this makes sense. Therefore, team information will have to be entered before job records. Using a referential integrity constraint will enforce this constraint. That is, the reference is to be to the same number of columns of the same datatypes in the same order.
But look at what we have done!
Joe Celko's SQL Puzzles and Answers, Second Edition, Second Edition
Celko’s SQL Stumper: The Data Warehouse Problem
Subscribe to RSS