Downloadable 1Z0-071 Study Guides 2021

NEW QUESTION 1
Which two statements are true regarding the SQL GROUP BY clause?

• A. You can use a column alias in the GROUP BY clause.
• B. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
• C. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
• D. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
• E. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

Answer: DE

NEW QUESTION 2
Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

• A. By default, EM express is available for a database after database creation.
• B. You can use EM express to manage multiple databases running on the same server.
• C. You can perform basic administrative tasks for pluggable databases by using the EM express interface.
• D. You cannot start up or shut down a database Instance by using EM express.
• E. You can create and configure pluggable databases by using EM express.

Answer: A

NEW QUESTION 3
View the Exhibit and examine the structure of the PROMOTION table.

You have to generate a report that displays the promo named start data for all promos that started after that last promo in the ‘INTTERNET’ category.

• A. Select promo_name, promo_being_date FROM promoptions WHERE promo_being_data > ANY (SELCT promo_being-date FROM promotionsWHERE promo_category = ‘INTERNET’
• B. SELECT promo_neme, promo_being_date FROM promotions WHERE promo_being_date > All (SELECT promo_beinjg-date FROM promotionsWHERE promo_category =’INTERNET’ );
• C. SELECT promo-name, promo-being _date FROM promotionsWhere promo_being_data >ALL (SELECT MAX (promo_being-date) FROM promotions ) ANDPromo-category =’INTERNET’;
• D. SELECT promo-name, promo-being_date FROM promotion WHERE promo-being-date IN (SELECT promo_biing_date FROM promotionsWHERE promo_category=’INTYERNET’);

Answer: B

NEW QUESTION 4
View the Exhibit and examine the structures of the employees and departments tables.

You must update the employees table according to these requirements::
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department id corresponding to London (locationid 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission In location_id 2100 to 1.5 times the average commission of their department. You issue this command:

What is the result?

• A. It executes successfully but does not produce the desired update.
• B. It executes successfully and produces the desired update.
• C. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
• D. It generates an error because a subquery cannot have a join condition in an update statement.

Answer: A

NEW QUESTION 5
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 6
Examine the types and examples of relationship that follows: (Choose the best answer.)
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?

• A. 1-d, 2-b, 3-a, and 4-c
• B. 1-c, 2-d, 3-a, and 4-b
• C. 1-a, 2-b, 3-c, and 4-d
• D. 1-c, 2-a, 3-b, and 4-d

Answer: C

NEW QUESTION 7
Which two statements are true regarding the COUNT function?

• A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
• B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
• C. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
• D. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
• E. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Answer: BD

NEW QUESTION 8
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 9
View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best answer.)

You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?

• A. It generates an error because = is not valid and should be replaced by the IN operator.
• B. It executes successfully but does not give the correct output.
• C. It executes successfully and gives the correct output.
• D. It generate an error because the subquery does not have a GROUP BY clause.

Answer: D

NEW QUESTION 10
Which two are the minimal requirements for a self-join? (Choose two.)

• A. Only equijoin conditions may be used in the query.
• B. Outer joins must not be used in the query.
• C. There must be a condition on which the self-join is performed.
• D. No other condition except the self-join may be specified.
• E. The table used for the self-join must have two different alias names in the query.

Answer: CE

NEW QUESTION 11
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 12
Which statements are true? (Choose all that apply.)

• A. The data dictionary is created and maintained by the database administrator.
• B. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
• C. The usernames of all the users including the database administrators are stored in the data dictionary.
• D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
• E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
• F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.

Answer: CDF

Explanation: References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

NEW QUESTION 13
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM departments
WHERE departments_id=90 UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort output?

• A. ORDER BY DEPT_NAME;
• B. ORDER BY DEPT_ID;
• C. ORDER BY 'b';
• D. ORDER BY 3;

Answer: BD

NEW QUESTION 14
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 15
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 16
Examine the following query:
SQL> SELECT prod_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?

• A. It displays 5 percent of the products with the highest amount sold.
• B. It displays the first 5 percent of the rows from the SALES table.
• C. It displays 5 percent of the products with the lowest amount sold.
• D. It results in an error because the ORDER BY clause should be the last clause.

Answer: C

Explanation: References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

NEW QUESTION 17
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 18
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