SQL 小测验每周一题

newkid · 发表于 2011-11-10 00:25

2011-7-30 答案A, 其他的语法都是错的。
===========================
2011-8-6 ADD_MONTHS在月末的表现
作者：Patrick Barel
难度：低

下列查询的结果是什么？(注：其中两处NLS_DATE_LANGUAGE=american参数为译者所加)
WITH the_current_date AS
(SELECT to_date('Jan 30 2012', 'Mon DD YYYY','NLS_DATE_LANGUAGE=american') the_date
FROM dual)
SELECT to_char(add_months(add_months(the_current_date.the_date, 1), 1), 'Mon DD YYYY','NLS_DATE_LANGUAGE=american') what_date
FROM the_current_date;

(A)
Mar 31 2012

(B)
Mar 30 2012

(C)
Mar 29 2012

(D)
Mar 28 2012

gyhgood · 发表于 2011-11-10 09:36

zhang_xiaobo · 发表于 2011-11-10 12:04

abdf 哈哈对否啊？

laryad · 发表于 2011-11-10 14:22

看来to_char（sysdate，'dd'）>= 28 的时候, 最好还是用 last_day - n 比较准确。

newkid · 发表于 2011-11-11 00:01

2011-8-6 答案A.

函数执行顺序是从里到外。第一次调用add_months，把1月30号加上一个月会得到2月29号，即月末。第二次调用会得到3月的月末即3月31号。
如果只调用一次add_months，为1月30号加上两个月，则会得到3月30号。
add_months对月末处理有些特殊，必须记住。

===================================================
2011-8-13 NULL的处理
作者：Patrick Barel
难度：低

假设我们有这张表：
create table plch_employees (
employee_id number (4) NOT NULL
, employee_name varchar2 (10) NOT NULL
, employee_salary number (7,2) NOT NULL
, employee_comm number (7,2) );

我们填入了下列数据：
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7369, 'SMITH' , 800 , NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7499, 'ALLEN' , 1600, 300 );
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7521, 'WARD'  , 1250, 500 );
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7566, 'JONES' , 2975, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7654, 'MARTIN', 1250, 1400);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7698, 'BLAKE' , 2850, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7782, 'CLARK' , 2450, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7788, 'SCOTT' , 3000, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7839, 'KING'  , 5000, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7844, 'TURNER', 1500, 0 );
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7876, 'ADAMS' , 1100, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7900, 'JAMES' , 950 , NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7902, 'FORD'  , 3000, NULL);
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)
               values ( 7934, 'MILLER', 1300, NULL);

commit;
我们想要得到一个员工的所有收入(即工资 salary ＋佣金commission)。哪些查询能够给我们正确结果？

(A)
select e.employee_name, e.employee_salary + nvl(e.employee_comm, 0)
from plch_employees e
/
(B)
select e.employee_name
   , coalesce(e.employee_comm + e.employee_salary, e.employee_salary)
  from plch_employees e
/

(C)
select e.employee_name
   , coalesce(e.employee_salary, e.employee_comm + e.employee_salary)
  from plch_employees e
/
(D)
select e.employee_name
   , e.employee_salary + coalesce(e.employee_comm, 0)
  from plch_employees e
/

(E)
select e.employee_name, e.employee_salary + case
                        when e.employee_comm is null then 0
                        else e.employee_comm
                     end
  from plch_employees e
/

(F)
select e.employee_name, e.employee_salary + e.employee_comm
  from plch_employees e
/

gyhgood · 发表于 2011-11-11 08:25

ABDE

laryad · 发表于 2011-11-11 13:34

Rain清风 · 发表于 2011-11-11 14:33

ABDE

gclizh · 发表于 2011-11-11 14:41

ABDE

newkid · 发表于 2011-11-11 23:23

2011-8-13 答案ABDE.

===================================================
2011-8-20 WHERE和HAVING子句的对比
作者：Sohilkumar Bhavsar
难度：中

我创建了这张表：
CREATE TABLE PLCH_EMP ( EMP_NO NUMBER, ENAME VARCHAR2(20), COUNTRY VARCHAR2(5), SAL NUMBER);

并且插入了下列记录：
INSERT INTO PLCH_EMP VALUES(1, 'Steven', 'US', 5000);
INSERT INTO PLCH_EMP VALUES(2, 'Finn', 'UK', 2000);
INSERT INTO PLCH_EMP VALUES(3, 'Larry',  'US', 1000);
INSERT INTO PLCH_EMP VALUES(4, 'Anil', 'IN', 3000);
INSERT INTO PLCH_EMP VALUES(5, 'Elison', 'US', 2000);
INSERT INTO PLCH_EMP VALUES(6, 'Kim', 'UK', 2000);
INSERT INTO PLCH_EMP VALUES(7, 'Mike', 'DE', 1500);

COMMIT;

I want to calculate total salary of employees per country, for all countries except 'UK' where the country salary total is greater than 2000.

我想要每个国家的员工工资总值，英国(UK)除外，而且只要总值大于2000的结果。

下列的哪些选项能够正确计算总值，从而使得屏幕上能输出下面的结果并且按国家排序：

IN 3000
US 8000

(A)
SELECT COUNTRY,
  SUM(SAL)
FROM PLCH_EMP
WHERE COUNTRY != 'UK'
GROUP BY COUNTRY
HAVING SUM(SAL) > 2000
ORDER BY COUNTRY;

(B)
SELECT COUNTRY,
  SUM(SAL)
FROM PLCH_EMP
GROUP BY COUNTRY
HAVING COUNTRY != 'UK'
AND SUM(SAL) > 2000
ORDER BY COUNTRY;

(C)
SELECT COUNTRY,
  SUM_SAL
FROM
  (SELECT COUNTRY, SUM(SAL) SUM_SAL FROM PLCH_EMP GROUP BY COUNTRY
  )
WHERE COUNTRY != 'UK'
AND SUM_SAL > 2000;

(D)
SELECT COUNTRY,
  SUM(SAL)
FROM PLCH_EMP
WHERE COUNTRY != 'UK'
AND SUM(SAL) > 2000
GROUP BY COUNTRY
ORDER BY COUNTRY;

(E)
SELECT COUNTRY,
  SUM(SAL)
FROM PLCH_EMP
GROUP BY COUNTRY
HAVING COUNTRY != 'UK'
AND SAL       > 2000
ORDER BY COUNTRY;