SQL 小测验每周一题

newkid

马克不想说话 发表于 2012-4-18 21:18
2012-1-7 层次查询
作者：Kim Berg Hansen
难度：中

谢谢，我已经纠正。

newkid

2012-4-7 答案CD.

(A)
差一点就对了，见下面注释。
with observed_sections as
( select category
  ,      seq
  ,       ( ---- 这个子查询往下查找category发生变化的最近一条记录，再减去当前记录，得到连续的长度
            ---- 当category是最后一个，min(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

修改：对NULL的情况做修正。
with observed_sections as
( select category
  ,      seq
  ,      nvl( ( select min(seq)
                from   amis_observations n
                where  n.seq > o.seq
                and    n.category != o.category
              )
            , ( select max(seq)+1
                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) 语法错误，LEAD函数不允许在WHERE中使用。

(C) 正确使用了CONNECT BY, 用当前的SEQ减去得到根的SEQ得到路径长度。这种写法十分啰嗦，作者其实可以这么写：

select  category
       ,connect_by_root(seq) seq
       ,LEVEL sequence_length-----原来写的是seq - connect_by_root(seq)+1
from    amis_observations
connect
by      seq = prior seq + 1
        and
        category = prior category
order
by   sequence_length desc

(D) 虽然也对了但十分曲折啰嗦。第一个子查询用外连接+IS NULL的办法找出断点；第二个子查询对第一个的结果排序编号；然后再把第二个子查询的结果做自连接（外连接）并相减得到长度。
同样的思路，我把两个自连接用分析函数来改写如下：
with category_switches as
(   select seq
        ,category
    from (select seq
                ,category
                ,LEAD(category) OVER(ORDER BY seq) next_c
          from   amis_observations a
          )
  WHERE category<>next_c OR next_c IS NULL
)
SELECT * FROM
(SELECT category
      ,NVL(LAG(seq) OVER(ORDER BY seq),0)+1 seq
      ,seq - NVL(LAG(seq) OVER(ORDER BY seq),0) sequence_length
  FROM category_switches
  ORDER BY sequence_length desc nulls last
)
where rownum = 1

(E) 还是用分析函数找断点的办法，差一点就对了，但是没有考虑到最后一行的LEAD函数会返回空。

====================================
我更推崇itpub流行的写法：
SELECT *
  FROM (SELECT category,MIN(seq) seq,COUNT(*) len
          FROM (SELECT *
                 FROM amis_observations
                ORDER BY category,seq
               )
        GROUP BY category,seq-ROWNUM
        ORDER BY COUNT(*) DESC
       )
WHERE ROWNUM=1;

如果最长的有多个而且全部要求出来：
SELECT category,seq,len
  FROM (SELECT category,MIN(seq) seq,COUNT(*) len,RANK() OVER(ORDER BY COUNT(*) DESC) rnk
          FROM (SELECT *
                 FROM amis_observations
                ORDER BY category,seq
               )
        GROUP BY category,seq-ROWNUM
       )
WHERE rnk=1;

newkid

2012-4-14: 分析函数
作者：Patrick Barel
难度：中

我的数据库中有如下的表：
CREATE TABLE AMIS_EMP
( empno number
, ename varchar2(10)
);

表里有如下数据:
INSERT INTO AMIS_EMP (empno, ename) VALUES (10, 'BAREL');
INSERT INTO AMIS_EMP (empno, ename) VALUES (20, 'NUIJTEN');
INSERT INTO AMIS_EMP (empno, ename) VALUES (30, 'JELLEMA');
INSERT INTO AMIS_EMP (empno, ename) VALUES (40, 'NUIJTEN');
INSERT INTO AMIS_EMP (empno, ename) VALUES (50, 'SCHEFFER');
INSERT INTO AMIS_EMP (empno, ename) VALUES (60, 'JELLEMA');
INSERT INTO AMIS_EMP (empno, ename) VALUES (70, 'BAREL');
INSERT INTO AMIS_EMP (empno, ename) VALUES (80, 'SCHEFFER');
INSERT INTO AMIS_EMP (empno, ename) VALUES (90, 'JELLEMA');
COMMIT;

下列哪些选项删除了重复的ENAME，从而使得ENAME在AMIS_EMP表中是唯一的？

(A)
DELETE FROM amis_emp
WHERE empno IN
(SELECT empno
    FROM (SELECT empno
                ,row_number()
                       over(PARTITION BY ename ORDER BY empno) rn
            FROM amis_emp)
   WHERE rn > 1)

(B)
DELETE FROM amis_emp
WHERE empno > 30
   AND empno <> 50

(C)
DELETE FROM amis_emp
WHERE empno IN (SELECT MAX(empno)
                   FROM amis_emp)

(D)
DELETE FROM amis_emp
WHERE empno IN (SELECT MAX(empno)
                   FROM amis_emp
                  GROUP BY ename)

(E)
DELETE FROM amis_emp
WHERE empno NOT IN (SELECT MIN(empno)
                       FROM amis_emp
                      GROUP BY ename)

〇〇

abe
解释
a 删除第1次出现外的
b 刚好留下不重复
c 没有分组，只删除1条
d 没有处理出现3次的
e 把行号最小以外的删除了

jixch

好早啊....
4:00的，6:00的....
木有机会了

newkid

2012-4-14 答案ABE, 详见OO的分析。
==============================================================================

2012-4-21: UPDATE 语句
作者：        Lucas Jellema
难度：中

我的数据库中有如下的表:
create table amis_emp
( empno  number not null primary key
, ename  varchar2(10)
, sal    number(7,2)
, deptno number(3) not null
)
/

create table amis_dept
( deptno number(3) not null primary key
, dname  varchar2(10)
, loc    varchar2(20) not null
)
/

alter table amis_emp
add constraint amis_emp_dept_fk foreign key (deptno) references amis_dept (deptno)
/

表里有如下数据：
insert into amis_dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into amis_dept values (20, 'RESEARCH',   'DALLAS');
insert into amis_dept values (30, 'SALES',      'CHICAGO');
insert into amis_dept values (40, 'OPERATIONS', 'BOSTON');
insert into amis_dept values (50, 'TRAINING',   'BOSTON');
insert into amis_dept values (60, 'MARKETING',  'NEW YORK');
insert into amis_dept values (70, 'FINANCE',    'CHICAGO');
insert into amis_dept values (80, 'HRM',        'DALLAS');
insert into amis_dept values (90, 'LOGISTICS',  'BOSTON');


insert into amis_emp (empno, ename, sal, deptno) values (10, 'BAREL',      2500, 20);
insert into amis_emp (empno, ename, sal, deptno) values (20, 'NUIJTEN',    4300, 10);
insert into amis_emp (empno, ename, sal, deptno) values (30, 'JELLEMA',    3800, 20);
insert into amis_emp (empno, ename, sal, deptno) values (40, 'JANSEN',      900, 10);
insert into amis_emp (empno, ename, sal, deptno) values (50, 'SCHEFFER',   6500, 30);
insert into amis_emp (empno, ename, sal, deptno) values (60, 'VAN ROSSUM', 2100, 10);
insert into amis_emp (empno, ename, sal, deptno) values (70, 'VINK',       3000, 20);
insert into amis_emp (empno, ename, sal, deptno) values (80, 'DE ROOIJ',   2780, 30);
insert into amis_emp (empno, ename, sal, deptno) values (90, 'MICHELS',    3340, 40);

commit
/

哪些语句能够把所有位于DALLAS的部门的员工的薪水提升10%?

(A)
update (select sal
        from   amis_emp join amis_dept using (deptno)
        where  loc = 'DALLAS'
        )
set    sal = sal * 1.1

(B)
with dallas_employees
( select sal
  from   amis_emp
  where  deptno in ( select deptno
                     from   amis_dept
                     where  loc = 'DALLAS'
                    )
)
update dallas_employees
set    sal = sal * 1.1

(C)
merge into amis_emp t
using ( select sal*1.1 as sal
        ,      empno
        from amis_emp
             join
             amis_dept
             using (deptno)
        where  loc = 'DALLAS'
      ) updated_emps
on (t.empno = updated_emps.empno)
when matched
then update set t.sal = updated_emps.sal

(D)
update amis_emp
set    sal = sal * 1.1
where  deptno = ( select deptno
                   from   amis_dept
                   where  loc = 'DALLAS'
                 )

(E)
update amis_emp e
set    sal = sal * ( select case loc
                            when 'DALLAS' then 1.1
                            else 1
                            end
                     from   amis_dept  d     
                     where  d.deptno = e.deptno
                   )

hbb239

ACE

liang11920

好东西，以后要坚持来这里

newkid

2012-4-21答案ACE, 200楼答对了。

A: 对内联视图的修改是允许的，条件是被修改的表中的一行只能映射为内联视图中的一行。如果有连接，被连接或外连接的标在连接键上必须有唯一索引。在本例中，要修改的表是amis_emp，连接到表amis_dept, 它在连接键deptno上有唯一索引(是主键)，所以部分发生一个emp对应多个dept的情况，该内联视图是可修改的。这是最高效的做法。
如果不存在这样的唯一索引则会报错：
ORA-01779: cannot modify a column which maps to a non key-preserved table

B: WITH子查询不允许修改。也许有一天ORACLE会支持这个功能。
C: MERGE也是一种高效的UPDATE办法，虽然有点绕。对USING后面的集合的要求和内联视图类似，也是修改表的一行只能映射到USING结果集中的一行；但这个关系由开发者保证，解析的时候并不要求连接表有唯一索引，只是碰到不合格数据时会报一个RUN TIME ERROR.
在内联视图不能使用的情况下，比如查询带有聚合或分析函数等复杂操作，这时候用MERGE是最佳选择。
D: 这个选项会报如下错误：
ORA-01427: single-row subquery returns more than one row.
因为等号后面的子查询会返回多行结果。
E: 带了一个相关子查询，结果是正确的，只是效率不高。

newkid

2012-4-28 关于NULL的基础知识
作者：Patrick Barel
难度：低

我有一张表:
CREATE TABLE AMIS_EMPLOYEES
(
  employee_id     NUMBER(4),
  employee_name   VARCHAR2(10),
  employee_salary NUMBER(7, 2),
  employee_comm   NUMBER(7, 2)
)
/

表里有如下数据：
INSERT INTO AMIS_EMPLOYEES VALUES (400, 'BAREL'   , 2000,    0 )
/
INSERT INTO AMIS_EMPLOYEES VALUES (100, 'JELLEMA' , 4000, 1400 )
/
INSERT INTO AMIS_EMPLOYEES VALUES (300, 'NUIJTEN' , 2000, NULL )
/
INSERT INTO AMIS_EMPLOYEES VALUES (200, 'SCHEFFER', 2500, NULL )
/
COMMIT
/

下列哪些查询能够选出只有薪水(salary)而没有佣金(commission是零或者NULL)的员工?

(A)
SELECT *
  FROM AMIS_EMPLOYEES
WHERE EMPLOYEE_COMM = 0
/   

(B)
SELECT *
  FROM AMIS_EMPLOYEES
WHERE EMPLOYEE_COMM IS NULL   
/


(C)
SELECT *
  FROM AMIS_EMPLOYEES
WHERE NULLIF(EMPLOYEE_COMM,0) IS NULL   
/


(D)
SELECT *
  FROM AMIS_EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID
                             FROM AMIS_EMPLOYEES
                            WHERE EMPLOYEE_COMM >= 0)
/


(E)
SELECT *
  FROM AMIS_EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID
                             FROM AMIS_EMPLOYEES
                            WHERE EMPLOYEE_COMM > 0)
/

(F)
SELECT *
  FROM AMIS_EMPLOYEES
WHERE EMPLOYEE_COMM = NULL OR EMPLOYEE_COMM = 0
/