Free 1Z0-071 Dumps 2021

NEW QUESTION 1
Evaluate the following query:

Which is the correct output of the above query?

• A. +00-300, +54-02,+00 11:12:10.123457
• B. +00-300,+00-650,+00 11:12:10.123457
• C. +25-00, +54-02, +00 11:12:10.123457
• D. +25-00,+00-650,+00 11:12:10.123457

Answer: C

NEW QUESTION 2
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 3
Which statement is true regarding the UNION operator?

• A. By default, the output is not sorted.
• B. Null values are not ignored during duplicate checking.
• C. Names of all columns must be identical across all select statements.
• D. The number of columns selected in all select statements need not be the same.

Answer: B

NEW QUESTION 4
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?

• A. 150
• B. 200
• C. 160
• D. 16
• E. 100

Answer: C

Explanation: References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

NEW QUESTION 5
Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement: SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?

• A. Disable the primary key in the STORES table.
• B. Use CASCADE keyword with DELETE statement.
• C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
• D. Disable the FOREIGN KEY in SALES table and then delete the rows.
• E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Answer: CDE

NEW QUESTION 6
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 7
Which two statements are true regarding constraints? (Choose two.)

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

Answer: BC

NEW QUESTION 8
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 9
Which three statements are true regarding subqueries?

• A. Multiple columns or expressions can be compared between the main query and subquery.
• B. Subqueries can contain ORDER BY but not the GROUP BY clause.
• C. Main query and subquery can get data from different tables.
• D. Subqueries can contain GROUP BY and ORDER BY clauses.
• E. Main query and subquery must get data from the same tables.
• F. Only one column or expression can be compared between the main query and subquery.

Answer: ACD

Explanation: References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

NEW QUESTION 10
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement: SELECT first_name, department_id, salary FROM employees
ORDER BY department_id, first_name, salary desc; Which two statements are true regarding the result?

• A. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
• B. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
• C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
• D. The values in the all columns would be returned in descending order.
• E. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Answer: AB

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
Which two statements are true about Data Manipulation Language (DML) statements?

• A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
• B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
• C. ADELETE FROM..... statement can remove rows based on only a single condition on a table.
• D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
• E. ADELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
• F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

Answer: BE

Explanation: References:
http://www.techonthenet.com/sql/and_or.php

NEW QUESTION 13
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 14
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 15
Which two statements are true regarding constraints? (Choose two.)

• A. A constraint is enforced only for an INSERT operation on a table.
• B. A foreign key cannot contain NULL values.
• C. The column with a UNIQUE constraint can store NULLS.
• D. You can have more than one column in a table as part of a primary key.

Answer: CD

NEW QUESTION 16
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 17
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 cause.

Answer: DE

NEW QUESTION 18
Which statements are correct regarding indexes? (Choose all that apply.)

• A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to creates a unique index.
• B. Indexes should be created on columns that are frequently referenced as part of any expression.
• C. When a table is dropped, the corresponding indexes are automatically dropped.
• D. For each DML operation performed, the corresponding indexes are automatically updated.

Answer: ACD

Explanation: References:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/