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 Jun 1z0-047 Study Guide Questions:
Q121. The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following
GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
A. PUBLICshould bereplacedwithspecific usernames.
B. ALL should be replaced with a list of specific privileges.
C. WITH GRANTOPTIONshould beaddedto the statement.
D. Separate GRANT statements are required for ORDERSandORDER_ITEMS tables.
Answer: D
Q122. 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
Q123. Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2), job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS
SELECT employee_id, last_name job_id
FROM employees
WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;
Which statements are true regarding the above view? (Choose all that apply.)
A. It allows you to insert details of allnewstaff into the EMPLOYEES table.
B. Itallowsyou todeletethedetails of the existing sales staff fromtheEMPLOYEES table.
C. It allows you to updatethejob ids oftheexisting sales staff to any other job id in the EMPLOYEES table.
D. It allows you to insert the IDs, last names and job ids of the sales staff from theviewif it is used in multitable INSERT statements.
Answer: BD
Regenerate oca 1z0-047 dumps free download:
Q124. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city
FROM locations JOIN departments
USING(location_id);
A. UNION
B. MINUS
C. INTERSECT
D. UNIONALL
Answer: C
Q125. 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_idx ON empdet(empno);
Answer: C
Q126. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A
Approved oca oracle database sql expert exam guide exam 1z0-047 ebook:
Q127. 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
Q128. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECTfirst_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 query would execute to give the desired output.
B. The query would not execute because date functions cannot be nested.
C. The query would execute but the output would give review dates that are Sundays.
D. The query would not execute because the NEXT_DAY function accepts a string as argument.
Answer: C
Q129. 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
Q130. View the Exhibit and examine the description of the EMPLOYEES table.
You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id
FROM employees
WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104)
AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for this?
A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B. TheWHERE clause condition in themainquery is usingthe= comparison operator,insteadof theINoperator.
C. The WHERE clause condition in themainquery is using the=comparison operator,insteadof the =ANYoperator.
D. The columns in the WHERE clause condition of the main query andthecolumns selected inthesubquery should be in the same order.
Answer: D