NEW QUESTION NO: 10
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)

A. CREATE INDEX ord_idx1
ON ord(ord_no);
B. CREATE INDEX ord_idx2
ON ord_items(ord_no);
C. CREATE INDEX ord_idx3
ON ord_items(item_no);
D. CREATE INDEX ord_idx4
ON ord, ord_items(ord_no, ord_date, qty);
Answer: B,C
Explanation/Reference:
Explanation:
How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval.
Note: You can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index.

NEW QUESTION NO: 11
For which action can you use the TO_DATE function?
A. Convert any numeric literal to a date
B. Convert any date to a character literal
C. Format '10-JAN-99' to 'January 10 1999'
D. Convert any date literal to a date
E. Convert any character literal to a date
Answer: E

NEW QUESTION NO: 12
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 v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE - 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;
Answer: A,B
Explanation/Reference:
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 NO: 13
Examine the structure of the EMPLOYEES table:

Which INSERT statement is valid?
A. INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, 'John', 'Smith', '01/01/01');
B. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, 'John', 'Smith', '01 January 01');
C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, 'John', 'Smith', To_date('01/01/01'));
D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, 'John', 'Smith', 01-Jan-01);
Answer: D
Explanation/Reference:
Explanation: It is the only statement that has a valid date; all other will result in an error.
Answer A is incorrect, syntax error, invalid date format

NEW QUESTION NO: 14
View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the promotions table, and the required output format.

Which two queries give the correct result?

A. Option B
B. Option C
C. Option D
D. Option A
Answer: B,C

NEW QUESTION NO: 15
View the Exhibit and examine the data in the products table.

You need to display product names from the products table that belong to the 'software/other' category with minimum prices as either S2000 or S4000 and no unit of measure.
You issue the following query:

Which statement is true regarding the above query?
A. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
B. It executes successfully but returns no result.
C. It executes successfully and returns the required result.
D. It generates an error because the condition specified for the prod category column is not valid.
Answer: B

NEW QUESTION NO: 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. finding the total commission earned by the employees in department 10
B. listing the employees whose annual commission is more than 6000
C. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
D. listing the departments whose average commission is more that 600
E. listing the employees who earn the same amount of commission as employee 3
F. finding the number of employees who earn a commission that is higher than the average commission of the company
Answer: E,F

NEW QUESTION NO: 17
Examine the structure of the customers table:

CUSTNO is the primary key in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result?
A. Full outer-join with self-join
B. Right outer-join with self-join
C. Left outer-join with self-join
D. Subquery
E. Self-join
Answer: D,E

NEW QUESTION NO: 18
You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Explanation/Reference:
Explanation:
A column alias:
- Renames a column heading
- Is useful with calculations
- Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
- Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.

NEW QUESTION NO: 19
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
B. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
C. SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
D. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
Answer: A,D

NEW QUESTION NO: 20
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/Reference:
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 '\';