SQL 小测验每周一题

jboracle1981

ABD

dingjun123

some,any,all前面说了，some和any一个掉样，不知道为什么非要搞两个

newkid

jboracle1981 发表于 2011-11-22 08:50
ABD

正确。
=================================
2011-10-8 XML
作者：Tim Hall
难度：高

我们分到一个任务，需要产生一个XML文件，里面包含employees表的数据。我们先建表并填入数据：
CREATE TABLE plch_emp (
empno     NUMBER(4,0),
ename     VARCHAR2(10 BYTE),
job       VARCHAR2(9 BYTE),
mgr       NUMBER(4,0),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2,0)
);

BEGIN
   INSERT INTO plch_emp
        VALUES (7369
              ,  'SMITH'
              ,  'CLERK'
              ,  7902
              ,  TO_DATE ('17-DEC-80', 'DD-MON-RR')
              ,  800
              ,  NULL
              ,  20);

   INSERT INTO plch_emp
        VALUES (7499
              ,  'ALLEN'
              ,  'SALESMAN'
              ,  7698
              ,  TO_DATE ('20-FEB-81', 'DD-MON-RR')
              ,  1600
              ,  300
              ,  30);

   INSERT INTO plch_emp
        VALUES (7521
              ,  'WARD'
              ,  'SALESMAN'
              ,  7698
              ,  TO_DATE ('22-FEB-81', 'DD-MON-RR')
              ,  1250
              ,  500
              ,  30);

   INSERT INTO plch_emp
        VALUES (7566
              ,  'JONES'
              ,  'MANAGER'
              ,  7839
              ,  TO_DATE ('02-APR-81', 'DD-MON-RR')
              ,  2975
              ,  NULL
              ,  20);

   INSERT INTO plch_emp
        VALUES (7654
              ,  'MARTIN'
              ,  'SALESMAN'
              ,  7698
              ,  TO_DATE ('28-SEP-81', 'DD-MON-RR')
              ,  1250
              ,  1400
              ,  30);

   INSERT INTO plch_emp
        VALUES (7698
              ,  'BLAKE'
              ,  'MANAGER'
              ,  7839
              ,  TO_DATE ('01-MAY-81', 'DD-MON-RR')
              ,  2850
              ,  NULL
              ,  30);

   INSERT INTO plch_emp
        VALUES (7782
              ,  'CLARK'
              ,  'MANAGER'
              ,  7839
              ,  TO_DATE ('09-JUN-81', 'DD-MON-RR')
              ,  2450
              ,  NULL
              ,  10);

   INSERT INTO plch_emp
        VALUES (7788
              ,  'SCOTT'
              ,  'ANALYST'
              ,  7566
              ,  TO_DATE ('19-APR-87', 'DD-MON-RR')
              ,  3000
              ,  NULL
              ,  20);

   INSERT INTO plch_emp
        VALUES (7839
              ,  'KING'
              ,  'PRESIDENT'
              ,  NULL
              ,  TO_DATE ('17-NOV-81', 'DD-MON-RR')
              ,  5000
              ,  NULL
              ,  10);

   INSERT INTO plch_emp
        VALUES (7844
              ,  'TURNER'
              ,  'SALESMAN'
              ,  7698
              ,  TO_DATE ('08-SEP-81', 'DD-MON-RR')
              ,  1500
              ,  0
              ,  30);

   INSERT INTO plch_emp
        VALUES (7876
              ,  'ADAMS'
              ,  'CLERK'
              ,  7788
              ,  TO_DATE ('23-MAY-87', 'DD-MON-RR')
              ,  1100
              ,  NULL
              ,  20);

   INSERT INTO plch_emp
        VALUES (7900
              ,  'JAMES'
              ,  'CLERK'
              ,  7698
              ,  TO_DATE ('03-DEC-81', 'DD-MON-RR')
              ,  950
              ,  NULL
              ,  30);

   INSERT INTO plch_emp
        VALUES (7902
              ,  'FORD'
              ,  'ANALYST'
              ,  7566
              ,  TO_DATE ('03-DEC-81', 'DD-MON-RR')
              ,  3000
              ,  NULL
              ,  20);

   INSERT INTO plch_emp
        VALUES (7934
              ,  'MILLER'
              ,  'CLERK'
              ,  7782
              ,  TO_DATE ('23-JAN-82', 'DD-MON-RR')
              ,  1300
              ,  NULL
              ,  10);

   COMMIT;
END;

下列哪些查询会产生一个有效的XML文档，包含employee表的数据？

注意，如果你不知道一个XML文档的格式是否正确，可以输出到一个文件并用浏览器打开它。如果格式不对你将会看到错误信息。请记住，你的客户工具(SQL*Plus, SQL*Developer, TOAD)可能会将一行断成多行。你要检查它是否在一个标记的中间被断开了。

(A)
SET PAGESIZE 0

SELECT xml
FROM
(
SELECT 1 nr, '<?xml version="1.0" encoding="UTF-8"?><employees>' xml
FROM   dual
UNION ALL
SELECT 2 nr, '<employee>' ||
       '<id>' || e.empno || '</id>' ||
       '<name>' || DBMS_XMLGEN.convert(e.ename) || '</name>' ||
       '<job>' || DBMS_XMLGEN.convert(e.job) || '</job>' ||
       '</employee>'
FROM   plch_emp e
WHERE  e.deptno = 10
UNION ALL
SELECT 3 nr, '</employees>'
FROM   dual
)
ORDER BY nr;

(B)
SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 100000

SELECT XMLROOT(
         XMLELEMENT(
           "employees",
           XMLAGG(
             XMLELEMENT(
               "employee",
               XMLELEMENT("id", e.empno),
               XMLELEMENT("name", e.ename),
               XMLELEMENT("job", e.job)
             )
           )
         )
       ,VERSION NO VALUE)
FROM   plch_emp e
WHERE  e.deptno = 10;

(C)
SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 100000

SELECT XMLROOT(
         XMLELEMENT("employees",
           XMLAGG(
             XMLELEMENT(
               "employee",
               XMLFOREST(
                 e.empno AS "id",
                 e.ename AS "name",
                 e.job AS "job"
               )
             )
           )
         )
       ,VERSION NO VALUE)
FROM   plch_emp e
WHERE  e.deptno = 10;


(D)
SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 100000

SELECT XMLELEMENT(
         "employee",
         XMLFOREST(
           e.empno AS "id",
           e.ename AS "name",
           e.job AS "job"
         )
       )
FROM   plch_emp e
WHERE  e.deptno = 10;


(E)
SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 100000

SELECT XMLROOT(
         XMLELEMENT("employees",
           XMLELEMENT(
             "employee",
             XMLATTRIBUTES(
               e.empno AS "id",
               e.ename AS "name",
               e.job AS "job"
             )
           )
         )
       ,VERSION NO VALUE)
FROM   plch_emp e
WHERE  e.deptno = 10;

newkid

2011-10-8 答案ABCE.

A: 你可以用传统的字符串拼接方法自己构造XML. 注意DBMS_XMLGEN.CONVERT能够把你数据中的特殊字符转换为对XML安全的标记。
B: XMLELEMENT函数可以用来把单个列转换成标记。然后这些被组合到一起，再次由XMLELEMENT变成一个"employee"标记 。每个employee被XMLAGG函数聚合到一起。然后XMLELEMENT又被用来提供外部（根）的"employees"标记。最后，XMLROOT函数提供了XML文档起始点的XML标记。
C: 这个类似于B, 除了XMLFOREST函数被用来将一系列的字段一下子转换为XML标记。注意列取了别名来强制将标记指定为小写。如果你要处理好几个列，用XMLFOREST会显得很清楚。
D: 这个查询构造了一系列的employee数据。它们没有用根标记包含起来，并且没有起始的XML标记。
E: 这是答案C的重复，但是用XMLATTRIBUTES子句代替了XMLFOREST。这会将指定的列转换为它们的父亲元素的属性，而不是子标记的属性。这对于压缩XML文档很有用。典型情况下，文档越小则解析越快，所以为了性能起见，用属性取代标记是有道理的。
F: 因为没有用 XMLAGG 函数，我们为每个员工创建了一个完整的 XML 文档，而不是将他们组合成单个文档。

===================================

2011-10-15 Top-N 查询
作者：Tim Hall
难度：中

下列代码创建了员工表和数据：
CREATE TABLE plch_emp (
empno     NUMBER(4,0),
ename     VARCHAR2(10 BYTE),
job       VARCHAR2(9 BYTE),
mgr       NUMBER(4,0),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2,0)
);

INSERT INTO plch_emp VALUES (7369,'SMITH', 'CLERK',    7902
   ,TO_DATE('17-DEC-80','DD-MON-RR'), 800,NULL,20);
INSERT INTO plch_emp VALUES (7900,'JAMES', 'CLERK',    7698
   ,TO_DATE('03-DEC-81','DD-MON-RR'), 950,NULL,30);
INSERT INTO plch_emp VALUES (7876,'ADAMS', 'CLERK',    7788
   ,TO_DATE('23-MAY-87','DD-MON-RR'),1100,NULL,20);
INSERT INTO plch_emp VALUES (7521,'WARD',  'SALESMAN', 7698
   ,TO_DATE('22-FEB-81','DD-MON-RR'),1250, 500,30);
INSERT INTO plch_emp VALUES (7654,'MARTIN','SALESMAN', 7698
   ,TO_DATE('28-SEP-81','DD-MON-RR'),1250,1400,30);
INSERT INTO plch_emp VALUES (7844,'TURNER','SALESMAN', 7698
   ,TO_DATE('08-SEP-81','DD-MON-RR'),1500,   0,30);
INSERT INTO plch_emp VALUES (7934,'MILLER','CLERK',    7782
   ,TO_DATE('23-JAN-82','DD-MON-RR'),1300,NULL,10);
INSERT INTO plch_emp VALUES (7499,'ALLEN', 'SALESMAN', 7698
   ,TO_DATE('20-FEB-81','DD-MON-RR'),1600, 300,30);
INSERT INTO plch_emp VALUES (7782,'CLARK', 'MANAGER',  7839
   ,TO_DATE('09-JUN-81','DD-MON-RR'),2450,NULL,10);
INSERT INTO plch_emp VALUES (7698,'BLAKE', 'MANAGER',  7839
   ,TO_DATE('01-MAY-81','DD-MON-RR'),2850,NULL,30);
INSERT INTO plch_emp VALUES (7566,'JONES', 'MANAGER',  7839
   ,TO_DATE('02-APR-81','DD-MON-RR'),2975,NULL,20);
INSERT INTO plch_emp VALUES (7902,'FORD',  'ANALYST',  7566
   ,TO_DATE('03-DEC-81','DD-MON-RR'),3000,NULL,20);
INSERT INTO plch_emp VALUES (7788,'SCOTT', 'ANALYST',  7566
   ,TO_DATE('19-APR-87','DD-MON-RR'),3000,NULL,20);
INSERT INTO plch_emp VALUES (7839,'KING',  'PRESIDENT',NULL
   ,TO_DATE('17-NOV-81','DD-MON-RR'),5000,NULL,10);
COMMIT;

下列哪些查询显示了薪水最高（忽略佣金commission）的五名员工？
(A)
SELECT e.ename,
       e.sal
FROM   plch_emp e
WHERE  rownum <= 5
ORDER BY e.sal DESC, e.ename;

(B)
SELECT e.ename,
       e.sal
FROM   (SELECT ename,
               sal,
               DENSE_RANK() OVER (ORDER BY sal DESC) AS sal_rank
        FROM   plch_emp) e
WHERE  e.sal_rank <= 5
ORDER BY e.sal DESC, e.ename;

(C)
SELECT e.ename,
       e.sal
FROM   (SELECT ename, sal
        FROM   plch_emp
        ORDER BY sal DESC) e
WHERE  rownum <= 5
ORDER BY e.sal DESC, e.ename;

(D)
WITH emp_query AS (
  SELECT ename, sal
  FROM   plch_emp
  ORDER BY sal DESC)
SELECT e.ename,
       e.sal
FROM   emp_query e
WHERE  rownum <= 5
ORDER BY e.sal DESC, e.ename;

(E)
SELECT e.ename,
       e.sal
FROM   (SELECT ename,
               sal,
               RANK() OVER (ORDER BY sal DESC) AS sal_rank
        FROM   plch_emp) e
WHERE  e.sal_rank <= 5
ORDER BY e.sal DESC, e.ename;


(F)
SELECT e.ename,
       e.sal
FROM   (SELECT ename,
               sal,
               ROW_NUMBER() OVER (ORDER BY sal ASC) AS sal_rank
        FROM   plch_emp) e
WHERE  e.sal_rank <= 5
ORDER BY e.sal DESC, e.ename;

jboracle1981

B

gyhgood

B

gyhgood

RANK(),DENSE_RANK() 记错了。

ORDER BY 排序居然没想明白。。。。
闭门思过去

mcl0813

答案：ABCDF。
还等价于下面查询语句：
SELECT e.last_name, e.salary, d.department_name
     FROM plch_employees e, plch_departments d
    where e.department_id = d.department_id(+);

wzj124

newkid 发表于 2011-11-1 05:15
2011-06-18 ANSI JOIN 语法
作者：Patrick Barel
难度：中

版主你说答案有a，可是natural联接中不是不可以出现限定词吗？我用的是11g的，练习过都提示查询的列不能有限定词啊

newkid

wzj124 发表于 2011-11-24 13:04
版主你说答案有a，可是natural联接中不是不可以出现限定词吗？我用的是11g的，练习过都提示查询的列不能有 ...

A里面确实没有限定词啊？你看到的A在哪里？

(A)
SELECT e.last_name, e.salary, d.department_name
  FROM plch_employees e NATURAL JOIN plch_departments d
/