Oracle 1Z0-071 Quiz 18 Topic 16 Questions 86-90

Question: 1

Examine the structure of the DEPARTMENTS table.

q1_1Z0-071

You execute the following command:

q1_1Z0-071

Which two statements are true?

ASynonyms existing of the DEPARTMENTS table would have to be re-created.

BUnique key constraints defined on the COUNTRY column are removed.

CViews created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.

DIndexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.

EA new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

Show Answer

Question: 2

View the exhibit and examine the description of SALES and PROMOTIONS tables.

q2_1Z0-071

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 three DELETE statements are valid? (Choose three.)

ADELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promo_name = 'blowout sale')AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price')FROM promotionsWHERE promo_name = 'everyday low price');

BDELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale')OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price')

CDELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale')OR promo_name = 'everyday low price');

DDELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN = 'blowout sale','everyday low price'));

Show Answer

Question: 3

You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position.

Which query would give the required output?

ASELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, -2, 1)='a';

BSELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>'' ANDSUBSTR(cust_first_name, -2, 1)='a';

CSELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')IS NOT NULL ANDSUBSTR(cust_first_name, 1, -2)='a';

DSELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a';

Show Answer

Question: 4

Examine the data in the ORD_ITEMS table:

q4_1Z0-071

Evaluate this query:

q4_1Z0-071

Which statement is true regarding the result?

AIt returns an error because the HAVING clause should be specified after the GROUP BY clause.

BIt returns an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.

CIt displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

DIt displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

Show Answer

Question: 5

Which two statements are true regarding the DELETE and TRUNCATE commands?

ADELETE can be used to remove rows from only one table in one statement.

BDELETE can be used to remove rows from multiple tables in one statement.

CDELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.

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

EDELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.

Show Answer

Oracle 1Z0-071 Quiz:18 Topic:16 Questions:86-90