NEW QUESTION 1
Which three statements are true regarding sub queries? (Choose three.)

• A. Multiple columns or expressions can be compared between the main query and sub query
• B. Main query and sub query can get data from different tables
• C. Sub queries can contain GROUP BY and ORDER BY clauses
• D. Main query and sub query must get data from the same tables
• E. Sub queries can contain ORDER BY but not the GROUP BY clause
• F. Only one column or expression can be compared between the main query and subqeury

Answer: ABC

NEW QUESTION 2
View the Exhibits and examine the structures of the products and sales tables.


Which two SQL statements would give the same output?

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

Answer: AC

Explanation: INTERSECT and JOIN works on comparable columns.

NEW QUESTION 3
Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

• A. SELECT . student_ id, marks, ROWNUM "Rank"FROM . studentsWHERE . ROWNUM<= 10AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-99AND . course_id = 'INT_SQL'ORDER BY . marks DESC;
• B. SELECT . student_id, marks, ROWID "Rank". FROM . student
• C. WHERE . ROWID <=10. AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'. AND . course_id = 'INT_SQL'. ORDER BY . marks;
• D. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, mark
• E. FROM . student
• F. WHERE . ROWNUM <= 10. AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-. 99'. AND . course_id = 'INT_SQL'. ORDER BY . marks DESC);
• G. SELECT . student_id, marks, ROWNUM "Rank”. FROM . (SELECT student_id, mark
• H. FROM . student
• I. WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’. AND course_id = ‘INT_SQL’. ORDER BY marks DESC). WHERE . ROWNUM <= 10 ;.
• J. SELEC
• K. student id, marks, ROWNUM “Rank”. FRO
• L. (SELECT student_id, mark
• M. FROM student
• N. ORDER BY marks). WHER
• O. ROWNUM <= 10. AN
• P. finish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’. AN
• Q. course_id = ‘INT_SQL’;

Answer: D

NEW QUESTION 4
What does the FORCE option for creating a view do?

• A. creates a view with constraints
• B. creates a view even if the underlying parent table has constraints
• C. creates a view in another schema even if you don't have privileges
• D. creates a view regardless of whether or not the base tables exist

Answer: D

Explanation: create a view regardless of whether or not the base tables exist.
Incorrect
A- the option is not valid
B- the option is not valid
C- the option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3

NEW QUESTION 5
Which two statements are true regarding indexes? (Choose two.)

• A. They can be created on tables and clusters.
• B. They can be created on tables and simple views.
• C. You can create only one index by using the same columns.
• D. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.

Answer: AD

NEW QUESTION 6
Evaluate the SQL statement DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four.)

• A. You cannot roll back this statement.
• B. All pending transactions are committed.
• C. All views based on the DEPT table are deleted.
• D. All indexes based on the DEPT table are dropped.
• E. All data in the table is deleted, and the table structure is also deleted.
• F. All data in the table is deleted, but the structure of the table is retained.
• G. All synonyms based on the DEPT table are deleted.

Answer: ABDE

Explanation: You cannot roll back DROP TABLE statement. All pending transactions related on this table are committed. If the table is dropped, Oracle automatically drops any index, trigger and constraint associated with the table as well. All data in the table is deleted, and the table structure is also deleted.
Incorrect Answers
C:. All views based on the DEPT table become invalid, but they are not deleted.
F:. All data in the table is deleted, and the table structure is also deleted. Command TRUNCATE deletes all data in the table, but does not delete the structure of the table. G:. All synonyms based on the DEPT table are not deleted after dropping the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 7
Examine the types and examples of relationships that follow:
1. One-to-one a) Teacher to students
2. One-to-many b) Employees to Manager
3. Many-to-one c) Person to SSN
4. Many-to-many d) Customers to products
Which option indicates the correctly matched relationships?

• A. 1-a, 2-b, 3-c, and 4-d
• B. 1-c, 2-d, 3-a, and 4-b
• C. 1-c, 2-a, 3-b, and 4-d
• D. 1-d, 2-b, 3-a, and 4-c

Answer: C

NEW QUESTION 8
What is true about sequences?

• A. Once created, a sequence belongs to a specific schema.
• B. Once created, a sequence is linked to a specific table.
• C. Once created, a sequence is automatically available to all users.
• D. Only the DBA can control which sequence is used by a certain table.
• E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.

Answer: A

NEW QUESTION 9
Which four are types of functions available in SQL? (Choose 4)

• A. string
• B. character
• C. integer
• D. calendar
• E. numeric
• F. translation
• G. date
• H. conversion

Answer: BEGH

Explanation: SQL have character, numeric, date, conversion function.
Incorrect
A- SQL have character, numeric, date, conversion function.
C- SQL have character, numeric, date, conversion function.
D- SQL have character, numeric, date, conversion function.
F- QL have character, numeric, date, conversion function.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3

NEW QUESTION 10
Which statement is true regarding the default behavior of the order by clause?

• A. In a character sort, the values are case-sensitive.
• B. NULL values are not considered at all by the sort operation.
• C. Only those columns that are specified in the select list can be used in the order by clause.
• D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Answer: A

Explanation: Character Strings and Dates
Character strings and date values are enclosed with single quotation marks. Character values are case-sensitive and date values are format-sensitive. The default date display format is DD-MON-RR.

NEW QUESTION 11
The PART_CODE column in the SPARES table contains the following list of values:

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

• A. It produces an error.
• B. It displays all values.
• C. It displays only the values A%_WQ123 and AB_WQ123 .
• D. It displays only the values A%_WQ123 and A%BWQ123 .
• E. It displays only the values A%BWQ123 and AB_WQ123.

Answer: D

Explanation: Combining Wildcard Characters
The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter “o” as the second character.
ESCAPE Identifier
When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement: SELECT employee_id, last_name, job_id
FROM employees WHERE job_id LIKE '%SA_%' ESCAPE '';

NEW QUESTION 12
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12) SEMESTER_END DATE
GPA NUMBER(4, 3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

• A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
• B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
• C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
• D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
• E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
• F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

Answer: F

Explanation: Sorted by highest to lowest is DESCENDING order
Incorrect
A- result in ascending order
B- wrong syntax with SORT keyword
C- wrong syntax with SORT keyword
D- default value for ORDER by is in ascending order
E- wrong syntax with SORT keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

NEW QUESTION 13
Evaluate the following SQL statements: Exhibit:

Which is the correct output of the above query?

• A. +00-300, +54-02, +00 11:12:10.123457
• B. +00-300, +00-650, +00 11:12:10.123457
• C. +25-00, +54-02, +00 11:12:10.123457
• D. +25-00, +00-650, +00 11:12:10.123457

Answer: C

NEW QUESTION 14
Examine the structure of the EMPLOYEES table:

You want to generate a report that contains the department IDs and last names of employees ordered by hire date in their respective departments. Employees from one department must appear in a single row.
Which query will provide the required output?

• A. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP (ORDER BY hire_date)” Employees”FROM employeesGROUP BY department_idORDER BY department_id;
• B. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP (ORDER BY hire_date)” Employees”FROM employeesGROUP BY department_idORDER BY hire_date;
• C. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP (ORDER BY hire_date)” Employees”)FROM employeesGROUP BY department_idORDER BY hire_date;
• D. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP ORDER BY (hire_date)” Employees”)FROM employeesGROUP BY department_idORDER BY department_id;

Answer: C

NEW QUESTION 15
Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

• A. DELETE can be used to remove only rows from only one table at a time.
• B. DELETE can be used to remove only rows from multiple tables at a time.
• C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
• D. DELETE can be used to remove data from specific columns as well as complete rows.
• E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.

Answer: AE

Explanation: Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed.
The Transaction Control Statements
A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables: a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT
Explicit Transaction Control Statements
You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements.
Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.

NEW QUESTION 16
Examine the data in the CUST_NAME column of the customers table.

You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?

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

Answer: B

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

• A. A sub query that defines a view cannot include the GROUP BY clause
• B. A view is created with the sub query having the DISTINCT keyword can be updated
• C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
• D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Answer: CD

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 18
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 19
Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced.
What happens when you run the statement a second time?

• A. An error is returned.
• B. You are prompted to enter a new value.
• C. A report is produced that matches the first report produced.
• D. You are asked whether you want a new value or if you want to run the report based on the previous value.

Answer: C

Explanation: Use the double-ampersand if you want to reuse the variable value without prompting the user each time.
Incorrect
A- is not an error
B- && will not prompt user for second time
D- && will not ask the user for new value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13