1Z0-071 | The Avant-garde Guide To 1Z0-071 vce
Q11. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: D,E
Q12. When does a transaction complete? (Choose all that apply.)
A. When a PL/SQL anonymous block is executed
B. When a DELETE statement is executed
C. When a data definition language statement is executed
D. When a TRUNCATE statement is executed after the pending transaction
E. When a ROLLBACK command is executed
Answer: C,D,E
Q13. Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They can contain group functions.
B. They always contain a subquery within a subquery.
C. They use the < ALL operator to imply less than the maximum.
D. They can be used to retrieve multiple rows from a single table only.
E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
Answer: A,E
Q14. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
Answer: D
Q15. Examine thestructureof the members table:
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?
A. SELECT * FROM MEMBERS WHERE state LIKE '%A_* ;
B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%'
D. SELECT * FROM MEMBERS WHERE state LIKE 'A%'
Answer: A
Q16. View the Exhibit and examine the descriptions of the DEPT and LOCATIOMS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?
A. UPDATE dept d
SET city = ANY (SELECT city FROM locations l);
B. UPDATE dept d
SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id;
C. UPDATE dept d
SET city = (SELECT city FROM locations l
WHERE d.location_id = l.location_id);
D. UPDATE dept d
SET city = ALL (SELECT city FROM locations l
WHERE d.location_id = l.location_id);
Answer: C
Q17. You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?
A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
B. The EXECUTION _DATETIME must be of TIMESTAMP data type.
C. The EXECUTION_DATATIME must be of DATE data type.
D. The COMPONENT_ID must be of ROWID data type.
E. The COMPONENT_ID must be of VARCHAR2 data type.
F. The COMPONENT_ID column must be of CHAR data type.
Answer: C,E
Q18. Examine thestructure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
A)
B)
C)
D)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q19. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
A. DELETE order_id FROM orders
WHERE order_total < 1000;
B. DELETE orders
WHERE order_total < 1000;
C. DELETE
FROM orders
WHERE (SELECT order_id FROM order_items);
D. DELETE orders o, order_items i WHERE o.order id = i.order id;
Answer: B
Q20. Examine the commands used to createDEPARTMENT_DETAILS andCOURSE_DETAILS:
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
A)
B)
C)
D)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C