Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (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.
Q2. - (Topic 1)
You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
Answer: B
Explanation:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs where job_id like 'SA\_%' escape '\'
Q3. - (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
Q4. - (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.
Q5. - (Topic 1)
Which statement correctly describes SQL and /SQL*Plus?
A. Both SQL and /SQL*plus allow manipulation of values in the database.
B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
Answer: A
Q6. - (Topic 1)
Examine the structure and data of the CUSTJTRANS table:
CUSTJRANS
Name Null? Type
CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM custjrans;
B. SELECT * FROM custjrans WHERE transdate = '01-01-07':
C. SELECT transamt FROM custjrans WHERE custno > '11':
D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07':
E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000:
Answer: A,C,D
Q7. - (Topic 2)
Which describes the default behavior when you create a table?
A. The table is accessible to all users.
B. Tables are created in the public schema.
C. Tables are created in your schema.
D. Tables are created in the DBA schema.
E. You must specify the schema when the table is created.
Answer: C
Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Agrant the table privilege to PUBLIC Blogin as sysoper Dlogin as DBA or sysdba Eno such option is allow.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-9
Q8. - (Topic 1)
View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?
A.
UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
B.
UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;
C.
UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;
D.
UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;
Answer: A
Explanation:
Updating Two Columns with a Subquery
You can update multiple columns in the SET clause of an UPDATE statement by writing
multiple subqueries. The syntax is as follows:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,
column =
(SELECT column
FROM table
WHERE condition)]
[WHERE condition ] ;
Q9. - (Topic 1)
Which two statements are true regarding the ORDER BY clause? (Choose two.)
A. It is executed first in the query execution.
B. It must be the last clause in the SELECT statement.
C. It cannot be used in a SELECT statement containing a HAVING clause.
D. You cannot specify a column name followed by an expression in this clause.
E. You can specify a combination of numeric positions and column names in this clause.
Answer: B,E
Q10. - (Topic 2)
A data manipulation language statement _____.
A. completes a transaction on a table
B. modifies the structure and data in a table
C. modifies the data but not the structure of a table
D. modifies the structure but not the data of a table
Answer: C
Explanation:
modifies the data but not the structure of a table
Incorrect Answer:
ADML does not complete a transaction
BDDL modifies the structure and data in the table
DDML does not modified table structure.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-3
Q11. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
A. Display the minimum list price for each product status.
B. Display all suppliers whose list price is less than 1000.
C. Display the number of products whose list price is more than the average list price.
D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.
Answer: C,E
Q12. - (Topic 2)
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?
A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
Answer: F
Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
Q13. - (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.
Q14. - (Topic 2)
What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3
Q15. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:
You issue the following SQL statement:
Which statement is true regarding the execution of the above query?
A. It produces an error because the AMT_SPENT column contains a null value.
B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
Answer: C
Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.