SQL 小测验每周一题

newkid

2011-10-15 答案CDE.
A: 未经排序引用ROWNUM是不正确的
B: DENSE_RANK用错了，会导致超过5人的也被选中，应该用RANK
F: ROW_NUMBER里面的排序应该用倒序才对。
==================================
2011-10-22 多表插入
作者：Tim Hall
难度：中

我需要将数据从一张源表转移到三张目标表。源表是用如下代码创建并且填入了1000行数据：

CREATE TABLE plch_source_tab (
  id          NUMBER,
  short_desc  VARCHAR2(20),
  description VARCHAR2(100)
);

INSERT INTO plch_source_tab
SELECT level,
       'ID = ' || level,
       'A long description for ' || level
FROM   dual
CONNECT BY level <= 1000;
COMMIT;Next, create the three destination tables.

CREATE TABLE plch_dest_tab_1 (
  id          NUMBER
);

CREATE TABLE plch_dest_tab_2 (
  id          NUMBER,
  short_desc  VARCHAR2(20)
);

CREATE TABLE plch_dest_tab_3 (
  id          NUMBER,
  short_desc  VARCHAR2(20),
  description VARCHAR2(100)
);

下列的哪些选项会导致每一张目标表里面都会被插入一行或者多行数据？

(A)
INSERT ALL
  INTO plch_dest_tab_1 VALUES (id)
  INTO plch_dest_tab_2 VALUES (id, short_desc)
  WHEN id > 1000 THEN
    INTO plch_dest_tab_3 VALUES (id, short_desc, description)
SELECT id, short_desc, description FROM plch_source_tab;

(B)
INSERT ALL
  WHEN 1=1 THEN
    INTO plch_dest_tab_1 VALUES (id)
    INTO plch_dest_tab_2 VALUES (id, short_desc)
  WHEN id <= 500 THEN
    INTO plch_dest_tab_3 (id, short_desc) VALUES (id, short_desc)
SELECT * FROM plch_source_tab;


(C)
INSERT ALL
  WHEN 1=1 THEN
    INTO plch_dest_tab_1 VALUES (id)
  WHEN id <= 500 THEN
    INTO plch_dest_tab_2 VALUES (id, short_desc)
  ElSE
    INTO plch_dest_tab_3 (id, short_desc) VALUES (id, short_desc)
SELECT * FROM plch_source_tab;


(D)
INSERT ALL
  INTO plch_dest_tab_1 VALUES (id)
  INTO plch_dest_tab_2 VALUES (id, short_desc)
  INTO plch_dest_tab_3 VALUES (id, short_desc, description)
SELECT id, description FROM plch_source_tab;


(E)
INSERT ALL
  INTO plch_dest_tab_1 VALUES (id)
  INTO plch_dest_tab_2 VALUES (id, short_desc)
  INTO plch_dest_tab_3 VALUES (id, short_desc, description)
SELECT id, short_desc, description FROM plch_source_tab;


(F)
INSERT FIRST
  WHEN id <= 250 THEN
    INTO plch_dest_tab_1 VALUES (id)
  WHEN id <= 500 THEN
    INTO plch_dest_tab_2 VALUES (id, short_desc)
  ELSE
    INTO plch_dest_tab_3 (id, short_desc) VALUES (id, short_desc)
SELECT id, short_desc FROM plch_source_tab;


(G)
INSERT INTO plch_dest_tab_1
SELECT id FROM plch_source_tab;

jboracle1981

======================
2011-10-15 答案CDE.
A: 未经排序引用ROWNUM是不正确的
B: DENSE_RANK用错了，会导致超过5人的也被选中，应该用RANK
F: ROW_NUMBER里面的排序应该用倒序才对。

这题理解出错了
rownum,row_number()显示前5行

jboracle1981

2011-10-22 多表插入
===================
BCEF

jboracle1981

做了两遍还是不对

看语法去了

dingjun123

这个要注意下，multi-insert的insert依赖于后面查询的语句，可以不指定table的列，也可以不指定values,但是有这种依赖关系
另外insert all和first的区别，all默认，first必须是带条件的，all带条件是互相可交叉的，除了最后的else,first不交叉
如果带条件，则从最开始就要加上条件

KEN6503

dingjun123 发表于 2011-11-25 10:41
这个要注意下，multi-insert的insert依赖于后面查询的语句，可以不指定table的列，也可以不指定values,但是 ...

谢谢你的解释

wzj124

newkid 发表于 2011-11-24 22:57
A里面确实没有限定词啊？你看到的A在哪里？

(A)

恩，我说的限定词其实就是两个表的别名啦，可能我没说清楚，我用版主给的代码做了一遍，A选项能够得出结果，可是我用自己 表用natural联接老是出现“natural联接中使用给的列不能有限定词”也就是那个别名限定列，我找了好久也没找出个所以然，还望版主赐教，谢谢
表product_types
create table product_types(
product_type_id integer  primary key ,
name varchar2(20));
表products
create table products(
product_id  integer  primary key,
product_type_id integer references product_types (product_type_id)
name varchar2(20),
description varchar2(100),
price number(4,2));
begin
insert into product_types values(1,'CD');
insert into product_types values(2,'DVD');
insert into product_types values(3,'BOOK');
insert into product_types values(4,'VEDIO');
insert into product_types values(5,'MAGAZINE');
insert into products values(1,1,'Modern Science','a description of modern science',19.25);
insert into products values(2,1,'Chemistry','introduction of chemistry',19.89);
insert into products values(3,2,'Tank War','a description of Tank War',23.08);
insert into products values(4,2,'Fibera','a description of Fibera',20.00);
insert into products values(5,3,'Kmazi','a description of Kmazi',31.24);
insert into products values(6,,'Hua','a description of Hua',19.48);
commit;
end;
/

newkid

wzj124 发表于 2011-11-25 13:12
恩，我说的限定词其实就是两个表的别名啦，可能我没说清楚，我用版主给的代码做了一遍，A选项能够得出结果 ...

你没有给出错误的SQL?

还是以那道题为例，如果我要把连接列DEPARTMENT_ID选出来，不能加别名：

SELECT e.last_name, e.salary, d.department_name, E.DEPARTMENT_ID
   FROM plch_employees e NATURAL JOIN plch_departments d
/

ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier

改这样就可以：
SELECT e.last_name, e.salary, d.department_name, DEPARTMENT_ID
   FROM plch_employees e NATURAL JOIN plch_departments d
/

newkid

2011-10-22 答案BEF.
A: 语法错误，无条件插入和条件插入不可以混用。
B: 正确，注意在同一个WHEN条件下可以有多个插入子句。
C: 语法没错，但是ELSE条件只有在其他条件全部不满足时才触发，所以永远执行不到，导致plch_dest_tab_3没有数据。
D: SELECT列表必须包含VALUES里面用到的所有列。
E: 正确，相当于D的修正版。
F: 正确
G: 虽然语法正确但是只插入到一张表。
=========================================
2011-10-29 用户对象的引用

作者：Chris Saxon
难度：中

我有两个schema, HR和SCOTT.

在HR SCHEMA我创建了如下的表:
create table plch_users (usr varchar2(30));

insert into plch_users values ('HR');

create table plch_users_2 (usr varchar2(30));

insert into plch_users_2 values ('HR');

commit;


仅仅是plch_users表上的SELECT权限授予SCOTT:

grant select on plch_users to SCOTT;

然后我用SCOTT连接，并且创建了这两张表的副本：

create table plch_users (usr varchar2(30));

insert into plch_users values ('SCOTT');

create table plch_users_2 (usr varchar2(30));

insert into plch_users_2 values ('SCOTT');

commit;

我仍然是以SCOTT连接，这时候我运行了如下语句：
alter session set current_schema = HR;

哪些语句在执行后会输出：

USR
------------------------------
HR

(A)
select * from plch_users;

(B)
select * from HR.plch_users;

(C)
select * from SCOTT.plch_users;

(D)
select * from plch_users_2;

(E)
select * from HR.plch_users_2;

(F)
select * from SCOTT.plch_users_2;

wzj124

newkid 发表于 2011-11-25 23:41
你没有给出错误的SQL?

还是以那道题为例，如果我要把连接列DEPARTMENT_ID选出来，不能加别名：

我当时是要select出product_types表的name和products表的name和description来组成一个结果的,这三个属性都不是连接列来的，找不出错误。。