SQL 小测验每周一题

hotdog04

就知道题目中的写法和选项中join on两种方式，using cross第一次看见。。。

newkid

6-25 答案BDE.
A: 按照别名的 ORDER BY导致总合计不在最后一行。
B: 虽然正确，但依赖于一个前提，即UNION ALL不修改结果集的顺序。更好的写法：
SELECT s2.total
     , s2.product
     , s2.country
     , s2.sales
FROM (
   SELECT 1 subselect
        , GROUPING(s.product) subtotal
        , 'Product' total
        , CASE GROUPING(s.product)
            WHEN 1 THEN 'TOTAL'
            ELSE s.product
          END product
        , 'TOTAL' country
        , SUM(s.sales) sales
   FROM plch_sales s
   GROUP BY s.product
   UNION ALL
   SELECT 2 subselect
        , GROUPING(s.country) subtotal
        , 'Country' total
        , 'TOTAL' product
        , CASE GROUPING(s.country)
            WHEN 1 THEN 'TOTAL'
            ELSE s.country
          END country
        , SUM(s.sales) sales
   FROM plch_sales s
   GROUP BY s.country
   UNION ALL
   SELECT 3 subselect
        , 1 subtotal
        , 'Grand' total
        , 'TOTAL' product
        , 'TOTAL' country
        , SUM(s.sales) sales
   FROM plch_sales s
) s2
ORDER BY s2.subselect
       , s2.subtotal
       , s2.product
       , s2.country;

C:错误：ROLLUP(s.product, s.country)会按照(product,country)和(product)汇总，不会产生(country)汇总。
D:正确：GROUPING SET允许你自己定义GROUP BY组合。
E:正确：CUBE产生了所有四种可能的 GROUP BY 组合， HAVING GROUPING_ID 去除了 (product,country) 组合。
F:错误：按照别名的 ORDER BY 导致了排序错误。

newkid

2011-7-2 ALL比较条件
作者：Steven Feuerstein
难度：低

我创建了这张表并填入数据：
CREATE TABLE plch_employees
(
   employee_id   INTEGER
, last_name     VARCHAR2 (100)
, salary        NUMBER
)
/

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

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

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

   INSERT INTO plch_employees
        VALUES (400, 'Buffet', 4000);

   INSERT INTO plch_employees
        VALUES (500, 'Slim', 5000);

   INSERT INTO plch_employees
        VALUES (600, 'Arnault', 6000);

   COMMIT;
END;
/

下列哪些查询执行之后会显示这三行文本？
Arnault
Buffet
Slim

(A)
SELECT last_name
    FROM plch_employees
   WHERE salary > ALL (2000, 3000)
ORDER BY last_name
/

(B)
SELECT last_name
    FROM plch_employees
   WHERE salary > ANY (2000, 3000)
ORDER BY last_name
/

(C)
SELECT last_name
    FROM plch_employees
   WHERE salary > 2000 AND salary > 3000
ORDER BY last_name
/

(D)
SELECT last_name
    FROM plch_employees
   WHERE salary >  (SELECT salary
                         FROM plch_employees
                        WHERE last_name IN ('Ellison', 'Gates'))
ORDER BY last_name
/

(E)
SELECT last_name
    FROM plch_employees
   WHERE salary > ALL (SELECT salary
                         FROM plch_employees
                        WHERE last_name IN ('Ellison', 'Gates'))
ORDER BY last_name
/

dingjun123

这个题目还不错，比较冷门，其实all,some,any操作符还挺复杂的，特别是>,>=,<,<=，还有用于关联子查询的时候
all就是全部都满足的意思，any some是任何一条满足
>all就是大于全部都满足的，那只要找最大的
>any就是大于任何一条，那就大于最小的，和all是相反的，<都是相反的
any子查询无结果，则无结果，all子查询无结果，则全部返回

chicheng_cn421

都是非常好的题目，学习了^_^

gyhgood

看完兔子的解释后选了ACE

all,some,any又是第一次看到。。。
这题越做越给力

newkid

2011-7-2 答案ACE, 兔子的解说很给力，大家现在都知道了回字有四种写法。

==================================================================
2011-7-9 行列转换
作者：Kim Berg Hansen
难度：中

我创建了这张表并填入数据：
CREATE TABLE plch_employees
(
   employee_id   INTEGER
, department_id INTEGER
, last_name     VARCHAR2 (20)
, salary        NUMBER
)
/

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

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

   INSERT INTO plch_employees
        VALUES (300, 100, 'Gates', 199500);

   INSERT INTO plch_employees
        VALUES (400, 200, 'Feuerstein', 199400);

   INSERT INTO plch_employees
        VALUES (500, 200, 'Hansen', 200600);

   COMMIT;
END;
/

我老板想知道每个部门有多少个员工赚钱少于250000，多少人大于等于250000。
哪些选项能够产生符合条件的输出：
DEPARTMENT_ID       LESS       MORE
------------- ---------- ----------
          100          2          0
          200          2          1

(A)
SELECT *
FROM (
   SELECT department_id
        , CASE
             WHEN salary < 250000 THEN 'LESS'
             ELSE 'MORE'
          END salary_category
   FROM plch_employees
) PIVOT (
   COUNT(salary_category)
   FOR department_id IN (
      100,200
   )
);

(B)
SELECT *
FROM (
   SELECT department_id
        , CASE
             WHEN salary < 250000 THEN 'LESS'
             ELSE 'MORE'
          END salary_category
   FROM plch_employees
) PIVOT (
   COUNT(*)
   FOR salary_category IN (
      'LESS' AS less,
      'MORE' AS more
   )
)
ORDER BY department_id;


(C)
SELECT department_id
     , COUNT(salary < 250000) less
     , COUNT(salary >= 250000) more
FROM plch_employees
GROUP BY department_id
ORDER BY department_id;

(D)
SELECT department_id
     , COUNT(
          CASE
             WHEN salary < 250000 THEN 1
          END
       ) less
     , COUNT(
          CASE
             WHEN salary >= 250000 THEN 1
          END
       ) more
FROM plch_employees
GROUP BY department_id
ORDER BY department_id;

(E)
SELECT *
FROM (
   SELECT department_id
        , CASE
             WHEN salary < 250000 THEN 'LESS'
             ELSE 'MORE'
          END salary_category
   FROM plch_employees
) PIVOT (
   COUNT(*)
   FOR salary_category IN (
      'LESS',
      'MORE'
   )
)
ORDER BY department_id;

(F)
SELECT department_id
     , COUNT(
          CASE
             WHEN salary < 250000 THEN 1
             ELSE 0
          END
       ) less
     , COUNT(
          CASE
             WHEN salary >= 250000 THEN 1
             ELSE 0
          END
       ) more
FROM plch_employees
GROUP BY department_id
ORDER BY department_id;

jboracle1981

(A)就算值对，输出的标题也不对，标题是100,200
(C)COUNT()里面的语法不对，不仔细看还以为是对的
(F)COUNT应该改成SUM
所以答案应该是BDE

dingjun123

jboracle1981 发表于 2011-11-4 08:49
(A)就算值对，输出的标题也不对，标题是100,200
(C)COUNT()里面的语法不对，不仔细看还以为是对的
...

这家伙考的东西很多陷阱的，他说的是一模一样

taobup

abdf