NEW QUESTION 1
Examine the structure of the PRODUCTS table:

You want to change the definition of the PRODUCTS table. The PROD_DETAILS column must be changed to allow 4000 characters.
Which statement is valid?

• A. ALTER TABLE productsMODIFY (prod_details CHAR2 (4000));
• B. ALTER TABLE productsMODIFY COLUMN (prod_details CHAR (4000));
• C. ALTER TABLE productsCHANGE (prod_details VARCHAR2 (4000));
• D. ALTER TABLE productsMODIFY (prod_details VARCHAR2 (4000));

Answer: B

NEW QUESTION 2
Which SQL statement displays the date March 19, 2001 in a format that appears as “Nineteenth of March 2001 12:00:00 AM”?

• A. SELECTTO_CHAR(TO_DATE('19-Mar-2001’, ‘DD-Mon-YYYY’), ‘fmDdspth“of” Month YYYY fmHH:MI:SS AM’) NEW_DAT
• B. FROM dual;
• C. SELECTTO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY’), ‘Ddspth“of” Month YYYY fmHH:MI:SS AM’) NEW_DATEFROM dual;
• D. SELECTTO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYYHH:MI:SS AM’) NEW_DATE FROM dual;
• E. SELECTTO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY), ‘fmDdspth “of” Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual;

Answer: A

NEW QUESTION 3
You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
• B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;

Answer: B

Explanation: function MAX(column_name)
Incorrect
A- invalid statement
C- inner query return more than one line
D- column maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

NEW QUESTION 4
Which two statements are true regarding constraints?

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

Answer: BD

Explanation: B: Any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything.
D: Constraints can be added, dropped, enabled, disabled, or validated. DISABLE allows incoming data, regardless of whether it conforms to the constraint
Incorrect:
Not A: The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null.

NEW QUESTION 5
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_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);
• B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM 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_chargeFROM 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_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);

Answer: B

NEW QUESTION 6
Consider these three statements:
create synonym s1 for employees; create public synonym s1 for departments; select * from s1;
Which of the following statements is correct?

• A. The second statement will fail because an object S1 already exists.
• B. The third statement will show the contents of EMPLOYEES.
• C. The third statement will show the contents of DEPARTMENTS.
• D. The third statement will show the contents of the table S1, if such a table exists in the current schema.

Answer: B

Explanation: The order of priority is to search the schema namespace before the public namespace, so it will be the private synonym (to EMPLOYEES) that will be found.

NEW QUESTION 7
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 output.
• B. It gives an error because the HAVING clause is not valid.
• C. It executes successfully but does not give the correct output.
• D. It gives an error because the GROUP BY expression is not valid.

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.

NEW QUESTION 8
The user Sue issues this SQL statement:
GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;
The user Alice issues this SQL statement:
GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement: GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

• A. Alice only
• B. Alice and Reena
• C. Alice, Reena, and Timber
• D. Sue, Alice, Reena, and Timber

Answer: C

Explanation: use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked.
Alice, Reena and Timber will be revoke.
Incorrect
A- the correct answer should be Alice, Reena and Timber
B- the correct answer should be Alice, Reena and Timber
D- the correct answer should be Alice, Reena and Timber
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-17

NEW QUESTION 9
You need to list the employees in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE.
Examine the sample output:

Which query will provide the required output?

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

Answer: B

NEW QUESTION 10
Examine the structure of the INVOICE table: Exhibit:

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

• A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
• B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
• C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
• D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Answer: AC

Explanation: The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not.
5-6 The NVL function
If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG.
They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

NEW QUESTION 11
Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL;
What would be the outcome?

• A. 16
• B. 100
• C. 160
• D. 200
• E. 150

Answer: C

Explanation: Function Purpose
ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)
TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

NEW QUESTION 12
You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?

• A. Compare Period ADDM report
• B. AWR Compare Period report
• C. Active Session History (ASH) report
• D. Taking a new snapshot and comparing it with a preserved snapshot

Answer: B

Explanation: The awrddrpt.sql report is the Automated Workload Repository Compare Period Report. The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory. Incorrect:
Not A:Compare Period ADDM
Use this report to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. Only workload replays that contain at least 5 minutes of database time can be compared using this report.

NEW QUESTION 13
Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

• A. First normal form
• B. Second normal form
• C. Third normal form
• D. Fourth normal form

Answer: B

Explanation: According to the Second Normal Form (2NF) there must be no partial dependencies on a concatenated key.

NEW QUESTION 14
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
A- EMPLOYEE_ID cannot be null
B- EMPLOYEE_ID cannot be null
D= mismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11

NEW QUESTION 15
Evaluate the following SQL statement:

Which statement is true regarding the above query if one of the values generated by the subquery is null?

• A. It produces an error.
• B. It executes but returns no rows.
• C. It generates output for null as well as the other values produced by the subquery.
• D. It ignores the null value and generates output for the other values produced by the subquery.

Answer: D

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

• A. no privileges
• B. only the SELECT privilege
• C. only the CONNECT privilege
• D. all the privileges of a default user

Answer: A

Explanation: when a user is created, by default no privilege is granted
Incorrect
B- SELECT is not grant
C- CONNECT is not grant
D- default profile is grant by default not privilege.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-6

NEW QUESTION 17
Evaluate the following SQL statement:

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

• A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
• B. It produces an error because positional notation cannot be used in the order by clause with set operators.
• C. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.
• D. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.

Answer: D

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

Evaluate the following query:

Which statement is true regarding the above query?

• A. It executes successfully.
• B. It produces an error because the condition on the CUST_CITY column is not valid.
• C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
• D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.

Answer: A

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 result.
• B. Only statement 2 executes successfully and gives the required result.
• C. Both statements 1 and 2 execute successfully and give different results.
• D. Both statements 1 and 2 execute successfully and give the same required result.

Answer: D