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

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
C- is not true
D- is not true
E- is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18

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

Which two statements are true regarding data type conversion in expressions used in queries?

• 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.

Evaluate the query statement:

What would be the outcome of the above statement?

• A. It executes successfully.
• B. It produces an error because the condition on CUST_LAST_NAME is invalid.
• C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
• D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.

Answer: A

NEW QUESTION 4
Which object privileges can be granted on a view?

• A. none
• B. DELETE, INSERT, SELECT
• C. ALTER, DELETE, INSERT, SELECT
• D. DELETE, INSERT, SELECT, UPDATE

Answer: D

Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.
Incorrect
A- Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
B- Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
C- Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

NEW QUESTION 5
Examine the statement:
GRANT select, insert, update ON student_grades
TO manager
WITH GRANT OPTION;
Which two are true? (Choose two.)

• A. MANAGER must be a role.
• B. It allows the MANAGER to pass the specified privileges on to other users.
• C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.
• D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.
• E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.
• F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.

Answer: BE

Explanation: GRANT ROLE to ROLE/USER
Incorrect
A- Role can be grant to user
C- Create table privilege is not granted
D- Execute privilege is not granted
F- Delete privilege is not granted
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

NEW QUESTION 6
View the Exhibit and examine the structure of the promotions table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A. It shows COST_REMARK for all the promos in the table.
• B. It produces an error because the SUBQUERY gives an error.
• C. It shows COST_REMARK for all the promos in the promo category 'TV'
• D. It produces an error because SUBQUERIES cannot be used with the case expression.

Answer: A

NEW QUESTION 7
Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?

• A. The set of statements contains an error and does not work.
• B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
• C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
• D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
• E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
• F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.

Answer: C

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1, user2 and user3.
Incorrect:
A- the statement does not contain error
B- there is no MODIFY privilege
D- statement does not create 3 users with the role
E- privilege is grant to role then grant to user
F- privilege is grant to role then grant to user

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

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

Answer: A

Explanation: group functions on column ignore NULL values
Incorrect
B- group functions on column ignore NULL values
C- group functions on column ignore NULL values
D- NVL function can be use for column with NULL values
E- no such INC_NULLS keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-12

NEW QUESTION 9
Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)

• A. It locks only the columns specified in the SELECT list.
• B. It locks the rows that satisfy the condition in the SELECT statement.
• C. It can be used only in SELECT statements that are based on a single table.
• D. It can be used in SELECT statements that are based on a single or multiple tables.
• E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.

Answer: BD

Explanation: FOR UPDATE Clause in a SELECT Statement
Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT.
If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement.
FOR UPDATE Clause in a SELECT Statement
When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data). There are times, however, when you may want to lock a set of records even before you change them in your program.
Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records “for your changes only.” No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.
You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command.

NEW QUESTION 10
Examine the structure of the transactions table:

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. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

NEW QUESTION 11
Which describes the default behavior when you create a table?

• A. The table is accessible to all users.
• B. Tables are created in the public schema.
• C. Tables are created in your schema.
• D. Tables are created in the DBA schema.
• E. You must specify the schema when the table is created.

Answer: C

Explanation: Sorted by highest to lowest is DESCENDING order
Incorrect
A- grant the table privilege to PUBLIC
B- login as sysoper
D- login as DBA or sysdba
E- no such option is allow.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-9

NEW QUESTION 12
A UNIQUE constraint on a column requires an index.
Which of the following scenarios is correct? (Choose one or more correct answers.)

• A. If a UNIQUE index already exists on the column, it will be used.
• B. If a NONUNIQUE index already exists it will be used.
• C. If a NONUNIQUE index already exists on the column, a UNIQUE index will be created implicitly.
• D. If any index exists on the column, there will be an error as Oracle attempts to create another index implicitly.

Answer: AB

NEW QUESTION 13
Examine the structure and data of the CUST_TRANS table:

Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three SQL statements would execute successfully?

• A. SELECT transdate + '10' FROM cust_trans;
• B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
• C. SELECT transamt FROM cust_trans WHERE custno > '11';
• D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
• E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

Answer: ACD

NEW QUESTION 14
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 15
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. Option A
• B. Option B
• C. Option C
• D. Option D

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.

NEW QUESTION 16
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
Exhibit:

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?

• A. Number of days since the promo started based on the current Chicago data and time
• B. Number of days since the promo started based on the current Singapore data and time.
• C. An error because the WHERE condition specified is invalid
• D. An error because the ROUND function specified is invalid

Answer: A

NEW QUESTION 17
The PRODUCTS table has the following structure:

Evaluate the following two SQL statements:

Which statement is true regarding the outcome?

• A. Both the statements execute and give the same results.
• B. Both the statements execute and give different results.
• C. Only the second SQL statement executes successfully.
• D. Only the first SQL statement executes successfully.

Answer: B

Explanation: Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax
NVL2(expr1, expr2, expr3) In the syntax:
Expr1 is the source value or expression that may contain a null
Expr2 is the value that is returned if expr1 is not null Expr3 is the value that is returned if expr1 is null

NEW QUESTION 18
Examine the structure of the employees table.

You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: AC

Explanation: Incorrect:
Not B, not D: You cannot use a GROUP BY clause in a WHERE statement.

NEW QUESTION 19
View the Exhibit and examine the structure of the customers table.

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement:

The insert statement fails when executed. What could be the reason?

• A. The values clause cannot be used in an INSERT with a subquery.
• B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
• C. The where clause cannot be used in a subquery embedded in an INSERT statement.
• D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.

Answer: A

Explanation: Copying Rows from Another Table
Write your INSERT statement with a subquery: Do not use the VALUES clause.
Match the number of columns in the INSERT clause to those in the subquery. Inserts all the rows returned by the subquery in the table, sales_reps.