Up To The Minute 1Z0-071 Study Guides 2021

NEW QUESTION 1
Which two tasks can be performed by using Oracle SQL statements?

• A. changing the password for an existing database user
• B. connecting to a database instance
• C. querying data from tables across databases
• D. starting up a database instance
• E. executing operating system (OS) commands in a session

Answer: AC

Explanation:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

NEW QUESTION 2
View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90 UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

• A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
• B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
• C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
• D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Answer: D

NEW QUESTION 3
Which two statements are true regarding constraints?

• A. A table can have only one primary key and one foreign key.
• B. A table can have only one primary key but multiple foreign keys.
• C. Only the primary key can be defined at the column and table levels.
• D. The foreign key and parent table primary key must have the same name.
• E. Both primary key and foreign key constraints can be defined at both column and table levels.

Answer: BE

NEW QUESTION 4
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SQL>SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?

• A. The statement would execute successfully to produce the required output.
• B. The statement would not execute because inline views and outer joins cannot be used together.
• C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
• D. The statement would not execute because the GROUP BY clause cannot be used in the inline.

Answer: A

NEW QUESTION 5
The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

• A. It recovers only the table structure.
• B. It recovers the table structure, data, and the indexes.
• C. It recovers the table structure and data but not the related indexes.
• D. It is not possible to recover the table structure, data, or the related indexes.

Answer: D

Explanation:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

NEW QUESTION 6
View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have “Not Available” displayed. Which SQL statement would produce the required result?

• A. SELECT NVL (TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT” FROM customers
• B. SELECT TO_CHAR(NVL(cust_credit_limit*.15), ‘Not Available’)) “NEW CREDIT” FROMcustomers
• C. SELECT NVL (cust_credit_limit*.15, ‘Not Available’) “NEW CREDIT” FROM customers
• D. SELECT NVL (cust_credit_limit, ‘Not Available’)*.15 “NEW CREDIT” FROM customers

Answer: C

NEW QUESTION 7
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 8
Which three statements are true regarding subqueries? (Choose three.)

• A. The ORDER BY Clause can be used in a subquery.
• B. A subquery can be used in the FROM clause of a SELECT statement.
• C. If a subquery returns NULL, the main query may still return rows.
• D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
• E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Answer: ABC

NEW QUESTION 9
Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

• A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
• B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
• C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
• D. The DESCRIBE command would still display the ORDER_DATE column.

Answer: A

NEW QUESTION 10
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.

Answer: AD

NEW QUESTION 11
Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries: (Choose three.)

• 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 are the same, then the table name takes 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

Answer: ABD

NEW QUESTION 12
Which task can be performed by using a single Data Manipulation Language (DML) statement?

• A. Removing all data only from a single column on which a primary key constraint is defined.
• B. Removing all data from a single column on which a unique constraint is defined.
• C. Adding a column with a default value while inserting a row into a table.
• D. Adding a column constraint while inserting a row into a table.

Answer: A

NEW QUESTION 13
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: AE

NEW QUESTION 14
View the Exhibit and examine the structure of the ORDERS table.

Which UPDATE statement is valid?

• A. UPDATE ordersSET order_date = ’12-mar-2007’,order_total IS NULLWHERE order_id = 2455;
• B. UPDATE ordersSET order_date = ’12-mar-2007’,AND order_total = TO_NUMBER(NULL)WHERE order_id = 2455;
• C. UPDATE ordersSET order_date = ’12-mar-2007’,order_total = NULLWHERE order_id = 2455;
• D. UPDATE ordersSET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;

Answer: C

NEW QUESTION 15
Which three statements are true regarding single-row functions? (Choose three.)

• A. The data type returned, can be different from the data type of the argument that is referenced.
• B. They can return multiple values of more than one data type.
• C. They can accept only one argument.
• D. They can be nested up to only two levels.
• E. They can be used in SELECT, WHERE, and ORDER BY clauses.
• F. They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Answer: AEF

NEW QUESTION 16
View the Exhibit and examine the data in the employees table.

You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

What is the outcome?

• A. It executes successfully but does not give the correct output.
• B. It generates an error because the concatenation operator can be used to combine only two items.
• C. It generates an error because the usage of the round function in the expression is not valid
• D. It generates an error because the alias is not valid.
• E. It executes successfully and gives the correct output.

Answer: A

NEW QUESTION 17
Which statement is true regarding external tables?

• A. The CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.
• B. The data and metadata for an external table are stored outside the database.
• C. The default REJECT LIMIT for external tables is UNLIMITED.
• D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Answer: A

Explanation:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm

NEW QUESTION 18
Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4) );
CREATE TABLE ord_items (ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?

• A. SYSDATE cannot be used with the CHECK constraint.
• B. The BETWEEN clause cannot be used for the CHECK constraint.
• C. The CHECK constraint cannot be placed on columns having the DATE data type.
• D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: A

NEW QUESTION 19
Which three statements are true regarding the SQL WHERE and HAVING clauses?

• A. The HAVING clause conditions can have aggregating functions.
• B. The HAVING clause conditions can use aliases for the columns.
• C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
• D. The WHERE clause is used to exclude rows before grouping data.
• E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Answer: ADE

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

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

• A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
• B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN.department_details d ON (c.depatrment_id=d.department_id)
• C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=
• D. department_id)
• E. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>
• F. department_id)

Answer: C

NEW QUESTION 21
Which statement is true regarding the USING clause in table joins? (Choose two.)

• A. It can be used to join a maximum of three tables.
• B. It can be used to access data from tables through equijoins as well as nonequijoins.
• C. It can be used to join tables that have columns with the same name and compatible data types.
• D. It can be used to restrict the number of columns used in a NATURAL join.

Answer: CD

NEW QUESTION 22
View the exhibit and examine the structure and data in the invoice table. (Choose two.)

Which two SQL statements would execute successfully?

• A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
• B. SELECT AVG(inv_date) FROM invoice
• C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
• D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Answer: CD

NEW QUESTION 23
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. (Choose two.)
Which two SQL statements would create the required table?

• A. CREATE TABLE employees(employee_id NUMBER,Login_id NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));
• B. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
• C. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, Login_id NUMBER UNIQUE, Employee_name VARCHAR2(25),Hire_date DATE);
• D. CREATE TABLE employees(employee_id NUMBER,Login_id NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));
• E. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL, Login_id NUMBER CONSTRAINT login_id_nn NOT NULL,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));

Answer: BE

NEW QUESTION 24
......