NEW QUESTION 1
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?

• A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
• B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
• C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);
• D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);

Answer: C

NEW QUESTION 2
You create a sequence as follows: create sequence seq1 start with 1;
After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated.
How can you do this?

• A. You must drop and re-create the sequence.
• B. You can'
• C. Under no circumstances can numbers from a sequence be reissued once they have been used.
• D. Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1.
• E. Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.

Answer: A

Explanation: It is not possible to change the next value of a sequence, so you must re-create it.

NEW QUESTION 3
This statement will fail:
create unique bitmap index on employees(department_id,hire_date); Why?

• A. Bitmap indexes cannot be unique.
• B. The two columns are of different data types.
• C. A bitmap index can be on only one column.
• D. There is already a B*Tree index on DEPARTMENT_ID.

Answer: A

NEW QUESTION 4
Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

• A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
• B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
• C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
• D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
• E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
• F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

Answer: C

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

Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

• A. listing of customers who do not have a credit limit and were born before 1980
• B. finding the number of customers, in each city, whose marital status is 'married'
• C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
• E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

Answer: DE

Explanation: Describe the Types of Problems That the Subqueries Can Solve
There are many situations where you will need the result of one query as the input for another.
Use of a Subquery Result Set for Comparison Purposes
Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements:
select avg(salary) from employees;
select last_name from employees where salary < result_of_previous_query ; Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison with the rows retrieved by
the parent query.
The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in (select distinct(department_id) from employees);

NEW QUESTION 6
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 results.
• B. Only the first query executes successfully but gives a wrong result.
• C. Only the first query executes successfully and gives the correct result.
• D. Only the second query executes successfully but gives a wrong result.
• E. Only the second query executes successfully and gives the correct result.

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 7
See the structure of the PROGRAMS table:

Which two SQL statements would execute successfully? (Choose two.)

• A. SELECT NVL(ADD_MONTHS(END_DATE, 1), SYSDATE)FROM programs;
• B. SELECT TO_DATE(NVL(SYSDATE-END_DATE, SYSDATE))FROM programs;
• C. SELECT NVL(MONTHS_BETWEEN(start_date, end_date), 'Ongoing')FROM programs;
• D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date, end_date)), 'Ongoing') FROM programs;

Answer: AD

Explanation: NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number. Data types must match:
– NVL(commission_pct, 0)
– NVL(hire_date, '01-JAN-97')
– NVL(job_id, 'No Job Yet')
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.
MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes - numeric and strings, which is not possible!
The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

NEW QUESTION 8
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60)
Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||', '||e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
• D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);

Answer: A

Explanation: The correct statement for MERGE is MERGE INTO table_name
Incorrect
B- Wrong statement with the keyword EXISTS
C- Wrong statement with the keyword EXISTS
D- Wrong statement on the MERGE new_employees
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

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

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_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 1 DESC;
• B. SELECT promo_name, promo_begin_date "START DATE"FROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY "START DATE" DESC;
• C. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 2 DESC;
• D. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY promo_name DESC;

Answer: BC

NEW QUESTION 10
You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.

Evaluate the following SQL statement: Exhibit:

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

• A. It would execute and restrict modifications to only the column specified in the SELECT statement
• B. It would not execute because two tables cannot be used in a single UPDATE statement
• C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
• D. It would not execute because the SELECT statement cannot be used in place of the table name

Answer: A

NEW QUESTION 11
You are granted the CREATE VIEW privilege. What does this allow you to do?

• A. Create a table view.
• B. Create a view in any schema.
• C. Create a view in your schema.
• D. Create a sequence view in any schema.
• E. Create a view that is accessible by everyone.
• F. Create a view only of it is based on tables that you created.

Answer: C

Explanation: You can create a view in your own schema only if you are granted the CREATE VIEW privilege.
Incorrect Answers
A:. You can create a view in your own schema only.
B:. You can create a view in your own schema only, not in any schema. D:. There is no sequence view in Oracle.
E:. You cannot create a view that is accessible by everyone. You will need specially grant SELECT privileges on this view for everyone.
F:. You can create a view in your own schema, but not only for tables in your schema. You can use object from other users schemas if you have privileges to retrieve data from them.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301 Chapter 7: Creating Other Database Objects in Oracle

NEW QUESTION 12
You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’.
Which SQL statement would give the required result?

• A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL;
• B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
• C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
• D. SELECT TO_DATE(TO_CHAR('11-oct-2007', 'fmDdspth ''of'' Month, Year')) FROM DUAL;

Answer: C

Explanation: Using the TO_CHAR Function with Dates
TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for
the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the
TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines
• The format model must be enclosed with single quotation marks and is case-sensitive.
• The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
• The names of days and months in the output are automatically padded with blanks.
• To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model
---------------------------------------------------------------------
DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)

NEW QUESTION 13
In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:

What would be the outcome?

• A. Abigail PA
• B. Abigail Pa
• C. Abigail IS
• D. An error message

Answer: B

NEW QUESTION 14
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)
SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?

• A. It would not execute because two tables cannot be used in a single UPDATE statement.
• B. It would not execute because the SELECT statement cannot be used in place of the table name.
• C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
• D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

Answer: C

Explanation: One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.

NEW QUESTION 15
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code" FROM promotions ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost "code" FROM promotions ORDER BY 1;
Which statement is true regarding the execution of the above queries?

• A. Only the first query executes successfully.
• B. Only the second query executes successfully.
• C. Both queries execute successfully but give different results.
• D. Both queries execute successfully and give the same result.

Answer: B

NEW QUESTION 16
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?

• A. The two statements produce identical results.
• B. The second statement returns a syntax error.
• C. There is no need to specify DESC because the results are sorted in descending order by default.
• D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Answer: A

Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect
B- there is no syntax error
C- result are sorted in ascending order by default
D- ORDER BY 2 will take the second column as sorting column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

NEW QUESTION 17
Which two statements are true regarding savepoints? (Choose two.)

• A. Savepoints are effective only for COMMIT.
• B. Savepoints may be used to ROLLBACK.
• C. Savepoints can be used for only DML statements.
• D. Savepoints are effective for both COMMIT and ROLLBACK.
• E. Savepoints can be used for both DML and DDL statements.

Answer: BC

NEW QUESTION 18
You need to create a table named ORDERS that contain four columns:
1. AN ORDER_ID column of number data type
2. A CUSTOMER_ID column of number data type
3. AN ORDER_STATUS column that contains a character data type
4. A DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead.
Which statement accomplishes this?

• A. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
• B. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
• C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
• D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
• E. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE);
• F. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE DEFAULT SYSDATE);

Answer: B

Explanation: Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.

NEW QUESTION 19
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name" FROM customers
WHERE country_id = 10 UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers WHERE country_id = 30;
Which ORDER BY clause are valid for the above query? (Choose all that apply.)

• A. ORDER BY 2, 1
• B. ORDER BY CUST_NO
• C. ORDER BY 2, cust_id
• D. ORDER BY "CUST_NO"
• E. ORDER BY "Last Name"

Answer: AC

Explanation: Using the ORDER BY Clause in Set Operations
- The ORDER BY clause can appear only once at the end of the compound query.
- Component queries cannot have individual ORDER BY clauses.
- The ORDER BY clause recognizes only the columns of the first SELECT query.
- By default, the first column of the first SELECT query is used to sort the output in an ascending order.