Q1. - (Topic 1) 

You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result? 

A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Month. Year') FROM DUAL: 

B. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL; 

C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Month. Year') FROM DUAL; 

D. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Month. Year')) FROM DUAL: 

View Answer

Q2. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

Which UPDATE statement is valid? 

A. 

UPDATE employees 

SET first_name = ‘John’ 

SET last_name = ‘Smith’ 

WHERE employee_id = 180; 

B. 

UPDATE employees 

SET first_name = ‘John’, 

SET last_name = ‘Smoth’ 

WHERE employee_id = 180; 

C. 

UPDATE employee 

SET first_name = ‘John’ 

AND last_name = ‘Smith’ 

WHERE employee_id = 180; 

D. 

UPDATE employee 

SET first_name = ‘John’, last_name = ‘Smith’ 

WHERE employee_id = 180; 

View Answer

Q3. - (Topic 1) 

Evaluate these two SQL statements: 

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; 

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; 

What is true about them? 

A. The two statements produce identical results. 

B. The second statement returns a syntax error. 

C. There is no need to specify DESC because the results are sorted in descending order by default. 

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement. 

View Answer

Q4. - (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

Q5. - (Topic 1) 

The PART_CODE column in the SPARES table contains the following list of values: 

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

A. It produces an error. 

B. It displays all values. 

C. It displays only the values A%_WQ123 and AB_WQ123 . 

D. It displays only the values A%_WQ123 and A%BWQ123 . 

E. It displays only the values A%BWQ123 and AB_WQ123. 

View Answer

Q6. - (Topic 1) 

Which statements are correct regarding indexes? (Choose all that apply.) 

A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated. 

B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. 

C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key 

D. When a table is dropped, the corresponding indexes are automatically dropped 

View Answer

Q7. - (Topic 1) 

The PRODUCTS table has the following structure: 

Evaluate the following two SQL statements: 

Which statement is true regarding the outcome? 

A. Both the statements execute and give the same result 

B. Both the statements execute and give different results 

C. Only the second SQL statement executes successfully 

D. Only the first SQL statement executes successfully 

View Answer

Q8. - (Topic 1) 

Examine the structure and data of the CUSTJTRANS table: 

CUSTJRANS 

Name Null? Type 

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 

11 01-JAN-07 1000 

22 01-FEB-07 2000 

33 01-MAR-07 3000 

Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.) 

A. SELECT transdate + '10' FROM custjrans; 

B. SELECT * FROM custjrans WHERE transdate = '01-01-07': 

C. SELECT transamt FROM custjrans WHERE custno > '11': 

D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': 

E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: 

View Answer

Q9. - (Topic 1) 

Which object privileges can be granted on a view? 

A. none 

B. DELETE, INSERT,SELECT 

C. ALTER, DELETE, INSERT, SELECT 

D. DELETE, INSERT, SELECT, UPDATE 

View Answer

Q10. - (Topic 1) 

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.) 

A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL: 

B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL: 

C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL: 

D. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL: 

E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL: 

View Answer