Get Latest 1z0-071 Dumps Questions - 2019 1z0-071 Dumps - Realexamdumps.com
                            O   r  a  c  l e    1  z  0-071 Dumps PDF
Oracle Database 12c SQL
For More Info:
https://www.realexamdumps.com/oracle/1z0-071-practice-test.html
 
Question: 1 
   
Evaluate the following SQL statement: 
SQL> select cust_id, cust_last_name "Last name" 
FROM customers 
WHERE country_id = 10 
UNION 
SELECT cust_id CUST_NO, cust_last_name 
FROM customers 
WHERE country_id = 30 
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.) 
 
A. ORDER BY "Last name" 
B. ORDER BY 2, cust_id 
C. ORDER BY CUST_NO 
D. ORDER BY 2,1 
E. ORDER BY "CUST_NO" 
 
Answer: A,B,D     
 
Using the ORDER BY Clause in Set Operations 
-The ORDER BY clause can appear only once at the end of the compound query. 
-Component queries cannot have individual ORDER BY clauses. 
-The ORDER BY clause recognizes only the columns of the first SELECT query. 
-By default, the first column of the first SELECT query is used to sort the output in an ascending order. 
 
Question: 2 
   
Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? 
(Choose three.) 
 
A. WHERE and HAVING clauses cannot be used together in a SQL statement. 
B. The HAVING clause conditions can have aggregate functions. 
C. The HAVING clause conditions can use aliases for the columns. 
D. The WHERE clause is used to exclude rows before the grouping of data. 
E. The HAVING clause is used to exclude one or more aggregated results after grouping data. 
 
Answer: A,B,D     
 
Question: 3 
   
Which statement is true regarding external tables? 
 
 
A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the 
database from an external table. 
B. The data and metadata for an external table are stored outside the database. 
C. The default REJECT LIMIT for external tables is UNLIMITED. 
D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an 
external table. 
 
Answer: A     
 
Explanation: 
References: 
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm 
 
Question: 4 
   
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.) 
 
A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table. 
B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table. 
C. A DELETE FROM..... statement can remove rows based on only a single condition on a table. 
D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a 
table. 
E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table. 
F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a 
table. 
 
Answer: B,E     
 
Explanation: 
References: 
http://www.techonthenet.com/sql/and_or.php 
 
Question: 5 
   
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: B,E     
 
Explanation: 
References: 
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28 
 
Question: 6 
   
Which two statements are true regarding constraints? (Choose two.) 
 
A. A constraint is enforced only for an INSERT operation on a table. 
B. A foreign key cannot contain NULL values. 
C. A column with the UNIQUE constraint can store NULLS. 
D. You can have more than one column in a table as part of a primary key. 
 
Answer: C,D     
 
Question: 7 
   
Evaluate the following statement. 
 
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT 
statement? 
 
A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any 
other WHEN clause. 
B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be 
evaluated by the subsequent WHEN clauses. 
C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be 
evaluated by the subsequent WHEN clauses. 
D. The insert statement would give an error because the ELSE clause is not present for support in 
case none of WHEN clauses are true. 
 
Answer: A     
 
 
Explanation: 
References: 
http://psoug.org/definition/WHEN.htm 
Question: 8 
   
Examine the structure of 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%'; 
 
Answer: B     
 
Question: 9 
   
You want to display 5 percent of the rows from the SALES table for products with the lowest 
AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this 
causes the output to exceed 5 percent of the rows. 
Which query will provide the required result? 
 
A. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT 
ROWS WITH TIES; 
B. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT 
ROWS ONLY WITH TIES; 
C. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT 
ROWS WITH TIES ONLY; 
D. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT 
ROWS ONLY; 
 
Answer: A     
 
 
Question: 10 
   
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     
 
Question: 11 
   
You issue the following command to drop the PRODUCTS table: 
SQL > DROP TABLE products; 
Which three statements are true about the implication of this command? (Choose three.) 
 
A. All data along with the table structure is deleted. 
B. A pending transaction in the session is committed. 
C. All indexes on the table remain but they are invalidated. 
D. All views and synonyms on the table remain but they are invalidated. 
E. All data in the table is deleted but the table structure remains. 
 
Answer: A,B,D     
 
Question: 12 
   
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     
 
Question: 13 
   
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. 
ORDER_ID is the primary key in the ORDERS table and the foreign key of the ORDER_ITEMS table, 
whose constraint is defined with the ON DELETE CASCADE option. 
Which DELETE statement would execute successfully? 
 
 
A. DELETE orders o, order_items iWHERE o.order_id = i.order_id; 
B. DELETEFROM ordersWHERE (SELECT order_idFROM order_items); 
C. DELETE ordersWHERE order_total < 1000; 
D. DELETE order_idFROM ordersWHERE order_total < 1000; 
 
Answer: C     
 
 
Question: 14 
   
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     
 
Question: 15 
   
 
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. 
 
You want to update EMPLOYEES table as follows: 
Update only those employees who work in Boston or Seattle (locations 2900 and 2700). 
Set department_id for these employees to the department_id corresponding to London (location_id 
2100). 
Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. 
Set the employees' commission in location_id 2100 to 1.5 times the average commission of their 
department. 
You issue the following command: 
 
What is outcome? 
 
A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified 
together in an UPDATE statement. 
 
B. It generates an error because a subquery cannot have a join condition in a UPDATE statement. 
C. It executes successfully and gives the desired update. 
D. It executes successfully but does not give the desired update. 
 
Answer: D     
 
Question: 16 
   
Evaluate the following two queries: 
 
Which statement is true regarding the above two queries? 
 
A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT 
column. 
B. There would be no change in performance. 
C. Performance would degrade in query 2. 
D. Performance would improve in query 2. 
 
Answer: B     
 
Question: 17 
   
Examine the business rule: 
Each student can work on multiple projects and each project can have multiple students. 
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for 
generating reports in this format: 
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK 
Which two statements are true in this scenario? (Choose two.) 
 
A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities. 
B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must 
be resolved into 1:M relationships. 
C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS 
entity. 
D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS 
entity. 
 
E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which 
is the foreign key linked to the STUDENTS and PROJECTS entities. 
 
Answer: B,E     
 
Explanation: 
References: 
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html 
Question: 18 
   
View the Exhibit and examine the details of PRODUCT_INFORMATION table. 
 
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID 
column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the 
following SQL statement: 
SELECT product_name 
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 same column has been used in both sides of the AND logical 
 
operator to form the condition. 
B. It would not execute because the entire WHERE clause condition is not enclosed within the 
parentheses. 
C. It would execute and the output would display the desired result. 
D. It would execute but the output would return no rows. 
 
Answer: D     
 
Question: 19 
   
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? 
(Choose two.) 
 
A. The outer query stops evaluating the result set of the inner query when the first value is found. 
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer 
query. 
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner 
query. 
D. The outer query continues evaluating the result set of the inner query until all the values in the 
result set are processed. 
 
Answer: A,C     
 
Explanation: 
References: 
http://www.techonthenet.com/oracle/exists.php 
Question: 20 
   
View the exhibit and examine the structure of the STORES table. 
 
You must display the NAME of stores 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? 
 
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')