Up To Date 1Z0-071 Bible 2021

NEW QUESTION 1
View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

• A. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);
• B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• C. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
• D. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);

Answer: C

NEW QUESTION 2
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_DATETIME 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: CF

NEW QUESTION 3
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table. You execute the SQL statement:

What is the outcome?

• A. It fails because the join type used is incorrect.
• B. It executes successfully and displays the required list.
• C. It executes successfully but displays an incorrect list.
• D. It fails because the ON clause condition is not valid.

Answer: B

NEW QUESTION 4
Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

• A. WHERE and HAVING clauses cannot be used together in a SQL statement.
• B. The HAVING clause conditions can have aggregate functions.
• C. The HAVING clause conditions can use aliases for the columns.
• 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: ABD

NEW QUESTION 5
Which two statements are true regarding roles? (Choose two.)

• A. A role can be granted to itself.
• B. A role can be granted to PUBLIC.
• C. A user can be granted only one role at any point of time.
• D. The REVOKE command can be used to remove privileges but not roles from other users.
• E. Roles are named groups of related privileges that can be granted to users or other roles.

Answer: BE

Explanation:
References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28

NEW QUESTION 6
Which two statements are true regarding constraints?

• A. A foreign key column cannot contain null values.
• B. A column with the UNIQUE constraint can contain null values.
• C. A constraint is enforced only for INSERT operation on the table.
• D. A constraint can be disabled even if the constraint column contains data.
• E. All constraints can be defined at the column level and at the table level.

Answer: BD

NEW QUESTION 7
SCOTT is a user in the database.
Evaluate the commands issued by the DBA:

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 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
• C. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
• D. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

Answer: B

NEW QUESTION 8
Examine these SQL statements that are executed in the given order:
CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR 2 (15),
salary NUMBER (8, 2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no)); ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What will be the status of the foreign key EMP_MGR_FK?

• A. It will be enabled and immediate.
• B. It will be enabled and deferred.
• C. It will remain disabled and can be re-enabled manually.
• D. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer: C

NEW QUESTION 9
Which two statements are true about sequences crated in a single instance Oracle database?

• A. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
• B. DELETE <sequencename> would remove a sequence from the database.
• C. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
• D. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
• E. When the database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Answer: CD

NEW QUESTION 10
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

• A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
• B. It would execute but would return no rows.
• C. It would not execute because the same column has been used twice with the AND logical operator.
• D. It would execute and return the desired.

Answer: B

NEW QUESTION 11
View the exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

• A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;
• B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
• C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);
• D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

Answer: C

NEW QUESTION 12
View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?

• A. SELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items GROUP BY order_id);
• B. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);
• C. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id);
• D. SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

Answer: A

NEW QUESTION 13
Which three statements are true regarding the data types?

• A. The minimum column width that can be specified for a VARCHAR2 data type column is one.
• B. Only one LONG column can be used per table.
• C. A TIMESTAMP data type column stores only time values with fractional seconds.
• D. The BLOB data type column is used to store binary data in an operating system file.
• E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Answer: ABE

NEW QUESTION 14
You issued this command:
CHOOSE THREE
SQL > DROP TABLE employees; Which three statements are true?

• A. Sequences used in the EMPLOYEES table become invalid.
• B. If there is an uncommitted transaction in the session, it is committed.
• C. All indexes and constraints defined on the table being dropped are also dropped.
• D. The space used by the EMPLOYEES table is always reclaimed immediately.
• E. The EMPLOYEES table can be recovered using the ROLLBACK command.
• F. The EMPLOYEES table may be moved to the recycle bin.

Answer: BCF

NEW QUESTION 15
The following are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
The candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, till all the rows are processed.
Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

• A. 2, 1, 4, 3
• B. 4, 1, 2, 3
• C. 4, 2, 1, 3
• D. 2, 4, 1, 3

Answer: D

Explanation:
References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

NEW QUESTION 16
You execute the SQL statement: SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY, last_name VARCHAR2 (50) NOT NULL, first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) ); What is the outcome?

• A. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
• B. It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.
• C. It fails because the condition for the CANAMES constraint is not valid.
• D. It succeeds and an index is crated for CITIZEN_ID.

Answer: A

NEW QUESTION 17
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. The outer query stops evaluating the result set of the inner query when the first value is found.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: AC

Explanation:
References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION 18
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL
• B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;
• D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;
• E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Answer: ACE

NEW QUESTION 19
View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.)

Which two statements are true regarding data type conversion in query expressions?

• A. inv_date = '15-february-2008' :uses implicit conversion
• B. inv_amt = '0255982' : requires explicit conversion
• C. inv_date > '01-02-2008' : uses implicit conversion
• D. CONCAT(inv_amt, inv_date) : requires explicit conversion
• E. inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: AE

NEW QUESTION 20
Examine the commands used to create the DEPARTMENT_DETAILS and the COURSE-DETAILS tables: SQL> CREATE TABLE DEPARTMfiNT_D£TAILS
DEPARTMENT_ID NUMBER PRIMARY KEY , DEPARTMEHT_NAME VARCHAR2(50) ,
HOD VARCHAP2(50));
SQL> CREATE TABLE COURSE-DETAILS (COURSE ID NUMBER PRIMARY KEY , COURS_NAME VARCHAR2 (50) ,
DEPARTMEHT_ID NUMBER REFERENCES DEPARTMENT_DETAIL
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. SELECT d.departranc_id, c.cours«_id FROM cource_deatils c LEFT OUTER JOIN departmnt_details d ON (c.dapartmsnt_id=d.departtnent_id);
• B. SELECT d.department_id,
• C. course_id FROM dapartment_details d RIGHT OUTER JOIN course_dotails c ON (c.depattnient_id=d.department_id) ;
• D. SELECT d.department i
• E. ccours_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id);
• F. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department id).- (DEPARTMENT_ID) ;

Answer: D

NEW QUESTION 21
Which two statements best describe the benefits of using the WITH clause? (Choose two.)

• A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
• B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
• D. It enables sessions to store the results of a query permanently.

Answer: AB

NEW QUESTION 22
You issue the following command to drop the PRODUCTS table: (Choose all that apply.) SQL > DROP TABLE products;
Which three statements are true about the implication of this command?

• A. All data along with the table structure is deleted.
• B. A pending transaction in the session is committed.
• C. All indexes on the table remain but they are invalidated.
• D. All views and synonyms on the table remain but they are invalidated.
• E. All data in the table is deleted but the table structure remains.

Answer: ABD

NEW QUESTION 23
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

• A. INTERSECT
• B. UNION ALL
• C. MINUS
• D. UNION

Answer: C

NEW QUESTION 24
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

• A. The table prefix is missing for the column names in the SELECT clause.
• B. The NATURAL JOIN clause is missing the USING clause.
• C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
• D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Answer: D

Explanation:
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)

NEW QUESTION 25
......