# SQL Puzzles & Answers- P7

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:40

0
44
lượt xem
4

## SQL Puzzles & Answers- P7

Mô tả tài liệu

Tham khảo tài liệu 'sql puzzles & answers- p7', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: SQL Puzzles & Answers- P7

1. 222 PUZZLE 55 PLAYING THE PONIES UNION ALL SELECT show_name, COUNT(*), 'show_name' FROM RacingResults GROUP BY show_name; Now use that view to get the final summary: SELECT horse, SUM(tally) FROM InMoney GROUP BY horse; There are two reasons for putting those string constants in the SELECT lists. The first is so that we will not drop duplicates incorrectly in the UNION ALL. The second reason is so that if the bookie wants to know how many times each horse finished in each position, you can just change the query to: SELECT horse, position, SUM(tally) FROM InMoney GROUP BY horse, position; Answer #2 If you have a table with all the horses in it, you can write the query as: SELECT H1.horse, COUNT(*) FROM HorseNames AS H1, RacingResults AS R1 WHERE H1.horse IN (R1.win_name, P1.place_name, R1.show_name) GROUP BY H1.horse; If you use an OUTER JOIN, you can also see the horse that did not show up in the RacingResults table. There is an important design principle demonstrated here; it is hard to tell if something is an entity or an attribute. A horse is an entity and therefore should be in a table. But the horse’s name is also used as a value in three columns in the RacingResults table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
2. PUZZLE 55 PLAYING THE PONIES 223 Answer #3 Another approach that requires a table of the horses’ names is to build the totals with scalar subqueries. SELECT H1.horse, (SELECT COUNT(*) FROM RacingResults AS R1 WHERE R1.win_name = H1.horse) + (SELECT COUNT(*) FROM RacingResults AS R1 WHERE R1.place_name = H1.horse) + (SELECT COUNT(*) FROM RacingResults AS R1 WHERE R1.show_name = H1.horse) FROM Horses AS H1; While this works, it is probably going to be expensive to execute. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3. 224 PUZZLE 56 HOTEL ROOM NUMBERS PUZZLE 56 HOTEL ROOM NUMBERS Ron Hiner put this question on CompuServe. He had a data conversion project where he needed to automatically assign some values to use as part of the PRIMARY KEY to a table of hotel rooms. The floor number is part of the PRIMARY KEY is the FOREIGN KEY to another table of floors within the building. The part of the hotel key we need to create is the room number, which has to be a sequential number starting at x01 for each floor number x. The hotel is small enough that we know we will only have three-digit numbers. The table is defined as follows: CREATE TABLE Hotel (floor_nbr INTEGER NOT NULL, room_nbr INTEGER, PRIMARY KEY (floor_nbr, room_nbr), FOREIGN KEY floor_nbr REFERENCES Bldg(floor_nbr); Currently, the data in the working table looks like this: floor_nbr room_nbr =================== 1 NULL 1 NULL 1 NULL 2 NULL 2 NULL 3 NULL WATCOM (and other versions of SQL back then) had a proprietary NUMBERS(*) function that begins at 1 and returns an incremented value for each row that calls it. The current SQL Standard now has a DENSE_RANK () OVER() function that makes this easy to compute, but can you do it the old-fashion way? Is there an easy way via the numbering functions (or some other means) to automatically populate the room_nbr column? Mr. Hiner was thinking of somehow using a “GROUP BY floor_nbr” clause to restart the numbering back at 1. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4. PUZZLE 56 HOTEL ROOM NUMBERS 225 Answer #1 The WATCOM support people came up with this approach. First, make one updating pass through the whole database, to fill in the room_nbr numbers. This trick will not work unless you can guarantee that the Hotel table is updated in sorted order. As it happens, WATCOM can guarantee just that with a clause on the UPDATE statement, thus: UPDATE Hotel SET room_nbr = (floor_nbr*100)+NUMBER(*) ORDER BY floor_nbr; which would give these results: room_nbr ========== 1 101 1 102 1 103 2 204 2 205 3 306 followed by: UPDATE Hotel SET room_nbr = (room_nbr - 3) WHERE floor_nbr = 2; UPDATE Hotel SET room_nbr = (room_nbr - 5) WHERE floor_nbr = 3; which would give the correct results: floor_nbr room_nbr ========== 1 101 1 102 1 103 2 201 2 202 3 301 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
5. 226 PUZZLE 56 HOTEL ROOM NUMBERS Unfortunately, you have to know quite a bit about the number of rooms in the hotel. Can you do better without having to use the ORDER BY clause? Answer #2 I would use SQL to write SQL statements. This is a neat trick that is not used enough. Just watch your quotation marks when you do it and remember to convert numeric values to characters, thus: SELECT DISTINCT 'UPDATE Hotel SET room_nbr = (' || CAST (floor_nbr AS CHAR(1)) || '* 100)+NUMBER(*) WHERE floor_nbr = ' || CAST (floor_nbr AS CHAR(1)) || ';' FROM Hotel; This statement will write a result table with one column that has a test, like this: UPDATE Hotel SET room_nbr = (floor_nbr*100)+NUMBER(*) WHERE floor_nbr = 1; UPDATE Hotel SET room_nbr = (floor_nbr*100)+NUMBER(*) WHERE floor_nbr = 2; UPDATE Hotel SET room_nbr = (floor_nbr*100)+NUMBER(*) WHERE floor_nbr = 3; ... Copy this column as text to your interactive SQL tool or into a batch file and execute it. This does not depend on the order of the rows in the table. You could also put this into the body of a stored procedure and pass the floor_nbr as a parameter. You are only going to do this once, so writing and compiling procedure is not going to save you anything. Answer #3 What was such a problem in older SQLs is now trivial in SQL-99. UPDATE Hotel SET room_nbr = (floor_nbr * 100) + ROW_NUMBER()OVER (PARTITION BY floor_nbr); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6. PUZZLE 57 GAPS—VERSION ONE 227 PUZZLE 57 GAPS—VERSION ONE This is a classic SQL programming problem that comes up often in newsgroups. In the simplest form, you have a table of unique numbers and you want to either find out if they are in sequence or find the gaps in them. Let’s construct some sample data. CREATE TABLE Numbers (seq INTEGER NOT NULL PRIMARY KEY); INSERT INTO Numbers VALUES (2), (3), (5), (7), 8), (14), (20); Answer #1 Finding out if you have a sequence from 1 to (n) is very easy. This will not tell you where the gaps are, however. SELECT CASE WHEN COUNT(*) = MAX(seq) THEN 'Sequence' ELSE 'Not Sequence' END FROM Numbers; The math for the next one is obvious, but this test does not check that the set starts at one (or at zero). It is only for finding if a gap exists in the range. SELECT CASE WHEN COUNT(*) + MIN(seq) - 1 = MAX(seq) THEN 'Sequence' ELSE 'Not Sequence' END FROM Numbers; Answer #2 This will find the starting and ending values of the gaps. But you have to add a sentinel value of zero to the set of Numbers. SELECT N1.seq+1 AS gap_start, N2.seq-1 AS gap_end FROM Numbers AS N1, Numbers AS N2 WHERE N1.seq +1 < N2.seq AND (SELECT SUM(seq) FROM Numbers AS Num3 WHERE Num3.seq BETWEEN N1.seq AND N2.seq) = (N1.seq + N2.seq); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
7. 228 PUZZLE 57 GAPS—VERSION ONE Bad starts are common in this problem. For example, this query will return only the start of a gap and one past the maximum value in the Numbers table, and it misses 1 if it is not in Numbers. -- does not work; only start of gaps SELECT N1.seq + 1 FROM Numbers AS N1 LEFT OUTER JOIN Numbers AS N2 ON N1.seq = N2.seq -1 WHERE N2.seq IS NULL; A more complicated but accurate way to find the first missing number is: --first missing seq SELECT CASE WHEN MAX(seq) = COUNT(*) THEN MAX(seq) + 1 WHEN MIN(seq) < 1 THEN 1 WHEN MAX(seq) COUNT(*) THEN (SELECT MIN(seq)+1 FROM Numbers WHERE (seq + 1) NOT IN (SELECT seq FROM Numbers)) ELSE NULL END FROM Numbers; The first case adds the next value to Numbers if there is no gap. The second case fills in the value 1 if it is missing. The third case finds the lowest missing value. Answer #3 Let’s use the usual Sequence auxiliary table and one of the SQL-99 set operators: SELECT X.seq FROM ((SELECT seq FROM Sequence AS S1) EXCEPT ALL (SELECT seq FROM Numbers AS N1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8. PUZZLE 57 GAPS—VERSION ONE 229 WHERE seq
9. 230 PUZZLE 58 GAPS—VERSION TWO PUZZLE 58 GAPS—VERSION TWO Here is a second version of the classic SQL programming problem of finding gaps in a sequence. How many ways can you do it? Can you make it better with SQL-92 and SQL-99 features? CREATE TABLE Tickets (buyer_name CHAR(5) NOT NULL, ticket_nbr INTEGER DEFAULT 1 NOT NULL CHECK (ticket_nbr > 0), PRIMARY KEY (buyer_name, ticket_nbr)); INSERT INTO Tickets VALUES ('a', 2), ('a', 3), ('a', 4), ('b', 4), ('c', 1), ('c', 2), ('c', 3), ('c', 4), ('c', 5), ('d', 1), ('d', 6), ('d', 7), ('d', 9), ('e', 10); Answer #1 Tom Moreau, another well-known SQL author in Toronto, came up with this solution that does not use a UNION ALL. It finds buyers with a gap in the tickets they hold, but it does not “fill in the holes” for you. For example, Mr. D is holding (1, 6, 7, 9) so he has gaps for (2, 3,4, 5, 8), but Tom did not count Mr. A because there is no gap within the range he holds. SELECT buyer_name FROM Tickets GROUP BY buyer_name HAVING NOT (MAX(ticket_nbr) - MIN(ticket_nbr)