Which two statements best describe the benefits of using the WITH clause? (Choose two.)
Answer : C,D
View the Exhibit and examine the description of the ORDERS table.
The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose?
Answer : D
View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id, LEVEL
FROM employees -
START WITH employee_id = 101 -
CONNECT BY PRIOR employee_id=manager_id ;
Which two statements are true regarding the output of this command? (Choose two.)
Answer : A,D
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)
Answer : B,C
View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review"
FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.
The review date is the first Monday after the completion of six months of the hiring. The
NLS_TERRITORY parameter is set to AMERICA in the session.
Which statement is true regarding this query?
Answer : C
View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables.
You want to display all the cities that have no departments and the departments that have not been allocated cities.
Which type of join between DEPARTMENTS and LOCATIONS tables would produce this information as part of its output?
Answer : B
View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city -
FROM locations -
I WHERE NOT EXISTS (SELECT location_id
FROM departments -
WHERE location_id <> I. location_id);
This statement was written to display LOCATIONJD and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?
Answer : C
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items -
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
Answer : A
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price -
FROM product_information -
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?
Answer : A
Given below is a list of functions and the tasks performed by using these functions, in random order.
Function Usage -
1) LPAD a) Used to truncate a column, expression, or value to n decimal places 2) TRUNC b) Used to remove heading or trailing or both characters from the character string 3)
DECODE c) Pads the character value right-justified to a total width of n character positions
4) TRIM d) Used to return the numeric value for position of a named character from the character string 5) INSTR e) Used to translate an expression after comparing it with each search value
Which option correctly matches the function names with their usage?
Answer : D
Which statements are true regarding the hierarchical query in Oracle Database 10g?
(Choose all that apply.
Answer : B,C
View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city -
FROM locations -
SELECT location_id, city -
FROM locations JOIN departments -
USING(location_id);
Answer : C
The details of the order ID, order date, order total, and customer ID are obtained from the
ORDERS table. If the order value is more than 30000, the details have to be added to the
LARGE_DRDERS table. The order ID, order date, and order total should be added to the
ORDER_HISTORY table, and order ID and customer ID should be added to the
CUST_HISTORY table. Which multitable INSERT statement would you use?
Answer : C
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?
Answer : A
View the Exhibit and examine the structure of the ORDERS table. The columns
ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
Answer : C,D