SQL 小测验每周一题

newkid

2012-3-10 答案ABCD. 比较罕见的答案是D.

ORACLE提供了多达四种的不等比较符：
<>
!=
^=

最后一种是ASCII 170后面再跟一个等号，我这里打不出来。这个不保证在所有平台下能用。

---------------------------------------
2012-3-17 全外连接
作者：Kim Berg Hansen
难度：中

我创建了如下的表并填入数据:
CREATE TABLE plch_salaries
(
   emp_id      INTEGER  NOT NULL
, salary      NUMBER   NOT NULL
)
/

CREATE TABLE plch_commissions
(
   emp_id      INTEGER  NOT NULL
, commission  NUMBER   NOT NULL
)
/

INSERT INTO plch_salaries VALUES (10, 110000)
/

INSERT INTO plch_salaries VALUES (20, 120000)
/

INSERT INTO plch_salaries VALUES (30, 130000)
/

INSERT INTO plch_commissions VALUES (10, 10000)
/

INSERT INTO plch_commissions VALUES (30, 30000)
/

INSERT INTO plch_commissions VALUES (40, 40000)
/

COMMIT
/

老板想要一个每个员工的报酬清单（报酬=工资salary＋佣金commission）。每个员工可能有工资或佣金或者两种都有。

哪些选项会产生如下的输出：
    EMP_ID        PAY
---------- ----------
        10     120000
        20     120000
        30     160000
        40      40000

(A)
SELECT NVL(c.emp_id, s.emp_id) emp_id
     , NVL(s.salary, 0) + NVL(c.commission, 0) pay
  FROM plch_salaries s
     , plch_commissions c
WHERE c.emp_id(+) = s.emp_id(+)
ORDER BY emp_id

(B)
SELECT emp_id
     , NVL(s.salary, 0) + NVL(c.commission, 0) pay
  FROM plch_salaries s
  FULL OUTER JOIN plch_commissions c
    ON c.emp_id = s.emp_id
ORDER BY emp_id

(C)
SELECT emp_id
     , s.salary + c.commission pay
  FROM plch_salaries s
  FULL OUTER JOIN plch_commissions c
  USING (emp_id)
ORDER BY emp_id

(D)
SELECT emp_id
     , pay
  FROM (
   SELECT s.emp_id
        , s.salary + NVL(c.commission, 0) pay
     FROM plch_salaries s
        , plch_commissions c
    WHERE c.emp_id(+) = s.emp_id
    UNION ALL
   SELECT c.emp_id
        , NVL(s.salary, 0) + c.commission pay
     FROM plch_salaries s
        , plch_commissions c
    WHERE c.emp_id = s.emp_id(+)
      AND s.emp_id IS NULL
)
ORDER BY emp_id

(E)
SELECT COALESCE(c.emp_id, s.emp_id) emp_id
     , COALESCE(s.salary, 0) + COALESCE(c.commission, 0) pay
  FROM plch_salaries s
  FULL OUTER JOIN plch_commissions c
    ON c.emp_id = s.emp_id
ORDER BY emp_id

(F)
SELECT emp_id
     , COALESCE(s.salary, 0) + COALESCE(c.commission, 0) pay
  FROM plch_salaries s
  FULL OUTER JOIN plch_commissions c
  USING (emp_id)
ORDER BY emp_id        

(G)
SELECT emp_id
     , sum(pay) as pay
  FROM
  ( SELECT emp_id, salary     as pay FROM plch_salaries
    UNION ALL
    SELECT emp_id, commission as pay FROM plch_commissions
  )
GROUP BY emp_id
ORDER BY emp_id

SGPM_OUT

D,E,F,G
A不能 c.emp_id(+) = s.emp_id(+) 这样写
B emp_id 没有指定是那个表的

牛MAN牛

ABCDF

newkid

2012-3-17 答案DEFG

A: WHERE c.emp_id(+) = s.emp_id(+) 这种写法不被支持，你会得到 ORA-01468.
B: FULL OUTER JOIN 部分是对的，但是 SELECT emp_id 不对，ORACLE不知道emp_id是来自哪个表，会报 ORA-00918错误.
C: FULL OUTER JOIN 部分是对的，但是s.salary + c.commission 会有NULL的问题
D: 正确，这是9i出现之前的写法，用两个外连接的UNION来实现。
E: 正确的FULL OUTER JOIN写法，COALESCE处理了NULL的问题。
F: 正确的FULL OUTER JOIN写法，并且用了USING子句而不是ON子句。这使得SELECT中的emp_id既非s.emp_id 也非 c.emp_id，而是一个虚拟列，隐性实现了COALESCE(c.emp_id, s.emp_id)
G: 正确，用聚合函数来实现全外连接，这也是9i之前的做法。这种方法有时候比FULL OUTER JOIN效率更高，你可以根据自己需要对比之后做出选择。

-----------------------------------------

2012-3-24 XMLTABLE
作者：Kim Berg Hansen
难度：高

我们创建了如下的表:
CREATE TABLE plch_orders
(
   order_id    INTEGER PRIMARY KEY
, customer    VARCHAR2(20)
, state       VARCHAR2(20)
)
/

CREATE TABLE plch_orderlines
(
   order_id    INTEGER REFERENCES plch_orders (order_id)
, line_no     INTEGER
, item        VARCHAR2(20)
, quantity    NUMBER
)
/

我们从外部数据源接受到包含有订单数据的文件，需要插入到这些表。

在产品库上这可能会通过CLOB或者BFILE或其他方式来装载，但是为了测试我们只是设置了一个绑定变量来包含这个XML:

VARIABLE received_xml VARCHAR2(4000)

BEGIN
   :received_xml :=
'<Orders>
  <Order Id="100">
    <Customer>Smith</Customer>
    <State>OHIO</State>
    <Lines>
      <Line No="1">
        <Item>Mouse</Item>
        <Qty>3</Qty>
      </Line>
      <Line No="2">
        <Item>Monitor</Item>
        <Qty>2</Qty>
      </Line>
    </Lines>
  </Order>
  <Order Id="102">
    <Customer>Jackson</Customer>
    <State>TEXAS</State>
    <Lines>
      <Line No="2">
        <Item>Monitor</Item>
        <Qty>1</Qty>
      </Line>
      <Line No="4">
        <Item>Keyboard</Item>
        <Qty>2</Qty>
      </Line>
      <Line No="6">
        <Item>RJ-45 Cable</Item>
        <Qty>6</Qty>
      </Line>
    </Lines>
  </Order>
</Orders>
';
END;
/

哪些选项会从上述XML插入数据，从而使得执行之后，下列的SELECT会显示如下的输出：

SQL> SELECT   order_id
  2         , customer
  3         , state
  4      FROM plch_orders
  5  ORDER BY order_id
  6  /

  ORDER_ID CUSTOMER             STATE
---------- -------------------- --------------------
       100 Smith                OHIO
       102 Jackson              TEXAS

SQL> SELECT   order_id
  2         , line_no
  3         , item
  4         , quantity
  5      FROM plch_orderlines
  6  ORDER BY order_id, line_no
  7  /

  ORDER_ID    LINE_NO ITEM                   QUANTITY
---------- ---------- -------------------- ----------
       100          1 Mouse                         3
       100          2 Monitor                       2
       102          2 Monitor                       1
       102          4 Keyboard                      2
       102          6 RJ-45 Cable                   6

(A)
INSERT INTO plch_orders
SELECT   xmlorders.order_id
       , xmlorders.customer
       , xmlorders.state
    FROM XMLTABLE(
            '/Orders/Order'
            PASSING XMLTYPE(:received_xml)
            COLUMNS order_id INTEGER      PATH '@Id'
                  , customer VARCHAR2(20) PATH 'Customer'
                  , state    VARCHAR2(20) PATH 'State'
         ) xmlorders
/

INSERT INTO plch_orderlines
SELECT   xmllines.order_id
       , xmllines.line_no
       , xmllines.item
       , xmllines.quantity
    FROM XMLTABLE(
            '/Orders/Order'
            PASSING XMLTYPE(:received_xml)
            COLUMNS order_id INTEGER      PATH '@Id'
                  , line_no  INTEGER      PATH 'Lines/Line/@No'
                  , item     VARCHAR2(20) PATH 'Lines/Line/Item'
                  , quantity NUMBER       PATH 'Lines/Line/Qty'
         ) xmllines
/

(B)
INSERT INTO plch_orders
SELECT   xmlorders.order_id
       , xmlorders.customer
       , xmlorders.state
    FROM XMLTABLE(
            '/Orders/Order'
            PASSING XMLTYPE(:received_xml)
            COLUMNS order_id INTEGER      PATH '@Id'
                  , customer VARCHAR2(20) PATH 'Customer'
                  , state    VARCHAR2(20) PATH 'State'
         ) xmlorders
/

INSERT INTO plch_orderlines
SELECT   xmlorders.order_id
       , xmllines.line_no
       , xmllines.item
       , xmllines.quantity
    FROM XMLTABLE(
            '/Orders/Order'
            PASSING XMLTYPE(:received_xml)
            COLUMNS order_id INTEGER      PATH '@Id'
                  , lines    XMLTYPE      PATH 'Lines'
         ) xmlorders
       , XMLTABLE(
            '/Lines/Line'
            PASSING xmlorders.lines
            COLUMNS line_no  INTEGER      PATH '@No'
                  , item     VARCHAR2(20) PATH 'Item'
                  , quantity NUMBER       PATH 'Qty'
         ) xmllines
/

(C)
INSERT ALL
WHEN seq = 1
THEN
     INTO plch_orders
   VALUES (order_id
         , customer
         , state)
WHEN 1 = 1
THEN
     INTO plch_orderlines
   VALUES (order_id
         , line_no
         , item
         , quantity)
SELECT   xmlorders.order_id
       , xmlorders.customer
       , xmlorders.state
       , xmllines.line_no
       , xmllines.item
       , xmllines.quantity
       , xmllines.seq
    FROM XMLTABLE(
            '/Orders/Order'
            PASSING XMLTYPE(:received_xml)
            COLUMNS order_id INTEGER      PATH '@Id'
                  , customer VARCHAR2(20) PATH 'Customer'
                  , state    VARCHAR2(20) PATH 'State'
                  , lines    XMLTYPE      PATH 'Lines'
         ) xmlorders
       , XMLTABLE(
            '/Lines/Line'
            PASSING xmlorders.lines
            COLUMNS line_no  INTEGER      PATH '@No'
                  , item     VARCHAR2(20) PATH 'Item'
                  , quantity NUMBER       PATH 'Qty'
                  , seq      FOR ORDINALITY
         ) xmllines
/

newkid

2012-3-24 答案BC
(A)
这个选项是错的，因为第二个INSERT中的XQuery字串/Orders/Order将只能取到两行记录，就像第一个INSERT。line_no, item 和 quantity列将会包含多余一个的值，我们会得到ORA-19279错误。

(B)
这个选项将会正确处理insert. 对于订货清单（plch_orderlines）我们用了两个XMLTABLE表达式来分别从Order XML获得order_id和将lines子记录分离到另一个XMLTABLE中并获取plch_orderlines表的列。

(C)
我们能用INSERT ALL语法来一步完成两个表的插入。为了达到这个目的我们为LINES的XMLTABLE增加了SEQ列，并用了FOR ORDINALITY，这使得SEQ会被填充为1,2,3, ... 这样的自然数（我们不能假设line_no会从1开始）。在这种情况下，INSERT ALL会在seq = 1 （每个ORDER会出现一次）的情况下插入到plch_orders表，在1=1(永远为真)的情况下插入到plch_orderlines表。

newkid

2012-3-31 表连接
作者：Patrick Barel
难度：低

我有两个表:
CREATE TABLE AMIS_DEPT
(
  deptno NUMBER(2) NOT NULL,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);
ALTER TABLE AMIS_DEPT
  ADD CONSTRAINT PK_AMIS_DEPT PRIMARY KEY (DEPTNO)
  USING INDEX;
CREATE TABLE AMIS_EMP
(
  empno    NUMBER(4) NOT NULL,
  ename    VARCHAR2(10),
  deptno   NUMBER(2) NOT NULL
);
ALTER TABLE AMIS_EMP
  ADD CONSTRAINT PK_AMIS_EMP PRIMARY KEY (EMPNO)
  USING INDEX ;
ALTER TABLE AMIS_EMP
  ADD CONSTRAINT FK_AMIS_DEPTNO FOREIGN KEY (DEPTNO)
  REFERENCES AMIS_DEPT (DEPTNO);

里面有这些数据:
INSERT INTO AMIS_DEPT( DEPTNO, DNAME) VALUES ( 10, 'ACCOUNTING');
INSERT INTO AMIS_DEPT( DEPTNO, DNAME) VALUES ( 20, 'RESEARCH');
INSERT INTO AMIS_DEPT( DEPTNO, DNAME) VALUES ( 30, 'SALES');
INSERT INTO AMIS_DEPT( DEPTNO, DNAME) VALUES ( 40, 'OPERATIONS');
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7369, 'SMITH',  20);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7499, 'ALLEN',  30);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7521, 'WARD',   30);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7566, 'JONES',  20);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7654, 'MARTIN', 30);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7698, 'BLAKE',  30);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7782, 'CLARK',  10);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7788, 'SCOTT',  20);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7839, 'KING',   10);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7844, 'TURNER', 30);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7876, 'ADAMS',  20);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7900, 'JAMES',  30);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7902, 'FORD',   20);
INSERT INTO AMIS_EMP( EMPNO, ENAME, DEPTNO) VALUES ( 7934, 'MILLER', 10);

COMMIT
/

哪些选项的查询能够返回没有员工的部门？

(A)
SELECT d.*
  FROM amis_dept d
WHERE NOT EXISTS (SELECT 1
                     FROM amis_emp e
                    WHERE e.deptno = d.deptno)

(B)
SELECT d.*
  FROM amis_dept d
WHERE d.deptno NOT IN (SELECT e.deptno
                          FROM amis_emp e)

(C)
SELECT d.*
  FROM amis_dept d
  LEFT OUTER JOIN amis_emp e ON (d.deptno = e.deptno)
WHERE e.empno IS NULL


(D)
SELECT d.*
  FROM amis_dept d
  JOIN amis_emp e ON (d.deptno <> e.deptno)


(E)
SELECT d.*
  FROM amis_dept d
NOT INTERSECT
SELECT d.*
  FROM amis_dept d
  JOIN amis_emp e ON (d.deptno = e.deptno)
  
(F)
SELECT d.*
  FROM amis_dept d
MINUS
SELECT d.*
  FROM amis_dept d
  JOIN amis_emp e ON (d.deptno = e.deptno)

colin_liu2009

太好的题目啦  估计拿出去公司作为面试米有几个能够搞得定

newkid

2012-3-31 答案：ABCF

B:虽然在本例中是正确的(amis_emp.deptno是非空列)要注意当子查询结果中有NULL时，外层的NOT IN是不会返回任何记录的。
C:连接条件为不等，对于d中每行记录都会返回e中多条不等的记录，而不是需求的结果。
D:INTERSECT不能和NOT连用。

----------------------------------------
2012-4-7 分解子查询
作者：Lucas Jellema
难度：高

我有一张表保存着观测结果，按照分类(category)和特定的顺序(SEQ)保存数据。

I have a table with observations, each in a specific category and made in a specific order:
create table amis_observations
( seq      number(5) not null primary key
, category varchar2(1) not null
)
/

注意这是非常简化的表现形式，它可以是在一场球赛中对球的持有记录，政治民意调查结果，对经过车辆颜色的记录，一支运动队伍连续取胜的记录，或者一个论坛的贴子，等等。在我们的例子中，数据简化为如下的INSERT语句：

insert into amis_observations values (1, 'A');
insert into amis_observations values (2, 'A');
insert into amis_observations values (3, 'B');
insert into amis_observations values (4, 'B');
insert into amis_observations values (5, 'B');
insert into amis_observations values (6, 'A');
insert into amis_observations values (7, 'B');
insert into amis_observations values (8, 'A');
insert into amis_observations values (9, 'B');
insert into amis_observations values (10, 'B');
insert into amis_observations values (11, 'B');
insert into amis_observations values (12, 'A');
insert into amis_observations values (13, 'B');
insert into amis_observations values (14, 'B');
insert into amis_observations values (15, 'B');
insert into amis_observations values (16, 'B');
insert into amis_observations values (17, 'A');
insert into amis_observations values (18, 'A');
insert into amis_observations values (19, 'A');
insert into amis_observations values (20, 'A');
insert into amis_observations values (21, 'A');
insert into amis_observations values (22, 'B');
insert into amis_observations values (23, 'B');
insert into amis_observations values (24, 'A');
insert into amis_observations values (25, 'B');
insert into amis_observations values (26, 'B');
insert into amis_observations values (27, 'B');
insert into amis_observations values (28, 'A');
insert into amis_observations values (29, 'B');
insert into amis_observations values (30, 'B');

commit
/

现在的挑战是要找出这个集合中连续出现同一类别(category)的最长的观测结果，这个最长的观测结果从哪个序号开始，长度为多少，属于哪个类别。下列的查询哪些会返回报告这些信息的一行记录的结果？在例子中这个结果应该是这样：

CATEGORY           SEQ SEQUENCE_LENGTH
-------- ------------- ---------------
A                   17               5

如果表AMIS_OBSERVATIONS中的数据改变了，比如第28号记录的category改为B, 这个查询应该照样能够找出最长的序列来。
(译者注：以上是废话，而且作者的答案中没有包含我们开发版最喜欢的GROUP BY ROWNUM方法)

(A)
with observed_sections as
( select category
  ,      seq
  ,       ( select min(seq)
            from   amis_observations n
            where  n.seq > o.seq
            and    n.category != o.category
            ) - seq   as sequence_length
  from   amis_observations o
  order
  by     sequence_length desc nulls last
)
select *
from   observed_sections
where  rownum=1

(B)
with section_boundaries as
( select seq
  ,      category
  from   amis_observations o
  where  category != lead(category,1) over (order by seq)
)
, ordered_boundaries as
( select category
  ,      seq
  ,      lead(seq) over (order by seq)
         - seq   sequence_length
  from   section_boundaries
  order
  by     sequence_length desc nulls last
)
select *
from   ordered_boundaries
where  rownum = 1

(C)
select *
from  ( select  category
        ,       connect_by_root(seq) seq
        ,       seq - connect_by_root(seq)+1 sequence_length
        from    amis_observations
        connect
        by      seq = prior seq + 1
                and
                category = prior category
        order
        by   sequence_length desc
      )
where  rownum = 1

(D)
with category_switches as
( select a.seq
  ,      a.category
  from   amis_observations a
         left outer join
         amis_observations b
         on ( a.category = b.category
              and  
              b.seq = a.seq+1
            )
  where  b.rowid is null
  order
  by    seq
)
, rownumbered_category_switches as
( select csw.*
  ,      rownum rwnm
  from   category_switches csw
)
select *
from   ( select rcs2.category
         ,      nvl(rcs1.seq,0) +1 seq
         ,      rcs2.seq - nvl(rcs1.seq,0) sequence_length
         from   rownumbered_category_switches rcs1
                right outer join
                rownumbered_category_switches rcs2
                on (rcs1.rwnm + 1 = rcs2.rwnm)
         order
         by     sequence_length desc nulls last
       )
where rownum = 1

(E)
with observed_sections as
( select seq
  ,      lag(category) over (order by seq) previous_category
  ,      category
  from   amis_observations o
)
, sectioned_observations as
( select category
  ,      seq
  ,      lead( case previous_category
               when category then null
               else seq
               end) ignore nulls over (order by seq)  
          - seq sequence_length
  from   observed_sections
  order
  by     sequence_length desc nulls last
)
select *
from   sectioned_observations
where  rownum = 1

马克不想说话

2012-1-7 层次查询
作者：Kim Berg Hansen
难度：中

这道题目中的C选项，where not exists(select null ...) 把null 换成1也行啊
我想问的是null在这里是不是表示后面条件成立返回子查询为空，这样不就等价于 exists(select 1)
还是null没有特殊意义，从答案来看貌似是第二项

马克不想说话

2012-1-7 层次查询
作者：Kim Berg Hansen
难度：中

这道题目关于答案B的说法不对
“不是其他员工的经理”的员工  这句话虽然可以表示一个经理可以没有下设的员工，但是在这张表中没有体现
where empno not in (
   select mgr
   from plch_emp
)
这个条件应该在里面加上where mgr is not null，这样亦可成立。