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.
2016 Jun 1z0-047 Study Guide Questions:
Q1. 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.
Q2. 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.
Q3. 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.
Refresh oracle database sql expert 1z0-047 dumps:
Q4. Given below are the SQL statements executed in a user session:
CREATE TABLE product
INSERT INTO product VALUES(1, ‘pen’);
INSERT INTO product VALUES (2,'pencil);
UPDATE product SET pcode = 10 WHERE pcode = 1;
DELETE FROM product WHERE pcode = 2;
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.
Q5. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Q6. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM order_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
A. t wouldreturn thesubtotals for theOrder Amount of every CUSTOMER_ID.
B. twould returnthesubtotals fortheOrderAmountfor every PRODUCT_ID.
C. twould return the subtotals fortheOrder Amount of every PRODUCT_IDandCUSTOMER_ID asonegroup.
D. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
E. t wouldreturnonly thegrandtotal for theOrderAmount ofeveryCUSTOMER_ID and PRODUCT_ID as onegroup.
Accurate oca 1z0-047 dumps:
Q7. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?
C. GROUP BY
D. START WITH
E. CONNECT BY PRIOR
Q8. View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id
WHERE order_date > 'June 30 2001';
Which statement is true regarding the execution of this SQL statement?
A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
B. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.
Q9. You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name, (character data type) which stores the employee name
3. Hire date, to store the date when the employee joined the organization
4. Status (character data type). It should contain the value if no data is entered.
5. Resume (character large object [CLOB] data type), which would contain the resume submitted by the employee
Which is the correct syntax to create this table?
e_status VARCHAR2(10) DEFAULTACTIVE',
emp_status VARCHAR2(10) DEFAULT ACTIVE',
emp_name VARCHAR2(25),start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
D. CREATE TABLEEMP_1
(emp_id NUMBER, emp_nameVARCHAR2(25),
Q10. 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 LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use?
A. Pivoting INSERT
B. Unconditional INSERT
D. Conditional FIRST INSERT