Oracle 1Z0-071 Study Guides 2021

NEW QUESTION 1
In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?

• A. second normal form
• B. first normal form
• C. third normal form
• D. fourth normal form

Answer: A

Explanation: References:
https://blog.udemy.com/database-normal-forms/

NEW QUESTION 2
View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "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"FROM customers;
• C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;

Answer: A

NEW QUESTION 3
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A. LEFT OUTER JOIN
• B. JOIN
• C. NATURAL JOIN
• D. RIGHT OUTER JOIN
• E. FULL OUTER JOIN

Answer: BD

NEW QUESTION 4
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 5
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

• A. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
• B. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
• C. The WHERE clause can be used to exclude rows after dividing them into groups.
• D. The HAVING clause can be used with aggregate functions in subqueries.
• E. The WHERE clause can be used to exclude rows before dividing them into groups.

Answer: CD

NEW QUESTION 6
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.


Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));
What would be the outcome of the above SQL statement?

• A. It displays prod IDs in the promo with the lowest cost.
• B. It displays prod IDs in the promos with the lowest cost in the same time interval.
• C. It displays prod IDs in the promos with the highest cost in the same time interval.
• D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

Answer: D

NEW QUESTION 7
SCOTT is a user in the database.
Evaluate the commands issued by the DBA:

Which statement is true regarding the execution of the above commands?

• A. Statement 1 would not execute because the WITH GRANT option is missing.
• B. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
• C. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
• D. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

Answer: B

NEW QUESTION 8
On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command?

• A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
• B. It fails because no SQL *Loader data file location is specified.
• C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
• D. It fails because no SQL *Loader control file location is specified.

Answer: AC

NEW QUESTION 9
You execute the following commands: SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary FROM employees
WHERE hire_date > '&hiredate' AND manager_id >&mgr_id;
For which substitution variables are you prompted for the input?

• A. none, because no input required
• B. both the substitution variables ''hiredate' and 'mgr_id'.
• C. only hiredate'
• D. only 'mgr_id'

Answer: D

NEW QUESTION 10
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)

• A. The CUST_STATUS column would store exactly one character.
• B. The TRANS_VALIDITY column would have a maximum size of one character.
• C. The CUST_CREDIT_LIMIT column would be able to store decimal values.
• D. The CUST_STATUS column would give an error.
• E. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
• F. The TRANS_VALIDITY column would give an error.

Answer: AF

NEW QUESTION 11
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

• A. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• D. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS

Answer: B

NEW QUESTION 12
Which two statements are true regarding single row functions? (Choose two.)

• A. MOD : returns the quotient of a division.
• B. TRUNC : can be used with NUMBER and DATE values.
• C. CONCAT : can be used to combine any number of values.
• D. SYSDATE : returns the database server current date and time.
• E. INSTR : can be used to find only the first occurrence of a character in a string.
• F. TRIM : can be used to remove all the occurrences of a character from a string.

Answer: BD

NEW QUESTION 13
Which three statements are true regarding the data types?

• A. The minimum column width that can be specified for a VARCHAR2 data type column is one.
• B. Only one LONG column can be used per table.
• C. A TIMESTAMP data type column stores only time values with fractional seconds.
• D. The BLOB data type column is used to store binary data in an operating system file.
• E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Answer: ABE

NEW QUESTION 14
Which three statements are true about multiple-row subqueries?

• A. They can contain a subquery within a subquery.
• B. They can return multiple columns as well as rows.
• C. They cannot contain a subquery within a subquery.
• D. They can return only one column but multiple rows.
• E. They can contain group functions and GROUP BY and HAVING clauses.
• F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Answer: ABE

NEW QUESTION 15
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 16
You must create a table for a banking application. (Choose the best answer.) One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid. Which data type would you use?

• A. Date
• B. Number
• C. Timestamp
• D. Interval day to second
• E. Interval year to month

Answer: D

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

• A. A role can be granted to itself.
• B. A role can be granted to PUBLIC.
• C. A user can be granted only one role at any point of time.
• D. The REVOKE command can be used to remove privileges but not roles from other users.
• E. Roles are named groups of related privileges that can be granted to users or other roles.

Answer: BE

Explanation: References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28