Oracle Database SQL Expert v3.8 (1z0-047)

Page:    1 / 18   
Total 260 questions

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

  • A. It enables users to store the results of a query permanently.
  • B. It enables users to store the query block permanently in the memory and use it to create complex queries.
  • C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
  • D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace.


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?

  • A. SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders " HAVING COUNT(customer_id) > 10;
  • B. SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders " WHERE COUNT(customer_id) > 10 GROUP BY sales_rep_id;
  • C. SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders " GROUP BY sales_rep_id HAVING total > 10;
  • D. SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders " GROUP BY sales_rep_id HAVING COUNT(customer_id) > 10;


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.)

  • A. The output would be in top-down hierarchy starting with EMPLOYEE_ID having value 101.
  • B. The output would be in bottom-up hierarchy starting with EMPLOYEE_ID having value 101.
  • C. The LEVEL column displays the number of employees in the hierarchy under the employee having the EMPLOYEE_ID 101.
  • D. The LEVEL column displays the level in the hierarchy at which the employee is placed under the employee having the EMPLOYEE_ID 101


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.)

  • A. CREATE INDEX ord_idx ON ord(ord_no);
  • B. CREATE INDEX ord_idx ON ord_items(ord_no);
  • C. CREATE INDEX ord_idx ON ord_items(item_no);
  • D. CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty);


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?

  • A. The query would execute to give the desired output.
  • B. The query would not execute because date functions cannot be nested.
  • C. The query would execute but the output would give review dates that are Sundays.
  • D. The query would not execute because the NEXT_DAY function accepts a string as argument.


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?

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


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?

  • A. The statement would execute and would return the desired results.
  • B. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.
  • C. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.
  • D. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.


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?

  • A. The statement would execute successfully to produce the required output.
  • B. The statement would not execute because inline views and outer joins cannot be used together.
  • C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
  • D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.


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?

  • A. It would execute but the output would return no rows.
  • B. It would execute and the output would display the desired result.
  • C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
  • D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.


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?

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


Answer : D

Which statements are true regarding the hierarchical query in Oracle Database 10g?
(Choose all that apply.

  • A. It is possible to retrieve data only in top-down hierarchy.
  • B. It is possible to retrieve data in top-down or bottom-up hierarchy.
  • C. It is possible to remove an entire branch from the output of the hierarchical query.
  • D. You cannot specify conditions when you retrieve data by using a hierarchical query.


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);

  • A. UNION
  • B. MINUS
  • C. INTERSECT
  • D. UNION ALL


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?

  • A. Pivoting INSERT
  • B. Unconditional INSERT
  • C. Conditional ALL INSERT
  • D. Conditional FIRST INSERT


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?

  • A. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  • B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total) VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);
  • C. INSERT INTO orders (order_id.order_date.order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit _limit=600) .order_total) VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);
  • D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND Credit_limit=600), 1000);


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.)

  • A. INSERT INTO orders VALUES (1, O9-mar-2007', 'online',",1000);
  • B. INSERT INTO orders (order_id ,order_date ,order_mode, customer_id ,order_total) VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);
  • C. INSERT INTO (SELECT order_id ,order_date .customer_id FROM orders) VALUES (1,O9-mar-2007', 101);
  • D. INSERT INTO orders VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);
  • E. INSERT INTO orders (order_id ,order_date ,order_mode .order_total) VALUES (1 ,'10-mar-2007','online',1000);


Answer : C,D

Page:    1 / 18   
Total 260 questions