SQL 小测验每周一题

newkid

wzj124 发表于 2011-11-27 09:06
我当时是要select出product_types表的name和products表的name和description来组成一个结果的,这三个属性都 ...

把SQL贴出来，否则就是说再多也没用。

newkid

2011-10-29答案AB.

下面这句话可疑修改当前会话的SCHEMA名称：
alter session set current_schema = <user>;

从此，会话中的语句都被解释为当前的SCHEMA, 而不是连接用户的SCHEMA.
然而，用户权限保持不变，你仍然只能够访问具备权限的那些对象。

A: 会被重定向到HR.PLCH_USERS
B: 显式指明访问HR.PLCH_USERS
C: 显式指明访问SCOTT.plch_users，这不是我们需要的
D: 会被重定向到HR.PLCH_USERS_2，由于SCOTT用户没有这张表的权限，会报如下错误：
   ORA-00942: table or view does not exist
E: 错误同D
F: 显式指明访问SCOTT.plch_users_2, 这不是我们需要的表。   
==============================================================
2011-11-5 GROUP BY和HAVING

我创建了这张表并填入数据：
CREATE TABLE plch_parts
(
   partnum    INTEGER PRIMARY KEY
, partname   VARCHAR2 (100) UNIQUE
, partloc    VARCHAR2 (100)
)
/

INSERT INTO plch_parts VALUES (1, 'Part1', 'Bottom');
INSERT INTO plch_parts VALUES (2, 'Part2', 'Middle');
INSERT INTO plch_parts VALUES (3, 'Part3', 'Top');
INSERT INTO plch_parts VALUES (4, 'Part4', 'Bottom');
INSERT INTO plch_parts VALUES (5, 'Part5', 'Middle');
COMMIT;

下列哪些查询在执行后将会显示：
Bottom                                                                          
Middle  

(A)
SELECT partloc
    FROM plch_parts
GROUP BY partloc
   WHERE COUNT (*) > 1
ORDER BY partloc
/

(B)
SELECT partloc
    FROM plch_parts
  HAVING COUNT (*) > 1
GROUP BY partloc
ORDER BY partloc
/

(C)
SELECT partloc
    FROM plch_parts
GROUP BY partloc
  HAVING COUNT (*) > 1
ORDER BY partloc
/

(D)
SELECT partloc
    FROM plch_parts
   WHERE COUNT (*) > 1
ORDER BY partloc
/

gyhgood

c

gyhgood

虽然知道HAVING  是在GROUP BY后执行
但是他们俩在语句中的位置可以互换还真没试验过

dear2170

学习下

newkid

2011-11-5答案BC. 注意B这种写法比较不常见。
===============================
2011-11-12 列转行
作者：Kim Berg Hansen
难度：高

我创建了如下的表并填入数据:
CREATE TABLE plch_employees
(
   employee_id    INTEGER
, last_name      VARCHAR2 (20)
, hire_date      DATE
, promote_date   DATE
, fire_date      DATE
)
/

INSERT INTO plch_employees
VALUES (
   100,
   'Jobs',
   DATE '1995-01-01',
   DATE '2005-01-01',
   DATE '2010-01-01'
)
/

INSERT INTO plch_employees
VALUES (
   200,
   'Ellison',
   DATE '1990-01-01',
   DATE '2000-01-01',
   NULL
)
/

INSERT INTO plch_employees
VALUES (
   300,
   'Gates',
   DATE '2001-01-01',
   NULL,
   DATE '2002-01-01'
)
/

COMMIT
/

我老板想要一张 HR 部门的录用、提升、解雇时间的时间表。换句话说，他想要一个清单，包含了事件日期、事件（录用、提升或解雇）和员工的姓。

哪些选项能够产生这样一个清单（如下所示）包含了上述的七个HR事件？

EVENT_DATE    EVENT      LAST_NAME                     
------------- ---------- ------------------------------
01-JAN-90     Hire       Ellison                       
01-JAN-95     Hire       Jobs                          
01-JAN-00     Promote    Ellison                       
01-JAN-01     Hire       Gates                        
01-JAN-02     Fire       Gates                        
01-JAN-05     Promote    Jobs                          
01-JAN-10     Fire       Jobs  

(A)
SELECT event_date
     , event
     , last_name
FROM plch_employees
UNPIVOT INCLUDE NULLS (
   event_date FOR event IN (
      hire_date AS 'Hire',
      promote_date AS 'Promote',
      fire_date AS 'Fire'
   )
)
ORDER BY event_date;

(B)
SELECT CASE d.dummy
          WHEN 1 THEN e.hire_date
          WHEN 2 THEN e.promote_date
          WHEN 3 THEN e.fire_date
       END event_date,
       CASE d.dummy
          WHEN 1 THEN 'Hire'
          WHEN 2 THEN 'Promote'
          WHEN 3 THEN 'Fire'
       END event,
       e.last_name
FROM plch_employees e
CROSS JOIN (
   SELECT LEVEL AS dummy
   FROM DUAL
   CONNECT BY LEVEL <= 3
) d
ORDER BY event_date;

(C)
SELECT event_date
     , event
     , last_name
FROM plch_employees
PIVOT (
   MAX(event_date)
   FOR event IN (
      hire_date AS 'Hire',
      promote_date AS 'Promote',
      fire_date AS 'Fire'
   )
)
ORDER BY event_date;

(D)
SELECT event_date
     , event
     , last_name
FROM plch_employees
UNPIVOT (
   event_date FOR event IN (
      hire_date AS 'Hire',
      promote_date AS 'Promote',
      fire_date AS 'Fire'
   )
)
ORDER BY event_date;

(E)
WITH events AS (
   SELECT 'Hire'    event FROM DUAL
   UNION ALL
   SELECT 'Promote' event FROM DUAL
   UNION ALL
   SELECT 'Fire'    event FROM DUAL
)
SELECT event_date
     , event
     , last_name
FROM (
   SELECT CASE events.event
             WHEN 'Hire'    THEN e.hire_date
             WHEN 'Promote' THEN e.promote_date
             WHEN 'Fire'    THEN e.fire_date
          END event_date,
          events.event,
          e.last_name
   FROM plch_employees e
   CROSS JOIN events
) evt
WHERE event_date IS NOT NULL
ORDER BY event_date;

(F)
     , event
     , last_name
FROM (
   SELECT CASE d.dummy
             WHEN 1 THEN e.hire_date
             WHEN 2 THEN e.promote_date
             WHEN 3 THEN e.fire_date
          END event_date,
          CASE d.dummy
             WHEN 1 THEN 'Hire'
             WHEN 2 THEN 'Promote'
             WHEN 3 THEN 'Fire'
          END event,
          e.last_name
   FROM plch_employees e
   CROSS JOIN (
      SELECT LEVEL AS dummy
      FROM DUAL
      CONNECT BY LEVEL <= 3
   ) d
) evt
WHERE event_date IS NOT NULL
ORDER BY event_date;

ttxing

newkid 发表于 2011-11-28 23:41
2011-10-29答案AB.

下面这句话可疑修改当前会话的SCHEMA名称：

BC都可以

gyhgood

BEF

newkid

2011-11-12 答案DEF
A:UNPIVOT是对的，但是INCLUDE NULLS产生了9行输出，（3列×3行）
B:和三行集合做笛卡尔积是对的，但是依然包含了NULL，结果同上。
C:UNPIVOT(列转行)和PIVOT(行转列)的用法搞反了。
D:UNPIVOT缺省是EXCLUDE NULLS的，相当于修正了A的写法。
E:和三行集合做笛卡尔积是对的，并且过滤掉 NULL, 相当于DIY的UNPIVOT
F:类似E的实现方法。
=================================================
2011-11-19 XML函数

作者：Sergey Porokh
难度：高

我创建了如下的plch_employees_xml表并填入数据：
CREATE TABLE plch_employees_xml
(
   id         INTEGER PRIMARY KEY
, xml_body   VARCHAR2 (4000)
)
/

INSERT INTO plch_employees_xml (id, xml_body)
VALUES (1,
'<MAIN>
  <PERSON name="LARRY ELLISON">
    <COMPANY>ORACLE</COMPANY>
  </PERSON>
  <PERSON name="VIRGINIA ROMETTY">
    <COMPANY>IBM</COMPANY>
  </PERSON>
</MAIN>')
/

COMMIT
/

下列哪些选项的查询结果会显示下列两行文本？
LARRY ELLISON
VIRGINIA ROMETTY

(A)
select
  value(p).extract( '/PERSON/@name' ).getStringVal() person_name
from
  plch_employees_xml pex
, table( xmlsequence(
                     xmltype(pex.xml_body).extract('//PERSON')
                    )
       ) p
order by 1

(B)
select
  p."Name" person_name
from
  plch_employees_xml pex
, xmltable('/PERSON'
           passing xmltype(pex.xml_body).extract('//PERSON')
           columns
             "Name" varchar2(20) path '/PERSON/@name'
          ) p
order by 1

(C)
select
   xmlquery(
            'for $i in /PERSON
             return ($i/@name)'
             passing xmltype(pex.xml_body).extract('//PERSON')
             returning content
           ).getStringVal() person_name
from
   plch_employees_xml pex
order by 1

(D)
select
  p."Name" person_name
from  
  plch_employees_xml pex
, xmltable('for $i in /PERSON
            return ($i)'
            passing xmltype(pex.xml_body).extract('//PERSON')
            columns
              "Name" varchar2(20) path '/PERSON/@name'
          ) p
order by 1

周应侯

这个是挺难的，但想通了，也很简单的，答案应该是ABD~