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 2)
You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement?
DELETE employees;
A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. The data in the EMPLOYEES table is deleted but not the structure.
D. You get an error because the statement is not syntactically correct.
Answer: C
Explanation: Explanation: You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHEREcondition]; Incorrect Answer: AStatement will not cause error BDelete statement will not delete the table structure DStatement will not cause error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19
Q3. - (Topic 2)
You need to produce a report where each customer's credit limit has been incremented by $1000.
In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit.
The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000
"New Credit Limit"
FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers;
C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000
AS "New Credit Limit"
FROM customers;
D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW
CREDIT LIMIT")
FROM customers;
Answer: C
Explanation:
A column alias:
-Renames a column heading
-Is useful with calculations
-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.
Q4. - (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
Q5. - (Topic 2)
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)
A. finding the remainder of a division
B. adding a number to a date for a resultant date value
C. comparing two expressions to check whether they are equal
D. checking whether a specified character exists in a given string
E. removing trailing, leading, and embedded characters from a character string
Answer: A,C,D
Q6. - (Topic 1)
Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
Answer: A,D,F
Explanation:
A: The TRUNCATE TABLE Statement releases storage space used by the table,
D: Can not rollback the deletion of rows after the statement executes,
F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.
Incorrect Answer: Cis not true Dis not true Eis not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18
Q7. - (Topic 2)
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C. 50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Answer: C
Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13
Q8. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
B. SELECT promo_name. promo_begiii_date FROM promotions
WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
C. SELECT promo_name. promo_begin_date FROM promotions
WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
D. SELECT promo_name, promo_begin_date "START DATE" FROM promotions
WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
Answer: A,D
Q9. - (Topic 2)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith');
B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith');
C. INSERT INTO employees VALUES ( 1000, 'John', NULL);
D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id) VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' ');
Answer: C,E,F
Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11
Q10. - (Topic 2)
Examine the structure of the SHIPMENTS table:
You want to generate a report that displays the PO_ID and the penalty amount to be paid if
the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
Which statement is true regarding the above commands?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.
Answer: C
Explanation:
The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates: months_between('01-FEB-2008','01-JAN-2008') = 1 The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.
If the default value is omitted, a null value is returned where a search value does not match any of the result values.
Q11. - (Topic 2)
Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?
A. No change is required to achieve the desired results.
B. SELECT ename, sal, 12*(sal+100) FROM emp;
C. SELECT ename, sal, (12*sal)+100 FROM emp;
D. SELECT ename, sal+100,*12 FROM emp;
Answer: B
Explanation:
to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP;
Incorrect Answer: AMultiplication and division has priority over addition and subtraction in Operator precedence. CGive wrong results DWrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11
Q12. - (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
Q13. - (Topic 1)
Which two statements are true about constraints? (Choose two.)
A. The UNIQUE constraint does not permit a null value for the column.
B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D. The NOT NULL constraint ensures that null values are not permitted for the column.
Answer: B,D
Explanation:
B: A unique constraint can contain null values because null values cannot be compared to anything.
D: The NOT NULL constraint ensure that null value are not permitted for the column
Incorrect Answer: Astatement is not true Cstatement is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-9
Q14. - (Topic 1)
Examine the structure of the INVOICE table: Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;
Answer: A,C
Explanation:
The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function\ If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.
Q15. - (Topic 2)
View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.
Which two queries give the correct result? (Choose two.)
A. SELECT promo_name, TO_CHAR(promo_end_date,'Day') ', '
TO_CHAR(promo_end_date,'Month') ' '
TO_CHAR(promo_end_date,'DD, YYYY') AS last_day
FROM promotions;
B. SELECT promo_name,TO_CHAR (promo_end_date,'fxDay') ', '
TO_CHAR(promo_end_date,'fxMonth') ' '
TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day
FROM promotions;
C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR
(promo_end_date,'Month')) ' '
TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day
FROM promotions;
D. SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')','
TO_CHAR(promo_end_date,'fmMonth') ' '
TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day
FROM promotions;
Answer: C,D