1Z0-071 Free Practice Test

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?

1. A. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;
2. B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
3. C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
4. D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Correct Answer: B

Which statement is true about transactions?

1. A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
2. B. Each Data Definition Language (DDL) statement executed forms a single transaction.
3. C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
4. D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Correct Answer: B
References:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038

View the exhibit and examine the structure of the STORES table. STORES table
NameNull?Type
---------------------- ------------- STORE_IDNUMBER NAMEVARCHAR2(100)
ADDRESSVARCHAR2(200) CITYVARCHAR2(100) COUNTRYVARCHAR2(100) START_DATEDATE END_DATEDATE PROPERTY_PRICENUMBER
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?

1. A. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, '01-JAN-2000')<=36;
2. B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERETO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
3. C. SELECT name, address||','||city||','||country AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
4. D. SELECT name, concat (address||','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Correct Answer: D

View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM departments
WHERE departments_id=90 UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort output?

1. A. ORDER BY DEPT_NAME;
2. B. ORDER BY DEPT_ID;
3. C. ORDER BY 'b';
4. D. ORDER BY 3;

Correct Answer: BD

View the Exhibits and examine PRODUCTS and SALES tables. Exhibit 1

Exhibit 2

You issue the following query to display product name the number of times the product has been sold:

What happens when the above statement is executed?

1. A. The statement executes successfully and produces the required output.
2. B. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
3. C. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
4. D. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.

Correct Answer: A