NEW QUESTION 1
For which action can you use the TO_DATE function?

• A. Convert any date literal to a date
• B. Convert any numeric literal to a date
• C. Convert any character literal to a date
• D. Convert any date to a character literal
• E. Format ’10-JAN-99’ to ‘January 10 1999’

Answer: C

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

You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)

• A. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
• B. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
• C. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount';
• D. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount';

Answer: AD

NEW QUESTION 3
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
Lex De Haan Renske Ladwig Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the
first name as follows:
CUST NAME
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)

• A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0;
• B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)<>0;
• C. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,-2)<>0;
• D. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0 ;

Answer: AB

NEW QUESTION 4
You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

• A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Mont
• B. Year') FROM DUAL:
• C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
• D. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Mont
• E. Year') FROM DUAL;
• F. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Mont
• G. Year')) FROM DUAL:

Answer: A

NEW QUESTION 5
View the Exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?

• A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET';
• B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');
• C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
• D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

Answer: C

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

NEW QUESTION 7
Which statements are true regarding single row functions? (Choose all that apply.)

• A. MOD : returns the quotient of a division
• B. TRUNC : can be used with NUMBER and DATE values
• C. CONCAT : can be used to combine any number of values
• D. SYSDATE : returns the database server current date and time
• E. INSTR : can be used to find only the first occurrence of a character in a string
• F. TRIM : can be used to remove all the occurrences of a character from a string

Answer: BD

Explanation:
ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time.
Date-Manipulation Functions
Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function
The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com','.',1,2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:

NEW QUESTION 8
View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.

What would be the outcome if all the parentheses are removed from the above statement?

• A. It produces a syntax erro
• B. The result remains unchange
• C. The total price value would be lower than the correct valu
• D. The total price value would be higher than the correct valu

Answer: B

NEW QUESTION 9
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1.
The promo name should not begin with 'T' or 'N'.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?

• A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
• C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')

Answer: C

NEW QUESTION 10
The CUSTOMERS table has these columns:

A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France?

• A. WHERE lower(country_address) = "france"
• B. WHERE lower(country_address) = 'france'
• C. WHERE lower(country_address) IS 'france'
• D. WHERE lower(country_address) = '%france%'
• E. WHERE lower(country_address) LIKE %france%

Answer: B

Explanation:
WHERE lower(country_address)=’france’
Incorrect Answer: Ainvalid use of symbol “” Cinvalid use of IS keyword Dinvalid use of % in condition Einvalid use of condition Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-12

NEW QUESTION 11
Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?

• A. SELECT &1, "&2" FROM &3 WHERE last_name = '&4';
• B. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4' ';
• C. SELECT &1, &2 FROM &3 WHERE last_name = '&4';
• D. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

Answer: C

Explanation:
In a WHERE clause, date and characters values must be enclosed within single quotation marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME FROM EMPLOYEES
Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D. No input for table name as EMP has been use in the statement.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8

NEW QUESTION 12
The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?

• A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE '';
• B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';
• C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "";
• D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_';

Answer: A

Explanation: ESCAPE identifier to search for the _ symbol
Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13

NEW QUESTION 13
In which two cases would you use an outer join? (Choose two.)

• A. The tables being joined have NOT NULL column
• B. The tables being joined have only matched dat
• C. The columns being joined have NULL value
• D. The tables being joined have only unmatched dat
• E. The tables being joined have both matched and unmatched dat
• F. Only when the tables have a primary key/foreign key relationshi

Answer: CE

Explanation:
You use an outer join to also see rows that do not meet the join condition.
Incorrect Answer: Ameet a join condition Bmeet a join condition Dmeet non join condition only Fdoes not take into consideration of primary key and foreign key relationship
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-17

NEW QUESTION 14
Which two statements are true regarding tables? (Choose two.)

• A. A table name can be of any lengt
• B. A table can have any number of column
• C. A column that has a DEFAULT value cannot store null value
• D. A table and a view can have the same name in the same schem
• E. A table and a synonym can have the same name in the same schem
• F. The same table name can be used in different schemas in the same databas

Answer: EF

Explanation:
Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.

NEW QUESTION 15
View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format: CATEGORIES 5MP Digital Photo Camera's category is Photo Y Box's category is Electronics Envoy Ambassador's category is Hardware Which two queries would give the required output? (Choose two.)

• A. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products;
• B. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products;
• C. SELECT prod_name || q''s' || ' category is ' || prod_category CATEGORIES FROM products;
• D. SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;

Answer: CD

Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks. Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character The syntax of the alternative quote operator is as follows: q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself
contains a single quotation mark, you can use the quote (q) operator and select your own
quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following
character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

NEW QUESTION 16
Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)

• A. self-join
• B. subquery
• C. full outer-join with self-join
• D. left outer-join with self-join
• E. right outer-join with self-join

Answer: AB

NEW QUESTION 17
The CUSTOMERS table has the following structure: Exhibit:

You need to write a query that does the following task:
*
Display the first name and tax amount of the customers. Tax is 5% of their credit limit
*
Only those customers whose income level has a value should be considered
*
Customers whose tax amount is null should not be considered
Which statement accomplishes all the required tasks?

• A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND tax_amount IS NOT NULL;
• B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
• C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level <> NULL AND tax_amount <> NULL;
• D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level,tax_amount) IS NOT NULL;

Answer: B