Which two statements are true about substitution variables? (Choose two.)

• A.A substitution variable can be used with any clause in a SELECT statement
• B.A substitution variable used to prompt for a column name must be enclosed in a single quotation marks
• C.A substitution variable prefixed with & always prompts only once for a value in a session
• D.A substitution variable can be used only in a SELECT statement
• E.A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session
• F.A substitution variable used to prompt for a column name must be enclosed in double quotation marks

Comment: *

Name: *

Email: *

Verification: *

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?

• A.The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• B.The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
• C.The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
• D.The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

Comment: *

Name: *

Email: *

Verification: *

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A.The outer query stops evaluating the result set of the inner query when the first value is found.
• B.It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• C.The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
• D.It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

Comment: *

Name: *

Email: *

Verification: *

View the Exhibit and examine the description of the EMPLOYEEStable.

You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?
SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,

• A.salary,salary+commission_pct))"Total"
  FROM EMPLOYEES;
  SELECT first_name, salary, salary*12+salary*commission_pct "Total"
• B."Total"
  FROM EMPLOYEES;
  SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, "Total"
• C.FROM EMPLOYEES;
• D.FROM EMPLOYEES;
  SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12

Comment: *

Name: *

Email: *

Verification: *

Examine the structure of the MEMBERS table:

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution? (Choose the best answer.)

• A.It displays all cities in descending order, within which the last names are further sorted in descending order.
• B.It fails because a column alias cannot be used in the ORDER BY clause.
• C.It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
• D.It displays all cities in ascending order, within which the last names are further sorted in descending order.

Comment: *

Name: *

Email: *

Verification: *