Proper study guides for Regenerate Oracle Oracle Database SQL Expert certified begins with Oracle 1z0 047 pdf preparation products which designed to deliver the Guaranteed 1z0 047 pdf questions by making you pass the 1z0 047 dumps test at your first time. Try the free 1z0 047 dumps demo right now.

Q31. Evaluate the following SELECT statement and view the Exhibit to examine its output: 

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS 

Which two statements are true about the output? (Choose two.) 

A. Inthe secondcolumn, indicates a check constraint. 

B. TheSTATUS columnindicateswhether the tableiscurrently in use. 

C. The R_CONSTRAINT_NAME column givesthealternative name for the constraint. 

D. The column DELETE_RULE decides the state oftherelated rows inthechild tablewhenthe corresponding row is deleted from the parent table. 

Answer: AD


Q32. View the Exhibit and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables. 

The following SQL statement was executed: 

SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total, 

GROUPING(e. department_id) GRP_DEPT, 

GROUPING(e.job_id) GRPJOB, 

GROUPING(d. location_id) GRP_LOC 

FROM employees e JOIN departments d 

ON e.department_id = d.department_id 

GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id); 

View the Exhibit2 and examine the output of the command. 

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

A. The value1in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal. 

B. The value 1 in GRPJOBandGRP_LOC meansthatJOB_IDandLOCATION_ID columns are nottakeninto accountto generatethesubtotal. 

C. Thevalue1inGRPJOB and GRP_LOC meansthatthe NULL value in JOBJD and LOCATIONJD columns aretakeninto account togeneratethe subtotal. 

D. The value 0 inGRP_DEPT,GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columnsaretaken into account to generate the subtotal 

Answer: BD


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


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


Q35. The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT'FROM ord; 

A. CREATE SYNONYM ord FOR orders; This command is issuedbyOE. 

B. CREATEPUBLIC SYNONYM ordFORorders; This command is issued by OE. 

C. CREATESYNONYM ord FOR oe.orders; This command isissuedby thedatabaseadministrator. 

D. CREATE PUBLIC SYNONYM ord FOR oe.orders;Thiscommand is issued by the database administrator. 

Answer: D


Q36. Which statement correctly grants a system privilege? 

A. GRANT EXECUTE ON prod TO PUBLIC; 

B. GRANT CREATE VIEW ON tablel TO used; 

C. GRANT CREATE TABLE TO used ,user2; 

D. GRANT CREATE SESSION TO ALL; 

Answer: C


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


Q38. Which three statements are true regarding group functions? (Choose three.) 

A. They can be used on columns or expressions. 

B. They can be passed as an argument to another group function. 

C. They can be used only with a SQL statement that has the GROUP BY clause. 

D. They can be used on only one column in the SELECT clause of a SQL statement. 

E. They can be used along with the single-row function in the SELECT clause of a SQL statement. 

Answer: ABE


Q39. Evaluate the following SQL statements in the given order: 

DROP TABLE dept; 

CREATE TABLE dept 

(deptno NUMBER(3) PRIMARY KEY, 

deptname VARCHAR2(10)); 

DROP TABLE dept; 

FLASHBACK TABLE dept TO BEFORE DROP; Which statement is true regarding the above FLASHBACK operation? 

A. It recovers only the firstDEPTtable. 

B. Itrecovers onlythesecondDEPTtable. 

C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case. 

D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN. 

Answer: B


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

You executed the following SQL statement: 

SELECT first_name, department_id, salary 

FROM employees 

ORDER BY department_id, first_name, salary desc; 

Which two statements are true regarding the output of the above query? (Choose two.) 

A. The valuesinall the columns wouldbe sortedin the descending order. 

B. Thevalues in theSALARYcolumn wouldbesorted indescendingorder for all the employees havingthesame valueinthe DEPARTMENT_ID column. 

C. The values intheFIRST_NAME column would be sortedinascending order for alltheemployees having the same valueinthe DEPARTMENT_ID column. 

D. Thevalues in the FIRST_NAME column would be sorted in the descendingorder forall the employees having the same valueinthe DEPARTMENT_ID column. 

E. Thevalues in the SALARY column wouldbe sortedin descending order foralltheemployeeshaving thesame value intheDEPARTMENT_IDandFIRST_NAME column. 

Answer: CE