Oracle 1z0-071 2019.10最新题库&解答41-50

QUESTION 41

choose three

The ORDERS table has a primary key constraint on the ORDER_ID column.

The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.

The constraint is defined with on DELETE CASCADE.

There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.

Which three DELETE statements execute successfully?

A) DELETE FROM orders WHERE order_total<1000;

B) DELETE * FROM orders WHERE order_total<1000;

C) DELETE orders WHERE order_total<1000;   不加FROM 也可以

D) DELETE FROM orders;

E) DELETE order_id FROM orders WHERE order_total<1000;

Correct Answer: ACD

考察delete 语法

 

QUESTION 42

Choose two

Examine this SQL statement:

DELETE FROM employees e

WHERE EXISTS

(SELECT 'dummy'  FROM emp_history WHERE employee_id = e.employee_id)

Which two are true?

A) The subquery is executed for every row in the EMPLOYEES table.

B) The subquery is not a correlated subquery.

C) The subquery is executed before the DELETE statement is executed.   ->先招待主查询

D) All existing rows in the EMPLOYEEE table are deleted.

E) The DELETE statement executes successfully even if the subquery selects multiple rows.

Correct Answer: AE

解析：相关子查询，只会将父表中匹配条件的行记录删除，保留其它行。用exist，子表中反馈多行，也可以操作成功，只要反馈行，说明子查询结果为真，与反馈几行没有关系。

 

QUESTION 43

Choose two.

You execute this command:

TRUNCATE TABLE depts;

Which two are true?

A) It drops any triggers defined on the table.

B) It retains the indexes defined on the table.

C) It retains the integrity constraints defined on the table.

D) A ROLLBACR statement can be used to retrieve the deleted data.

E) It always retains the space used by the removed rows.

F) A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Correct Answer: BC

解析：truncate 是ddl 语句，不能用闪回技术查看，会报错表结构已改变

QUESTION 44

choose two

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully?

A) SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;

B) SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10;

C) SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;

D) SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;

E) SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;

Correct Answer: AB

考察where 和having、聚合函数出现的位置

 

QUESTION 45

choose the best answer

You have been asked to create a table for a banking application.

One of the columns must meet three requirements:

1) Be stored in a format supporting date arithmetic without using conversion functions

2) Store a loan period of up to 10 years

3) Be used for calculating interest for the number of days the loan remains unpaid

Which data type should you use?

A) TIMESTAMP WITH TIMEZONE

B) TIMESTAMP

C) TIMESTAMP WITH LOCAL TIMEZONE

D) INTERVAL YEAR TO MONTH

E) INTERVAL DAY TO SECOND

Correct Answer: E

解析：官档SQL Language reference--->Basic Elements of Oracle SQL