Q1. - (Topic 1) 

Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.) 

A. Combining more than two columns or expressions into a single column in the output 

B. Displaying a date in a nondefault format 

C. Substituting a character string in a text expression with a specified string 

D. Finding the number of characters in an expression 

View Answer

Q2. - (Topic 1) 

The COMMISSION column shows the monthly commission earned by the employee. Exhibit 

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.) 

A. listing the employees who earn the same amount of commission as employee 3 

B. finding the total commission earned by the employees in department 10 

C. finding the number of employees who earn a commission that is higher than the average commission of the company 

D. listing the departments whose average commission is more that 600 

E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID 

F. listing the employees whose annual commission is more than 6000 

View Answer

Q3. - (Topic 2) 

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value? 

A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL; 

B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL; 

C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL; 

D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL; 

E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL; 

F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL; 

View Answer

Q4. - (Topic 1) 

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

A. A sub query that defines a view cannot include the GROUP BY clause 

B. A view is created with the sub query having the DISTINCT keyword can be updated 

C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table 

D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated 

View Answer

Q5. - (Topic 2) 

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. 

Which statement accomplishes this task? 

A. ALTER TABLE students ADD PRIMARY KEY student_id; 

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id); 

C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id; 

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id); 

View Answer

Q6. - (Topic 2) 

Examine the following SQL commands: 

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

A. Both commands execute successfully. 

B. The first CREATE TABLE command generates an error because the NULL constraint is not valid. 

C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid. 

D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column. 

E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints. 

View Answer

Q7. - (Topic 2) 

Examine the structure of the PROMOS table: 

You want to generate a report showing promo names and their duration (number of days). 

If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.) 

A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos; 

B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos; 

C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos; 

D. SELECT promo_name, DECODE(promo_end_date 

-promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM 

promos; 

E. SELECT 

promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING', 

promo_end_date - promo_start_date) 

FROM promos; 

View Answer

Q8. - (Topic 1) 

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

Q9. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS tables. 

You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. 

Which query would give the correct output? 

A. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

B. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

C. 

SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

D. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products); 

View Answer

Q10. - (Topic 2) 

Examine the structure of the PRODUCTS table: 

You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND. 

Which SQL statement gives the required output? 

A. 

SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products); 

B. 

SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name); 

C. 

SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name); 

D. 

SELECT prod_name 

FROM products 

WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) 

FROM products) 

GROUP BY prod_name; 

View Answer