SQL 小测验每周一题

newkid · 发表于 2011-12-20 00:53

2011-12-10 答案B.
A: 它指定的窗口范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，其实就是整个PARTITION (整个部门)的合计。
B: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW正确地实现了截至当前行为止的小计。
C: 没有写窗口子句，缺省是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即取值范围截至当前行的值为止，因为里面有两条工资相等，这两条会被同时计入，而不是逐行计入。
D: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING是从当前行往后，不符合题目要求。
-------------------------------------------
2011-12-17 UPDATE语句(内联视图，相关查询)
作者：Darryl Hurley
难度：中

执行如下代码:
CREATE TABLE plch_parts (
partnum NUMBER
, partname VARCHAR2(10)
, partprice NUMBER )
/

INSERT INTO plch_parts VALUES(1, 'Part 1', 1);
INSERT INTO plch_parts VALUES(2, 'Part 2', 2);
COMMIT;

下列的哪些UPDATE语句，会导致此查询执行之后：
SELECT *
  FROM plch_parts
ORDER BY partnum
/

产生如下的结果：
PARTNUM PARTNAME PARTPRICE
---------- ---------- ----------
      1 Part 1             1
      2 Part 2             4

(A)
UPDATE plch_parts
SET partprice = partprice * 2
WHERE MOD (partnum, 2) = 0

(B)
UPDATE ( SELECT *
         FROM plch_parts
      WHERE MOD (partnum, 2) = 0 )
SET partprice = partprice * 2

(C)
UPDATE plch_parts a
SET a.partprice = ( SELECT b.partprice * 2
                     FROM plch_parts b
                     WHERE a.partnum = b.partnum )
WHERE MOD (a.partnum, 2) = 0

(D)
UPDATE plch_parts a
SET a.partprice = ( SELECT b.partprice * 2
                     FROM plch_parts b
                     WHERE a.partnum = b.partnum
                        AND MOD (a.partnum, 2) = 0 )

KEN6503 · 发表于 2011-12-20 09:05

答安是ABC，感觉 D 是不对的，但是说不出道理

newkid · 发表于 2011-12-27 10:11

2011-12-17 答案ABC
D: 错误地修改了所有的行，因为外层的UPDATE没有带WHERE条件。

==================================================
2011-12-24 MERGE语句
作者：Jeff Kemp
难度：中

我创建了两张表并填入一些数据：
CREATE TABLE plch_empdept
( empid NUMBER NOT NULL
, deptid NUMBER NOT NULL
, CONSTRAINT plch_empdept_pk PRIMARY KEY (empid, deptid)
);

CREATE TABLE plch_newrecs
( empid NUMBER NOT NULL
, deptid NUMBER NOT NULL
, deleteme CHAR(1) NOT NULL
, CONSTRAINT plch_newrecs_pk PRIMARY KEY (empid, deptid)
);

INSERT INTO plch_empdept VALUES (10, 100);
INSERT INTO plch_empdept VALUES (10, 200);

INSERT INTO plch_newrecs VALUES (10, 200, 'Y');
INSERT INTO plch_newrecs VALUES (10, 300, 'N');
INSERT INTO plch_newrecs VALUES (10, 400, 'Y');

COMMIT;

然后我执行了一些SQL(即下列的每个答案)，目的是向 plch_empdept 表加入数据, 来源是 plch_newrecs 表中的deleteme等于'N' 的数据(假如数据在plch_empdept中已经存在则不修改), 如果 plch_newrecs的deleteme等于'Y'则从 plch_empdept删去相应数据。对应关系由组合键(empid, deptid)决定。

然后运行如下查询：
SELECT * FROM plch_empdept ORDER BY deptid

哪些选项提供的SQL语句能够 (a)执行不出错，并且(b)上述查询输出下列的结果：
EMPID  DEPTID
-----  ------
10    100
10    300

(A)
MERGE INTO plch_empdept
   USING (SELECT * FROM plch_newrecs) src
      ON ( plch_empdept.empid = src.empid
         AND plch_empdept.deptid = src.deptid)
WHEN MATCHED
THEN
UPDATE SET
   plch_empdept.empid = src.empid
, plch_empdept.deptid = src.deptid
DELETE
         WHERE src.deleteme = 'Y'
WHEN NOT MATCHED
THEN
INSERT    (empid, deptid)
   VALUES (src.empid, src.deptid)
      WHERE src.deleteme = 'N'
/

(B)
DELETE plch_empdept
WHERE (empid, deptid) IN (SELECT empid, deptid
                           FROM plch_newrecs
                        WHERE deleteme = 'Y')
/

INSERT INTO plch_empdept (empid, deptid)
SELECT empid, deptid
   FROM plch_newrecs
WHERE deleteme = 'N'
MINUS
SELECT empid, deptid FROM plch_empdept
/

(C)
DELETE plch_empdept
WHERE (empid, deptid) IN (SELECT empid, deptid
                           FROM plch_newrecs
                        WHERE deleteme = 'Y')
/

MERGE INTO plch_empdept
   USING (SELECT * FROM plch_newrecs) src
      ON ( plch_empdept.empid = src.empid
         AND plch_empdept.deptid = src.deptid)
WHEN NOT MATCHED
THEN
INSERT    (empid, deptid)
   VALUES (src.empid, src.deptid)
      WHERE src.deleteme = 'N'
/

(D)
MERGE INTO plch_empdept
   USING (SELECT * FROM plch_newrecs) src
      ON ( plch_empdept.empid = src.empid
         AND plch_empdept.deptid = src.deptid)
WHEN MATCHED
THEN
DELETE WHERE src.deleteme = 'Y'
WHEN NOT MATCHED
THEN
INSERT    (empid, deptid)
   VALUES (src.empid, src.deptid)
      WHERE src.deleteme = 'N'
/

KEN6503 · 发表于 2011-12-30 09:41

答案 C

hsk19880820 · 发表于 2011-12-30 10:03

BCD?

anima521 · 发表于 2011-12-30 12:00

俺又落武了，学习吧

visual2006 · 发表于 2011-12-30 15:07

A肯定不对，格式都错了。

B,C,D

newkid · 发表于 2012-1-5 03:22

2011-12-24 答案BC.

A: MERGE语句的匹配列（即ON后面的列）是不可以修改的。
D: MATCHED里面单独的DELETE目前还不支持，必须和UPDATE一起使用。这个一点道理也没有，希望ORACLE以后能改进。
=========================================
2011-12-31 NUMBER型的存储
作者：_Nikotin
难度：中

我在自己的schema中创建了PLCH_NUMBERS表并插入大量数据：
create table plch_numbers (n number) pctfree 0;

insert into plch_numbers select 1 from dual connect by level <= 1e5;
commit;

然后我又运行了选项中的update语句。
/* update */

commit;

然后我检查plch_numbers表的大小：
select blocks from user_segments where segment_name = 'PLCH_NUMBERS';

哪个选项的UPDATE会使得这个查询返回的值最大？

(A)
update plch_numbers set n = n / 2

(B)
update plch_numbers set n = n / 3

(C)
update plch_numbers set n = n / 5

(D)
update plch_numbers set n = n / 10

〇〇 · 发表于 2012-1-5 07:04

newkid 发表于 2012-1-5 03:22
2011-12-24 答案BC.

A: MERGE语句的匹配列（即ON后面的列）是不可以修改的。

b,因为1/3除不尽

KEN6503 · 发表于 2012-1-5 07:26

答案 B