Which is true about the and and andand prefixes with substitution variables? (Choose all that apply.)

A. and can prefix a substitution variable name only in queries. DML

B. An and prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .

C. The andand prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

D. An andand prefix to an undefined substitution variable, Which is referenced multiple times in multiple queries, will prompt for a value once per query.

E. Both and and andand can prefix a substitution variable name in queries and DML statements.

You execute this command:

ALTER TABLE employees SET UNUSED (department_id);

Which two are true?

A. A query can display data from the DEPARTMENT_ID column.

B. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

C. The DEPARTMENT_ID column is set to null for all tows in the table

D. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

E. No updates can be made to the data in the DEPARTMENT_ID column.

F. The DEPARTMENT_ID column can be recovered from the recycle bin

Which two statements are true about the ORDER BY clause? (Choose two.)

A. In a character sort, the values are case-sensitive.

B. NULLS are not included in the sort operation.

C. Numeric values are displayed in descending order if they have decimal positions.

D. Column aliases can be used in the ORDER BY clause.

E. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

Which three statements are true about Data Manipulation Language (DML)?

A. UPDATE statements can have different subqueries to specify the values for each updated column.

B. INSERT INTO...SELECT...FROM statements automatically commit.

C. DML statements require a primary key be defined on a table.

D. DELETE statements can remove multiple rows based on multiple conditions.

E. INSERT statements can insert NULLs explicitly into a column.

Which three statements are true about defining relations between tables in a relational database? (Choose three.)

A. Primary key columns allow null values.

B. Every primary or unique key value must refer to a matching foreign key value.

C. Every foreign key value must refer to a matching primary or unique key value.

D. Foreign key columns allow null values.

E. Unique key columns allow null values.

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

A. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.

B. DELETE would remove a sequence from the database.

C. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.

D. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

E. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.

Which CREATE VIEW statement would create the view successfully?

A. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;

B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date) AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;

C. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;

D. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) || "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date WHITH CHECK OPTION;

Which statement is true about Data Manipulation Language (DML)?

A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table.

B. Each DML statement forms a transaction by default.

C. A transaction can consist of one or more DML statements.

D. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

View the exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the `INTERNET' category.

Which query would give you the required output?

A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date) FROM promotions) AND promo_category= `INTERNET';

B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= `INTERNET');

C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = `INTERNET');

D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ANY (SELECT promo_begin_date FROM promotions WHERE promo_category= `INTERNET');

Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.)

A. It can be used with SET operators (UNION, INTERSECT etc.).

B. It cannot be used with the DISTINCT keyword.

C. If the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait.

D. The statement skips rows locked by other transactions.

E. It can be used with joins.