Q1. - (Topic 2) 

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

A. They can be created on tables and clusters. 

B. They can be created on tables and simple views. 

C. You can create only one index by using the same columns. 

D. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns. 

View Answer

Q2. - (Topic 2) 

View the Exhibit and examine the data in the PROJ_TASK_DETAILS table. 

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them. 

The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks. 

You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on. 

Which query would give the required result? 

A. 

SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id); 

B. 

SELECT p.task_id, p.based_on, d.task_in_charge 

FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = 

d.task_id); 

C. 

SELECT p.task_id, p.based_on, d.task_in_charge 

FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = 

d.task_id); 

D. 

SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id); 

View Answer

Q3. - (Topic 2) 

View the Exhibits and examine PRODUCTS and SALES tables. 

You issue the following query to display product name and the number of times the product has been sold: 

SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p 

ON i.prod_id = p.prod_id; 

What happens when the above statement is executed? 

A. The statement executes successfully and produces the required output. 

B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query. 

C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together. 

D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause. 

View Answer

Q4. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. 

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. 

You issue the following query: 

Which statement is true regarding the above query? 

A. It executes successfully and gives the required result. 

B. It executes successfully but does not give the required result. 

C. It produces an error because the join order of the tables is incorrect. 

D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement. 

View Answer

Q5. - (Topic 1) 

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

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times. 

Which statement is true regarding this SQL statement? 

A. It executes successfully and generates the required result. 

B. It produces an error because COUNT(*) should be specified in the SELECT clause also. 

C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause. 

D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*). 

View Answer

Q6. - (Topic 1) 

Which one is a system privilege? 

A. SELECT 

B. DELETE 

C. EXECUTE 

D. ALTER TABLE 

E. CREATE TABLE 

View Answer

Q7. - (Topic 2) 

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

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold. 

Evaluate the following query: 

SQL>SELECT p.prod_id, COUNT(s.prod_id) 

FROM products p _____________ sales s 

ON p.prod_id = s.prod_id 

GROUP BY p.prod_id; 

Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.) 

A. JOIN 

B. FULL OUTER JOIN 

C. LEFT OUTER JOIN 

D. RIGHT OUTER JOIN 

View Answer

Q8. - (Topic 1) 

What is true regarding sub queries? 

A. The inner query always sorts the results of the outer query 

B. The outer query always sorts the results of the inner query 

C. The outer query must return a value to the outer query 

D. The inner query returns a value to the outer query 

E. The inner query must always return a value or the outer query will give an error 

View Answer

Q9. - (Topic 2) 

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME 

Lex De Haan Renske Ladwig Jose Manuel Urman 

Jason Mallin 

You want to extract only those customer names that have three names and display the * symbol in place of the 

first name as follows: 

CUST NAME 

*** De Haan 

**** Manuel Urman 

Which two queries give the required output? (Choose two.) 

A. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0; 

B. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',-1,2)<>0; 

C. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',-1,-2)<>0; 

D. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0 ; 

View Answer

Q10. - (Topic 2) 

What does the FORCE option for creating a view do? 

A. creates a view with constraints 

B. creates a view even if the underlying parent table has constraints 

C. creates a view in another schema even if you don't have privileges 

D. creates a view regardless of whether or not the base tables exist 

View Answer