[Up to the minute] 1z0-047 vs 1z0-051

Exam Code: 1Z0-047 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-047 Exam.

2021 Apr 1Z0-047 Study Guide Questions:

Q1. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table? 

A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234; 

B. DELETE FROM empdet WHERE ename LIKE 'J%' 

C. CREATE VIEWempvu AS SELECT* FROMempdept; 

D. CREATEINDEX empdet_dx ON empdet(empno); 

Answer: C

Q2. 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: CD

Q3. View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables. 

Evaluate the following SQL statement: 

SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id); 

Which two statements are true regarding the output of this command? (Choose two.) 

A. Theoutputwould display thetotal salaryforallthedepartments. 

B. Theoutputwoulddisplay the total salaryforall theJOB_IDsinadepartment. 

C. The output would display only the grand total ofthesalary for allJOB_IDsin a LOCATION_ID. 

D. Theoutput would displaythegrandtotalof thesalaryfor only the groups specified in the GROUP BY clause. 

Answer: AB

Q4. View the Exhibit and examine the details of the EMPLOYEES table. 

Evaluate the following SQL statements: 

Statement 1: 

SELECT employee_id, last_name, job_id, manager_id 

FROM employees START WITH employee_id = 101 

CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ; 

Statement 2: 

SELECT employee_id, last_name, job_id, manager_id 

FROM employees 

WHERE manager_id != 108 

START WITH employee_id = 101 

CONNECT BY PRIOR employee_id = manager_id; 

Which two statements are true regarding the above SQL statements? (Choose two.) 

A. Statement 2 would not execute because theWHEREclause condition is not 

allowed in a statementthathas the START WITH clause. 

B. Theoutput forstatement1 would displaytheemployee with MANAGER_ID108 and 

all the employeesbelowhim or herin thehierarchy. 

C. The output of statement 1 wouldneither display the employee with MANAGER_ID 108 nor any 

employee below him or herinthe hierarchy. 

D. The output for statement 2 would not display theemployee with MANAGER_ID 108 but it 

would display all the employees belowhimor her in the hierarchy. 

Answer: CD

Q5. View the Exhibit and examine the data in EMPLOYEES and 

DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY. 

Evaluate the following UPDATE statement: 

UPDATE employees a 

SET department_jd = 

(SELECT department_id 

FROM departments 

WHERE location_id = ‘2100’), 

(salary, commission_pct) = 

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) 

FROM employees b 

WHERE a. department_jd = b. department_id) 

WHERE first_name|| '||last_name = 'Amit Banda' 

What would be the outcome of the above statement? 

A. Itwould execute successfullyandupdate therelevant data. 

B. It would not execute successfully because there isno LOCATION_ID2100 in theDEPARTMENTStable. 

C. It wouldnotexecute successfully because the condition specified with the concatenation operator is not valid. 

D. Itwould not execute successfully becausemultiplecolumns (SALARY,COMMISSION_PCT)cannot be used in an UPDATE statement. 

Answer: A

Renovate 1z0-047 oracle database sql expert:

Q6. Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET? 

CREATE TABLE empdet 

(empno CHAR(2), ename CHAR(5), deptno NUMBER(4)) 

ORGANIZATION EXTERNAL 

(LOCATION ('emp.daf)); 

A. TYPE 

B. REJECTLIMIT 

C. DEFAULT DIRECTORY 

D. ACCESS PARAMETERS 

Answer: C

Q7. 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. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: C

Q8. 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 OUTERJOIN 

C. LEFT OUTERJOIN 

D. RIGHT OUTER JOIN 

Answer: B

Q9. View the Exhibit and examine the description of the EMPLOYEES table. 

Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, 

DEPARTMENT_ID, and the total annual salary: 

SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5; 

When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly? 

A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation". 

B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation". 

C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation". 

D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation". 

Answer: C

Q10. View the Exhibit and examine the structure of the EMP table. 

You executed the following command to add a primary key to the EMP table: 

ALTER TABLE emp 

ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) 

USING INDEX emp_id_idx; 

Which statement is true regarding the effect of the command? 

A. The PRIMARY KEY is created alongwitha new index. 

B. The PRIMARY KEY is created anditwould use an existinguniqueindex. 

C. The PRIMARY KEYwould becreated in a disabled state because it is usinganexisting index. 

D. The statementproduces an error because the USING clause is permitted only intheCREATE TABLE command. 

Answer: B

Guaranteed oracle 1z0-047 pdf material:

Q11. View the Exhibit and examine the structure of the ORDERS table. 

The ORDERS table belongs to the user OE. HR is another user in the database. 

Evaluate the commands issued by users OE and HR in the following order: 

Statement 1 by user OE: GRANT SELECT, 

UPDATE(customer_id, order_total) 

ON orders 

TOhr; 

Statement 1 by user HR: SELECT * FROM oe.orders; 

Statement 2 by user HR: UPDATE oe.orders 

SET order_totah 10000; 

Which statement is true regarding the above commands? 

A. Statement 1 by user OE would not work because the statement has to be issued by the DBA. 

B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update. 

C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully. 

D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns. 

Answer: C

Q12. Given below are the SQL statements executed in a user session: 

CREATE TABLE product 

(pcode NUMBER(2), 

pnameVARCHAR2(10)); 

INSERT INTO product VALUES(1, ‘pen’); 

INSERT INTO product VALUES (2,'pencil); 

SAVEPOINT a; 

UPDATE product SET pcode = 10 WHERE pcode = 1; 

SAVEPOINT b; 

DELETE FROM product WHERE pcode = 2; 

COMMIT; 

DELETE FROM product WHERE pcode=10; 

ROLLBACK TO SAVEPOINT a; 

Which statement describes the consequences? 

A. No SQL statementwouldberolledback. 

B. Both the DELETE statements would be rolled back. 

C. Only the second DELETE statementwould be rolledback. 

D. Both theDELETE statementsand the UPDATE statement would berolledback. 

Answer: A

Q13. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.) 

A. It can be used only with the SELECT clause. 

B. The WITH clause can hold more than one query. 

C. If the query block name and the table name were the same, then the table name would take precedence. 

D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block. 

Answer: ABD

Q14. 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 LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command? 

A. The statementwouldexecute andwouldreturn the desired results. 

B. Thestatement wouldnotexecute becausethe= comparison operatorismissing in the WHERE clause of theouterquery. 

C. Thestatement wouldexecutebutitwillreturnzerorows because the WHERE clause intheinner query shouldhavethe= operatorinstead of <>. 

D. The statement wouldnot execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result. 

Answer: C

Q15. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.) 

A. The outer query stops evaluating the result set of the inner query when the first value is found. 

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query. 

C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. 

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed. 

Answer: AC

Q16. Evaluate the CREATE TABLE statement: 

CREATE TABLE products 

(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, 

product_name VARCHAR2(15)); 

Which statement is true regarding the PROD_ID_PK constraint? 

A. Itwould becreated only if a unique index is manually created first. 

B. Itwould becreated andwould use an automatically created unique index. 

C. It would be createdandwould use an automaticallycreatednonunique index. 

D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command. 

Answer: B

see more http://www.2passeasy.com/exam/1Z0-047/