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 May 1z0-047 Study Guide Questions:
Q101. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGEcommand
C. themultitableINSERT command
D. INSERTusingWITHCHECK OPTION
Answer: C
Q102. Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED order_date;
Which statement is true?
A. The DESCRIBE command would still display the ORDER_DATE column.
B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
Answer: D
Q103. 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
Latest 1z0-047 free dumps pdf:
Q104. Evaluate the following expression using meta character for regular expression:
'[AAle|ax.r$]'
Which two matches would be returned by this expression? (Choose two.)
A. Alex
B. Alax
C. Alxer
D. Alaxendar
E. Alexender
Answer: DE
Q105. View the Exhibit and examine the structure of the ORDERS table.
The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following
CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date.customer_id
FROM orders;
Which statement is true regarding the above command?
A. The NEW_ORDERS table would not get created because the DEFAULT value can not be specified in the column definition.
B. The NEW_ORDERS table would get created and only the NOT NULL constraint definedon the specified columns would bepassed to the new table.
C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
Answer: B
Q106. 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
100% Guarantee 1z0-047 oracle database sql expert syllabus:
Q107. Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All the constraints can be defined at the column level as well as the table level
Answer: BD
Q108. Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
A. The HAVING clause conditions can have aggregate functions.
B. The HAVING clause conditions can use aliases for the columns.
C. WHERE and HAVING clauses cannot be used together in a SQL statement.
D. The WHERE clause is used to exclude rows before the grouping of data.
E. The HAVING clause is used to exclude one or more aggregated results after grouping data.
Answer: ADE
Q109. 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 withMANAGERJD108 and all the employeesbelowhim or herin thehierarchy.
C. The output of statement 1 wouldneitherdisplay the employeewithMANAGERJD 108 nor any employee below him or herinthe hierarchy.
D. The output for statement 2 would not displaytheemployee with MANAGERJD 108 but it would display all the employees belowhimor her in the hierarchy.
Answer: CD
Q110. SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
1 - CREATE ROLE mgr;
2 - GRANT CREATE TABLE, SELECT
ON oe. orders
TO mgr;
3 - GRANT mgr, create table TO SCOTT;
Which statement is true regarding the execution of the above commands?
A. Statement 1 would not execute because the WITH GRANT option is missing.
B. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.
C. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
D. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
Answer: D