1z1-071 by Oracle Valid Free Exam Practice Test

Question 1

You must create a table for a banking application.
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. INTERVAL DAY TO SECOND B. DATE C. TIMESTAMP D. NUMBER E. INTERVAL YEAR TO MONTH

Correct Answer: A

Question 2

In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

A. It gives an error because multiple NVL functions are used in an expression. B. It executes successfully but no rows updated. C. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days. D. It gives an error because NVL function cannot be used with UPDATE.

Correct Answer: C

Question 3

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 outer query executes only once for the result returned by the inner query. C. Each row returned by the outer query is evaluated for the results returned by the inner query. D. The nested query executes first and then the outer query executes.

Correct Answer: A,C

Question 4

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 join tables that have columns with the same name and compatible data types. C. It can be used to access data from tables through equijoins as well as nonequijoins. D. It can be used to restrict the number of columns used in a NATURAL join.

Correct Answer: B,D

Question 5

Examine thestructureof the members table:

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_* ;

Correct Answer: C

Question 6

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

A. The statement would not execute because the GROUP BY clause cannot be used in the inline view. B. The statement would not execute because inline views and outer joins cannot be used together. C. The statement would execute successfully to produce the required output. D. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

Correct Answer: C

Question 7

BOOK_SEQis an existing sequence in your schema.
Which two CREATE TABLEcommands are valid? (Choose two.)

A.

B.

C.

D.

E.

Correct Answer: B,D

Question 8

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 INITCAP (cust_last_name) "Name", cust_credit_limit + 1000 INITCAP ("NEW CREDIT LIMIT") FROM customers; B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 "New Credit Limit" FROM customers; C. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers; D. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;

Correct Answer: D

Question 9

You execute this command:
ALTER TABLE employees SET UNUSED(department_id)
Which two are true? (Choose two.)

A. The DEPARTMENT_ID column is set to null for all rows in the table. B. No updates can be made to the data in the DEPARTMENT_ID column. C. The DEPARTMENT_ID column can be recovered from the recycle bin. D. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table. E. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued. F. A query can be display data from the DEPARTMENT_ID column.

Correct Answer: B,D

Question 10

Which two statements are true regarding the COUNT function? (Choose two.)

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

Correct Answer: C,E

Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).

Question 11

Which statements are true? (Choose all that apply.)

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

Correct Answer: A,B,E

Oracle Database SQL - 1z1-071 Exam Practice Test