最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：Chris Saxon
运行环境：SQLPLUS, SERVEROUTPUT已打开, 最低版本要求：11.2
注：本题给出答案时候要求给予简要说明才能得到奖品
你执行了这些语句：
create table qz_bricks (
brick_id integer
not null primary key,
colour varchar2(10)
not null,
shapevarchar2(10)
not null,
weight integer
not null
);
insert into qz_bricks
values ( 1, 'blue', 'cylinder', 1 );
insert into qz_bricks
values ( 2, 'blue', 'cylinder', 1 );
insert into qz_bricks
values ( 3, 'green', 'cube', 2 );
insert into qz_bricks
values ( 4, 'green', 'cylinder', 3 );
insert into qz_bricks
values ( 5, 'red', 'cube', 5 );
insert into qz_bricks
values ( 6, 'red', 'cube', 8 );
insert into qz_bricks
values ( 7, 'red', 'cylinder', 13 );
commit;
哪些查询会找到这些数据：
颜色是红色
每种形状的总重量
该种形状的总重量必须大于10?
正确的选项应该返回这些行：
SHAPETOTAL_WEIGHT
---------- ------------
cube
13
cylinder
13
(A)
select shape, sum ( weight ) total_weight
from qz_bricks
wherecolour = 'red'
andsum ( weight ) > 10
groupby shape
orderby shape;
(B)
select shape, sum ( weight ) total_weight
from qz_bricks
wherecolour = 'red'
groupby shape
having sum ( weight ) > 10
orderby shape ;
(C)
select shape, sum ( weight ) total_weight
from qz_bricks
groupby shape
having sum ( weight ) > 10
andcolour = 'red'
orderby shape ;
(D)
select shape, sum ( weight ) total_weight
from qz_bricks
groupby shape
having sum ( weight ) > 10
andmax ( colour ) = 'red'
orderby shape ;
(E)
select shape, total_weight
from (
select shape, sum ( weight ) total_weight
from qz_bricks
wherecolour = 'red'
groupby shape
)
wheretotal_weight > 10
orderby shape ;
|
