SQL解惑(第2版) 的一些样题

aszsj1984

好贴，佩服

newkid

原帖由 jackiewss 于 2010-3-16 15:16 发表
我认为第一题“谜题18　广告信件”的另一个答案是不是可以这样写：
DELETE FROM consumers WHERE fam IS NULL
AND EXISTS (SELECT * FROM consumers C1
            WHERE consumers.con_id = C1.fam);

作者没有说明CON_ID和FAM之间的关系。但是你用关联子查询的思路是可行的，把第一个答案改写为等价的：

DELETE FROM consumers WHERE fam IS NULL
AND EXISTS (SELECT * FROM consumers C1
            WHERE consumers.address = C1.address
                  AND c1.fam IS NOT NULL);

newkid

原帖由 dingjun123 于 2010-3-16 11:50 发表
希望newkid好好维护这个帖子，争取把所有谜题用oracle最佳实现，让我等也好好学习一把！然后整理成PDF，哈哈

里面很多题目都很简单，也没什么意思。
我的本意是尽量发现更多的答案，拓宽思路，当然有很多答案一看就很笨，但也可以作为反面教材。在实际工作中，如果碰到问题可以有多种解决方法，就可以选择计划最佳的。希望大家踊跃补充，各显神通！

newkid

PUZZLE 64 BOXES

This is an interesting puzzle from Mikito Harakiri. Imagine that you have
a Cartesian space, and you are filling it with n-dimensional boxes. The
boxes are modeled as shown below:
CREATE TABLE Boxes
(box_id INTEGER NOT NULL PRIMARY KEY,
dimension_nbr INTEGER NOT NULL,
low INTEGER NOT NULL,
high INTEGER NOT NULL);
The problem is to find all the pairs of boxes that intersect (e.g., the
3D cubes):
A = {(x,0,2),(y,0,2),(z,0,2)}
B = {(x,1,3),(y,1,3),(z,1,3)}
C = {(x,10,12),(y,0,4),(z,0,100)}

Boxes A and B intersect, but box C intersects neither A nor B. Bonus
point: is there anything special about this kind of query?

Answer #1
This is from Bob Badour:
SELECT B1.box_id AS box1, B2.box_id AS box2
FROM  Boxes AS B1, Boxes AS B2
WHERE B1.box_id < B2.box_id
       AND NOT EXISTS (SELECT *
                         FROM Boxes AS B3, Boxes AS B4
                        WHERE B3.box_id = B1.box_id
                              AND B4.box_id = B2.box_id
                              AND B4.dimension_nbr = B3.dimension_nbr
                              AND (B4.high < B3.low OR B4.low > B3.high)
                      )
GROUP BY B1.box_id, B2.box_id;

Mikito Harakiri thought that this was interesting because it is a
generalization of relational division. Now, relational division expressed
in SQL is either a query with two levels of nested subqueries, or one level
of nested subqueries with EXCEPT operators, or the query with counting
subquery in the HAVING clause. Bob’s query is none of those.

SELECT B1.box_id AS box1, B2.box_id AS box2, B1.dim
  FROM Boxes AS B1, Boxes AS B2
WHERE B1.low BETWEEN B2.low AND B2.high
       AND B1.dim = B2.dim
GROUP BY B1.box_id, B2.box_id
HAVING COUNT(*) = (SELECT COUNT(*)
                     FROM Boxes AS B3
                     WHERE B3.box_id = B1.box_id
                     AND B3.dim = B1.dim);

Answer #2
Try a slightly different approach. Begin with one dimension and
stronger DDL:

CREATE TABLE Boxes
(box_id CHAR (1) NOT NULL,
dim CHAR(1) NOT NULL,
PRIMARY KEY (box_id, dim),
low INTEGER NOT NULL,
high INTEGER NOT NULL,
CHECK (low < high));

INSERT INTO Boxes VALUES ('A', 'x', 0, 2);
INSERT INTO Boxes VALUES ('B', 'x', 1, 3);
INSERT INTO Boxes VALUES ('C', 'x', 10, 12);

--in 1 dimension
SELECT B1.box_id, B2.box_id
FROM Boxes AS B1, Boxes AS B2
WHERE B1.box_id < B2.box_id
      AND (B1.high - B1.low) + (B2.high - B2.low)
          > ABS(B1.high - B2.low);

This says that two lines segments overlap when their combined
lengths are less than their span in the dimension. I am using math rather
than BETWEEN-ness.
Cubes A={(x,0,2),(y,0,2),(z,0,2)} and B={(x,1,3),(y,1,3),(z,1,3)}
intersect, while box C={(x,10,12),(y,0,4),(z,0,100)}. Let's go to two
dimensions:
INSERT INTO Boxes VALUES ('A', 'y', 0, 2);
INSERT INTO Boxes VALUES ('B', 'y', 1, 3);
INSERT INTO Boxes VALUES ('C', 'y', 0, 4);

--in 2 dimension: first shot:
SELECT B1.box_id, B2.box_id, B1.dim
  FROM Boxes AS B1, Boxes AS B2
WHERE B1.box_id < B2.box_id
       AND B1.dim = B2.dim
       AND (B1.high - B1.low) + (B2.high - B2.low)
       ABS(B1.high - B2.low);

Now look for a common area in (x,y) by having overlaps in both
dimensions:
SELECT B1.box_id, B2.box_id
  FROM Boxes AS B1, Boxes AS B2
WHERE B1.box_id < B2.box_id
       AND B1.dim = B2.dim
       AND (B1.high - B1.low) + (B2.high - B2.low) > ABS(B1.high - B2.low)
GROUP BY B1.box_id, B2.box_id
HAVING COUNT(B1.dim) = 2;

--3 dimensions:
INSERT INTO Boxes VALUES ('A', 'z', 0, 2);
INSERT INTO Boxes VALUES ('B', 'z', 1, 3);
INSERT INTO Boxes VALUES ('C', 'z', 0, 100);
Now change the HAVING clause to
COUNT(B1.dim) = 3
or
(SELECT COUNT (DISTINCT dim) FROM Boxes)
if you do not know the dimension of the space you are using.

题目大意：BOXES表描述每个箱子占据的空间（维度，起点坐标，终点坐标），要求列出在空间上有交叠的每一对箱子。

CREATE TABLE Boxes
(box_id CHAR (1) NOT NULL,
dim CHAR(1) NOT NULL,
PRIMARY KEY (box_id, dim),
low INTEGER NOT NULL,
high INTEGER NOT NULL,
CHECK (low < high));

INSERT INTO Boxes VALUES ('A', 'x', 0, 2);
INSERT INTO Boxes VALUES ('B', 'x', 1, 3);
INSERT INTO Boxes VALUES ('C', 'x', 10, 12);
INSERT INTO Boxes VALUES ('A', 'y', 0, 2);
INSERT INTO Boxes VALUES ('B', 'y', 1, 3);
INSERT INTO Boxes VALUES ('C', 'y', 0, 4);
INSERT INTO Boxes VALUES ('A', 'z', 0, 2);
INSERT INTO Boxes VALUES ('B', 'z', 1, 3);
INSERT INTO Boxes VALUES ('C', 'z', 0, 100);

我写完这个答案才发现和作者的#2很像：
SELECT b1.box_id,b2.box_id
  FROM Boxes b1, Boxes b2
WHERE b1.box_id < b2.box_id
       AND b1.dim = b2.dim
       AND b1.high > b2.low
       AND b2.high > b1.low
GROUP BY b1.box_id,b2.box_id
HAVING COUNT(*)=3;


如果仅仅是三维也可以这么写：

WITH b AS (
SELECT box_id
      ,MAX(CASE WHEN dim='x' THEN low  END) AS x1
      ,MAX(CASE WHEN dim='x' THEN high END) AS x2
      ,MAX(CASE WHEN dim='y' THEN low  END) AS y1
      ,MAX(CASE WHEN dim='y' THEN high END) AS y2
      ,MAX(CASE WHEN dim='z' THEN low  END) AS z1
      ,MAX(CASE WHEN dim='z' THEN high END) AS z2
  FROM boxes
GROUP BY box_id
)
SELECT b1.box_id,b2.box_id
  FROM b b1,b b2
WHERE b1.box_id<b2.box_id
       AND b1.x2>b2.x1 AND b2.x2>b1.x1
       AND b1.y2>b2.y1 AND b2.y2>b1.y1
       AND b1.z2>b2.z1 AND b2.z2>b1.z1;

newkid

PUZZLE 65 AGE RANGES FOR PRODUCTS
David Poole posted what he called a simple problem with which he was
struggling. Given an inventory of products, he has prices broken down
by age ranges.
CREATE TABLE PriceByAge
(product_id INTEGER NOT NULL,
low_age INTEGER NOT NULL,
high_age INTEGER NOT NULL,
CHECK (low_age < high_age),
product_price DECIMAL (12,4) NOT NULL,
PRIMARY KEY (product_id, low_age));

INSERT INTO PriceByAge ('Product1', 5, 15, 20.00);
INSERT INTO PriceByAge ('Product1', 16, 60, 18.00);
INSERT INTO PriceByAge ('Product1', 65, 150, 17.00);
INSERT INTO PriceByAge ('Product2', 1, 5, 20.00);
...etc

You are also given a table containing various persons' ages. This table
should be a VIEW, based on birthdates, but let me be sloppy.

CREATE TABLE Buyers
(person_name VARCHAR(20) NOT NULL PRIMARY KEY,
age INTEGER NOT NULL CHECK (age > 0));

What he wanted was to bring back all products that could satisfy all
ages.
In the sample data, if I had an age of 4 in the list of ages, then no
rows for products that did not have an age band that included 4 were
to be returned.

Answer #1
Define "all ages." I will guess you mean a range of 1 to (max_age), say
150, since Elizabeth Israel, also known as Ma Pampo, was born on
January 27, 1875 and is believed to be the world’s oldest living human
being, so we should be safe. A quick way is to use your auxiliary
Sequence table.
SELECT P.product_id
  FROM PriceByAge AS P, Sequence AS S
WHERE S.seq BETWEEN P.low_age AND P.high_age
       AND S.seq <= 150
GROUP BY P.product_id
HAVING COUNT(seq) = 150;

谜题65　产品面向的年龄范围
PriceByAge表列出了每项产品的适用年龄范围及其价格，现在要求列出满足所有年龄的产品。

CREATE TABLE PriceByAge
(product_id VARCHAR2(20) NOT NULL,
low_age INTEGER NOT NULL,
high_age INTEGER NOT NULL,
CHECK (low_age < high_age),
product_price DECIMAL (12,4) NOT NULL,
PRIMARY KEY (product_id, low_age));

INSERT INTO PriceByAge VALUES('Product1', 5, 15, 20.00);
INSERT INTO PriceByAge VALUES('Product1', 16, 60, 18.00);
INSERT INTO PriceByAge VALUES('Product1', 65, 150, 17.00);
INSERT INTO PriceByAge VALUES('Product2', 1, 5, 20.00);

作者的答案是生成一个连续数集合(相当于SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY ROWNUM<=150) 然后进行表连接。
其实这道题和前面"谜题59　合并时间段"很像，因此我们可以有其他方法：

VAR v_low NUMBER;
VAR v_high NUMBER;
EXEC :v_low :=10;
EXEC :v_high :=50;

SELECT product_id,MIN(low_age),MAX(high_age)
  FROM (SELECT p.*
              ,SUM(broken) OVER (PARTITION BY product_id ORDER BY low_age,high_age) flag
         FROM (SELECT p.*
                     ,(CASE WHEN low_age-1 <= MAX(high_age) OVER (PARTITION BY product_id ORDER BY low_age,high_age ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
                           THEN 0
                           ELSE 1
                       END) AS broken
                 FROM PriceByAge p
                WHERE low_age BETWEEN :v_low AND :v_high OR high_age BETWEEN :v_low AND :v_high
               ) p
       )  
GROUP BY product_id,flag
HAVING MIN(low_age) <= :v_low
       AND MAX(high_age) >= :v_high
;

newkid

PUZZLE 66 SUDOKU

作者并没有给出解法。他建了一张表来存放所有单元的候选值：

CREATE TABLE SudokuGrid
(i INTEGER NOT NULL
CHECK (i BETWEEN 1 AND 9),
j INTEGER NOT NULL
CHECK (j BETWEEN 1 AND 9),
val INTEGER NOT NULL
CHECK (val BETWEEN 1 AND 9),
region_nbr INTEGER NOT NULL,
PRIMARY KEY (i, j, val));

这个SQL用1-9填充所有的格子，总共有9x9x9=729行：
INSERT INTO SudokuGrid (i, j, val, region_nbr)
WITH Digits AS (SELECT ROWNUM d FROM DUAL CONNECT BY ROWNUM<=9)
SELECT D1.d, D2.d, D3.d
      ,10*CEIL(D1.d/3) + CEIL(D1.d/3) AS region_nbr  -- 原来是10*((D1.d+2)/3) + ((D2.d+2)/3) 其实是错的被我改过来了
FROM Digits D1
CROSS JOIN Digits D2
CROSS JOIN Digits D3;

接下来这个SQL根据一个已知节点删除这个值在同一行、同一列、同一区的其他位置:
DELETE FROM SudokuGrid
WHERE CASE WHEN :my_i = i AND :my_j = j AND my_val = val
           THEN 'Keep'
           WHEN :my_i = i AND :my_j = j AND my_val <> val
           THEN 'Delete'
           WHEN :my_i = i AND :my_j <> j -- row
           THEN 'Delete'
           WHEN :my_i <> i AND :my_j = j -- column
           THEN 'Delete'
           WHEN i <> :my_i AND j <> :my_j -- square
           AND region_nbr = 10*CEIL(:my_i/3) + CEIL(:my_j/3) --- 原来是10*(:my_i+2)/3) + (:my_j+2)/3)其实是错的
           THEN 'Delete'
           ELSE NULL
       END = 'Delete'
       AND :my_val = val;

删除之后怎么办？作者狡猾地说剩下的事情交给读者完成。其实远远未结束，因为你还得从删剩下的行中找出新的已知的值，然后再继续下一轮删除。

PLSQL的解法参见我以前发的贴：
http://www.itpub.net/viewthread.php?tid=1074409&highlight=
里面也包含了一个牛人用10G的MODEL和11G的递归WITH子查询做出来的SQL解法。

jackiewss

原帖由 newkid 于 2010-3-16 23:10 发表


作者没有说明CON_ID和FAM之间的关系。

--> 题目第一行不是说：“这个表有一个家庭（fam）列，把家庭住址相同的客户（con_id）联系起来。”？

newkid

原帖由 jackiewss 于 2010-3-17 14:04 发表


--> 题目第一行不是说：“这个表有一个家庭（fam）列，把家庭住址相同的客户（con_id）联系起来。”？

这作者是个马大哈，书中的错误比比皆是。这个fam和con_id的关系并没有说明清楚，他的SQL中用的是地址来判断。万一某个con_id有相同地址，但是找不到其他指向这个con_id的fam的数据，那么你那个删除语句就找不出来了。

newkid

PUZZLE 74 DERIVED TABLES OR NOT?

Allen Davidson was trying to join three tables with two LEFT OUTER
JOINs and an INNER JOIN to get the SUM() of a few of the columns. Can
his query be rewritten to avoid the derived tables?
CREATE TABLE Accounts
(acct_nbr INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Accounts VALUES(1), (2), (3), (4);
Please notice that the following, Foo and Bar, are not tables, since
they have no keys.
CREATE TABLE Foo
(acct_nbr INTEGER NOT NULL
REFERENCES Accounts(acct_nbr),
foo_qty INTEGER NOT NULL);
INSERT INTO Foo VALUES (1, 10);
INSERT INTO Foo VALUES (2, 20);
INSERT INTO Foo VALUES (2, 40);
INSERT INTO Foo VALUES (3, 80);
CREATE TABLE Bar
(acct_nbr INTEGER NOT NULL
REFERENCES Accounts(acct_nbr),
bar_qty INTEGER NOT NULL);
INSERT INTO Bar VALUES (2, 160);
INSERT INTO Bar VALUES (3, 320);
INSERT INTO Bar VALUES (3, 640);
INSERT INTO Bar VALUES (3, 1);
His proposed query:
SELECT A.acct_nbr,
COALESCE(F.foo_qty, 0) AS foo_qty_tot,
COALESCE(B.bar_qty, 0) AS bar_qty_tot
FROM Accounts AS A
LEFT OUTER JOIN
PUZZLE 74 DERIVED TABLES OR NOT? 307
(SELECT acct_nbr, SUM(foo_qty) AS foo_qty
FROM Foo
GROUP BY acct_nbr) AS F
ON F.acct_nbr = A.acct_nbr
LEFT OUTER JOIN
(SELECT acct_nbr, SUM(bar_qty) AS bar_qty
FROM Bar
GROUP BY acct_nbr) AS B
ON F.acct_nbr = B.acct_nbr;
This does just fine, but are there other answers?
Results
acct_nbr foo_qty_tot bar_qty_tot
=================================
1 10 0
2 60 160
3 80 961
4 0 0

Answer #1
R. Sharma found a way to avoid one derived table, but not both:
SELECT A.acct_nbr,
COALESCE(SUM(F.foo_qty), 0) AS foo_qty_tot,
COALESCE(MAX(B.bar_qty), 0) AS bar_qty_tot
FROM (SELECT * FROM Accounts) AS A
LEFT OUTER JOIN
(SELECT * FROM Foo) AS F
ON A.acct_nbr = F.acct_nbr
LEFT OUTER JOIN
(SELECT acct_nbr, SUM(bar_qty) AS bar_qty
FROM Bar
GROUP BY acct_nbr) AS B
ON A.acct_nbr = B.acct_nbr
GROUP BY A.acct_nbr;
This will work since the derived table will always get one row per
account number so the MAX() will ensure the right value. The first one, a
derived table, won’t be needed because of the one-to-many relationship
308 PUZZLE 74 DERIVED TABLES OR NOT?
between accounts and Foo and the grouping done on
Accounts.acct_nbr.
Answer #2
Here is my answer. First, assemble the two nontables with the little-used
FULL OUTER JOIN, which will give you a table with Foo and Bar
combined and then we add the Account information.
SELECT A.acct_nbr,
COALESCE (SUM(F.foo_qty), 0) AS foo_qty_tot,
COALESCE (SUM(B.bar_qty), 0) AS bar_qty_tot
FROM Accounts AS A
LEFT OUTER JOIN
(Foo AS F
FULL OUTER JOIN
Bar AS B
ON F.acct_nbr = B.acct_nbr)
ON A.acct_nbr = F.acct_nbr
GROUP BY A.acct_nbr;
The other queries have started with the accounts, added nontable
Foo, and then added nontable Bar to the mix. Notice that the OUTER
JOIN is a table! Wow! Maybe those RDBMS principles are useful after all.
I am hoping that the Foo-Bar JOIN table will be relatively small, so
the OUTER JOIN will be quick and they can go into main storage.

DERIVED TABLE:
http://www.sqlteam.com/article/u ... te-aggregate-values
A derived table is one that is created on-the-fly using the SELECT statement, and referenced just like a regular table or view. Derived tables exist in memory and can only be referenced by the outer SELECT in which they are created. A simple use of a derived table is shown here.
题目大意就是不用子查询要求出每个账号在其他两个表中的总额。

建表及数据：
CREATE TABLE Accounts
(acct_nbr INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Accounts VALUES(1);
INSERT INTO Accounts VALUES(2);
INSERT INTO Accounts VALUES(3);
INSERT INTO Accounts VALUES(4);

CREATE TABLE Foo
(acct_nbr INTEGER NOT NULL
REFERENCES Accounts(acct_nbr),
foo_qty INTEGER NOT NULL);
INSERT INTO Foo VALUES (1, 10);
INSERT INTO Foo VALUES (2, 20);
INSERT INTO Foo VALUES (2, 40);
INSERT INTO Foo VALUES (3, 80);
CREATE TABLE Bar
(acct_nbr INTEGER NOT NULL
REFERENCES Accounts(acct_nbr),
bar_qty INTEGER NOT NULL);
INSERT INTO Bar VALUES (2, 160);
INSERT INTO Bar VALUES (3, 320);
INSERT INTO Bar VALUES (3, 640);
INSERT INTO Bar VALUES (3, 1);

作者自鸣得意的第二个答案其实是错的，我只要简单地在第二表增加一行数据：
INSERT INTO Bar VALUES (2, 10);

SELECT A.acct_nbr,
COALESCE (SUM(F.foo_qty), 0) AS foo_qty_tot,
COALESCE (SUM(B.bar_qty), 0) AS bar_qty_tot
FROM Accounts A
LEFT OUTER JOIN
(Foo F
FULL OUTER JOIN
Bar B
ON F.acct_nbr = B.acct_nbr)
ON A.acct_nbr = F.acct_nbr
GROUP BY A.acct_nbr;

  ACCT_NBR FOO_QTY_TOT BAR_QTY_TOT
---------- ----------- -----------
         1          10           0
         2         120         340     ----- 翻倍了
         4           0           0
         3         240         961

这个题目根本没什么意思，写出来就是为了纠正这位大师的低级错误。
我改一下需求，如果不用外连接要怎么写？

SELECT acct_nbr
      ,(SELECT NVL(SUM(foo_qty),0) FROM Foo WHERE acct_nbr=a.acct_nbr)
      ,(SELECT NVL(SUM(bar_qty),0) FROM Bar WHERE acct_nbr=a.acct_nbr)
FROM  Accounts a;


SELECT acct_nbr,SUM(foo_qty),SUM(bar_qty)
  FROM (SELECT acct_nbr, 0 AS foo_qty, 0 AS bar_qty FROM Accounts
        UNION ALL
        SELECT acct_nbr, foo_qty, 0  FROM Foo
        UNION ALL
        SELECT acct_nbr, 0 , bar_qty FROM Bar
       )
GROUP BY acct_nbr;

[ 本帖最后由 newkid 于 2010-3-18 06:05 编辑 ]

durexlollipop

细细拜读