1z0-047 | All About Pinpoint 1z0-047 examcollection
Q131. 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. Itwould executebut theoutput would return no rows.
B. It would execute and the outputwould displaythedesired result.
C. It wouldnotexecute because the entireWHEREclause conditionisnot enclosedwithinthe parentheses.
D. Itwould not execute becausethesame column has been used in both sidesoftheANDlogical operatortoform the condition.
Answer: A
Q132. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders) AND
Customer_id = 101;
B. SELECT order_id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT order_date
FROM orders
WHERE customer_id = 101);
D. SELECT order_id, order_date FROM orders
WHERE order_date IN (SELECT order_date
FROM orders
WHERE customer id = 101);
Answer: C
Q133. View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?
A. ALTER TABLE products
ADD price NUMBER(8,2) NOT NULL;
B. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT NOT NULL;
C. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
D. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
Answer: C
Q134. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.)
A. 'os'
B. local
C. -8:00'
D. dbtimezone Li
E. 'Australia'
Answer: BCD
Q135. View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT phone_number,
REGEXP_REPLACE(phone_number,'([[: digit: ]]{3}).([[: digit: ]]{3}).([[: digit: ]]{4})', ,(1)2-3')
"PHONE NUMBER"
FROM employees;
The query was written to format the PHONE_NUMBER for the employees. Which option would be the correct format in the output?
A. xxx-xxx-xxxx
B. (xxx) xxxxxxx
C. (xxx) xxx-xxxx
D. xxx-(xxx)-xxxx
Answer: C
Q136. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
A. It would return the subtotals for the Order Amount of every CUSTOMER_ID.
B. It would return the subtotals for the Order Amount for every PRODUCT_ID.
C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.
D. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
E. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
Answer: ABD
Q137. View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.
You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID. The following SQL statement was written:
SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department" FROM employees m JOIN employees e
ON (m.employee_id = e.manager_id)
WHERE e.manager_id=100
JOIN departments d
ON (e.department_id = d.department_id);
Which statement is true regarding the output of this SQL statement?
A. The statementwouldprovide the desired results.
B. Thestatement wouldnotexecute because the ON clause iswritten twice.
C. Thestatement wouldnot execute because the WHERE clause is wrongly placed.
D. The statement wouldnot execute because the self join usestheON clauseinsteadof the USING clause.
Answer: C
Q138. 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
Q139. Which two statements are true about sequences created in a single instance database? (Choose two.)
A. The numbers generated by a sequence can be used only for one table.
B. DELETE <sequencename> would remove a sequence from the database.
C. CURRVAL is used to refer to the last sequence number that has been generated.
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
Answer: CD
Q140. Which three statements are true regarding single-row functions? (Choose three.)
A. They can accept only one argument.
B. They can be nested up to only two levels.
C. They can return multiple values of more than one data type.
D. They can be used in SELECT, WHERE, and ORDER BY clauses.
E. They can modify the data type of the argument that is referenced.
F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments.
Answer: DEF