Refined 1Z0-071 Exam Questions and Answers 2021
Exam Code: 1Z0-071 (1z0 071 dumps), Exam Name: Oracle Database 12c SQL, Certification Provider: Oracle Certifitcation, Free Today! Guaranteed Training- Pass 1Z0-071 Exam.
Free demo questions for Oracle 1Z0-071 Exam Dumps Below:
NEW QUESTION 1
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)
- A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
- B. During executing, the oracle server may read data from storage if the required data is not already in memory.
- C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
- D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.
NEW QUESTION 2
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
NEW QUESTION 3
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
NEW QUESTION 4
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.
NEW QUESTION 5
See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
What would be the outcome?
- A. It generates an error because multiple conditions cannot be specified for the WHEN clause.
- B. It executes successfully and gives the required result.
- C. It generates an error because CASE cannot be used with group functions.
- D. It generates an error because NULL cannot be specified as a return value.
Explanation: CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn ELSE else_expr]
NEW QUESTION 6
Which task can be performed by using a single Data Manipulation Language (DML) statement?
- A. adding a column constraint when inserting a row into a table
- B. adding a column with a default value when inserting a row into a table
- C. removing all data only from one single column on which a unique constraint is defined
- D. removing all data only from one single column on which a primary key constraint is defined
NEW QUESTION 7
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.
NEW QUESTION 8
Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?
- A. A column can be dropped only if it does not contain any data.
- B. A column can be dropped only if another column exists in the table.
- C. A dropped column can be rolled back.
- D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
- E. A parent key column in the table cannot be dropped.
NEW QUESTION 9
Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS'; CONSTRAINT_NAME
CON SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE
STATUS ORDER_DATE_NN C
"ORDER_DATE" IS NOT NULL ENABLED ORDER_CUSTOMER_ID_NN C
"CUSTOMER_ID" IS NOT NULL ENABLED ORDER_MODE_LOV C
order _mode in ('direct', 'online') ENABLED
ORDER TOTAL MIN C
order total >= 0 ENABLED ORDER PK
ORDERS CUSTOMER ID R
CUSTOMERS ID SET NULL ENABLED
ORDERS SALES REP R
EMP EMP ID SET NULL ENABLED
Which two statements are true about the output? (Choose two.)
- A. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
- B. In the second column, 'c' indicates a check constraint.
- C. The STATUS column indicates whether the table is currently in use.
- D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
NEW QUESTION 10
You issued this command:
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.
NEW QUESTION 11
Examine the structure of the MEMBERS table: NameNull?Type
------------------ --------------- ------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
LAST_NAMENOT NULLVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?
- A. It fails because the alias name specified after the column names is invalid.
- B. It fails because the space specified in single quotation marks after the first two column names is invalid.
- C. It executes successfully and displays the column details in a single column with only the alias column heading.
- D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.
NEW QUESTION 12
Which statement is true about an inner join specified in the WHERE clause of a query?
- A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
- B. It requires the column names to be the same in all tables used for the join conditions.
- C. It is applicable for equijoin and nonequijoin conditions.
- D. It is applicable for only equijoin conditions.
NEW QUESTION 13
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
- A. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
- B. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
- C. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
- D. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOINinventories iON (pi.product_id=i.product_id)USING (product_id) AND quantity_on_hand < 5;
NEW QUESTION 14
View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)
Evaluate this SQL statement: ALTER TABLE emp
DROP COLUMN first_name; Which two statements are true?
- A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
- B. The FIRST_NAME column would be dropped provided at least one column remains in the table.
- C. The FIRST_NAME column would be dropped provided it does not contain any data.
- D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
NEW QUESTION 15
Which two statements are true regarding subqueries? (Choose two.)
- A. A subquery can appear on either side of a comparison operator.
- B. Only two subqueries can be placed at one level.
- C. A subquery can retrieve zero or more rows.
- D. A subquery can be used only in SQL query statements.
- E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
NEW QUESTION 16
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.
NEW QUESTION 17
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.
NEW QUESTION 18
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
P.S. Certleader now are offering 100% pass ensure 1Z0-071 dumps! All 1Z0-071 exam questions have been updated with correct answers: https://www.certleader.com/1Z0-071-dumps.html (187 New Questions)