Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;
C. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;
D. SELECT AVG(inv_date) FROM invoice;
Answer: A,B
Q2. - (Topic 1)
Evaluate the following SQL commands:
The command to create a table fails. Identify the reason for the SQL statement failure?
(Choose all that apply.)
A. You cannot use SYSDATE in the condition of a CHECK constraint.
B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY.
Answer: A,C
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),
Q3. - (Topic 2)
What is true about sequences?
A. Once created, a sequence belongs to a specific schema.
B. Once created, a sequence is linked to a specific table.
C. Once created, a sequence is automatically available to all users.
D. Only the DBA can control which sequence is used by a certain table.
E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.
Answer: A
Q4. - (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);
Answer: D
Explanation:
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn);
Incorrect Answer:
Awrong syntax
Bwrong syntax
Cwrong syntax
Eno such MODIFY keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17
Q5. - (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;
Answer: A
Q6. - (Topic 2)
Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four)
A. You cannot roll back this statement.
B. All pending transactions are committed.
C. All views based on the DEPT table are deleted.
D. All indexes based on the DEPT table are dropped.
E. All data in the table is deleted, and the table structure is also deleted.
F. All data in the table is deleted, but the structure of the table is retained.
G. All synonyms based on the DEPT table are deleted.
Answer: A,B,D,E
Explanation:
You cannot roll back DROP TABLE statement. All pending transactions related on this
table are committed. If the table is dropped, Oracle automatically drops any index, trigger
and constraint associated with the table as well. All data in the table is deleted, and the
table structure is also deleted.
Incorrect Answers
C:All views based on the DEPT table become invalid, but they are not deleted.
F:All data in the table is deleted, and the table structure is also deleted. Command
TRUNCATE deletes all data in the table, but does not delete the structure of the table.
G:All synonyms based on the DEPT table are not deleted after dropping the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects
Q7. - (Topic 2)
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?
A. Only the synonym would be dropped.
B. The synonym would be dropped and the corresponding table would become invalid.
C. The synonym would be dropped and the packages referring to the synonym would be dropped.
D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
Answer: A
Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.
Q8. - (Topic 1)
Which statement is true regarding the UNION operator?
A. The number of columns selected in all SELECT statements need to be the same
B. Names of all columns must be identical across all SELECT statements
C. By default, the output is not sorted
D. NULL values are not ignored during duplicate checking
Answer: A
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q9. - (Topic 1)
You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data in the table are deleted but the table structure will remain
B. All data along with the table structure is deleted
C. All views and synonyms will remain but they are invalidated
D. The pending transaction in the session is committed
E. All indexes on the table will remain but they are invalidated
Answer: B,C,D
Q10. - (Topic 2)
Which statement describes the ROWID data type?
A. Binary data up to 4 gigabytes.
B. Character data up to 4 gigabytes.
C. Raw binary data of variable length up to 2 gigabytes.
D. Binary data stored in an external file, up to 4 gigabytes.
E. A hexadecimal string representing the unique address of a row in its table.
Answer: E
Explanation:
The ROWID datatype stores information related to the disk location of table rows. They
also uniquely identify the rows in your table. The ROWID datatype is stored as a
hexadecimal string.
Incorrect Answers
A:It is not a binary data. The ROWID datatype is a hexadecimal string.
B:It is not a character data. The ROWID datatype is a hexadecimal string.
C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal
string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects
Q11. - (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:
Answer: A
Q12. - (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
Answer: A
Explanation:
Character Strings and Dates
Character strings and date values are enclosed with single quotation marks.
Character values are case-sensitive and date values are format-sensitive.
The default date display format is DD-MON-RR.
Q13. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.
Exhibit:
The query generates an error.
What is the reason of generating error?
A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
B. The parenthesis should be added to enclose the entire expression
C. The column alias should be replaced with the expression in the WHERE clause
D. The double quotation marks should be removed from the column alias
Answer: C
Explanation: Note: You cannot use column alias in the WHERE clause.
Q14. - (Topic 1)
You need to perform these tasks:
. Create and assign a MANAGER role to Blake and Clark . Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark
Which set of SQL statements achieves the desired results?
A. CREATE ROLE manager;
GRANT create table, create view
TO manager;
GRANT manager TO BLAKE,CLARK;
B. CREATE ROLE manager;
GRANT create table, create voew
TO manager;
GRANT manager ROLE TO BLAKE,CLARK;
C. GRANT manager ROLE TO BLAKE,CLARK;
GRANT create table, create voew
TO BLAKE CLARK;
***MISSING***
Answer: A
Explanation: Result of commands:
Q15. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.
SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table:
SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;
What is the outcome?
A. It executes successfully.
B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
Answer: D