SQL 小测验每周一题

KEN6503

CEIL函数, answer: C, G, I

newkid

2012-1-14 答案C,I, 见ttxing分析。

================================================
2012-1-21 DML error logging 的限制
作者：mentzel.iudith
难度：高

我创建了如下两张表并填入数据：
CREATE TABLE plch_departments
(
  dept_id       INTEGER       PRIMARY KEY
, dept_name     VARCHAR2(100)
, total_salary  NUMBER
)
/

CREATE TABLE plch_employees
(
  employee_id   INTEGER       PRIMARY KEY
, last_name     VARCHAR2(100) UNIQUE DEFERRABLE INITIALLY DEFERRED
, salary        NUMBER        CHECK ( salary <= 50000 )
, dept_id       INTEGER
)
/

BEGIN
   INSERT INTO plch_departments  VALUES (10, 'Accounting', 30000);
   INSERT INTO plch_departments  VALUES (20, 'Marketing',  70000);

   INSERT INTO plch_employees    VALUES (100, 'Jobs',    10000, 10);
   INSERT INTO plch_employees    VALUES (200, 'Ellison', 20000, 10);
   INSERT INTO plch_employees    VALUES (300, 'Gates',   30000, 20);
   INSERT INTO plch_employees    VALUES (400, 'Scott',   40000, 20);

   COMMIT;
END;
/

然后我又为上述两张表创建了下面的错误日志表：
BEGIN
   DBMS_ERRLOG.CREATE_ERROR_LOG ('plch_departments');
   DBMS_ERRLOG.CREATE_ERROR_LOG ('plch_employees');
END;
/

下列的哪些DML语句会导致相应的错误日志表被插入恰好两条记录？

(A)
INSERT INTO plch_employees
SELECT 100*ROWNUM, 'New employee '||ROWNUM, 10000*ROWNUM, 10*ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 3
LOG ERRORS ('Choice1') REJECT LIMIT 2
/

(B)
UPDATE plch_employees
SET salary = salary * 2
LOG ERRORS ('Choice2') REJECT LIMIT UNLIMITED
/

(C)
UPDATE plch_employees
SET employee_id = employee_id + 200
WHERE salary <= 20000
LOG ERRORS ('Choice3') REJECT LIMIT UNLIMITED
/

(D)
INSERT INTO plch_employees
SELECT ROWNUM, 'PL/SQL Challenge', 10000*ROWNUM, 10*ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 3
LOG ERRORS ('Choice4') REJECT LIMIT UNLIMITED
/

(E)
INSERT INTO plch_employees
SELECT employee_id + 200, UPPER(last_name), salary + 5000, dept_id
FROM plch_employees
WHERE employee_id <= 200
LOG ERRORS ('Choice5') REJECT LIMIT UNLIMITED
/

(F)
UPDATE plch_employees
SET employee_id = employee_id + 200
LOG ERRORS ('Choice6') REJECT LIMIT UNLIMITED
/

(G)
UPDATE plch_employees
SET  salary =    CASE
                    WHEN dept_id = 10 THEN 100000
                 END,
     last_name = CASE
                    WHEN dept_id = 20 THEN 'PL/SQL Challenge'
                 END
LOG ERRORS ('Choice7') REJECT LIMIT UNLIMITED
/

(H)
INSERT /*+ APPEND */ INTO plch_departments
SELECT dept_id, 'PL/SQL department', SUM(salary)
FROM plch_employees
GROUP BY dept_id
LOG ERRORS ('Choice8') REJECT LIMIT UNLIMITED
/

newkid

2012-1-21: 答案BEG.

A: 这个语句试图插入三行，每行都会产生一个主键重复错误，总共会捕捉到三行错误日志。
   注意：REJECT LIMIT n实际上会允许(n + 1)条错误记录被捕获，然后语句才失败中止。
B: 这个语句会导致两行数据（employee_id=300和400）的salary列约束校验失败，从而会在错误日志中捕获两条记录。
C: 这个语句修改了两行，employee_id=100和200，这两行分别产生了一个employee_id列上的主键冲突错误。
   然而，UPDATE语句造成的唯一性冲突不会被错误日志捕获(!)，所以语句将会失败，但是错误日志中没有记录。
D: 这个语句试图插入三行，名字完全一样，所以后两行会在last_name列上产生唯一性约束的冲突。
   这个last_name上的唯一性约束被定义为延迟约束(DEFERRABLE INITIALLY DEFERRED), 所以语句本身是成功的，近在COMMIT的时候会报一个错：
   "ORA-02091: transaction rolled back."
   延迟约束的报错不会被捕获到错误日志中。所以错误日志表中没有记录。
E: 这个语句试图插入两行数据（employee_id=300和400），每个都会产生一个主键冲突，错误日志中会有两条记录。
F: 约束检查是在整个语句结束之后发生的，而不是逐行发生的，因此本语句并不会报错。
G: 这个语句会作一个全表修改。其中两行会产生一个salary列约束校验错误，这两个错误会被记录。有一行会产生一个last_name上的唯一性约束冲突错误，但是这是个延迟约束，不会被记录。
   在试图提交的时候将会报一个错：
   "ORA-02091: transaction rolled back."
H: 这个语句试图用直接路径插入两个行，用的是 /*+ APPEND */ 的方法。
   虽然这两行都会在dept_id上产生主键冲突错误，但是直接路径插入的错误不能被错误日志捕获。
   
======================================
2012-1-28: BITAND函数
作者：Kim Berg Hansen
难度：中

我创建了下列的表并填入数据:
create table plch_employees
(
   employee_id   integer
, last_name     varchar2 (20)
, expertise     number
)
/

insert into plch_employees
   values (100, 'Feuerstein',  7);

insert into plch_employees
   values (200, 'Hansen',     11);

insert into plch_employees
   values (300, 'Barel',       3);

insert into plch_employees
   values (300, 'Bager',       9);

commit;

我们的数据库架构师是个老学究，他觉得应该尽可能的节省磁盘空间。为了存储员工技能数据，他决定用一个NUMBER列，每一位二进制数字代表这个员工所精通的编程语言。（如果我们程序员能够拿主意，这样的数据模型早就该踢飞了，但我们被困住了，没办法;-)

位模式的值有如下含义：

Bit Value Language
--- ----- --------
0   1     SQL
1   2     PL/SQL
2   4     APEX
3   8     XAL (legacy language)

可以看到，Bager精通SQL和XAL, Feuerstein精通SQL, PL/SQL 和 APEX。
老板想要一个精通PLSQL的员工名单。
哪些选项执行后能够产生如下的输出？
LAST_NAME
--------------------
Feuerstein
Hansen
Barel

(A)
select last_name
  from plch_employees
where expertise && 2
order by employee_id

(B)
select last_name
  from plch_employees
where expertise AND bin_to_num(0,0,1,0)
order by employee_id

(C)
select last_name
  from plch_employees
where expertise = bin_to_num(0,0,1,0)
order by employee_id

(D)
select last_name
  from plch_employees
where mod(trunc(expertise/2),2) = 1
order by employee_id

(E)
select last_name
  from plch_employees
where bitand(expertise,2) = 2
order by employee_id

(F)
select last_name
  from plch_employees
where bitand(expertise,bin_to_num(0,0,1,0)) = bin_to_num(0,0,1,0)
order by employee_id

newkid

2012-1-28 答案：DEF
A: 没有&&这个操作符
B: AND在SQL中只可用于布尔逻辑，不是位操作
C: 条件相当于expertise = 2, 也就是找出全部技能为2的人, 而不是包含了2这一位的人。
D: mod(trunc(expertise/2),2)=1 这个公式能够正确分离出二进制的右起第二位。
E: bitand(expertise,2) = 2 能够正确分离出二进制的右起第二位。
F: bitand(expertise,bin_to_num(0,0,1,0))= bin_to_num(0,0,1,0) 这里bin_to_num(0,0,1,0)其实就是2, 所以等同于E.
===========================================================
2012-2-4 字符串比较中的通配符
作者：Chris Saxon
难度：中


我创建了如下的表并填入数据：
CREATE TABLE plch_types
  (
    type_name VARCHAR2(100)
  );

INSERT INTO plch_types
  VALUES ( 'table.column_name%type');

INSERT INTO plch_types
  VALUES ( 'table.column_nameXtype');

INSERT INTO plch_types
  VALUES ( 'table.columnsname%type');

INSERT INTO plch_types
  VALUES ( 'table.column_name_values%type');

COMMIT;

哪些选项在执行之后会仅仅显示 "table.column_name%type"?

(A)
SELECT type_name
  FROM plch_types
WHERE type_name LIKE 'table.column_name%type'

(B)
SELECT type_name
  FROM plch_types
WHERE type_name LIKE 'table.column_name?%type' ESCAPE '?'

(C)
SELECT type_name
  FROM plch_types
WHERE type_name LIKE 'table.column\_name\%%' ESCAPE '\'

(D)
SELECT type_name
  FROM plch_types
WHERE type_name LIKE 'table.column/_name/%type' ESCAPE '/'

(E)
SELECT type_name
  FROM plch_types
WHERE type_name = 'table.column_name%type'
  

〇〇

newkid 发表于 2012-2-10 05:14
2012-1-28 答案：DEF
A: 没有&&这个操作符
B: AND在SQL中只可用于布尔逻辑，不是位操作

看上去是d,e，但根据数据，c也对

newkid

2012-2-4 答案CDE.
A: _ 和 % 都是通配符，所以这个选项会匹配表中所有的行。
B: 只有%被转义了，_ 仍然是通配符，所以它会匹配表中的columnsname和column_name。
C: 正确；使用了 '\' 做转义符，第二个%未被转义，仍然可匹配剩下的部分(type)。
D: 正确使用了 '/' 做转义符
E: 直接使用等值匹配而不是LIKE, 所以_ 和 % 都是原值匹配，不会当作通配符。
==============================================
2012-2-11
作者：Kim Berg Hansen
难度：中

我创建了如下的表并填入数据：
create table plch_customers(
   cust_id     number         primary key,
   cust_name   varchar2(20),
   cust_phone  varchar2(20)
)
/

insert into plch_customers values (10, 'Oracle',    '(123) 555 9876');
insert into plch_customers values (20, 'Microsoft', '321-999-4321'  );
insert into plch_customers values (30, 'Apple',     '222/1111234'   );
commit;

我老板购买了一个电话系统，我们需要吧客户名称和电话导入。但它要求电话号码只能包含数字，不能有其他字符。(幸运的是我们这次不必考虑"+"这样的国家号码前缀:-)

哪些选项会产生期望的这种输出：

CUST_NAME                      PHONE                        
------------------------------ ------------------------------
Oracle                         1235559876                    
Microsoft                      3219994321                    
Apple                          2221111234  

(A)
  SELECT cust_name,
         REGEXP_REPLACE (cust_phone, '[^[:digit:]]') phone
    FROM plch_customers
ORDER BY cust_id

(B)
SELECT cust_name,
         REGEXP_REPLACE (cust_phone, '[^0-9]') phone
    FROM plch_customers
ORDER BY cust_id

(C)
SELECT cust_name,
         REGEXP_REPLACE (cust_phone, '[^0..9]') phone
    FROM plch_customers
ORDER BY cust_id

(D)
SELECT cust_name, REGEXP_REPLACE (cust_phone, '\D') phone
    FROM plch_customers
ORDER BY cust_id

(E)
SELECT cust_name, REPLACE (cust_phone, '(-/) ', '') phone
    FROM plch_customers
ORDER BY cust_id

(F)
SELECT cust_name, TRANSLATE (cust_phone, '(-/) ', '') phone
    FROM plch_customers
ORDER BY cust_id

(G)
SELECT cust_name,
         TRANSLATE (cust_phone, '^(-/) ', '^') phone
    FROM plch_customers
ORDER BY cust_id

(H)
SELECT cust_name,
         TRANSLATE (cust_phone, '0123456789%', '0123456789')
            phone
    FROM plch_customers
ORDER BY cust_id

(I)
SELECT cust_name,
         TRANSLATE (
            cust_phone,
            '#' || TRANSLATE (cust_phone, '^0123456789', '^'),
            '#')
            phone
    FROM plch_customers
ORDER BY cust_id

(J)
SELECT cust_name, TRIM (cust_phone) phone
    FROM plch_customers
ORDER BY cust_id

newkid

2012-2-11 答案 ABDGI
A,B: 正确的规则表达式语法
C: 中间的 0..9 是错误的规则表达式语法
D: \D是PERL风格的表达式，从10GR2开始支持
E: REPLACE会严格匹配整个串，而不是每个字符单独匹配。
F: TRANSLATE一旦接受NULL参数，就会导致整个返回值为NULL.
G: 正确的TRANSLATE用法，在两个参数前面拼上一个不需转换的字符。
H: TRANSLATE不接受%这种通配符。
I: 第一次TRANSLATE去掉所有数字，留下非数字，并用于第二次TRANSLATE。虽然有些曲折但结果是对的。
J: TRIM不能满足需求。
====================================
2012-2-18 LNNVL函数
作者：Steven Feuerstein
难度：中

我创建了如下的表并填入数据:

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


INSERT INTO plch_employees
     VALUES (100
           ,  'Picasso'
           ,  1000000
           ,  .3)
/

INSERT INTO plch_employees
     VALUES (200
           ,  'Mondrian'
           ,  1000000
           ,  .15)
/

INSERT INTO plch_employees
     VALUES (300
           ,  'O''Keefe'
           ,  1000000
           ,  NULL)
/

COMMIT
/

哪些选项提供了一个查询，可以显示那些佣金比例（commision）不大于20%, 或者为NULL的员工的姓?

(A)
SELECT last_name
    FROM plch_employees e
   WHERE e.commission_pct <= .2
      OR e.commission_pct IS NULL
ORDER BY last_name

(B)
SELECT last_name
    FROM plch_employees e
   WHERE LNNVL (e.commission_pct > .2)
ORDER BY last_name

(C)
SELECT last_name
    FROM plch_employees e
   WHERE e.commission_pct <= .2
ORDER BY last_name

(D)
SELECT last_name
  FROM plch_employees e1
WHERE e1.commission_pct <= .2
UNION ALL
SELECT last_name
  FROM plch_employees e2
WHERE e2.commission_pct IS NULL

ttxing

newkid 发表于 2012-2-23 04:05
2012-2-11 答案 ABDGI
A,B: 正确的规则表达式语法
C: 中间的 0..9 是错误的规则表达式语法

第一次用lnnvl(唉，都是客户环境限制了，到现还在用9)，确实好用
A、B、D可以

newkid

2012-2-18 答案ABD, 楼上正确。

A: 非常直观的经典做法。
B: LNNVL提供了一个简洁的方法来验证一个包含两个操作数(其中可能有NULL)的条件。这个函数可被用于查询的WHERE子句，或者CASE表达式的搜索条件。它接受一个条件作为参数，假如这个条件是FALSE或者UNKNOWN(即NULL)则返回真，假如条件为TRUE则返回假。
有时候，ORACLE会内部使用LNNVL来重写 NOT IN 或者 NOT EXISTS 条件。在这种情况下，EXPLAIN PLAN的输出会在PLAN TABLE的输出部分显示这个操作。这个条件可以包含任何标量值(SCALAR VALUES)但不能为包含AND, OR, 或者BETWEEN 的复合条件。

在本例中，LNNVL (e.commission_pct > .2) 在 e.commission_pct > .2的情况下返回假，而e.commission_pct <= .2或者e.commission_pct为NULL的时候返回真，这恰好是我们所需求的。

C: 条件没有考虑NULL的情况。
D: 用UNION ALL来表示OR条件，虽然也可以，但是会扫描两遍表。

=============================================
2012-2-25 对象表的DML
作者：Steven Feuerstein
难度：高


我定义了如下的嵌套表类型和关系表：

CREATE OR REPLACE TYPE plch_parents_t IS TABLE OF VARCHAR2 (100);
/

CREATE OR REPLACE TYPE plch_children_t IS TABLE OF VARCHAR2 (100);
/

CREATE TABLE plch_family
(
   last_name        VARCHAR2 (1000)
, parent_names     plch_parents_t
, children_names   plch_children_t
)
NESTED TABLE children_names
   STORE AS children_nt
NESTED TABLE parent_names
   STORE AS parents_nt                           
/

然后我往表中填入一行数据：

INSERT INTO plch_family (last_name, parent_names, children_names)
     VALUES ('Magnifico'
           , plch_parents_t ('Samuel', 'Dorothy')
           , plch_children_t ('Bonita', 'Charlie'))
/
COMMIT
/

哪些选项会把Magnifico的儿子的名字从"Charlie" 修改为 "Charles"？

(A)
UPDATE TABLE (SELECT children_names
                FROM plch_family
               WHERE last_name = 'Magnifico')
   SET COLUMN_VALUE = 'Charles'
WHERE COLUMN_VALUE = 'Charlie'



(B)
UPDATE plch_family
   SET children_names(2) = 'Charles'
WHERE children_names(2) = 'Charlie'


(C)
UPDATE plch_family.children_names
   SET COLUMN_VALUE = 'Charles'
WHERE last_name = 'Magnifico'
   AND COLUMN_VALUE = 'Charlie'

(D)
UPDATE (SELECT children_names
          FROM plch_family
         WHERE last_name = 'Magnifico')
   SET COLUMN_VALUE = 'Charles'
WHERE COLUMN_VALUE = 'Charlie'

〇〇

newkid 发表于 2012-3-1 05:56
2012-2-18 答案ABD, 楼上正确。

A: 非常直观的经典做法。

不运行先猜
a 对
b 应该不对，没有条件
c 子表不能引用主表
d 比a少一个table