Highest Quality of 1z0-047 training materials and resource for Oracle certification for IT examinee, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!
2021 Jun 1z0-047 Study Guide Questions:
Q51. Evaluate the following statement:
CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);
The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table.
The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table.
Which option should be used to perform this task most efficiently?
A. MERGE
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: A
Q52. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
A. It wouldbeautomatically enabled and deferred.
B. It wouldbeautomaticallyenabledand immediate.
C. Itwouldremaindisabled and has to beenabled manuallyusingthe ALTER TABLEcommand.
D. Itwould remain disabled and can be enabled only by droppingtheforeign key constraint andre-creating it.
Answer: ABD
Q53. View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.
CUSTOMERS needs to be updated to reflect the latest information about the customers.
What is the error in the following MERGE statement?
MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_id = cv.customer_id, c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.
income_level = cv. Income_level
WHEN NOT MATCHED THEN
INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level)
WHERE cv. Income_level >100000;
A. The CUSTOMER_ID column cannot be updated.
B. TheINTO clause is misplaced in the command.
C. The WHERE clause cannot be usedwithINSERT.
D. CUSTOMER VU cannot beusedasadata source.
Answer: A
Renovate 1z0-047 vs 1z0-051:
Q54. View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers.
What is the error in the following MERGE statement?
MERGE INTO customers c
USING customer_vu cv
ON (c.customer_id = cv.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.customer_id = cv.customer_id,
c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.income_level = cv. Income_level
WHEN NOT MATCHED THEN
INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level)
WHERE cv. Income_level >100000;
A. The CUSTOMERJD column cannot be updated.
B. The INTO clause is misplaced in the command.
C. The WHERE clause cannot be used with INSERT.
D. CUSTOMER VU cannot be used as a data source
Answer: A
Q55. 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. Thestatement would execute successfullytoproducetherequired output.
B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether.
C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query.
D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view.
Answer: A
Q56. View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)
A. ORDERBY 3;
B. ORDER BY 'b'
C. ORDER BY DEPT_ID;
D. ORDER BY DEPT NAME;
Answer: AC
Free 1z0-047 dumps:
Q57. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'.
Which SQL statement would give you the desired result?
A. SELECTfirst_name, salary FROM employees WHERE (manager_id,salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
B. SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
C. SELECT first_name, salary FROM employees WHERE (manager_id,salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena'
D. SELECT first_name, salary FROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena'
Answer: D
Q58. View the Exhibit and examine the structure of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id;
Which statement is true regarding the output for this command?
A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.
B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
C. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.
D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
Answer: C
Q59. 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 querywouldexecutetogive the desired output.
B. Thequerywouldnot execute becausedatefunctions cannot be nested.
C. Thequerywould executebuttheoutputwouldgive reviewdates that are Sundays.
D. Thequery would not execute becausetheNEXT_DAY function acceptsastring as argument.
Answer: C
Q60. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o
USING(order_id);
Which statement is true regarding the execution of this SQL statement?
A. The statementwouldnot execute because table aliasesarenot allowed in the JOIN clause.
B. Thestatement wouldnotexecute because thetablealias prefix is not usedin theUSING clause.
C. The statementwouldnot execute because allthecolumns in the SELECT clause are not prefixed with table aliases.
D. The statement would not execute because the column part of the USING clause cannot haveaqualifier in the SELECT list.
Answer: D