Prepare for the ocp_ SQL topic "DELETE" and prepare for ocp_sqldelete.

Prepare for the ocp_ SQL topic "DELETE" and prepare for ocp_sqldelete.

Original Works are from the blog of "Deep Blue blog". You are welcome to reprint them. Please note the following source when reprinting them. Otherwise, you will be held legally liable for copyright.

Deep Blue blog:Http://blog.csdn.net/huangyanlong/article/details/43957113

 

Delete Demo:

1. query lab data

2. Delete table data

3. query data under this session

4. Roll Back data

5. View data again after rollback

Library simulation:

 

167. DELETE

167. Evaluate the following DELETE statement:

Delete from sales;

There are no other uncommitted transactions on the SALES table.

Which statement is true about the DELETE statement?

A. It wocould not remove the rows if the table has a primary key.

B. It removes all the rows as well as the structure of the table.

C. It removes all the rows in the table and deleted rows can be rolled back.

D. It removes all the rows in the table and deleted rows cannot be rolled back.

Answer: C. Pay attention to the understanding of DML operations (delete command). uncommitted statements can be rolled back.

 

168. DELETE

168. View the Exhibit and examine the description of SALES and PROMOTIONS tables.

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.

Which DELETE statements are valid? (Choose all that apply .)

A. DELETE

FROM sales

WHERE promo_id = (SELECT promo_id

FROM promotions

WHERE promo_name = 'blowout sale ')

AND promo_id = (SELECT promo_id

FROM promotions

WHERE promo_name = 'everyday low price ');

 

B. DELETE

FROM sales

WHERE promo_id = (SELECT promo_id

FROM promotions

WHERE promo_name = 'blowout sale ')

OR promo_id = (SELECT promo_id

FROM promotions

WHERE promo_name = 'everyday low price ');

 

C. DELETE

FROM sales

WHERE promo_id IN (SELECT promo_id

FROM promotions

WHERE promo_name = 'blowout sale'

OR promo_name = 'everyday low price ');

 

D. DELETE

FROM sales

WHERE promo_id IN (SELECT promo_id

FROM promotions

WHERE promo_name IN ('blowout sale', 'everyday low price '));

Answer: BCD. "either A or B" in the question stem indicates "A or B ". The BCD range can be either of the two. A uses the and representation, which is obviously incorrect.

169. DELETE

169. View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.

Which is the valid DELETE statement?

A. DELETE

FROM products

WHERE prod_id = (SELECT prod_id

FROM sales

WHERE time_id-4*365 = SYSDATE );

 

B. DELETE

FROM products

WHERE prod_id = (SELECT prod_id

FROM sales

Where sysdate> = time_id-3*365 );

 

C. DELETE

FROM products

WHERE prod_id IN (SELECT prod_id

FROM sales

Where sysdate-3*365> = time_id );

 

D. DELETE

FROM products

WHERE prod_id IN (SELECT prod_id

FROM sales

WHERE time_id> = SYSDATE-3*365 );

Answer: C, only C is satisfied. This question is actually an understanding of the time. In the question stem, it is the product three years ago, that is, the system time (sysdate) the time minus 3 years is greater than the product ID time (time_id ). In addition, as the netizen said, this question ignores the inspection of the constraints. Such an operation violates the constraints and cannot complete the deletion operation. We can do the following experiment:

 

-- Create Experiment Table 1, pk_test

Createtable pk_test

(Pk_date varchar2 (32 ),

PK_ID varchar2 (32)

);

-- Create Experiment Table 2, fk_test

Createtable fk_test

(Pk_date varchar2 (32 ),

FK_ID varchar2 (32)

);

-- Create the primary key of Experiment Table 1

-- Create/Recreate primary, unique and foreign key constraints

Altertable PK_TEST

Addconstraint PK_PK_TESTprimarykey (PK_DATE );

-- Create the primary key and foreign key of Experiment Table 2 (the foreign key field is the primary key field of table 1)

-- Create/Recreate primary, unique and foreign key constraints

Altertable FK_TEST

Addconstraint pk_fk_testprimarykey (FK_ID );

Altertable FK_TEST

Addconstraint fk_pk_testforeignkey (PK_DATE)

References pk_test (PK_DATE );

-- Insert experiment data to experiment table 1

Insertinto pk_testvalues ('pk _ 1', '1 ');

Commit;

-- Insert experiment data to experiment table 2

Insertinto fk_testvalues ('pk _ 1', '123 ');

Commit;

-- Query the data in Experiment Table 1

Select * from pk_test;

1 pk_1 1

-- View the data in Experiment Table 2

Select * from fk_test;

1 pk_1 1001

-- Delete data in Experiment Table 1

Deletefrom pk_test;

-- Error: A foreign key exists and the sub-record is found

-- Delete the foreign key constraint in Experiment Table 2

-- Drop primary, unique and foreign key constraints

Altertable FK_TEST

Dropconstraint FK_PK_TEST;

-- The data in Experiment Table 1 is deleted again.

Deletefrom pk_test;

Commit;

-- The data in the query Experiment Table 1 is empty.

Select * from pk_test;

Summary:

This indicates that when a foreign key exists, the parent table cannot be deleted directly. To solve this problem, you can remove the constraints or delete them together with the data in the SALES table.

 

170. DELETE

170. Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two .)

A. DELETE can be used to remove only rows from only one table at a time.

B. DELETE can be used to remove only rows from multiple tables at a time.

C. DELETE can be used only on a table that is a parent of a referential integrity constraint.

D. DELETE can be used to remove data from specific columns as well as complete rows.

E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having on delete rule.

 

Answer: AE

For the parent table with integrity constraints, you must use the on delete cascade or on delete cascad set null Condition Clause when creating the table to delete the parent table with integrity constraints using DELETE or truncate.

For the two parameters on delete cascade or on delete cascad set null, view other information ON the Internet to explain this:

On delete cascade (when the parent table data is deleted, the child table data is also deleted)

On delete cascad set null (when the parent table data is deleted, the column related to the child table is SET to NULL)

You canSQL Language ReferenceInConstraintsPartially find the on delete clause.

 

Word Meaning:

Regarding: About

Referential: indicates, used as a reference

Integrity: complete

Constraint: Constraints

 

Original Works are from the blog of "Deep Blue blog". You are welcome to reprint them. Please note the following source when reprinting them. Otherwise, you will be held legally liable for copyright.

Deep Blue blog:Http://blog.csdn.net/huangyanlong/article/details/43957113