Question 1.
The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)

You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?

A. SELECT employee_id, last_name, job_id
    FROM employees 
    WHERE job_id = '%SA_';
B. SELECT employee_id, last_name, job_id
    FROM employees
    WHERE job_id LIKE '%SA_';
C. SELECT employee_id, last_name, job_id
    FROM employees
    WHERE job_id LIKE '%SA_' ESCAPE "\";
D. SELECT employee_id, last_name, job_id
    FROM employees
    WHERE job_id LIKE '%SA\_%' ESCAPE '\';

Answer: D

Explanation:
ESCAPE identifier to search for the actual % and _ symbol

Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 2-13

Question 2.
You own a table called EMPLOYEES with this table structure: 
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE

What happens when you execute this DELETE statement?
DELETE employees;

A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. You get an error because the statement is not syntactically correct.
D. The data in the EMPLOYEES table is deleted but not the structure.

Answer: D

Explanation:
You can remove existing rows from a table by using the DELETE statement.
DELETE [FROM] table
[WHERE condition];

Question 3.
You need to create a table named ORDERS that contains four columns:
1. an ORDER_ID column of number data type
2. a CUSTOMER_ID column of number data type
3. an ORDER_STATUS column that contains a character data type
4. a DATE_ORDERED column to contain the date the order was placed
When a row is inserted into the table, if no value is provided for the status of the order, the value PENDING should be used instead.

Which statement accomplishes this?

A. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered VARCHAR2 );
B. CREATE TABLE orders (
   order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
C. CREATE OR REPLACE TABLE orders (
   order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
D. CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status NUMBER(10) DEFAULT 'PENDING',
date_ordered DATE );
E. CREATE OR REPLACE TABLE orders (
   order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
F. CREATE TABLE orders (
   order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );

Answer: B

Explanation:
Requirement that Order_Status should be a character data type

Note: Order_status must be a character data type. There is also a syntax error.

Question 4.
Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

In the statement, which capabilities of a SELECT statement are performed?

A. selection, projection, join
B. selection, intersection, join
C. intersection, projection, join
D. difference, projection, product
E. difference, projection, join

Answer: A

Explanation:
Selection, projection and join capabilities of a SELECT statement are performed in this view.

Question 5.
Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)

A. INTERVAL YEAR TO MONTH
B. INTERVAL DAY TO SECOND
C. TIMESTAMP
D. INTERVAL MONTH TO DAY
E. TIMESTAMP WITH DATABASE TIMEZONE

Answer: A, B, C

Explanation:
TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be used to specify column definition.

Question 6.
Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?

A. SELECT ename, salary*12 'Annual Salary'
    FROM employees;
B. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY")
    FROM employees
C. SELECT ename, salary*12 "Annual Salary"
    FROM employees;
D. SELECT ename, salary*12 AS Annual Salary
    FROM employees;

Answer: C

Explanation:
This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated column SALARY*12.

Question 7.
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)

Which three statements insert a row into the table? (Choose three.)

A. INSERT INTO employees( first_name, last_name)
    VALUES( 'John', 'Smith');
B. INSERT INTO employees
    VALUES ( NULL, 'John', 'Smith');
C. INSERT INTO employees (employee_id, first_name, last_name)
    VALUES ( 1000, 'John', ' ');
D. INSERT INTO employees (employee_id)
    VALUES (1000);
E. INSERT INTO employees (first_name, last_name, employee_id)
    VALUES ( 1000, 'John', 'Smith');
F. INSERT INTO employees
    VALUES ( '1000', 'John', NULL);

Answer: C, D, F

Explanation:
EMPLOYEE_ID is a primary key.

Incorrect answer :
A EMPLOYEE_ID cannot be null
B EMPLOYEE_ID cannot be null

Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-11

Question 8.
Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

A. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
    FROM dual;
B. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
    FROM dual;
C. SELECT TO_DATE(SYSDATE,'yyyy')
    FROM dual;
D. SELECT TO_CHAR(SYSDATE,'yyyy')
    FROM dual;
E. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY')
    FROM dual;

Answer: D

Explanation:
Function TO_CHAR(x, y) converts the value x to a character or converts a date to a character string using formatting conventions.

Question 9.
Which two statements about sequences are true? (Choose two.)

A. You use a CURRVAL pseudo column to generate a value from a sequence that would be used  for a specified database column.
B. You use a CURRVAL pseudo column to look at the current value just generated from a  sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
D. You use a NEXTVAL pseudo column to look at the next possible value that would be  Generated from a sequence, without actually retrieving the value.
E. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
F. If a sequence starting from a value 100 and incremented by 1 is used by more than one  application, then all of these applications could have a value of 105 assigned to their column  whose value is being generated by the sequence.

Answer: B, C

Explanation:
You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

Incorrect Answers:
A: You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
D: You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
E: This statement is not correct. There is no limitation like that in Oracle.
F: You use CYCLE clause, not REUSE, when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 315-322Chapter 7: Creating Other Database Objects in Oracle

Question 10.
In which two cases would you use an outer join? (Choose two.)

A. Only when the tables have a primary key-foreign key relationship.
B. The tables being joined have NOT NULL columns.
C. The tables being joined have both matched and unmatched data.
D. The columns being joined have NULL values.
E. The tables being joined have only matched data.
F. The tables being joined have only unmatched data.

Answer: C, D

Explanation:
You use an outer join to also see rows that do not meet the join condition.

Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 4-17

	Web www.certsbraindumps.com