NEW QUESTION 1
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 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 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 3
View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables.
You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.
Which FROM clause gives the required result?

• A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
• B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);

Answer: C

NEW QUESTION 4
Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue the following command:
SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date,'yyyy');
Which statement is true regarding the outcome?

• A. It executes successfully and gives the correct outpu
• B. It gives an error because the TO_CHAR function is not vali
• C. It executes successfully but does not give the correct outpu
• D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY claus

Answer: D

NEW QUESTION 5
View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

The above query generates an error on execution.
Which clause in the above SQL statement causes the error?

• A. WHERE
• B. SELECT
• C. GROUP BY
• D. ORDER BY

Answer: C

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

NEW QUESTION 7
What is true of using group functions on columns that contain NULL values?

• A. Group functions on columns ignore NULL value
• B. Group functions on columns returning dates include NULL value
• C. Group functions on columns returning numbers include NULL value
• D. Group functions on columns cannot be accurately used on columns that contain NULL value
• E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULL

Answer: A

Explanation: group functions on column ignore NULL values
Incorrect Answer: Bgroup functions on column ignore NULL values Cgroup functions on column ignore NULL values DNVL function can be use for column with NULL values Eno such INC_NULLS keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-12

NEW QUESTION 8
View the Exhibits and examine PRODUCTS and SALES tables.

You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?

• A. The statement executes successfully and produces the required outpu
• B. The statement produces an error because ITEM_CNT cannot be displayed in the outer quer
• C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used togethe
• D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM claus

Answer: A

NEW QUESTION 9
Evaluate this SQL statement:
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?

• A. remove the ORDER BY clause
• B. remove the table alias prefix from the WHERE clause
• C. remove the table alias from the SELECT clause
• D. prefix the column in the USING clause with the table alias
• E. prefix the column in the ORDER BY clause with the table alias
• F. replace the condition ”d.department_id NOT IN (10,40)” in the WHERE clause with ”d.department_id <> 10 AND d.department_id <> 40”

Answer: B

NEW QUESTION 10
Which two statements are true regarding subqueries? (Choose two.)

• A. A subquery can retrieve zero or more row
• B. Only two subqueries can be placed at one leve
• C. A subquery can be used only in SQL query statement
• D. A subquery can appear on either side of a comparison operato
• E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statemen

Answer: AD

Explanation:
Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel’s salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. Subquery Syntax A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax: operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

NEW QUESTION 11
Evaluate the following query:
SELECT INTERVAL '300' MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?

• A. +25-00 , +54-02, +00 11:12:10.123457
• B. +00-300, +54-02, +00 11:12:10.123457
• C. +25-00 , +00-650, +00 11:12:10.123457
• D. +00-300 , +00-650, +00 11:12:10.123457

Answer: A

Explanation:
Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes, and seconds

NEW QUESTION 12
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 13
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO
columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)

• A. CREATE INDEX ord_idx1 ON ord(ord_no);
• B. CREATE INDEX ord_idx2 ON ord_items(ord_no);
• C. CREATE INDEX ord_idx3 ON ord_items(item_no);
• D. CREATE INDEX ord_idx4 ON ord,ord_items(ord_no, ord_date,qty);

Answer: BC

Explanation: How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a
column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index
is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create
the FOREIGN KEY column index for a join in a query to improve the speed of retrieval.
Note: You can manually create a unique index, but it is recommended that you create a
unique constraint, which implicitly creates a unique index.

NEW QUESTION 14
The DBA issues this SQL command:
CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

• A. No privilege
• B. Only the SELECT privileg
• C. Only the CONNECT privileg
• D. All the privileges of a default use

Answer: A

Explanation:
There are no privileges for the user Scott at this point. They are not added themselves to
the user immediately after creation. The DBA needs to grant all privileges explicitly.
Incorrect Answers
B:There are no privileges for the user Scott at this point. SELECT privilege needs to be
added to the user Scott.
C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be
added to the user Scott.
D:There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351
Chapter 8: User Access in Oracle

NEW QUESTION 15
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: CEF

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

NEW QUESTION 16
You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list. Winch two queries would achieve the required result? (Choose two.)

• A. SELECT cust_last_nam
• B. cust_credit_limit FROM customers ORDER BY cust_credit_limit DESC:
• C. SELECT cust_last_nam
• D. cust_credit_limit FROM customers ORDER BY cust_credit_limit:
• E. SELECT cust_last_nam
• F. cust_credit_limit FROM customers ORDER BY cust_credit_limit NULLS LAST:
• G. SELECT cust_last_nam
• H. cust_credit_limit FROM customers ORDER BY cust_last_nam
• I. cust_credit_limit NULLS LAST:

Answer: BC

Explanation:
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting The default sort order is ascending:
.
Numeric values are displayed with the lowest values first (for example, 1 to 999).
.
Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
.
Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
.
Null values are displayed last for ascending sequences and first for descending sequences.
-ANSWER B
. You can also sort by a column that is not in the SELECT list.

NEW QUESTION 17
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 18
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 19
View the Exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:

Which statement is true regarding the outcome?

• A. Only statement 1 executes successfully and gives the required resul
• B. Only statement 2 executes successfully and gives the required resul
• C. Both statements 1 and 2 execute successfully and give different result
• D. Both statements 1 and 2 execute successfully and give the same required resul

Answer: D