Q1. - (Topic 1)
Which are /SQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation:
Describe is a valid iSQL*Plus/ SQL*Plus command.
INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard
SQL Statement not an iSQL*Plus Statement.
RENAME is a DDL Statement.
Q2. - (Topic 2)
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30) TRANS_DATE DATE TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:
Which statement is true regarding the above queries?
A. Both give wrong results.
B. Both give the correct result.
C. Only the first query gives the correct result.
D. Only the second query gives the correct result.
Answer: C
Explanation:
Range Conditions Using the BETWEEN Operator Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Range Conditions Using the BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first. You can also use the BETWEEN operator on character values: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith'
Q3. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It executes successfully.
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
Answer: A
Q4. - (Topic 2)
View the Exhibit and examine the data in the EMPLOYEES table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
What is the outcome?
A. It generates an error because the alias is not valid.
B. It executes successfully and gives the correct output.
C. It executes successfully but does not give the correct output.
D. It generates an error because the usage of the ROUND function in the expression is not valid.
E. It generates an error because the concatenation operator can be used to combine only two items.
Answer: C
Explanation:
ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)
Q5. - (Topic 1)
See the Exhibit and Examine the structure of SALES and PROMOTIONS tables: Exhibit:
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)
A.
DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
B.
DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
C.
DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price');
D.
D DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN ('blowout sale','everyday low price'));
Answer: B,C,D
Q6. - (Topic 1)
Which two statements are true regarding constraints? (Choose two.)
A. A constraint can be disabled even if the constraint column contains data
B. A constraint is enforced only for the INSERT operation on a table
C. A foreign key cannot contain NULL values
D. All constraints can be defined at the column level as well as the table level
E. A columns with the UNIQUE constraint can contain NULL values
Answer: A,E
Q7. - (Topic 2)
What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges.
B. It is required syntax for object privileges.
C. It allows privileges on specified columns of tables.
D. It is used to grant an object privilege on a foreign key column.
E. It allows the grantee to grant object privileges to other users and roles.
Answer: E Explanation:
The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle
Q8. - (Topic 1)
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city='Singapore'):
Which statement is true regarding the above query if one of the values generated by the sub query is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the sub query.
D. It ignores the NULL value and generates output for the other values produced by the sub query.
Answer: C
Q9. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:
What would be the outcome of the above statement?
A. It executes successfully.
B. It produces an error because the condition on CUST_LAST_NAME is invalid.
C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.
Answer: A
Q10. - (Topic 1)
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. The BLOB data type column is used to store binary data in an operating system file
B. The minimum column width that can be specified for a VARCHAR2 data type column is one
C. A TIMESTAMP data type column stores only time values with fractional seconds
D. The value for a CHAR data type column is blank-padded to the maximum defined column width
E. Only One LONG column can be used per table
Answer: B,D,E
Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table. DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column. If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size) Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,000.) BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle
Net.
BFILE A locator pointing to a file stored on the operating system of the database server.
The size of the files is limited to 4GB.
TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on
the precision specified.
Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by
default.