NEW QUESTION 1
Which two statements are true regarding working with dates? (Choose two.)

• A. The default internal storage of dates is in the numeric format
• B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required
• C. The default internal storage of dates is in the character format
• D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century

Answer: AB

Explanation:
Working with Dates The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-RR. RR Date Format The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.

untitled Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want. This data is stored internally as follows: CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43

NEW QUESTION 2
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 3
View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?

• A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC;
• B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC;
• C. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY (cust_city, cust_income_level) DESC;
• D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC;

Answer: A

NEW QUESTION 4
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 5
What is true about sequences?

• A. The start value of the sequence is always 1.
• B. A sequence always increments by 1.
• C. The minimum value of an ascending sequence defaults to 1.
• D. The maximum value of descending sequence defaults to 1.

Answer: C

NEW QUESTION 6
Examine the description of the EMP_DETAILS table given below: Exhibit:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

• A. An EMP_IMAGE column can be included in the GROUP BY clause
• B. You cannot add a new column to the table with LONG as the data type
• C. An EMP_IMAGE column cannot be included in the ORDER BY clause
• D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Answer: BC

Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.
There can only be one LONG column in a table.
Guidelines
A LONG column is not copied when a table is created using a subquery.
A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
Only one LONG column can be used per table.
No constraints can be defined on a LONG column.
You might want to use a CLOB column rather than a LONG column.

NEW QUESTION 7
Which two statements are true regarding the COUNT function? (Choose two.)

• A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
• B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
• C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
• D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
• E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Answer: AC

Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT
statement, including duplicate rows and rows containing null values in any of the columns.
If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of
rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by
expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the
column identified by expr.

NEW QUESTION 8
What is true regarding sub queries?

• A. The inner query always sorts the results of the outer query
• B. The outer query always sorts the results of the inner query
• C. The outer query must return a value to the outer query
• D. The inner query returns a value to the outer query
• E. The inner query must always return a value or the outer query will give an error

Answer: D

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result

NEW QUESTION 9
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
• B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
• D. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
• E. SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;

Answer: ACD

NEW QUESTION 10
Which CREATE TABLE statement is valid?

• A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL);
• B. CREATE TABLE ord_details (ord_no NUMBER(2) UNIQUE, NOT NULL, item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL);
• C. CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));
• D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D

Explanation:
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

NEW QUESTION 11
Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
• B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
• D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
• E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

Answer: ADE

NEW QUESTION 12
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

You want to create a SALE_PROD view by executing the following SQL statements:

Which statement is true regarding the execution of the above statement?

• A. The view will be created and you can perform DLM operations on the view
• B. The view will not be created because the join statements are not allowed for creating a view
• C. The view will not be created because the GROUP BY clause is not allowed for creating a view
• D. The view will be created but no DML operations will be allowed on the view

Answer: D

Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION 13
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE TIMESTAMP
TRANS_AMT NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12 noon.
The value zero should be displayed for transactions where the transaction amount has not been entered.
Which query gives the required result?

• A. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), TO_CHAR(trans_amt,'$99999999D99') FROM transactions WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)<>NULL;
• B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL(TO_CHAR(trans_amt,'$99999999D99'),0) FROM transactions WHERE TO_CHAR(trans_date,'hh24') < 12;
• C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;
• D. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;

Answer: B

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
See the Exhibit and examine the structure of ORD table: Exhibit:

Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, ’25-jan-2007,101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?

• A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
• B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specifie
• C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specifie
• D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.

Answer: A

NEW QUESTION 16
Evaluate the following SQL statements: Exhibit:

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Exhibit:

Which two statements are true regarding the above view? (Choose two.)

• A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
• B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
• C. It allows you to insert rows into the EMPLOYEES table
• D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

Answer: BD

NEW QUESTION 17
You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?

• A. It executes successfully and gives the correct outpu
• B. It gives an error because the HAVING clause is not vali
• C. It executes successfully but does not give the correct outpu
• D. It gives an error because the GROUP BY expression is not vali

Answer: B

Explanation:
The general form of the SELECT statement is further enhanced by the addition of the
HAVING clause and becomes:
SELECT column|expression|group_function(column|expression [alias]),…}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
An important difference between the HAVING clause and the other SELECT statement
clauses is that it may only be specified if a GROUP BY clause is present. This dependency
is sensible since group-level rows must exist before they can be restricted. The HAVING
clause can occur before the GROUP BY clause in the SELECT statement. However, it is
more common to place the HAVING clause after the GROUP BY clause. All grouping is
performed and group functions are executed prior to evaluating the HAVING clause.