SQL 小测验每周一题

gyhgood

PIVOT 完全不懂

ziling2011

　BD　
E列名有引号

newkid

2011-7-9 答案BD.
A: 输出列名不对
C: 语法错误
E: 输出列名不对, 多了单引号
F: COUNT会计算所有非空列，0也会被计入。应该用NULL取代0.


==================================================================
2011-7-16 分析函数 FIRST 关键字
作者：mentzel.iudith
难度：中

在我们的HR（人力资源）应用中我们有如下的员工表：
CREATE TABLE plch_employees
(
   employee_id  INTEGER PRIMARY KEY
, last_name    VARCHAR2(100)
, salary       NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
     VALUES (100, 'Jobs', 1000);

   INSERT INTO plch_employees
     VALUES (200, 'Ellison', 1000);

   INSERT INTO plch_employees
     VALUES (300, 'Gates', 1000);

   COMMIT;
END;
/

我们还有一张日志表，记录了每次员工的薪资调整：
CREATE TABLE plch_emp_log
(
   employee_id  INTEGER  REFERENCES plch_employees (employee_id)
, log_date     DATE
, old_salary   NUMBER
, new_salary   NUMBER
, CONSTRAINT plch_emp_log_pk PRIMARY KEY (employee_id, log_date)
)
/

BEGIN
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-JAN-10','DD-MON-RR'), 1000, 2000);
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('18-JAN-10','DD-MON-RR'), 2000, 1800);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-JAN-10','DD-MON-RR'), 1000, 1600);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-MAR-10','DD-MON-RR'), 1600, 2500);

   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('01-APR-10','DD-MON-RR'), 1000, 2000);
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('05-APR-10','DD-MON-RR'), 1800, 1900);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('10-MAY-10','DD-MON-RR'), 2500, 2300);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('15-MAY-10','DD-MON-RR'), 2000, 3000);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-SEP-10','DD-MON-RR'), 1900, 1500);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-SEP-10','DD-MON-RR'), 2300, 3000);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('10-OCT-10','DD-MON-RR'), 3000, 2700);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('05-JAN-11','DD-MON-RR'), 1500, 2500);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('10-FEB-11','DD-MON-RR'), 3000, 2000);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('20-FEB-11','DD-MON-RR'), 2700, 2500);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-APR-11','DD-MON-RR'), 2500, 2200);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-JUL-11','DD-MON-RR'), 2000, 2800);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('15-SEP-11','DD-MON-RR'), 2500, 2900);

   COMMIT;
END;
/

我们决定实现一个视图，从而使得对这个视图的简单查询（只有过滤条件，没有连接、聚合、子查询等）能够回答诸如此类的需求：

返回一个员工列表，他们的工资在一个指定日期区间发生变动，要求显示每个员工在此区间的第一个和最后一个工资，这个区间可能是一年，一季度，或一个月。

这里有些例子：

1. 某一年，比如 2010
PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010             100          1000         1500
2010             200          1000         3000
2010             300          1000         2700

2. 某一季度，比如 2010 第一季度
PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010-1           100          1000         1800
2010-1           200          1000         2500

3. 某个月，比如 2010-01
PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010-01          100          1000         1800
2010-01          200          1000         1600

哪些选项可用来取代下列视图定义中的 /*FIRST_SALARY*/ 和 /*LAST_SALARY*/，使得这个视图可用来回答上述问题？

CREATE OR REPLACE VIEW plch_emp_salary_change
AS
SELECT
       TO_CHAR(log_date,'YYYY')  year,
       TO_CHAR(log_date,'Q')     quarter,
       TO_CHAR(log_date,'MM')    month,
       employee_id,
       /*FIRST_SALARY*/    first_salary,
       /*LAST_SALARY*/     last_salary,
       GROUPING_ID(TO_CHAR(log_date,'YYYY'),
                   TO_CHAR(log_date,'Q'),
                   TO_CHAR(log_date,'MM'))   gid
FROM
      plch_emp_log
GROUP BY
      ROLLUP(TO_CHAR(log_date,'YYYY'),
             TO_CHAR(log_date,'Q'),
             TO_CHAR(log_date,'MM')),
      employee_id
/

(A)
MIN(old_salary)
MAX(new_salary)

(B)
FIRST_VALUE(old_salary)
LAST_VALUE(new_salary)

(C)
MIN(old_salary) KEEP(DENSE_RANK FIRST ORDER BY log_date)
MIN(new_salary) KEEP(DENSE_RANK LAST  ORDER BY log_date)

(D)
FIRST_VALUE(old_salary)   OVER(PARTITION BY employee_id
                               ORDER BY log_date)     
LAST_VALUE(new_salary)    OVER(PARTITION BY employee_id
                               ORDER BY log_date)
                              
(E)
MIN(old_salary)    OVER(PARTITION BY employee_id
                        ORDER BY log_date)
MAX(new_salary)    OVER(PARTITION BY employee_id
                        ORDER BY log_date)
                        
(F)
TO_NUMBER(SUBSTR(MIN(TO_CHAR(log_date,'YYYYMMDDHH24MISS')||old_salary),15))
TO_NUMBER(SUBSTR(MAX(TO_CHAR(log_date,'YYYYMMDDHH24MISS')||new_salary),15))

ziling2011

D

dingjun123

注意分组的语义概念以及要求的最开始的sal和最后的sal,这个过程中可能降薪
所以答案和明显了，2个

吴哲仁

答案是：A,B,D,F
C最后差了一个括号

2011-06-18 ANSI JOIN 语法 作者：Patrick Barel 难度：中 我创建了这张表并填入数据： CREATE TABLE plch_departments (   department_id INTEGER PRIMARY KEY ,department_name VARCHAR2 (100)) / BEGIN    INSERT INTO plch_departments         VALUES (100, 'Marketing');    INSERT INTO plch_departments         VALUES (200, 'Catering');    COMMIT; END; / CREATE TABLE plch_employees (    employee_id     INTEGER , last_name       VARCHAR2 (100) , salary          NUMBER , department_id   INTEGER    REFERENCES plch_departments (department_id) ) / BEGIN    INSERT INTO plch_employees         VALUES (100               , 'Jobs'               , 1000000               , 100);    INSERT INTO plch_employees         VALUES (200               , 'Ellison'               , 1000000               , 200);    INSERT INTO plch_employees         VALUES (300               , 'Gates'               , 1000000               , 200);    COMMIT; END; / 哪些选项包含了一个查询，它返回的结果集和下面这个查询相同： SELECT e.last_name, e.salary, d.department_name   FROM plch_employees e, plch_departments d WHERE e.department_id = d.department_id (A) SELECT e.last_name, e.salary, d.department_name   FROM plch_employees e NATURAL JOIN plch_departments d / (B) SELECT e.last_name, e.salary, d.department_name   FROM plch_employees e JOIN plch_departments d USING (department_id) / (C) SELECT e.last_name, e.salary, d.department_name   FROM plch_employees e JOIN plch_departments d USING department_id / (D) SELECT e.last_name, e.salary, d.department_name   FROM    plch_employees e        JOIN           plch_departments d        ON (e.department_id = d.department_id) / (E) SELECT e.last_name, e.salary, d.department_name   FROM plch_employees e CROSS JOIN plch_departments d / (F) SELECT e.last_name, e.salary, d.department_name   FROM plch_employees e CROSS JOIN plch_departments d WHERE e.department_id = d.department_id /

世界上只有两种编程方法：Oracle的方法和错误的方法。

西雁大大

ACDF

javaxf

很是实践出真知啊。

outdo

CDF

gyhgood

ABDF