Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 2)
Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
A. DELETE can be used to remove only rows from only one table at a time.
B. DELETE can be used to remove only rows from multiple tables at a time.
C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
D. DELETE can be used to remove data from specific columns as well as complete rows.
E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.
Answer: A,E
Explanation:
Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed. The Transaction Control Statements A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables: a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT Explicit Transaction Control Statements You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements. Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.
untitled
Q2. - (Topic 1)
Evaluate the following two queries: Exhibit:
Exhibit:
Which statement is true regarding the above two queries?
A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
B. Performance would degrade in query 2
C. There would be no change in performance
D. Performance would improve in query 2
Answer: C
Explanation:
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator
has no performance benefits and is used only for logical simplicity.
Q3. - (Topic 1)
The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
A. GRANT select ON dept TO ALL_USERS;
B. GRANT select ON dept TO ALL;
C. GRANT QUERY ON dept TO ALL_USERS
D. GRANT select ON dept TO PUBLIC;
Answer: D
Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
Q4. - (Topic 2)
You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?
A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
Answer: B
Explanation: function MAX(column_name)
Incorrect Answer:
Ainvalid statement
Cinner query return more than one line
Dcolumn maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
Q5. - (Topic 1)
Which three statements are true regarding sub queries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and sub query
B. Sub queries can contain GROUP BY and ORDER BY clauses
C. Only one column or expression can be compared between the main query and subqeury
D. Main query and sub query can get data from different tables
E. Main query and sub query must get data from the same tables
F. Sub queries can contain ORDER BY but not the GROUP BY clause
Answer: A,B,D
Q6. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?
A.
SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
B.
SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
C.
SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
D.
SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);
Answer: A
Explanation:
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value
returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.
Q7. - (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
Q8. - (Topic 2)
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
Answer: B,D
Explanation:
B: The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually
any magnitude can be stored and are guaranteed portable among different systems
operating Oracle, up to 38 digits of precision.
The following numbers can be stored in a NUMBER column:
Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits Zero Positive and negative infinity (generated only by importing from an Oracle Version 5 database)
D: The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes, and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid.
A DAY TO MINUTE interval considers an interval of days to the nearest minute. Reference: Oracle Database Concepts 10g, Native Datatypes
Q9. - (Topic 1)
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. The number of columns and data types must be identical for all SELECT statements in the query
C. The names of columns in all SELECT statements must be identical
D. Reversing the order of the intersected tables the result
Answer: B
Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and
removing duplicates.
The columns in the queries that make up a compound query can have different names, but
the output result set will use the names of the columns in the first query.
Q10. - (Topic 2)
Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)
A. It locks only the columns specified in the SELECT list.
B. It locks the rows that satisfy the condition in the SELECT statement.
C. It can be used only in SELECT statements that are based on a single table.
D. It can be used in SELECT statements that are based on a single or multiple tables.
E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.
Answer: B,D
Explanation:
FOR UPDATE Clause in a SELECT Statement Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement. FOR UPDATE Clause in a SELECT Statement When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data). There are times, however, when you may want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records “for your changes only.” No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT. You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command.
Q11. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table.
Evaluate the following INSERT statement:
The INSERT statement fails when executed. What could be the reason?
A. The VALUES clause cannot be used in an INSERT with a subquery
B. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table
C. The WHERE clause cannot be used in a sub query embedded in an INSERT statement
D. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match
Answer: A
Explanation:
Copying Rows from Another Table
Write your INSERT statement with a subquery:
Do not use the VALUES clause.
Match the number of columns in the INSERT clause to those in the subquery.
Inserts all the rows returned by the subquery in the table, sales_reps.
Q12. - (Topic 1)
Examine the structure and data in the PRIC E_LIST table: Name Null? Type
PROD_D NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD PRICE
100 $234.55
101 $6,509.75
102 $1,234
in the same format as the PROD_PRICE. Which SQL statement would give the required result?
A. SELECT TO_CHAR(prod_price* .25.'$99.999.99') FROM PRICEJLIST:
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM PRICE_LIST:
D. SELECT TO_NUMBER(TO_NUMBER(prod_price.,$99.999.99')* .25/$99.999.00') FROM PRICE_LIST:
Answer: C
Q13. - (Topic 1)
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50%
Credit Limit"
FROM customers;
C. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
D. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM
customers;
Answer: C
Explanation: Duplicate Rows Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.
Q14. - (Topic 2)
Examine the data in the CUSTOMERS table:
You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:
SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
A. JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. FULL OUTER JOIN
E. RIGHT OUTER JOIN
Answer: A,E
Q15. - (Topic 2)
What is true about updates through a view?
A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in effect.
D. When you update a view the constraints on the views always override the constraints on the underlying tables.
Answer: A