[每日一题] OCP1z0-047 :2013-08-28 DELETE

[每日一题] OCP1z0-047 :2013-08-28 DELETE

[每日一题] OCP1z0-047 :2013-08-28 DELETE

[每日一题] OCP1z0-047 :2013-08-28 DELETE

正确答案:ACD

根据题库,操作如下:

 

A答案能删除:

[html]

[email protected]> delete from order_items

2 WHERE order_id IN (SELECT order_id FROM orders

3 WHERE order_status IN(0,1));

110 rows deleted.

[email protected]> rollback;

Rollback complete.

B答案不能删除:没有这样的语法(DELETE * FROM,即DELETE后直接跟表名,而不能用*)

[html]

[email protected]> DELETE *

2 FROM order_items

3 WHERE order_id IN(SELECT order_id

4 FROM orders

5 WHERE orders

6 WHERE order_status IN(0,1));

DELETE *

*

ERROR at line 1:

ORA-00903: invalid table name

C答案能删除:

[html]

[email protected]> delete from order_items i

2 WHERE order_id=(SELECT order_id FROM orders o

3 WHERE i.order_id=o.order_id AND

4 order_status IN(0,1));

110 rows deleted.

[email protected]> rollback;

Rollback complete.

D答案也能删除:

[html]

[email protected]> delete from(select * from order_items i,orders o

2 WHERE i.order_id=o.order_id AND order_status in(0,1));

110 rows deleted.

 分析D能删除的原因:

 1、ORDERS表有主键ORDER_ID:CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")。

 看下面红色字体:

 

[html]

[email protected]> select dbms_metadata.get_ddl('TABLE','ORDERS') from dual;

DBMS_METADATA.GET_DDL('TABLE','ORDERS')

--------------------------------------------------------------------------------

CREATE TABLE "OE"."ORDERS"

( "ORDER_ID" NUMBER(12,0),

"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,

"ORDER_MODE" VARCHAR2(8),

"CUSTOMER_ID" NUMBER(6,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,

"ORDER_STATUS" NUMBER(2,0),

"ORDER_TOTAL" NUMBER(8,2),

"SALES_REP_ID" NUMBER(6,0),

"PROMOTION_ID" NUMBER(6,0),

CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) ENABLE,

CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,

<span style="color:#ff0000;"> CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")</span>

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "EXAMPLE" ENABLE,

CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID")

REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL ENABLE,

CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")

REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "EXAMPLE"

2、ORDER_TIEMS表有外键ORDER_ID:CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID")

REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE,并加上了主外键可以级联删除。

看下面红色字体:

[html]

[email protected]> select dbms_metadata.get_ddl('TABLE','ORDER_ITEMS') from dual;

DBMS_METADATA.GET_DDL('TABLE','ORDER_ITEMS')

--------------------------------------------------------------------------------

CREATE TABLE "OE"."ORDER_ITEMS"

( "ORDER_ID" NUMBER(12,0),

"LINE_ITEM_ID" NUMBER(3,0) NOT NULL ENABLE,

"PRODUCT_ID" NUMBER(6,0) NOT NULL ENABLE,

"UNIT_PRICE" NUMBER(8,2),

"QUANTITY" NUMBER(8,0),

CONSTRAINT "ORDER_ITEMS_PK" PRIMARY KEY ("ORDER_ID", "LINE_ITEM_ID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "EXAMPLE" ENABLE,

<span style="color:#ff0000;">CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID")

REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE,</span>

CONSTRAINT "ORDER_ITEMS_PRODUCT_ID_FK" FOREIGN KEY ("PRODUCT_ID")

REFERENCES "OE"."PRODUCT_INFORMATION" ("PRODUCT_ID") ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "EXAMPLE"

[html]

删除了子表上的记录:

  

[html]

[email protected]> select count(*) from orders;    --主表

COUNT(*)

----------

105

[email protected]> select count(*) from order_items;  --子表

COUNT(*)

----------

665

[email protected]> delete from(select * from order_items i,orders o

2 WHERE i.order_id=o.order_id AND order_status in(0,1));

110 rows deleted.

[email protected]> select count(*) from orders;   --记录没变

COUNT(*)

----------

105

[email protected]> select count(*) from order_items;  --记录少了110条

COUNT(*)

----------

555

有些人D答案百思不得期解,模拟这个操作时一直报错(ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table)原因就在于自己去建了两个表order和order_items时没有在这两个表上建约束主外键,并且加上主外键可以级联删除(CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE)。

这个我就不再测试了,太简单了。

分类:默认分类 时间:2012-01-02 人气:5
本文关键词:
分享到:

相关文章

  • [每日一题] OCP1z0-047 :2013-07-22 group by子句 2012-02-20

    [每日一题] OCP1z0-047 :2013-07-22 group by子句 这道题就是考where group by having的顺序。。。 答案A不正确:where应该放在group by前面 答案B不正确:having子句是用多行函数(sum,avg,max,min,count)等做为条件 答案C不正确:where应该放在group by前面 参考如下:(其实having 也可以放在group by前面) SELECT column, group_function FROM tabl

  • [每日一题] OCP1z0-047 :2013-07-27外部表??不能被DML和建索引 2012-07-25

    [每日一题] OCP1z0-047 :2013-07-27外部表??不能被DML和建索引 首先看官方文档上的解释: Managing External Tables Oracle Database allows you read-only access to data in external tables. External tables are definedas tables that do not reside in the database, and can be in any form

  • [每日一题]OCP1z0-047:2013-08-16 merge的用法 2013-08-10

    [每日一题]OCP1z0-047:2013-08-16 merge的用法 正确答案:A(作为条件判断字段是不能被更新的) B:misplaced(错位),INSERT写法是对的,并没有错位。 C:WHERE子句可以放在INSERT后面做为条件。 D:视图可以做为数据源(真正的数据来自于视图所对应的表)。 MERGE语句具有按条件获取要更新或插入到表中的数据行,然后从1个或多个源头对表进行更新或者向表中插入行两方面的能力。它最经常被用在数据仓库中来移动大量的数据,但它的应用不仅限于数据仓库环境下。

  • [每日一题] OCP1z0-047 :2013-08-13 Flashback Version Query 2014-08-23

    [每日一题] OCP1z0-047 :2013-08-13 Flashback Version Query 正确答案:C 使用Flashback Version Query查询记录修改版本,查询表在不同时间点的不同版本的数据,闪回版本查询只能对提交后的数据进行查询,根据题意如下操作: [html] [email protected]> create table digits(id number(2), description varchar2(15)); Table created. [email protected]

  • [每日一题] OCP1z0-047 :2013-07-28多表插入??pivoting insert(旋转插入) 2012-01-10

    [每日一题] OCP1z0-047 :2013-07-28多表插入??pivoting insert(旋转插入) 这道题目的知识点是要了解Oracle 中的Insert用法 A、pivoting insert(旋转插入) 1、创建表marks_details [html] [email protected]> create table marks_details ( 2 student_id number(4) not null, 3 subject_id1 number(2), 4 marks_englis

  • [每日一题]OCP1z0-047 :2013-08-17 EXTERNAL TABLE??加载数据 2012-01-17

    [每日一题]OCP1z0-047 :2013-08-17 EXTERNAL TABLE??加载数据 正确答案:C 一、对答案解释: A、 TYPE:有两个选可供选择: 1、 ORACLE_LOADER:传统方式,与SQLLDR一样,参数从多,应用较多。 2、 ORACLE_DATADUMP:数据泵(Datapump),这是10gR2中新增的数据访问方式,这种方式的数据源是专用二进制文件,这种二进制文件就目前而言只能用外部表生成,也中能用外部表加载。如果你对Oracle内部格式理解的深入,并有高超

  • [每日一题] OCP1z0-047 :2013-07-16主键与唯一索引 2012-01-30

    [每日一题] OCP1z0-047 :2013-07-16主键与唯一索引 主键包括非空和唯一约束,它会自动创建唯一索引(注:唯一约束也会自动创建唯一索引),测试如下: 1、 创建一个表products [email protected]> Create table products( 2 product_id number(6) constraintprod_id_pk PRIMARY KEY, 3 product_name varchar2(15) 4 ); Table created. 2、 查表prod

  • [每日一题] OCP1z0-047 :2013-08-29 NULL 2012-02-09

    [每日一题] OCP1z0-047 :2013-08-29 NULL 正确答案:B 用函数可以针对各种数据类型时行操作,包括NULL值在内。其中有一类通用函数,是专门针对NULL值的。 1、针对NULL值的通用函数 (1)NVL(表达式1,表达式2): 转换空值为一个实际值。如果表达式1值为空,NVL将返回表达式2的值作为结果。如果表达式1 不为空,以表达式1的值作为结果。 这个函数在有些时候非常有用,在很多应用程序中,都要把空当作0处理,这利用NVL是容易实现的: [html] [email protected]

  • [每日一题] OCP1z0-047 :2013-08-08相关子查询中EXISTS的使用 2012-02-27

    [每日一题] OCP1z0-047 :2013-08-08相关子查询中EXISTS的使用 正确答案是:AC EXISTS谓词非常简单,它是对一个非空集的测试。如果在其子查询中存在任何行,则返回TRUE,否则为FALSE。该谓词不会返回UNKNOWN结果。EXIST()谓词语法如下: <EXISTS谓词>::=[NOTEXISTS]<表子查询> 对exists的测试,看执行计划: [html] [email protected]> create table t3(id number,n

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.063 (s). 10 q(s)