Precise of oracle database sql expert 1z0 047 exam cram materials and bundle for Oracle certification for customers, Real Success Guaranteed with Updated oracle database sql expert 1z0 047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1z0-047 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1z0-047 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1z0-047-exam-dumps.html
Q51. 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. CREATEINDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty);
Answer: BC
Q52. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
Evaluate the following UPDATE statement:
UPDATE
(SELECT order_date, order_total, customer_id
FROM orders)
SET order_date = 22-mar-2007'
WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts'AND Credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
A. Itwouldnot execute becausetwotables cannot be usedin a single UPDATEstatement.
B. It wouldexecuteandrestrict modifications toonly thecolumns specifiedin theSELECT statement.
C. It wouldnotexecute becauseasubquery cannot be used in the WHERE clause ofanUPDATE statement.
D. Itwould not execute becausetheSELECTstatementcannot be used in place of the table name.
Answer: B
Q53. View the Exhibit and examine the description of the CUSTOMERS table.
You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.
Which SOL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name1'^A-Z’))NOVALIDATE;
B. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]’))NOVALIDATE;
C. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]’))NOVALIDATE;
D. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit: ]]’))NOVALIDATE ;
Answer: C
Q54. Evaluate the SQL statements:
CREATE TABLE new_order
(orderno NUMBER(4),
booking_date TIMESTAMP WITH LOCAL TIME ZONE);
The database is located in San Francisco where the time zone is -8:00.
The user is located in New York where the time zone is -5:00.
A New York user inserts the following record:
INSERT INTO new_order
VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00");
Which statement is true?
A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.
C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000'
D. When the San Francisco user selectstherow,booking_dateis displayed
as 007-05-103.00.00.000000-8:00'
Answer: C
Q55. Which statement correctly grants a system privilege?
A. GRANT EXECUTE ON prod TO PUBLIC;
B. GRANT CREATE VIEW ON tablel TO User1;
C. GRANT CREATE TABLE TO used,user2;
D. GRANT CREATE SESSION TO ALL;
Answer: C
Q56. 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
Q57. View the Exhibit and examine the description of the ORDERS table.
You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement:
SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE GROUP BY customer_id;
Which statement is true regarding the execution of the above statement?
A. It would execute successfully and provide the desired result.
B. It would not execute because the WHERE clause cannot have an aggregate function.
C. It would not execute because the ORDER_ID column is not included in the GROUP BY clause.
D. It would not execute because the GROUP BY clause should be placed before the WHERE clause.
Answer: B
Q58. View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, 25-jan-2007',101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfullyandthe ORD_NO column would containthevalue 2 for the CUSTJD101.
B. TheCREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command wouldnotexecute because the starting value of the sequence and the incrementvaluehave not been specified.
D. Allthe statementswould execute successfully and the ORD_NO column wouldhave the value 20 for the CUST_ID101becausethedefault CACHE value is 20.
Answer: A
Q59. View the Exhibit and examine the description of the EMPLOYEES table.
Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement:
SELECTfirst_name, salary, salary*!2+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees;
Which statement is true regarding the above query?
A. It would execute and give you the desired output.
B. It would not execute because the AS keyword is missing between the column name and the alias.
C. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.
D. It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing.
Answer: A
Q60. View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?
A. UPDATE orders
SETorder_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455;
B. UPDATE orders
SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;
C. UPDATE orders
SET order_date = '12-mar-2007'
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
D. UPDATE orders
SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')lSET order_total =
TO_NUMBER(NULL) WHERE order id = 2455;
Answer: B