NEW QUESTION 1
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

• A. INTERSECT
• B. UNION ALL
• C. MINUS
• D. UNION

Answer: C

NEW QUESTION 2
View the Exhibit and examine the structure and data in the INVOICE table.

Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)

• A. inv_amt ='0255982' : requires explicit conversion
• B. inv_date > '01-02-2008' : uses implicit conversion
• C. CONCAT(inv_amt,inv_date) : requires explicit conversion
• D. inv_date = '15-february-2008' : uses implicit conversion
• E. inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: DE

Explanation:
In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user. Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output. Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.

NEW QUESTION 3
View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?

• A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers GROUP BY cust_city, cust_income_level, cust_credit_limit;
• B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;
• C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city ;
• D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

Answer: B

NEW QUESTION 4
Examine the structure of the PROMOTIONS table:

The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?

• A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
• B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
• C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
• D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Answer: D

NEW QUESTION 5
The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE
123456 152525.99
You issue the following query:
SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*')
FROM products
WHERE prod_id = 123456;
What would be the outcome?

• A. 152526****
• B. **152525.99
• C. 152525**
• D. an error message

Answer: A

Explanation:
The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

NEW QUESTION 6
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 result
• B. Only the first query executes successfully but gives a wrong resul
• C. Only the first query executes successfully and gives the correct resul
• D. Only the second query executes successfully but gives a wrong resul
• E. Only the second query executes successfully and gives the correct resul

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.

NEW QUESTION 7
When does a transaction complete? (Choose all that apply.)

• A. When a PL/SQL anonymous block is executed
• B. When a DELETE statement is executed
• C. When a data definition language statement is executed
• D. When a TRUNCATE statement is executed after the pending transaction
• E. When a ROLLBACK command is executed

Answer: CDE

NEW QUESTION 8
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: AB

NEW QUESTION 9
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 forma
• B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integer
• C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and second
• D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 character

Answer: BD

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

NEW QUESTION 10
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:

NEW QUESTION 11
View the Exhibit and examine the data in the EMPLOYEES table:
You want to display all the employee names and their corresponding manager names.
Evaluate the following query:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
Exhibit:

• A. only inner JOIN
• B. only FULL OUTER JOIN
• C. only LEFT OUTER JOIN
• D. only RIGHT OUTER JOIN

Answer: C

NEW QUESTION 12
Examine the structure proposed for the TRANSACTIONS table:

Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)

• A. The CUST_STATUS column would give an erro
• B. The TRANS_VALIDITY column would give an erro
• C. The CUST_STATUS column would store exactly one characte
• D. The CUST_CREDIT_LIMIT column would not be able to store decimal value
• E. The TRANS_VALIDITY column would have a maximum size of one characte
• F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of second

Answer: BC

Explanation:
VARCHAR2(size)Variable-length character data (A maximum size must be specified:
minimum size is 1; maximum size is 4,000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size
is 1; maximum size is 2,000.)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
decimal digits and scale is the number of digits to the right of the decimal point; precision
can range from 1 to 38, and scale can range from –84 to 127.)
DATE Date and time values to the nearest second between January 1, 4712 B.C., and
December 31, 9999 A.D.

NEW QUESTION 13
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clause are valid for the above query? (Choose all that apply.)

• A. ORDER BY 2,1
• B. ORDER BY CUST_NO
• C. ORDER BY 2,cust_id
• D. ORDER BY "CUST_NO"
• E. ORDER BY "Last Name"

Answer: ACE

Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.

NEW QUESTION 14
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: ABD

NEW QUESTION 15
See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

Which two SQL statements would executes successfully? (Choose two.)

• A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
• B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
• C. SELECT (AVG(inv_date) FROM invoice;
• D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;

Answer: AD

NEW QUESTION 16
EMPLOYEES and DEPARTMENTS data: EMPLOYEES

DEPARTMENTS

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary
FROM employees
WHERE emp_name = 'Smith')
WHERE job_id = 'IT_ADMIN';
What happens when the statement is executed?

• A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
• B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
• C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
• D. The statement fails because there is more than one row matching the employee name Smit
• E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES tabl
• F. The statement fails because there is no 'Administration' department in the DEPARTMENTS tabl

Answer: D

Explanation:
'=' is use in the statement and sub query will return more than one row.
Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A. The Update statement will fail no update was done.
B. The update statement will fail no update was done.
C. The update statement will fail no update was done.
E. The update statement will fail but not due to job_it='IT_ADMIN'
F. The update statement will fail but not due to department_id='Administration'
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12

NEW QUESTION 17
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