NEW QUESTION 1
The SQL statements executed in a user session as follows:

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

• A. Both the DELETE statements and the UPDATE statement are rolled back
• B. The rollback generates an error
• C. Only the DELETE statements are rolled back
• D. Only the seconds DELETE statement is rolled back
• E. No SQL statements are rolled back

Answer: BE

NEW QUESTION 2
You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement
creates the foreign key?

• A. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
• B. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
• C. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT FOREIGN KEY (student_id) REFERENCESstudents(student_id));
• D. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Answer: D

Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);
Incorrect
A- invalid syntax
B- invalid syntax
C- invalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14

NEW QUESTION 3
Examine the structure of the EMPLOYEES table:

Which UPDATE statement is valid?

• A. UPDATE employeesSET first_name = ‘John’SET last_name = ‘Smith’WHERE employee_id = 180;
• B. UPDATE employeesSET first_name = ‘John’,SET last_name = ‘Smoth’WHERE employee_id = 180;
• C. UPDATE employeeSET first_name = ‘John’AND last_name = ‘Smith’WHERE employee_id = 180;
• D. UPDATE employeeSET first_name = ‘John’, last_name = ‘Smith’WHERE employee_id= 180;

Answer: D

NEW QUESTION 4
Which two statements are true regarding the ORDER BY clause? (Choose two.)

• A. It is executed first in the query execution.
• B. It must be the last clause in the SELECT statement.
• C. It cannot be used in a SELECT statement containing a HAVING clause.
• D. You cannot specify a column name followed by an expression in this clause.
• E. You can specify a combination of numeric positions and column names in this clause.

Answer: BE

NEW QUESTION 5
You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
What happens when you execute this DELETE statement? DELETE employees;

• A. You get an error because of a primary key violation.
• B. The data and structure of the EMPLOYEES table are deleted.
• C. The data in the EMPLOYEES table is deleted but not the structure.
• D. You get an error because the statement is not syntactically correct.

Answer: C

Explanation: You can remove existing rows from a table by using the DELETE statement.
DELETE [FROM] table
[WHERE. condition];
Incorrect
A- Statement will not cause error
B- Delete statement will not delete the table structure
D- Statement will not cause error
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19

NEW QUESTION 6
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd "is the first
Monday for 'fmmonth rrrr') FROM DUAL; What is the outcome?

• A. It executes successfully and returns the correct result.
• B. It executes successfully but does not return the correct result.
• C. It generates an error because TO_CHAR should be replaced with TO_DATE.
• D. It generates an error because rrrr should be replaced by rr in the format string.
• E. It generates an error because fm and double quotation marks should not be used in the format string.

Answer: A

Explanation: • NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
• LAST_DAY(date): Finds the date of the last day of the month that contains date
The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

NEW QUESTION 7
You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?

• A. DATE
• B. NUMBER
• C. TIMESTAMP
• D. INTERVAL DAY TO SECOND
• E. INTERVAL YEAR TO MONTH

Answer: D

Explanation: INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.
You can perform a number of arithmetic operations on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH)
data.
– It stores duration of the credit as days
– The format stored is numeric format, and you know that numeric values can be easily added and subtracted with date data type without using conversion functions (i.e. SELECT SYSDATE – 1 FROM DUAL;)
– The interest has to be calculated for the number of days an individual has taken a credit for, so it will be easy to calculate interest by using the interest rate and duration of the the credit which is numeric format.

NEW QUESTION 8
Which three are true? (Choose three.)

• A. A MERGE statement is used to merge the data of one table with data from another.
• B. A MERGE statement replaces the data of one table with that of another.
• C. A MERGE statement can be used to insert new rows into a table.
• D. A MERGE statement can be used to update existing rows in a table.

Answer: ACD

Explanation: The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table

NEW QUESTION 9
Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
------------------------------------------- 1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query: SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

• A. It gives an error because the HAVING clause should be specified after the GROUP BYclause.
• B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
• C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
• D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

Answer: C

NEW QUESTION 10
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001. Which WHERE clause would give the required result?

• A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
• C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')

Answer: C

NEW QUESTION 11
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 12
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:
SQL>SELECT c1.custname, c1.city
FROM Customers c1 Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A. JOIN
• B. NATURAL JOIN
• C. LEFT OUTER JOIN
• D. FULL OUTER JOIN
• E. RIGHT OUTER JOIN

Answer: AE

NEW QUESTION 13
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 14
View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

• A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;
• B. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id <= SYSDATE - 2*365WITH CHECK OPTION;
• C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id<= SYSDATE - 2*365WITH CHECK OPTION;
• D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time_id <= SYSDATE - 2*365GROUP BY prod_id, cust_idWITH CHECK OPTION;

Answer: AB

Explanation: Creating a View
You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];
OR REPLACE Re-creates the view if it already exists
FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.)
View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.)
subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.)
WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint
WITH READ ONLY Ensures that no DML operations can be performed on this view 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 – ANSWER C

NEW QUESTION 15
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

• A. DROP emp_dept_vu;
• B. DELETE emp_dept_vu;
• C. REMOVE emp_dept_vu;
• D. DROP VIEW emp_dept_vu;
• E. DELETE VIEW emp_dept_vu;
• F. REMOVE VIEW emp_dept_vu;

Answer: D

Explanation: DROP VIEW viewname;
Incorrect
A- Not a valid drop view statement
B- Not a valid drop view statement
C- Not a valid drop view statement
E- Not a valid drop view statement
F- Not a valid drop view statement
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20

NEW QUESTION 16
Examine the following query:

What is the output of this query?

• A. It displays 5 percent of the products with the highest amount sold.
• B. It displays the first 5 percent of the rows from the SALES table.
• C. It displays 5 percent of the products with the lowest amount sold.
• D. It results in an error because the ORDER BY clause should be the last clause.

Answer: C

NEW QUESTION 17
View the Exhibit and examine the structure of the products table.

Evaluate the following query:

What would be the outcome of executing the above SQL statement?

• A. It produces an error.
• B. It shows the names of all products in the table.
• C. It shows the names of products whose list price is the second highest in the table.
• D. It shows the names of all products whose list price is less than the maximum list price.

Answer: C

NEW QUESTION 18
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

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

• A. It executes successfully.
• B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
• C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
• D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

Answer: A

NEW QUESTION 19
You need to display the date ll-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven'.
Which SQL statement would give the required result?

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

Answer: C

Explanation: The '11-oct-2007' is a string not a date. We need to user the TO_DATE function and convert it to a date.