SQL 小测验每周一题

dingjun123

这个题目不错，考察分析函数的窗口概念和报表函数RATIO_TO_REPORT的使用，以及如何用普通写法实现RATIO_TO_REPORT功能

newkid

嗯，NULLIF的技巧也值得关注，记得以前有个人在这里问过。

jboracle1981

CEF

jboracle1981

我
开窗都看错了

sxitsxit

sql练习很不错啊
虽然好多不懂
但是通过大牛们的讲解  又有新的进步
希望这样的活动 继续保持啊

newkid

2011-9-17 答案BCEF
A: SUM不带GROUP BY又不带OVER是不允许的。
B: 可以，OVER 里面的 ORDER BY和ROWS窗口都是不必要的，简单写OVER()就可以了
C: 最佳写法
D: 因为有 GROUP BY employee_id, 所有数据都只是当前员工的，没有任何意义。
E: 就是 B 的简化版
F: 用标量子查询求总计，虽然也可以, 但是有不必要的表扫描
G: OVER里面的窗口写错了，导致分母不是总合计而是到当前行的小计。
===================================================
2011-9-24 Literals: Datetime Literals
作者：Kim Berg Hansen
难度：中


我创建了如下的表：
CREATE TABLE plch_historic_events
(
   when        DATE
, what        VARCHAR2 (100)
)
/

在这张表我想存入一个有关太空探索的很有历史意义的事件：在 1969 年7月21号 02:56 UTC时间，Neil Armstrong的双足踏上了月球。

这个日期和时间是以UTC (世界标准时间, 或国际协调时间)表示的，正如PL/SQL Challenge网站所用的时间。如果用EDT(美国东部时间)来表示则是7月20号22:56 (10:56PM)。为了这个小测验，我们暂且忽略时区并假装大家都用UTC时间 :-)

于是我写了个INSERT来记录这次事件：
INSERT INTO plch_historic_events (
   when
, what
) VALUES (
   /* THE_DATE_AND_TIME */
, 'Neil Armstrong set foot on the moon'
);

下列的选项哪些可用来取代/* THE_DATE_AND_TIME */ 从而能够把这个事件和时间插入？

(A)
DATE '1969-07-21 02:56:00'
   
(B)
DATE '1969-07-21' + INTERVAL '02:56:00' HOUR TO SECOND
   
(C)
TIMESTAMP '1969-07-21 02:56:00.000'
   
(D)
DATE '1969-07-21' + INTERVAL '02:56' HOUR TO SECOND

(E)
TIMESTAMP '1969-07-21 02:56'

(F)
'1969-07-21 02:56:00'

(G)
TIMESTAMP '1969-07-21 02:56:00'

(H)
DATE '1969-07-21' + 2 / 24 + 56 / 1440

(I)
DATE '1969-07-21' + INTERVAL '2' HOUR + INTERVAL '56' MINUTE

sxitsxit

这个跟nls_date_format 参数也有关系啊 ，如果采用oracle默认日期函数格式，结果会不一样


SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------------
21-NOV-2011 14:34:02

SQL> INSERT INTO plch_historic_events (when, what) VALUES (DATE '1969-07-21' + INTERVAL '02:56:00' HOUR TO SECOND, 'Neil Armstrong set foot on the moon');

1 row created.

SQL> select * from plch_historic_events;

WHEN                    WHAT
----------------------- ----------------------------------------------------------------------------------------------------
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon

SQL> truncate table plch_historic_events;

Table truncated.

SQL> select * from plch_historic_events;

no rows selected

SQL> INSERT INTO plch_historic_events (when, what) VALUES (DATE '1969-07-21' + INTERVAL '02:56:00' HOUR TO SECOND, 'Neil Armstrong set foot on the moon');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from plch_historic_events;

WHEN                    WHAT
----------------------- ----------------------------------------------------------------------------------------------------
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon

SQL> INSERT INTO plch_historic_events (when, what) VALUES (TIMESTAMP '1969-07-21 02:56:00.000', 'Neil Armstrong set foot on the moon');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from plch_historic_events;

WHEN                    WHAT
----------------------- ----------------------------------------------------------------------------------------------------
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon

SQL> truncate table plch_historic_events;

Table truncated.

SQL> INSERT INTO plch_historic_events (when, what) VALUES (DATE '1969-07-21' + INTERVAL '02:56' HOUR TO SECOND, 'Neil Armstrong set foot on the moon');
INSERT INTO plch_historic_events (when, what) VALUES (DATE '1969-07-21' + INTERVAL '02:56' HOUR TO SECOND, 'Neil Armstrong set foot on the moon')
                                                                                   *
ERROR at line 1:
ORA-01867: the interval is invalid


SQL> INSERT INTO plch_historic_events (when, what) VALUES (TIMESTAMP '1969-07-21 02:56', 'Neil Armstrong set foot on the moon');
INSERT INTO plch_historic_events (when, what) VALUES (TIMESTAMP '1969-07-21 02:56', 'Neil Armstrong set foot on the moon')
                                                                *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> INSERT INTO plch_historic_events (when, what) VALUES ('1969-07-21 02:56:00', 'Neil Armstrong set foot on the moon');
INSERT INTO plch_historic_events (when, what) VALUES ('1969-07-21 02:56:00', 'Neil Armstrong set foot on the moon')
                                                      *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> INSERT INTO plch_historic_events (when, what) VALUES (TIMESTAMP '1969-07-21 02:56:00', 'Neil Armstrong set foot on the moon');

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO plch_historic_events (when, what) VALUES (DATE '1969-07-21' + 2 / 24 + 56 / 1440, 'Neil Armstrong set foot on the moon');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from plch_historic_events;

WHEN                    WHAT
----------------------- ----------------------------------------------------------------------------------------------------
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon
21-JUL-1969 02:56:00    Neil Armstrong set foot on the moon

SQL> INSERT INTO plch_historic_events (when, what) VALUES (DATE '1969-07-21' + INTERVAL '2' HOUR + INTERVAL '56' MINUTE, 'Neil Armstrong set foot on the moon');

1 row created.

SQL> commit;

Commit complete.

SQL>

sxitsxit

请楼主贴出答案中的考点的原文链接！！
见过函数 timestamp(n) ,  INTERVAL YEAR(n) TO MONTH  , INTERVAL DAY(n1) TO SECOND(n2 ..

但是答案中的写法还是第一次看到啊

newkid

2011-9-24 答案BCGHI
A: DATE 常量不可以带时间
D: HOUR TO SECOND的INTERVAL, 秒不可以忽略
E: TIMESTAMP 常量不可以忽略秒
F: 跟NLS_DATE_FORMAT 相关，即使能够成功插入数据也不推荐这么用。

楼上的同学可以到这个网站看原文：

www.plsqlchallenge.com


到LIBRARY页面去搜索题目。

newkid

2011-10-1 比较条件中的ALL, ANY 和 SOME
作者：Tim Hall
难度：中

下列的代码创建了plch_emp表及数据：

CREATE TABLE plch_emp (
    empno     NUMBER(4,0),
    ename     VARCHAR2(10 BYTE),
    sal       NUMBER(7,2),
    deptno    NUMBER(2,0)
)
/

BEGIN
    INSERT INTO plch_emp VALUES (7369,'SMITH',  800,20);
    INSERT INTO plch_emp VALUES (7900,'JAMES',  950,30);
    INSERT INTO plch_emp VALUES (7876,'ADAMS', 1100,20);
    INSERT INTO plch_emp VALUES (7521,'WARD',  1250,30);
    INSERT INTO plch_emp VALUES (7654,'MARTIN',1250,30);
    INSERT INTO plch_emp VALUES (7844,'TURNER',1500,30);
    INSERT INTO plch_emp VALUES (7934,'MILLER',1300,10);
    INSERT INTO plch_emp VALUES (7499,'ALLEN', 1600,30);
    INSERT INTO plch_emp VALUES (7782,'CLARK', 2450,10);
    INSERT INTO plch_emp VALUES (7698,'BLAKE', 2850,30);
    INSERT INTO plch_emp VALUES (7566,'JONES', 2975,20);
    INSERT INTO plch_emp VALUES (7902,'FORD',  3000,20);
    INSERT INTO plch_emp VALUES (7788,'SCOTT', 3000,20);
    INSERT INTO plch_emp VALUES (7839,'KING',  5000,10);
    COMMIT;
END;
/


下列哪些查询返回了名字为 "KING"的员工的信息？
(A)
SELECT empno, ename, sal
FROM   plch_emp
WHERE  sal > ALL (2000, 3000, 4000);

(B)
SELECT e1.empno, e1.ename, e1.sal
FROM   plch_emp e1
WHERE  e1.sal > ALL (SELECT e2.sal
                     FROM   plch_emp e2
                     WHERE  e2.deptno = 20);
                     
(C)
SELECT empno, ename, sal
FROM   plch_emp
WHERE  sal > ANY (2000, 3000, 4000);

(D)
SELECT empno, ename, sal
FROM   plch_emp
WHERE  sal > SOME (3000, 4000, 5000);

(E)
SELECT empno, ename, sal
FROM   plch_emp
WHERE  sal > 2000 OR sal > 3000 OR sal > 4000;