SQL 小测验每周一题

newkid

2011-11-26 答案A.
B:用了TO_CHAR函数，只有当使用函数索引才可能用上。
C:扫描范围太大，此时不如全表扫描。
D:hire_date变成表达式的一部分，ORACLE没法使用索引。

===================
2011-12-03 外键约束

作者：Steven Feuerstein
难度：低

我的SCHEMA中有两张表:

I have two tables in my schema:
CREATE TABLE plch_departments
(
   department_id     INTEGER PRIMARY KEY NOT NULL
, department_name   VARCHAR2 (100)
)
/

CREATE TABLE plch_employees
(
   employee_id     INTEGER PRIMARY KEY NOT NULL
, last_name       VARCHAR2 (100)
, salary          NUMBER
, department_id   INTEGER
)
/

哪些选项实现了如下需求：
1. plch_employees表中每一行的department_id列的值，必须能对应到plch_departments中某一行的department_id列。
2. 如果我从plch_departments表删除一行，所有plch_employees表中具有同样department_id值的行也必须被删除。

(A)
CREATE OR REPLACE TRIGGER plch_departments_adr
   AFTER DELETE
   ON plch_departments
   FOR EACH ROW
BEGIN
   DELETE FROM plch_employees
         WHERE department_id = :old.department_id;
END plch_departments_adr;
/

CREATE OR REPLACE TRIGGER plch_employees_biur
   BEFORE INSERT OR UPDATE
   ON plch_employees
   FOR EACH ROW
DECLARE
   l_dummy   CHAR (1);
BEGIN
   SELECT 'x'
     INTO l_dummy
     FROM plch_departments
    WHERE department_id = :new.department_id
      FOR UPDATE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      raise_application_error (
         -20000
       ,  'No department defined for ID ' || :new.department_id);
END plch_employees_biur;
/

(B)
ALTER TABLE plch_employees ADD (
  CONSTRAINT fk_plch_employees
  FOREIGN KEY (department_id)
  REFERENCES plch_departments (department_id)
)
/

(C)
ALTER TABLE plch_employees ADD (
  CONSTRAINT fk_plch_employees
  FOREIGN KEY (department_id)
  REFERENCES plch_departments (department_id)
  ON DELETE CASCADE
)
/

(D)
ALTER TABLE plch_departments ADD (
  CONSTRAINT fk_plch_departments
  FOREIGN KEY (department_id)
  REFERENCES plch_employees (department_id)
  ON DELETE CASCADE
)
/

gyhgood

D

newkid

2011-12-03 答案AC。A用触发器是个笨办法不值得推荐。
B:不满足自动删除子表的需求。
D:父子方向搞反了。

=================================================
2011-12-10 RANGE和ROWS窗口

作者：Patrick Barel
难度：高

我创建了如下的表并填入数据：
CREATE TABLE plch_emp
(
   ename    VARCHAR2 (10)
, sal      NUMBER (7, 2)
, deptno   NUMBER (2)
)
/

INSERT INTO plch_emp VALUES ('MILLER', 1300, 10);
INSERT INTO plch_emp VALUES ('CLARK',  2450, 10);
INSERT INTO plch_emp VALUES ('KING',   5000, 10);
INSERT INTO plch_emp VALUES ('SMITH',   800, 20);
INSERT INTO plch_emp VALUES ('ADAMS',  1100, 20);
INSERT INTO plch_emp VALUES ('JONES',  2975, 20);
INSERT INTO plch_emp VALUES ('FORD',   3000, 20);
INSERT INTO plch_emp VALUES ('SCOTT',  3000, 20);
COMMIT;

哪些选项会显示当前部门按工资排序后的逐行小计，在执行后你会看到如下输出：
       DEPTNO ENAME      RUNNING_TOTAL
------------- ---------- -------------
           10 MILLER              1300
           10 CLARK               3750
           10 KING                8750
           20 SMITH                800
           20 ADAMS               1900
           20 JONES               4875
           20 FORD                7875
           20 SCOTT              10875

或者：
       DEPTNO ENAME      RUNNING_TOTAL
------------- ---------- -------------
           10 MILLER              1300
           10 CLARK               3750
           10 KING                8750
           20 SMITH                800
           20 ADAMS               1900
           20 JONES               4875
           20 SCOTT               7875
           20 FORD               10875
           
(A)
  SELECT    deptno
          , ename
          , SUM (sal)
               OVER
                ( PARTITION BY deptno
                  ORDER BY sal
                  ROWS BETWEEN UNBOUNDED PRECEDING
                       AND     UNBOUNDED FOLLOWING
                ) as running_total
    FROM plch_emp
ORDER BY deptno, running_total

(B)
  SELECT    deptno
          , ename
          , SUM (sal)
               OVER
                ( PARTITION BY deptno
                  ORDER BY sal
                  ROWS BETWEEN UNBOUNDED PRECEDING
                       AND     CURRENT   ROW
                ) as running_total
    FROM plch_emp
ORDER BY deptno, running_total
/

(C)
  SELECT    deptno
          , ename
          , SUM (sal)
               OVER
                ( PARTITION BY deptno
                  ORDER BY sal
                ) as running_total
    FROM plch_emp
ORDER BY deptno, running_total

(D)
  SELECT    deptno
          , ename
          , SUM (sal)
               OVER
                ( PARTITION BY deptno
                  ORDER BY sal
                  ROWS BETWEEN CURRENT   ROW
                       AND     UNBOUNDED FOLLOWING
                ) as running_total
    FROM plch_emp
ORDER BY deptno, running_total

gyhgood

猜个AD
RANGE和ROWS一直没用过，总忘

gyhgood

我中奖了，泪流满面啊
三个错误答案我选上俩
我这就去买注大乐透去

jboracle1981

BC

visual2006

哎，还是选"B"。
自己做滴和C一样，然后发现‘FORD'和'SCOTT'一样鸟。

lijingeng

ROWS不常用，看了就忘，一开始还以为是C，跟visual2006 的发现一样，看来还是要多多学习分析函数

jazovo

B.

( PARTITION BY deptno      -- 按部门分组
      ORDER BY sal               -- 按工资排序
       ROWS BETWEEN UNBOUNDED PRECEDING     -- 行范围从第一行开始
            AND     CURRENT   ROW                             -- 截止于当前行
)

Oracle的Analytic Functions非常powerful，其中window功能功不可没。大家有时间可以好好研究。

JC

jboracle1981

jazovo 发表于 2011-12-16 03:11
B.

( PARTITION BY deptno      -- 按部门分组

大师，有时间跟newkid大师一样多来这个版块给俺们菜鸟指点指点啊