Most Recent 1Z0-071 Pdf 2021

NEW QUESTION 1
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

Answer: CDE

NEW QUESTION 2
Which statement is true regarding the INTERSECT operator?

• A. The names of columns in all SELECT statements must be identical.
• B. It ignores NULL values.
• C. Reversing the order of the intersected tables alters the result.
• D. The number of columns and data types must be identical for all SELECT statements in the query.

Answer: D

Explanation:
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
References:
http://oraclexpert.com/using-the-set-operators/

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
Examine the structure of the SALES table. (Choose two.)

Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS
SELECT product_id, customer_id, quantity_sold, price FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?

• A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
• B. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
• C. It will not be created because of the invalid WHERE clause.
• D. It is created with no rows.
• E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Answer: BD

NEW QUESTION 5
Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

• A. It displays all cities in descending order, within which the last names are further sorted in descending order.
• B. It fails because a column alias cannot be used in the ORDER BY clause.
• C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
• D. It displays all cities in ascending order, within which the last names are further sorted in descending order.

Answer: D

NEW QUESTION 6
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 7
View the exhibit and examine the ORDERS table. ORDERS
Name Null? Type
ORDER ID NOT NULL NUMBER(4) ORDATE DATE DATE CUSTOMER ID NUMBER(3) ORDER TOTAL NUMBER(7,2)
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

• A. ALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
• B. ALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
• C. ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
• D. ALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: C

NEW QUESTION 8
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 9
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 10
Examine the structure of the MEMBERS table: NameNull?Type
------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?

• A. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;
• B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
• C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
• D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Answer: B

NEW QUESTION 11
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 12
Examine the structure of the MEMBERS table: NameNull?Type
------------------ --------------- ------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (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.

Answer: D

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

Answer: C

NEW QUESTION 14
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 15
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 16
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 17
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?

• A. PUBLIC should be replaced with specific usernames.
• B. ALL should be replaced with a list of specific privileges.
• C. WITH GRANT OPTION should be added to the statement.
• D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Answer: D

Explanation:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

NEW QUESTION 18
Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

• A. Subquery
• B. Self-join
• C. Full outer-join with self-join
• D. Left outer-join with self-join
• E. Right outer-join with self-join

Answer: AB

NEW QUESTION 19
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.)
SQL > CREATE TABLE transaction (trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE, End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

• A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
• B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
• C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
• D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Answer: C

NEW QUESTION 20
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 21
You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

• A. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
• B. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
• C. SELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
• D. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;

Answer: A

NEW QUESTION 22
You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?

• A. Compare Period ADDM report.
• B. AWR Compare Period report.
• C. Active Session History (ASH) report.
• D. Taking a new snapshot and comparing it with a preserved snapshot.

Answer: B

NEW QUESTION 23
......