Q1. - (Topic 2) 

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. SELECT cust_last_name Name, cust_credit_limit + 1000 

"New Credit Limit" 

FROM customers; 

B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 

AS New Credit Limit 

FROM customers; 

C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 

AS "New Credit Limit" 

FROM customers; 

D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW 

CREDIT LIMIT") 

FROM customers; 

View Answer

Q2. - (Topic 2) 

Top N analysis requires _____ and _____. (Choose two.) 

A. the use of rowid 

B. a GROUP BY clause 

C. an ORDER BY clause 

D. only an inline view 

E. an inline view and an outer query 

View Answer

Q3. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit: 

You want to create a SALE_PROD view by executing the following SQL statements: 

Which statement is true regarding the execution of the above statement? 

A. The view will be created and you can perform DLM operations on the view 

B. The view will not be created because the join statements are not allowed for creating a view 

C. The view will not be created because the GROUP BY clause is not allowed for creating a view 

D. The view will be created but no DML operations will be allowed on the view 

View Answer

Q4. - (Topic 1) 

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. 

The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO 

columns are composite PRIMARY KEY in the ORD_ITEMS table. 

Which two CREATE INDEX statements are valid? (Choose two.) 

A. CREATE INDEX ord_idx1 

ON ord(ord_no); 

B. CREATE INDEX ord_idx2 

ON ord_items(ord_no); 

C. CREATE INDEX ord_idx3 

ON ord_items(item_no); 

D. CREATE INDEX ord_idx4 

ON ord,ord_items(ord_no, ord_date,qty); 

View Answer

Q5. - (Topic 2) 

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

Evaluate the following SQL statement: 

SQL>SELECT promo_name,CASE 

WHEN promo_cost >=(SELECT AVG(promo_cost) 

FROM promotions 

WHERE promo_category='TV') 

then 'HIGH' 

else 'LOW' 

END COST_REMARK 

FROM promotions; 

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

Q6. - (Topic 1) 

Which three are true? (Choose three.) 

A. A MERGE statement is used to merge the data of one table with data from another. 

B. A MERGE statement replaces the data of one table with that of another. 

C. A MERGE statement can be used to insert new rows into a table. 

D. A MERGE statement can be used to update existing rows in a table. 

View Answer

Q7. - (Topic 1) 

Evaluate the following two queries: Exhibit: 

Exhibit: 

Which statement is true regarding the above two queries? 

A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column 

B. Performance would degrade in query 2 

C. There would be no change in performance 

D. Performance would improve in query 2 

View Answer

Q8. - (Topic 2) 

Which two statements are true regarding constraints? (Choose two.) 

A. A foreign key cannot contain NULL values. 

B. The column with a UNIQUE constraint can store NULLS . 

C. A constraint is enforced only for an INSERT operation on a table. 

D. You can have more than one column in a table as part of a primary key. 

View Answer

Q9. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER\ SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of 

theDEPARTMENTS table 

You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table. 

Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.) 

A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column. 

B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column. 

C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table. 

D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence. 

E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table. 

F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column. 

View Answer

Q10. - (Topic 1) 

Which one is a system privilege? 

A. SELECT 

B. DELETE 

C. EXECUTE 

D. ALTER TABLE 

E. CREATE TABLE 

View Answer