Q1. - (Topic 2)
Which two statements are true regarding savepoints? (Choose two.)
A. Savepoints are effective only for COMMIT.
B. Savepoints may be used to ROLLBACK.
C. Savepoints can be used for only DML statements.
D. Savepoints are effective for both COMMIT and ROLLBACK.
E. Savepoints can be used for both DML and DDL statements.
Answer: B,C
Q2. - (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
Answer: B
Explanation:
Using the NVL2 Function The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null
Q3. - (Topic 2)
You are granted the CREATE VIEW privilege. What does this allow you to do?
A. Create a table view.
B. Create a view in any schema.
C. Create a view in your schema.
D. Create a sequence view in any schema.
E. Create a view that is accessible by everyone.
F. Create a view only of it is based on tables that you created.
Answer: C
Explanation:
You can create a view in your own schema only if you are granted the CREATE VIEW
privilege.
Incorrect Answers
A:You can create a view in your own schema only.
B:You can create a view in your own schema only, not in any schema.
D:There is no sequence view in Oracle.
E:You cannot create a view that is accessible by everyone. You will need specially grant
SELECT privileges on this view for everyone.
F:You can create a view in your own schema, but not only for tables in your schema. You
can use object from other users schemas if you have privileges to retrieve data from them.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301
Chapter 7: Creating Other Database Objects in Oracle
Q4. - (Topic 2)
You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:
CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150)
CUST_PHONEVARCHAR2(20)
Which SELECT statement accomplishes this task?
A. SELECT * FROM customers
B. SELECT name, address FROM customers;
C. SELECT id, name, address, phone FROM customers;
D. SELECT cust_name, cust_address FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
Answer: D
Explanation:
This answer provides correct list of columns for the output.
Incorrect Answers
A:This answer does not provide correct list of columns for the output. It is not required to
show all columns of the table. Symbol “*” is used in the SELECT command to substitute a
list of all columns of the table.
B:This answer does not provide correct list of columns for the output. There are not NAME
and ADDRESS columns in the CUSTOMERS table.
C:This answer does not provide correct list of columns for the output. There are not ID,
NAME, ADDRESS or PHONE columns in the CUSTOMERS table.
E:This answer does not provide correct list of columns for the output. It is not required to
show all columns of the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24
Chapter 1: Overview of Oracle Databases
Q5. - (Topic 2)
Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?
A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.
B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
Answer: C
Q6. - (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:
Answer: B,C
Q7. - (Topic 1)
Which four are types of functions available in SQL? (Choose 4)
A. string
B. character
C. integer
D. calendar
E. numeric
F. translation
G. date
H. conversion
Answer: B,E,G,H
Explanation: Explanation: SQL have character, numeric, date, conversion function.
Incorrect Answer:
ASQL have character, numeric, date, conversion function.
CSQL have character, numeric, date, conversion function.
DSQL have character, numeric, date, conversion function.
FSQL have character, numeric, date, conversion function.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3
Q8. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
Exhibit:
The above command fails when executed. What could be the reason?
A. The BETWEEN clause cannot be used for the CHECK constraint
B. SYSDATE cannot be used with the CHECK constraint
C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
D. The CHECK constraint cannot be placed on columns having the DATE data type
Answer: B
Explanation:
CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),
Q9. - (Topic 1)
Which two statements are true regarding sub queries? (Choose two.)
A. A sub query can retrieve zero or more rows.
B. Only two sub queries can be placed at one level.
C. A sub query can be used only in SQL query statements.
D. A sub query can appeal* on either side of a comparison operator.
E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.
Answer: A,D
Q10. - (Topic 1)
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators
Answer: A
Explanation:
Using the ORDER BY Clause in Set Operations
The ORDER BY clause can appear only once at the end of the compound query.
Component queries cannot have individual ORDER BY clauses.
The ORDER BY clause recognizes only the columns of the first SELECT query.
By default, the first column of the first SELECT query is used to sort the output in an
ascending order.