Q1. In which three situations does a transaction complete? 

A. When a DELETE statement is executed 

B. When a ROLLBACK command is executed 

C. When a PL/SQL anonymous block is executed 

D. When a data definition language (DDL) statement is executed 

E. When a TRUNCATE statement is executed after the pending transaction 

View Answer

Q2. View the Exhibit and examine the data in the employees table: 

You want to display all the employee names and their corresponding manager names. Evaluate the following query: 

Which join option can be used in the blank in the above query to get the required output? 

A. INNER JOIN 

B. FULL OUTER JOIN 

C. LEFT OUTER JOIN 

D. RIGHT OUTER JOIN 

View Answer

Q3. Evaluate the following SQL commands: 

The command to create a table fails. Identify the two reasons for the SQL statement failure? 

A. You cannot use SYSDATE in the condition of a check constraint. 

B. You cannot use the BETWEEN clause in the condition of a check constraint. 

C. You cannot use the NEXTVAL sequence value as a default value for a column. 

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key. 

View Answer

Q4. 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 executes successfully and gives the correct output. 

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 gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause. 

View Answer

Q5. You issue the following command to alter the country column in the departments table: Which statement is true? 

A. It produces an error because column definitions cannot be altered to add default values. 

B. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'. 

C. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table. 

D. It produces an error because the data type for the column is not specified. 

View Answer

Q6. View the Exhibit and examine the structure of the promotions table. 

Evaluate the following SQL statement: 

Which statement is true regarding the outcome of the above query? 

A. It shows COST_REMARK for all the promos in the table. 

B. It produces an error because the SUBQUERY gives an error. 

C. It shows COST_REMARK for all the promos in the promo category 'TV' 

D. It produces an error because SUBQUERIES cannot be used with the case expression. 

View Answer

Q7. Evaluate the following query: 

SQL> SELECT TRUNC(ROUND(156.00, -1), -1) 

FROM DUAL; 

What would be the outcome? 

A. 16 

B. 100 

C. 160 

D. 200 

E. 150 

View Answer

Q8. Examine the data in the ORD_ITEMS table: 

Evaluate the following query: 

Which statement is true regarding the outcome of the above query? 

A. It gives an error because the having clause should be specified after the group by clause. 

B. It gives an error because all the aggregate functions used in the having clause must be specified in the select list. 

C. 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. 

D. 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. 

View Answer

Q9. You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New credit Limit. The column headings should have only the first letter of each word in uppercase. 

Which statement would accomplish this requirement? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q10. Which statement is true regarding the default behavior of the order by clause? 

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

B. NULL values are not considered at all by the sort operation. 

C. Only those columns that are specified in the select list can be used in the order by clause. 

D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions. 

View Answer