NEW QUESTION 1
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?

• A. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);
• B. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
• C. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
• D. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);

Answer: B

NEW QUESTION 2
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 lis
• B. It locks the rows that satisfy the condition in the SELECT statemen
• C. It can be used only in SELECT statements that are based on a single tabl
• D. It can be used in SELECT statements that are based on a single or multiple table
• E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issue

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 3
Which statement correctly describes SQL and /SQL*Plus?

• A. Both SQL and /SQL*plus allow manipulation of values in the databas
• B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statement
• C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the serve
• D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the databas

Answer: A

NEW QUESTION 4
Examine the following SQL commands:

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

• A. Both commands execute successfull
• B. The first CREATE TABLE command generates an error because the NULL constraint is not vali
• C. The second CREATE TABLE command generates an error because the CHECK constraint is not vali
• D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same colum
• E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraint

Answer: B

Explanation:
Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create
constraints at either the column level or table level. Constraints defined at the column level
are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint
pertains in a set of parentheses. It is mainly the syntax that differentiates the two;
otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.

NEW QUESTION 5
Which statements are correct regarding indexes? (Choose all that apply.)

• A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically update
• B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique inde
• C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
• D. When a table is dropped, the corresponding indexes are automatically dropped

Answer: ABD

NEW QUESTION 6
You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

• A. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount > 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
• B. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount > 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
• C. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount >= 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
• D. CREATE TABLE ord_details (ord_id NUMBER(2), ord_date DATE NOT NULL DEFAULT SYSDATE, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount >= 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));

Answer: C

NEW QUESTION 7
Which statement is true regarding the COALESCE function?

• A. It can have a maximum of five expressions in a lis
• B. It returns the highest NOT NULL value in the list for all row
• C. It requires that all expressions in the list must be of the same data typ
• D. It requires that at least one of the expressions in the list must have a NOT NULL valu

Answer: C

Explanation:
The COALESCE Function The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2) COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter. To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter.

NEW QUESTION 8
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 9
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?

• A. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
• B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
• C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
• D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
• E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

Answer: D

NEW QUESTION 10
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 11
Which two statements are true regarding constraints? (Choose two.)

• A. A constraint can be disabled even if the constraint column contains data
• B. A constraint is enforced only for the INSERT operation on a table
• C. A foreign key cannot contain NULL values
• D. All constraints can be defined at the column level as well as the table level
• E. A columns with the UNIQUE constraint can contain NULL values

Answer: AE

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

NEW QUESTION 13
View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.

Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)

• A. It locks all the rows that satisfy the condition in the statemen
• B. It locks only the columns that satisfy the condition in both the table
• C. The locks are released only when a COMMIT or ROLLBACK is issue
• D. The locks are released after a DML statement is executed on the locked row

Answer: AC

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 SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;

NEW QUESTION 14
Evaluate the following query:

What would be the outcome of the above query?

• A. It produces an error because flower braces have been use
• B. It produces an error because the data types are not matchin
• C. It executes successfully and introduces an 's at the end of each promo_name in the outpu
• D. It executes successfully and displays the literal" {'s start date was} " for each row in the outpu

Answer: C

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 15
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 16
The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

• A. listing the employees who earn the same amount of commission as employee 3
• B. finding the total commission earned by the employees in department 10
• C. finding the number of employees who earn a commission that is higher than the average commission of the company
• D. listing the departments whose average commission is more that 600
• E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
• F. listing the employees whose annual commission is more than 6000

Answer: AC

NEW QUESTION 17
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of
theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

• A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID colum
• B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID colum
• C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES tabl
• D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequenc
• E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES tabl
• F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID colum

Answer: CD

Explanation: the EMP_ID_SEQ sequence is not affected by modification to the
EMPLOYEES table. Any other column of NUMBER data type in your schema can use the
EMP_ID_SEQ sequence.
Incorrect Answer:
AEMP_ID_SEQ sequence can be use to populate JOB_ID
BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES
table.
FEMP_ID_SEQ sequence will be dropped automatically when you drop the
EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4

NEW QUESTION 18
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 19
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)

• A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
• B. SELECT promo_nam
• C. promo_begiii_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
• D. SELECT promo_nam
• E. promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
• F. SELECT promo_name, promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;

Answer: AD