Which is the valid CREATE [TABLE statement?

• A. CREATE TABLE emp*123 (emp_no NUMBER(4));
• B. CREATE TABLE emp9$# (emp_no NUMBER(4));
• C. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);
• D. CREATE TABLE 9emp$# (emp_no NUMBER(4));

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. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE
  time_id >= SYSDATE - 3*365 );
• B. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE
  SYSDATE >= time_id - 3*365 );
• C. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE
  time_id - 3*365 = SYSDATE );
• D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE
  SYSDATE - 3*365 >= time_id);

Examine the structure of the orders table:

You want to find the total value of all the orders for each year and issue the following command:

Which statement is true regarding the outcome?

• A. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
• B. It gives an error because the TO_CHAR function is not valid.
• C. It executes successfully but does not give the correct output.
• D. It executes successfully and gives the correct output.

The following parameter are set for your Oracle 12c database instance:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
You want to manage the SQL plan evolution task manually. Examine the following steps:
1. Set the evolve task parameters.
2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.
3. Implement the recommendations in the task by using the
DBMS_SPM.IMPLEMENT_EVOLVE_TASK function.
4 . Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.
5 . Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function.
Identify the correct sequence of steps:

• A. 1, 2, 3, 4, 5
• B. 2, 1, 4, 3, 5
• C. 2, 4, 5
• D. 1, 2, 4, 5

Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
-------------------------------------------
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

• A. It 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.
• B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
• C. It gives an error because the HAVING clause should be specified after the GROUP BY clause.
• D. It 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.