Oracle 1z0-071
 Oracle Database 12c SQL
Version: 4.0
QUESTION NO: 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.
 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"
 ,B,D
Explanation:
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.
 Oracle 1z0-071 Exam
  2
 QUESTION NO: 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.
 ,B,D
Explanation:
QUESTION NO: 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.
 
Oracle 1z0-071 Exam
  3
 Explanation:
References:
 https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm
 QUESTION NO: 4
Which two statements are true about Data Manipulation Language (DML) statements?
 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.
 ,E
Explanation:
References:
 http://www.techonthenet.com/sql/and_or.php
 QUESTION NO: 5
Which two statements are true regarding roles? (Choose two.)
 A.
A role can be granted to itself.
 Oracle 1z0-071 Exam
  4
 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.
 ,E
Explanation:
References:
 http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28
 QUESTION NO: 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.
The column with a UNIQUE constraint can store NULLS.
 D.
You can have more than one column in a table as part of a primary key.
 ,D
Explanation:
QUESTION NO: 7
Evaluate the following statement.
 Oracle 1z0-071 Exam
  5
 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.
 
Explanation:
References:
 http://psoug.org/definition/WHEN.htm
 QUESTION NO: 8
Examine the structure of the MEMBERS table:
 Oracle 1z0-071 Exam
  6
 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%';
 
Explanation:
QUESTION NO: 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_sold
 FROM sales
 ORDER BY amount_sold
 FETCH FIRST 5 PERCENT ROWS WITH TIES;
 Oracle 1z0-071 Exam
  7
 B.
SELECT prod_id, cust_id, amount_sold
 FROM sales
 ORDER BY amount_sold
 FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
 C.
SELECT prod_id, cust_id, amount_sold
 FROM sales
 ORDER BY amount_sold
 FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
 D.
SELECT prod_id, cust_id, amount_sold
 FROM sales
 ORDER BY amount_sold
 FETCH FIRST 5 PERCENT ROWS ONLY;
 
Explanation:
QUESTION NO: 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:
 Oracle 1z0-071 Exam
  8
 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.
 
Explanation:
QUESTION NO: 11
You issue the following command to drop the PRODUCTS table: (Choose all that apply.)
 SQL > DROP TABLE products;
 Which three statements are true about the implication of this command?
 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.
 Oracle 1z0-071 Exam
  9
 E.
All data in the table is deleted but the table structure remains.
 ,B,D
Explanation:
QUESTION NO: 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'
 
Explanation:
QUESTION NO: 13
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
 Oracle 1z0-071 Exam
  10
 ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the
 ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
 Which DELETE statement would execute successfully?
 A.
DELETE orders o, order_items I
 WHERE o.order_id = i.order_id;
 B.
DELETE
 FROM orders
 WHERE (SELECT order_id
 FROM order_items);
 C.
DELETE orders
 WHERE order_total < 1000;
 D.
DELETE order_id
 FROM orders
 WHERE order_total < 1000;
 
Explanation:
Oracle 1z0-071 Exam
  11
 QUESTION NO: 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;
 
Oracle 1z0-071 Exam
  12
 Explanation:
QUESTION NO: 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:
 Oracle 1z0-071 Exam
  13
 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
 
Explanation:
QUESTION NO: 16
Evaluate the following two queries:
 Which statement is true regarding the above two queries?
 A.
Oracle 1z0-071 Exam
  14
 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.
 
Explanation:
QUESTION NO: 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.
Oracle 1z0-071 Exam
  15
 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.
 ,E
Explanation:
References:
 http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html
 QUESTION NO: 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
 Oracle 1z0-071 Exam
  16
 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.
 
Explanation:
QUESTION NO: 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.
 ,C
Oracle 1z0-071 Exam
  17
 Explanation:
References:
 http://www.techonthenet.com/oracle/exists.php
 QUESTION NO: 20
View the exhibit and examine the structure of the STORES table.
 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?
 A.
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
 start_date,
 property_price, property_price*115/100
 FROM stores
 WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
 B.
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
 start_date,
 Oracle 1z0-071 Exam
  18
 property_price, property_price*115/100
 FROM stores
 WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
 C.
SELECT name, address||','||city||','||country AS full_address,
 start_date,
 property_price, property_price*115/100
 FROM stores
 WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
 D.
SELECT name, concat (address||','| |city| |', ', country) AS full_address,
 start_date,
 property_price, property_price*115/100
 FROM stores
 WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
 
Explanation:
QUESTION NO: 21
The BOOKS_TRANSACTIONS table exists in your database.
 SQL>SELECT * FROM books_transactions ORDER BY 3;
 What is the outcome on execution?
 A.
The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
 B.
Rows are displayed in the order that they are stored in the table only for the three rows with the
 lowest values in the key column.
 Oracle 1z0-071 Exam
  19
 C.
Rows are displayed in the order that they are stored in the table only for the first three rows.
 D.
Rows are displayed sorted in ascending order of the values in the third column in the table.
 
Explanation:
QUESTION NO: 22
Examine the command:
 What does ON DELETE CASCADE imply?
 A.
When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
 B.
When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted
 but the table structure is retained.
 C.
When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose
 BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
 D.
When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in
 the BOOKS_TRANSACTIONS.BOOK_ID column.
 
Explanation:
QUESTION NO: 23
View the exhibit and examine the structure of the EMPLOYEES table.
 Oracle 1z0-071 Exam
  20
 You want to display all employees and their managers having 100 as the MANAGER_ID. You
 want the output in two columns: the first column would have the LAST_NAME of the managers
 and the second column would have LAST_NAME of the employees.
 Which SQL statement would you execute?
 A.
SELECT m.last_name "Manager", e.last_name "Employee"
 FROM employees m JOIN employees e
 ON m.employee_id = e.manager_id
 WHERE m.manager_id = 100;
 B.
SELECT m.last_name "Manager", e.last_name "Employee"
 FROM employees m JOIN employees e
 ON m.employee_id = e.manager_id
 WHERE e.manager_id = 100;
 C.
SELECT m.last_name "Manager", e.last_name "Employee"
 FROM employees m JOIN employees e
 ON e.employee_id = m.manager_id
 WHERE m.manager_id = 100;
 D.
SELECT m.last_name "Manager", e.last_name "Employee"
 FROM employees m JOIN employees e
 WHERE m.employee_id = e.manager_id and AND e.manager_id = 100
 Oracle 1z0-071 Exam
  21
 
Explanation:
QUESTION NO: 24
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.
 ,B,E
Explanation:
QUESTION NO: 25
Examine the structure of the EMPLOYEES table.
 Oracle 1z0-071 Exam
  22
 There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
 You want to display the last names and manager IDs of employees who work for the same
 manager as the employee whose EMPLOYEE_ID is 123.
 Which query provides the correct output?
 A.
SELECT e.last_name, m.manager_id
 FROM employees e RIGHT OUTER JOIN employees m
 on (e.manager_id = m.employee_id)
 AND e.employee_id = 123;
 B.
SELECT e.last_name, m.manager_id
 FROM employees e LEFT OUTER JOIN employees m
 on (e.employee_id = m.manager_id)
 WHERE e.employee_id = 123;
 C.
SELECT e.last_name, e.manager_id
 FROM employees e RIGHT OUTER JOIN employees m
 on (e.employee_id = m.employee_id)
 WHERE e.employee_id = 123;
 D.
SELECT m.last_name, e.manager_id
 Oracle 1z0-071 Exam
  23
 FROM employees e LEFT OUTER JOIN employees m
 on (e.manager_id = m.manager_id)
 WHERE e.employee_id = 123;
 
Explanation:
QUESTION NO: 26
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
 
Explanation:
References:
 https://blog.udemy.com/database-normal-forms/
 QUESTION NO: 27
Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being
 duplicated across the tables. You want to display the results from the SALES1 table, which are not
 present in the SALES2 table.
 Oracle 1z0-071 Exam
  24
 Which set operator generates the required output?
 A.
INTERSECT
 B.
UNION
 C.
PLUS
 D.
MINUS
 E.
SUBTRACT
 
Explanation:
References:
 https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
 QUESTION NO: 28
Evaluate the following ALTER TABLE statement:
 Oracle 1z0-071 Exam
  25
 ALTER TABLE orders
 SET UNUSED (order_date);
 Which statement is true?
 A.
After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to
 the ORDERS table.
 B.
The ORDER_DATE column should be empty for the ALTER TABLE command to execute
 successfully.
 C.
ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
 D.
The DESCRIBE command would still display the ORDER_DATE column.
 
Explanation:
QUESTION NO: 29
Evaluate the following SQL statements that are issued in the given order:
 CREATE TABLE emp
 (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
 ename VARCHAR2(15),
 salary NUMBER (8,2),
 mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));
 ALTER TABLE emp
 DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
 ALTER TABLE emp
 ENABLE CONSTRAINT emp_emp_no_pk;
 Oracle 1z0-071 Exam
  26
 What would be the status of the foreign key EMP_MGR_PK?
 A.
It would remain disabled and can be enabled only by dropping the foreign key constraint and
 recreating it.
 B.
It would remain disabled and has to be enabled manually using the ALTER TABLE command.
 C.
It would be automatically enabled and immediate.
 D.
It would be automatically enabled and deferred.
 
Explanation:
QUESTION NO: 30
Which three statements are true regarding the data types? (Choose three.)
 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.
 ,B,E
Explanation:
QUESTION NO: 31
Oracle 1z0-071 Exam
  27
 Which three statements are true regarding subqueries? (Choose three.)
 A.
Multiple columns or expressions can be compared between the main query and subquery.
 B.
Subqueries can contain ORDER BY but not the GROUP BY clause.
 C.
Main query and subquery can get data from different tables.
 D.
Subqueries can contain GROUP BY and ORDER BY clauses.
 E.
Main query and subquery must get data from the same tables.
 F.
Only one column or expression can be compared between the main query and subquery.
 ,C,D
Explanation:
References:
 http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html
 QUESTION NO: 32
Which statement is true regarding the default behavior of the ORDER BY clause?
 A.
In a character sort, the values are case-sensitive.
 B.
NULL values are not considered at all by the sort operation.
 C.
Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
 D.
Numeric values are displayed from the maximum to the minimum value if they have decimal
 positions.
 
Oracle 1z0-071 Exam
  28
 Explanation:
QUESTION NO: 33
Examine the structure of the MEMBERS table.
 Which query can be used to display the last names and city names only for members from the
 states MO and MI?
 A.
SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
 B.
SELECT last_name, city FROM members WHERE state LIKE 'M%';
 C.
SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
 D.
SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';
 
Explanation:
QUESTION NO: 34
Which two statements are true regarding the COUNT function?
 A.
A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a
 WHERE clause.
 B.
Oracle 1z0-071 Exam
  29
 COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and
 NULL values in the INV_AMT column.
 C.
COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and
 NULL value in the CUST_ID column.
 D.
COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value
 in any of the columns.
 E.
The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.
 ,D
Explanation:
QUESTION NO: 35
Which task can be performed by using a single Data Manipulation Language (DML) statement?
 A.
adding a column constraint when inserting a row into a table
 B.
adding a column with a default value when inserting a row into a table
 C.
removing all data only from one single column on which a unique constraint is defined
 D.
removing all data only from one single column on which a primary key constraint is defined
 
Explanation:
QUESTION NO: 36
Examine the structure of the BOOKS_TRANSACTIONS table:
 Oracle 1z0-071 Exam
  30
 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 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM
 BOOKS_TRANSACTIONS;
 C.
SELECT member_id AS "MEMBER ID", due_date AS "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;
 
Explanation:
QUESTION NO: 37
In which three situations does a transaction complete?
 A.
when a PL/SQL anonymous block is executed
 B.
when a DELETE statement is executed
 C.
when a ROLLBACK command is executed
 D.
Oracle 1z0-071 Exam
  31
 when a data definition language (DDL) statement is executed
 E.
when a TRUNCATE statement is executed after the pending transaction
 ,D,E
Explanation:
References:
 https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
 QUESTION NO: 38
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
 Evaluate the following MERGE statement:
 MERGE_INTO orders_master o
 USING monthly_orders m
 ON (o.order_id = m.order_id)
 WHEN MATCHED THEN
 UPDATE SET o.order_total = m.order_total
 DELETE WHERE (m.order_total IS NULL)
 Oracle 1z0-071 Exam
  32
 WHEN NOT MATCHED THEN
 INSERT VALUES (m.order_id, m.order_total)
 What would be the outcome of the above statement?
 A.
The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
 B.
The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
 C.
The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
 D.
The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
 
Explanation:
References:
 https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
 QUESTION NO: 39
Evaluate the following SQL statement:
 SELECT product_name || 'it's not available for order'
 FROM product_information
 WHERE product_status = 'obsolete';
 You received the following error while executing the above query:
 ERROR
 ORA-01756: quoted string not properly terminated
 What would you do to execute the query successfully?
 Oracle 1z0-071 Exam
  33
 A.
Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal
 character string.
 B.
Enclose the literal character string in the SELECT clause within the double quotation marks.
 C.
Do not enclose the character literal string in the SELECT clause within the single quotation marks.
 D.
Use escape character to negate the single quotation mark inside the literal character string in the
 SELECT clause.
 
Explanation:
References:
 http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
 QUESTION NO: 40
View the exhibit and examine the ORDERS table.
 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 orders
 MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
 B.
ALTER TABLE orders
 ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
 C.
Oracle 1z0-071 Exam
  34
 ALTER TABLE orders
 MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
 D.
ALTER TABLE orders
 ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
 
Explanation:
QUESTION NO: 41
Examine the structure of the INVOICE table.
 Which two SQL statements would execute successfully?
 A.
SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')
 FROM invoice;
 B.
SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')
 FROM invoice;
 C.
SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)
 FROM invoice;
 D.
SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')
 FROM invoice;
 ,C
Oracle 1z0-071 Exam
  35
 Explanation:
QUESTION NO: 42
Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?
 A.
A column can be dropped only if it does not contain any data.
 B.
A column can be dropped only if another column exists in the table.
 C.
A dropped column can be rolled back.
 D.
The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
 E.
A parent key column in the table cannot be dropped.
 ,D,E
Explanation:
QUESTION NO: 43
View the exhibit and examine the description of the PRODUCT_INFORMATION table.
 Oracle 1z0-071 Exam
  36
 Which SQL statement would retrieve from the table the number of products having LIST_PRICE
 as NULL?
 A.
SELECT COUNT (DISTINCT list_price)
 FROM product_information
 WHERE list_price is NULL
 B.
SELECT COUNT (NVL(list_price, 0))
 FROM product_information
 WHERE list_price is NULL
 C.
SELECT COUNT (list_price)
 FROM product_information
 WHERE list_price i= NULL
 D.
SELECT COUNT (list_price)
 FROM product_information
 WHERE list_price is NULL
 
Explanation:
QUESTION NO: 44
Which three tasks can be performed using SQL functions built into Oracle Database?
 A.
displaying a date in a nondefault format
 B.
finding the number of characters in an expression
 C.
substituting a character string in a text expression with a specified string
 Oracle 1z0-071 Exam
  37
 D.
combining more than two columns or expressions into a single column in the output
 ,B,C
Explanation:
QUESTION NO: 45
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT
 command:
 GRANT ALL
 ON orders, order_items
 TO PUBLIC;
 What must be done to fix the 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.
 
Explanation:
References:
 http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
 QUESTION NO: 46
You are designing the structure of a table in which two columns have the specifications:
 Oracle 1z0-071 Exam
  38
 COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and must
 uniquely identify the row
 EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the
 maximum precision and is used for calculations and comparisons between components.
 Which two options define the data types that satisfy these requirements most efficiently? (Choose
 two.)
 A.
The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
 B.
The EXECUTION_DATETIME must be of TIMESTAMP data type.
 C.
The EXECUTION_DATETIME must be of DATE data type.
 D.
The COMPONENT_ID must be of ROWID data type.
 E.
The COMPONENT_ID must be of VARCHAR2 data type.
 F.
The COMPONENT_ID column must be of CHAR data type.
 ,F
Explanation:
QUESTION NO: 47
You want to display the date for the first Monday of the next month and issue the following
 command:
 What is the outcome?
 A.
In generates an error because rrrr should be replaced by rr in the format string.
 B.
Oracle 1z0-071 Exam
  39
 It executes successfully but does not return the correct result.
 C.
It executes successfully and returns the correct result.
 D.
In generates an error because TO_CHAR should be replaced with TO_DATE.
 E.
In generates an error because fm and double quotation marks should not be used in the format
 string.
 
Explanation:
QUESTION NO: 48
Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose
 two.)
 A.
You can use column alias in the GROUP BY clause.
 B.
Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
 C.
The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT
 clause.
 D.
Using the WHERE clause before the GROUP BY clause excludes the rows before creating
 groups.
 E.
If the SELECT clause has an aggregate function, then those individual columns without an
 aggregate function in the SELECT clause should be included in the GROUP BY cause.
 ,E
Explanation:
QUESTION NO: 49
Oracle 1z0-071 Exam
  40
 Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
 You want to generate a list of all department IDs along with any course IDs that may have been
 assigned to them.
 Which SQL statement must you use?
 A.
SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN
 course_details c ON (d.department_id=c. department_id);
 B.
SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN
 course_details c ON (d.department_id=c. department_id);
 C.
SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN
 department_details d ON (c.department_id=d. department_id);
 D.
SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN
 course_details c ON (c.department_id=d. department_id);
 
Explanation:
QUESTION NO: 50
Which two tasks can be performed by using Oracle SQL statements? (Choose two.)
 A.
changing the password for an existing database user
 Oracle 1z0-071 Exam
  41
 B.
connecting to a database instance
 C.
querying data from tables in different databases
 D.
starting up a database instance
 E.
executing operating system (OS) commands in a session
 ,C
Explanation:
References:
 http://www.techonthenet.com/oracle/password.php
 https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm
 QUESTION NO: 51
View the exhibit for the structure of the STUDENT and FACULTY tables.
 You need to display the faculty name followed by the number of students handled by the faculty at
 the base location.
 Examine the following two SQL statements:
 Statement 1
 Oracle 1z0-071 Exam
  42
 SQL>SELECT faculty_name, COUNT(student_id)
 FROM student JOIN faculty
 USING (faculty_id, location_id)
 GROUP BY faculty_name;
 Statement 2
 SQL>SELECT faculty_name, COUNT(student_id)
 FROM student NATURAL JOIN faculty
 GROUP BY faculty_name;
 Which statement is true regarding the outcome?
 A.
Only statement 2 executes successfully and gives the required result.
 B.
Only statement 1 executes successfully and gives the required result.
 C.
Both statements 1 and 2 execute successfully and give different results.
 D.
Both statements 1 and 2 execute successfully and give the same required result.
 
Explanation:
QUESTION NO: 52
Which statement correctly grants a system privilege?
 A.
GRANT CREATE VIEW
 ON table1 TO
 user1;
 Oracle 1z0-071 Exam
  43
 B.
GRANT ALTER TABLE
 TO PUBLIC;
 C.
GRANT CREATE TABLE
 TO user1, user2;
 D.
GRANT CREATE SESSION
 TO ALL;
 
Explanation:
QUESTION NO: 53
View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.
 Which INSERT statement should be used to add a row into the ORDERS table for the customer
 whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one
 row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.
 A.
INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total
 FROM orders o, customers c
 Oracle 1z0-071 Exam
  44
 WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)
 VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id
 FROM customers
 WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
 B.
INSERT INTO orders (order_id, order_date, order_mode,
 (SELECT customer id
 FROM customers
 WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);
 VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
 C.
INSERT INTO orders
 VALUES (1,'10-mar-2007', 'direct',
 (SELECT customer_id
 FROM customers
 WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
 D.
INSERT INTO orders (order_id, order_date, order_mode,
 (SELECT customer_id
 FROM customers
 WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);
 VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
 
Explanation:
QUESTION NO: 54
Which three statements are correct regarding indexes? (Choose three.)
 Oracle 1z0-071 Exam
  45
 A.
A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to
 creates a unique index.
 B.
Indexes should be created on columns that are frequently referenced as part of any expression.
 C.
When a table is dropped, the corresponding indexes are automatically dropped.
 D.
For each DML operation performed, the corresponding indexes are automatically updated.
 ,C,D
Explanation:
References:
 http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/
 QUESTION NO: 55
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
 The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and
 DEPARTMENT NAME, the following SQL statement was written:
 SELECT employee_id, first_name, department_name
 FROM employees
 NATURAL JOIN departments;
 The desired output is not obtained after executing the above SQL statement. What could be the
 Oracle 1z0-071 Exam
  46
 reason for this?
 A.
The table prefix is missing for the column names in the SELECT clause.
 B.
The NATURAL JOIN clause is missing the USING clause.
 C.
The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
 D.
The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column
 name and data type.
 
Explanation:
Natural join needs only one column to be the same in each table. The EMPLOYEES and
 DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)
 QUESTION NO: 56
Which two statements are true about sequences created in a single instance Oracle database?
 (Choose two.)
 A.
When the MAXVALUE limit for the sequence is reached, it can be increased by using the ALTER
 SEQUENCE statement.
 B.
DELETE <sequencename> would remove a sequence from the database.
C.
The numbers generated by an explicitly defined sequence can only be used to insert data in one
 table.
 D.
CURRVAL is used to refer to the most recent sequence number that has been generated for a
 particular sequence.
 E.
When a database instance shuts down abnormally, the sequence numbers that have been cached
 but not used are available again when the instance is restarted.
 Oracle 1z0-071 Exam
  47
 ,D
Explanation:
References:
 http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
 https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm
 QUESTION NO: 57
View the exhibit and examine the structure of the CUSTOMERS table.
 Which two tasks would require subqueries or joins to be executed in a single statement?
 A.
finding the number of customers, in each city, whose credit limit is more than the average credit
 limit of all the customers
 B.
finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
 C.
listing of customers who do not have a credit limit and were born before 1980
 D.
finding the number of customers, in each city, who’s marital status is 'married'.
 E.
Oracle 1z0-071 Exam
  48
 listing of those customers, whose credit limit is the same as the credit limit of customers residing in
 the city 'Tokyo'.
 ,E
Explanation:
QUESTION NO: 58
Which statement is true about transactions?
 A.
A set of Data Manipulation Language (DML) statements executed in a sequence ending with a
 SAVEPOINT forms a single transaction.
 B.
Each Data Definition Language (DDL) statement executed forms a single transaction.
 C.
A set of DDL statements executed in a sequence ending with a COMMIT forms a single
 transaction.
 D.
A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms
 a single transaction.
 
Explanation:
References:
 https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
 QUESTION NO: 59
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
 Oracle 1z0-071 Exam
  49
 You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of
 items in each order.
 Which CREATE VIEW statement would create the views successfully?
 A.
CREATE OR REPLACE VIEW ord_vu
 AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
 FROM orders o JOIN order_items i
 ON (o.order_id = i.order_id)
 GROUP BY o.order_id, o.order_date;
 B.
CREATE OR REPLACE VIEW ord_vu (order_id, order_date)
 AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
 "NO OF ITEMS"
 FROM orders o JOIN order_items i
 ON (o.order_id = i.order_id)
 GROUP BY o.order_id, o.order_date;
 C.
CREATE OR REPLACE VIEW ord_vu
 AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
 "NO OF ITEMS"
 Oracle 1z0-071 Exam
  50
 FROM orders o JOIN order_items i
 ON (o.order_id = i.order_id)
 GROUP BY o.order_id, o.order_date;
 D.
CREATE OR REPLACE VIEW ord_vu
 AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
 "NO OF ITEMS"
 FROM orders o JOIN order_items i
 ON (o.order_id = i.order_id)
 GROUP BY o.order_id, o.order_date
 WHITH CHECK OPTION;
 
Explanation:
QUESTION NO: 60
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.
 
Explanation:
Oracle 1z0-071 Exam
  51
 QUESTION NO: 61
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.
 
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.
 QUESTION NO: 62
Examine the following query:
 SQL> SELECT prod_id, amount_sold
 FROM sales
 ORDER BY amount_sold
 FETCH FIRST 5 PERCENT ROWS ONLY;
 What is the output of this query?
 A.
It displays 5 percent of the products with the highest amount sold.
 B.
It displays the first 5 percent of the rows from the SALES table.
 Oracle 1z0-071 Exam
  52
 C.
It displays 5 percent of the products with the lowest amount sold.
 D.
It results in an error because the ORDER BY clause should be the last clause.
 
Explanation:
References:
 https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
 QUESTION NO: 63
The first DROP operation is performed on PRODUCTS table using this command:
 DROP TABLE products PURGE;
 Then a FLASHBACK operation is performed using this command:
 FLASHBACK TABLE products TO BEFORE DROP;
 Which is true about the result 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.
 
Explanation:
References:
 https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm
 Oracle 1z0-071 Exam
  53
 QUESTION NO: 64
The following are the steps for a correlated subquery, listed in random order:
 The WHERE clause of the outer query is evaluated.
 The candidate row is fetched from the table specified in the outer query.
 This is repeated for the subsequent rows of the table, till all the rows are processed.
 Rows are returned by the inner query, after being evaluated with the value from the candidate
 row in the outer query.
 Which is the correct sequence in which the Oracle server evaluates a correlated subquery?
 A.
2, 1, 4, 3
 B.
4, 1, 2, 3
 C.
4, 2, 1, 3
 D.
2, 4, 1, 3
 
Explanation:
References:
 http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html
 QUESTION NO: 65
Evaluate the following query:
 SQL> SELECT TRUNC (ROUND (156.00, -1),-1)
 FROM DUAL;
 Oracle 1z0-071 Exam
  54
 What would be the outcome?
 A.
150
 B.
200
 C.
160
 D.
16
 E.
100
 
Explanation:
QUESTION NO: 66
Examine the data in the CUST_NAME column of the CUSTOMERS table.
 CUST_NAME
 -------------------
 Renske Ladwig
 Jason Mallin
 Samuel McCain
 Allan MCEwen
 Irene Mikilineni
 Julia Nayer
 You need to display customers' second names where the second name starts with "Mc" or "MC".
 Which query gives the required output?
 Oracle 1z0-071 Exam
  55
 A.
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
 FROM customers
 WHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
 LIKE INITCAP ('MC%');
 B.
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
 FROM customers
 WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =
 'Mc';
 C.
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
 FROM customers
 WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
 LIKE 'Mc%';
 D.
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
 FROM customers
 WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =
 INITCAP 'MC%';
 
Explanation:
QUESTION NO: 67
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES
 tables.
 Oracle 1z0-071 Exam
  56
 The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS
 table.
 Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table
 referencing the CUSTOMERS and TIMES tables, respectively.
 Evaluate the following CREATE TABLE command:
 CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
 AS
 SELECT prod_id, cust_id, time_id
 FROM sales;
 Which statement is true regarding the above command?
 A.
The NEW_SALES table would get created and all the NOT NULL constraints defined on the
 specified columns would be passed to the new table.
 B.
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in
 the column definition.
 Oracle 1z0-071 Exam
  57
 C.
The NEW_SALES table would not get created because the column names in the CREATE
 TABLE command and the SELECT clause do not match.
 D.
The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the
 specified columns would be passed to the new table.
 
Explanation:
QUESTION NO: 68
Evaluate the following SELECT statement and view the exhibit to examine its output:
 SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule,
 status,
 FROM user_constraints
 WHERE table_name = 'ORDERS';
 Which two statements are true about the output? (Choose two.)
 A.
The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
 B.
In the second column, 'c' indicates a check constraint.
 C.
The STATUS column indicates whether the table is currently in use.
 Oracle 1z0-071 Exam
  58
 D.
The column DELETE_RULE decides the state of the related rows in the child table when the
 corresponding row is deleted from the parent table.
 ,D
Explanation:
QUESTION NO: 69
Which three statements are true regarding group functions? (Choose three.)
 A.
They can be used on columns or expressions.
 B.
They can be passed as an argument to another group function.
 C.
They can be used only with a SQL statement that has the GROUP BY clause.
 D.
They can be used on only one column in the SELECT clause of a SQL statement.
 E.
They can be used along with the single-row function in the SELECT clause of a SQL statement.
 ,B,E
Explanation:
References:
 https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html
 QUESTION NO: 70
Which three statements are true? (Choose three.)
 A.
The data dictionary is created and maintained by the database administrator.
 B.
Oracle 1z0-071 Exam
  59
 Data dictionary views consists of joins of dictionary base tables and user-defined tables.
 C.
The usernames of all 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
 constraints apply.
 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, reference the
 same base tables from the data dictionary.
 ,D,F
Explanation:
References:
 https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
 QUESTION NO: 71
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
 Oracle 1z0-071 Exam
  60
 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_datepromo_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.
 
Explanation:
QUESTION NO: 72
Oracle 1z0-071 Exam
  61
 View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
 You want to update the CITY column of the DEPT table for all the rows with the corresponding
 value in the CITY column of the LOCATIONS table for each department.
 Which SQL statement would you execute to accomplish the task?
 A.
UPDATE dept d
 SET city = ALL (SELECT city
 FROM locations l
 WHERE d.location_id = l.location_id);
 B.
UPDATE dept d
 SET city = (SELECT city
 FROM locations l)
 WHERE d.location_id = l.location_id;
 C.
UPDATE dept d
 SET city = ANY (SELECT city
 FROM locations l)
 D.
Oracle 1z0-071 Exam
  62
 UPDATE dept d
 SET city = (SELECT city
 FROM locations l
 WHERE d.location_id = l.location_id);
 
Explanation:
QUESTION NO: 73
The BOOKS_TRANSACTIONS table exists in your schema in this database.
 You execute this SQL statement when connected to your schema in your database instance.
 SQL> SELECT * FROM books_transactions ORDER BY 3;
 What is the result?
 A.
The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
 B.
All table rows are displayed sorted in ascending order of the values in the third column.
 C.
The first three rows in the table are displayed in the order that they are stored.
 D.
Only the three rows with the lowest values in the key column are displayed in the order that they
 are stored.
 
Explanation:
QUESTION NO: 74
Which statement is true about Data Manipulation Language (DML)?
 Oracle 1z0-071 Exam
  63
 A.
DML automatically disables foreign ley constraints when modifying primary key values in the
 parent table.
 B.
Each DML statement forms a transaction by default.
 C.
A transaction can consist of one or more DML statements.
 D.
DML disables foreign key constraints when deleting primary key values in the parent table, only
 when the ON DELETE CASCADE option is set for the foreign key constraint.
 
Explanation:
QUESTION NO: 75
View the exhibit and examine the structure of the PROMOTIONS table.
 You have to generate a report that displays the promo name and start date for all promos that
 started after the last promo in the ‘INTERNET’ category.
 Which query would give you the required output?
 A.
SELECT promo_name, promo_begin_date FROM promotions
 WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)
 FROM promotions) AND
 Oracle 1z0-071 Exam
  64
 promo_category= ‘INTERNET’;
 B.
SELECT promo_name, promo_begin_date FROM promotions
 WHERE promo_begin_date IN (SELECT promo_begin_date
 FROM promotions
 WHERE promo_category= ‘INTERNET’);
 C.
SELECT promo_name, promo_begin_date FROM promotions
 WHERE promo_begin_date > ALL (SELECT promo_begin_date
 FROM promotions
 WHERE promo_category = ‘INTERNET’);
 D.
SELECT promo_name, promo_begin_date FROM promotions
 WHERE promo_begin_date> ANY (SELECT promo_begin_date
 FROM promotions
 WHERE promo_category= ‘INTERNET’);
 
Explanation:
QUESTION NO: 76
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.
 Oracle 1z0-071 Exam
  65
 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.
 ,D
Explanation:
QUESTION NO: 77
Evaluate the following CREATE TABLE command:
 Which statement is true regarding the above SQL statement?
 A.
It would execute successfully and only ORD_ITM_IDX index would be created.
 B.
It would give an error because the USING INDEX clause cannot be used on a composite primary.
 C.
It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be
 created.
 D.
It would give an error because the USING INDEX is not permitted in the CREATE TABLE
 command.
 Oracle 1z0-071 Exam
  66
 
Explanation:
QUESTION NO: 78
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit
 amount in each income level. The report should NOT show any repeated credit amounts in each
 income level.
 Which query would give the required result?
 A.
SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM
 customers;
 B.
SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50
 AS “50% Credit Limit”
 FROM customers;
 C.
SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50
 AS “50% Credit Limit”
 FROM customers;
 D.
SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50
 AS “50% Credit Limit”
 FROM customers;
 
Explanation:
QUESTION NO: 79
Which three statements are true regarding the SQL WHERE and HAVING clauses?
 Oracle 1z0-071 Exam
  67
 A.
The HAVING clause conditions can have aggregating functions.
 B.
The HAVING clause conditions can use aliases for the columns.
 C.
The WHERE and HAVING clauses cannot be used together in a SQL statement.
 D.
The WHERE clause is used to exclude rows before grouping data.
 E.
The HAVING clause is used to exclude one or more aggregated results after grouping data.
 ,D,E
Explanation:
QUESTION NO: 80
You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand
 Seven’.
 Which SQL statement would give the required result?
 A.
SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)
 FROM DUAL
 B.
SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)
 FROM DUAL
 C.
SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)
 FROM DUAL
 D.
SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))
 FROM DUAL
 Oracle 1z0-071 Exam
  68
 
Explanation:
QUESTION NO: 81
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
 You want to generate a report that shows all course IDs irrespective of whether they have
 corresponding department IDs or not but no department IDs if they do not have any courses.
 Which SQL statement must you use?
 A.
SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN
 course_details c USING (department_id)
 B.
SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN
 .department_details d ON (c.depatrment_id=d.department_id)
 C.
SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN
 department_details d ON (c.department_id=d. department_id)
 D.
SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN
 department_details d ON (c.department_id<>d. department_id)
 
Explanation:
QUESTION NO: 82
Oracle 1z0-071 Exam
  69
 View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES
 tables.
 The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
 The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the
 CUSTOMERS and TIMES tables, respectively.
 Examine this command:
 CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
 AS
 SELECT prod_id, cust_id, time_id
 FROM sales;
 Which statement is true?
 A.
The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the
 selected columns from the SALES table would be created on the corresponding columns in the
 NEW_SALES table.
 Oracle 1z0-071 Exam
  70
 B.
The NEW_SALES table would not get created because the column names in the CREATE
 TABLE command and the SELECT clause do not match.
 C.
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in
 the column definition.
 D.
The NEW_SALES table would get created and all the NOT NULL constraints defined on the
 selected columns from the SALES table would be created on the corresponding columns in the
 NEW_SALES table.
 
Explanation:
QUESTION NO: 83
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the
 PRIMARY KEY in the ORDERS table.
 Evaluate the following CREATE TABLE command:
 CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
 AS
 SELECT order_id.order_date,customer_id
 FROM orders;
 Which statement is true regarding the above command?
 Oracle 1z0-071 Exam
  71
 A.
The NEW_ODRDERS table would not get created because the DEFAULT value cannot be
 specified in the column definition.
 B.
The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the
 specified columns would be passed to the new table.
 C.
The NEW_ODRDERS table would not get created because the column names in the CREATE
 TABLE command and the SELECT clause do not match.
 D.
The NEW_ODRDERS table would get created and all the constraints defined on the specified
 columns in the ORDERS table would be passed to the new table.
 
Explanation:
QUESTION NO: 84
Evaluate the following statement.
 Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT
 statement?
 A.
Each row is evaluated by the first WHEN clause and if the condition is false then the row would be
 evaluated by the subsequent when clauses.
 B.
All rows are evaluated by all the three WHEN clauses.
 C.
Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be
 Oracle 1z0-071 Exam
  72
 evaluated by the subsequent when clauses.
 D.
The INSERT statement will return an error because the ELSE clause is missing.
 
Explanation:
QUESTION NO: 85
Which two statements are true regarding the SQL GROUP BY clause?
 A.
You can use a column alias in the GROUP BY clause.
 B.
Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
 C.
The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT
 clause.
 D.
Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
 E.
If the SELECT clause has an aggregating function, then columns without an aggregating function
 in the SELECT clause should be included in the GROUP BY clause.
 ,E
Explanation:
QUESTION NO: 86
You issue this command which succeeds:
 SQL> DROP TABLE products;
 Which three statements are true?
 A.
Oracle 1z0-071 Exam
  73
 All existing views and synonyms that refer to the table are invalidated but retained.
 B.
Any uncommitted transaction in the session is committed.
 C.
Table data and the table structure are deleted.
 D.
All the table’s indexes if any exist, are invalidated but retained.
 E.
Table data is deleted but the table structure is retained.
 ,C,D
Explanation:
QUESTION NO: 87
You execute the SQL statement:
 SQL> CREATE TABLE citizens
 (citizen_id CHAR (10) PRIMARY KEY,
 last_name VARCHAR2 (50) NOT NULL,
 first_name VARCHAR2 (50),
 address VARCHAR2 (100),
 city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
 CONSTRAINT cnames CHECK (first_name<>last_name) );
 What is the outcome?
 A.
It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
 B.
It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.
 C.
It fails because the condition for the CANAMES constraint is not valid.
 Oracle 1z0-071 Exam
  74
 D.
It succeeds and an index is crated for CITIZEN_ID.
 
Explanation:
QUESTION NO: 88
Evaluate the following CREATE TABLE commands:
 CREATE_TABLE orders
 (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
 ord_date DATE,
 cust_id NUMBER (4) );
 CREATE TABLE ord_items
 (ord _no NUMBER (2),
 item_no NUMBER(3),
 qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
 expiry_date date CHECK (expiry_date> SYSDATE),
 CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
 CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );
 The above command fails when executed. What could be the reason?
 A.
SYSDATE cannot be used with the CHECK constraint.
 B.
The BETWEEN clause cannot be used for the CHECK constraint.
 C.
The CHECK constraint cannot be placed on columns having the DATE data type.
 D.
ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also
 Oracle 1z0-071 Exam
  75
 the FOREIGN KEY.
 
Explanation:
QUESTION NO: 89
Examine the structure of the PROGRAMS table:
 Which two SQL statements would execute successfully?
 A.
SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
 B.
SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
 C.
SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM programs;
 D.
SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’) FROM
 programs
 ,D
Explanation:
QUESTION NO: 90
View the Exhibit and examine the structure of the CUSTOMERS table.
 Oracle 1z0-071 Exam
  76
 Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of
 15% for all customers.
 Customers with no credit limit should have “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’)*.15 “NEW CREDIT”
 FROM customers
 
Explanation:
Oracle 1z0-071 Exam
  77
 QUESTION NO: 91
Examine these SQL statements that are executed in the given order:
 What will be the status of the foreign key EMP_MGR_FK?
 A.
It will be enabled and immediate.
 B.
It will be enabled and deferred.
 C.
It will remain disabled and can be re-enabled manually.
 D.
It will remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
 
Explanation:
QUESTION NO: 92
View the Exhibit and examine the structure in the EMPLOYEES tables.
 Oracle 1z0-071 Exam
  78
 Evaluate the following SQL statement:
 SELECT employee_id, department_id
 FROM employees
 WHERE department_id= 50 ORDER BY department_id
 UNION
 SELECT employee_id, department_id
 FROM employees
 WHERE department_id=90
 UNION
 SELECT employee_id, department_id
 FROM employees
 WHERE department_id=10;
 What would be the outcome of the above SQL statement?
 A.
The statement would not execute because the positional notation instead of the column name
 should be used with the ORDER BY clause.
 B.
The statement would execute successfully and display all the rows in the ascending order of
 DEPARTMENT_ID.
 C.
The statement would execute successfully but it will ignore the ORDER BY clause and display the
 rows in random order.
 Oracle 1z0-071 Exam
  79
 D.
The statement would not execute because the ORDER BY clause should appear only at the end
 of the SQL statement, that is, in the last SELECT statement.
 
Explanation:
QUESTION NO: 93
View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the
 best answer.)
 You issued this SQL statement:
 Which statement is true regarding the result?
 Oracle 1z0-071 Exam
  80
 A.
The statement will fail because the sub-query in the VALUES clause is not enclosed within single
 quotation marks.
 B.
The statement will fail because a subquery cannot be used in a VALUES clause.
 C.
The statement will execute and a new row will be inserted in the SALES table.
 D.
The statement will fail because the VALUES clause is not required with the subquery.
 
Explanation:
QUESTION NO: 94
View the Exhibit and examine the description of the ORDERS table.
 Which two WHERE clause conditions demonstrate the correct usage of conversion functions?
 (Choose two.)
 A.
WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003',
 'MON DD YYYY') )
 B.
WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
 C.
Oracle 1z0-071 Exam
  81
 WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
 D.
WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')
 ,D
Explanation:
QUESTION NO: 95
Which three arithmetic operations can be performed on a column by using a SQL function that is
 built into Oracle database? (Choose three.)
 A.
Finding the lowest value
 B.
Finding the quotient
 C.
Raising to a power
 D.
Subtraction
 E.
Addition
 ,C,E
Explanation:
QUESTION NO: 96
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
 Oracle 1z0-071 Exam
  82
 Examine this query which must select the employee IDs of all the employees who have held the
 job SA_MAN at any time during their employment.
 SELECT EMPLOYEE_ID
 FROM EMPLOYEES
 WHERE JOB_ID = 'SA_MAN'
 -------------------------------------
 SELECT EMPLOYEE_ID
 FROM JOB_HISTORY
 WHERE JOB_ID = 'SA_MAN';
 Choose two correct SET operators which would cause the query to return the desired result.
 A.
UNION
 B.
MINUS
 C.
INTERSECT
 D.
UNION ALL
 ,D
Explanation:
Oracle 1z0-071 Exam
  83
 QUESTION NO: 97
You must create a SALES table with these column specifications and data types: (Choose the
 best answer.)
 SALESID: Number
 STOREID: Number
 ITEMID: Number
 QTY: Number, should be set to 1 when no value is specified
 SLSDATE: Date, should be set to current date when no value is specified
 PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
 Which statement would create the table?
 A.
CREATE TABLE Sales
 (SALESID NUMBER (4),
 STOREID NUMBER (4),
 ITEMID NUMBER (4),
 QTY NUMBER DEFAULT = 1,
 SLSDATE DATE DEFAULT SYSDATE,
 PAYMENT VAR
 CHAR2(30) DEFAULT = "CASH");
 B.
CREATE TABLE Sales
 (SALESID NUMBER (4),
 STOREID NUMBER (4),
 ITEMID NUMBER (4),
 QTY NUMBER DEFAULT = 1,
 SLSDATE DATE DEFAULT 'SYSDATE',
 PAYMENT VARCHAR2(30) DEFAULT CASH);
 Oracle 1z0-071 Exam
  84
 C.
CREATE TABLE Sales
 (SALESID NUMBER (4),
 STOREID
 NUMBER (4),
 ITEMID NUMBER (4),
 qty NUMBER DEFAULT = 1,
 SLSDATE DATE DEFAULT SYSDATE,
 PAYMENT VARCHAR2(30) DEFAULT = "CASH");
 D.
Create Table sales
 (salesid NUMBER (4),
 Storeid NUMBER (4),
 Itemid NUMBER (4),
 QTY NUMBER DEFAULT 1,
 Slsdate DATE DEFAULT SYSDATE,
 payment VARCHAR2(30) DEFAULT 'CASH');
 
Explanation:
QUESTION NO: 98
View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)
 Oracle 1z0-071 Exam
  85
 Evaluate this SQL statement:
 SELECT TO_CHAR (list_price, '$9,999')
 From product_information;
 Which two statements are true regarding the output? (Choose two.)
 A.
A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
 B.
A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
 C.
A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
 D.
A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.
 ,C
Explanation:
QUESTION NO: 99
Which statement is true about SQL query processing in an Oracle database instance? (Choose
 the best answer.)
 A.
Oracle 1z0-071 Exam
  86
 During parsing, a SQL statement containing literals in the WHERE clause that has been executed
 by any session and which is cached in memory, is always reused for the current execution.
 B.
During executing, the oracle server may read data from storage if the required data is not already
 in memory.
 C.
During row source generation, rows that satisfy the query are retrieved from the database and
 stored in memory.
 D.
During optimization, execution plans are formulated based on the statistics gathered by the
 database instance, and the lowest cost plan is selected for execution.
 
Explanation:
QUESTION NO: 100
Examine the structure of the ORDERS table: (Choose the best answer.)
 You want to find the total value of all the orders for each year and issue this command:
 SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
 GROUP BY TO_CHAR(order_date, 'yyyy');
 Which statement is true regarding the result?
 A.
It executes successfully but does not give the correct output.
 B.
It executes successfully and gives the correct output.
 Oracle 1z0-071 Exam
  87
 C.
It returns an error because the TO_CHAR function is not valid.
 D.
It return an error because the datatype conversion in the SELECT list does not match the data
 type conversion in the GROUP BY clause.
 
Explanation:
QUESTION NO: 101
View the Exhibit and examine the structure of the ORDER_ITEMS table.
 You must select the ORDER_ID of the order that has the highest total value among all the orders
 in the ORDER_ITEMS table.
 Which query would produce the desired result?
 A.
SELECT order_id
 FROM order_items
 GROUP BY order_id
 HAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))
 Oracle 1z0-071 Exam
  88
 FROM order_items GROUP BY order_id);
 B.
SELECT order_id
 FROM order_items
 WHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)
 FROM order_items) GROUP BY order_id);
 C.
SELECT order_id
 FROM order_items
 WHERE(unit_price*quantity) = MAX(unit_price*quantity)
 GROUP BY order_id);
 D.
SELECT order_id
 FROM order_items
 WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)
 FROM order_items
 GROUP BY order_id)
 
Explanation:
QUESTION NO: 102
View the Exhibit and examine the structure of the EMP table which is not partitioned and not an
 index-organized table. (Choose two.)
 Oracle 1z0-071 Exam
  89
 Evaluate this SQL statement:
 ALTER TABLE emp
 DROP COLUMN first_name;
 Which two statements are true?
 A.
The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY
 provided the CASCADE option is added to the SQL statement.
 B.
The FIRST_NAME column would be dropped provided at least one column remains in the table.
 C.
The FIRST_NAME column would be dropped provided it does not contain any data.
 D.
The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is
 added to the SQL statement.
 
Explanation:
QUESTION NO: 103
View the exhibit and examine the structure and data in the INVOICE table.
 Oracle 1z0-071 Exam
  90
 Which two SQL statements would execute successfully? (Choose two.)
 A.
SELECT MAX(AVG(SYSDATE -inv_date))
 FROM invoice;
 B.
SELECT AVG(inv_date)
 FROM invoice;
 C.
SELECT MAX(inv_date), MIN(cust_id)
 FROM invoice;
 D.
SELECT AVG( inv_date -SYSDATE), AVG(inv_amt)
 FROM invoice;
 ,D
Explanation:
QUESTION NO: 104
Which two statements best describe the benefits of using the WITH clause? (Choose two.)
 Oracle 1z0-071 Exam
  91
 A.
It can improve the performance of a large query by storing the result of a query block having the
 WITH clause in the session's temporary tablespace.
 B.
It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than
 once in a complex query.
 C.
It enables sessions to store a query block permanently in memory and use it to create complex
 queries.
 D.
It enables sessions to store the results of a query permanently.
 ,B
Explanation:
QUESTION NO: 105
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.
 ,B,C
Explanation:
QUESTION NO: 106
Oracle 1z0-071 Exam
  92
 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.
 ,D
Explanation:
QUESTION NO: 107
View the Exhibit and examine the structure of the ORDERS table.
 You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order
 placed by CUSTOMER_ID 101.
 Oracle 1z0-071 Exam
  93
 Which query would give you the desired result?
 A.
SELECT order_id, order_date FROM orders
 WHERE order_date >
 ANY
 (SELECT order_date FROM orders WHERE customer_id = 101);
 B.
SELECT order_id, order_date FROM orders
 WHERE order_date > ALL
 (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
 C.
SELECT order_id, order_date FROM orders
 WHERE order_date > ALL
 (SELECT order_date FROM orders WHERE customer_id = 101);
 D.
SELECT order_id, order_date FROM orders
 WHERE order_date > IN
 (SELECT order_date FROM orders WHERE customer_id = 101);
 
Explanation:
QUESTION NO: 108
Which task can be performed by using a single Data Manipulation Language (DML) statement?
 A.
Removing all data only from a single column on which a primary key constraint is defined.
 B.
Removing all data from a single column on which a unique constraint is defined.
 C.
Adding a column with a default value while inserting a row into a table.
 Oracle 1z0-071 Exam
  94
 D.
Adding a column constraint while inserting a row into a table.
 
Explanation:
QUESTION NO: 109
You must display details of all users whose username contains the string 'ch_'. (Choose the best
 answer.)
 Which query generates the required output?
 A.
SELECT *
 FROM users
 Where user_name LIKE '%ch_';
 B.
SELECT *
 FROM users
 Where user_name LIKE '%ch_%'ESCAPE'%';
 C.
SELECT *
 FROM users
 Where user_name LIKE 'ch\_%' ESCAPE '_';
 D.
SELECT *
 FROM users
 Where user_name LIKE '%ch\_%' ESCAPE '\';
 
Explanation:
Oracle 1z0-071 Exam
  95
 QUESTION NO: 110
Which three statements are true regarding the usage of the WITH clause in complex correlated
 subqueries? (Choose three.)
 A.
It can be used only with the SELECT clause.
 B.
The WITH clause can hold more than one query.
 C.
If the query block name and the table name are the same, then the table name takes precedence.
 D.
The query name in the WITH clause is visible to other query blocks in the WITH clause as well as
 to the main query block
 ,B,D
Explanation:
QUESTION NO: 111
View the Exhibit and examine the data in the PRODUCTS table.
 You must display product names from the PRODUCTS table that belong to the 'Software/other'
 category with minimum prices as either $2000 or $4000 and with no unit of measure.
 You issue this query:
 Oracle 1z0-071 Exam
  96
 Which statement is true?
 A.
It executes successfully but returns no result.
 B.
It executes successfully and returns the required result.
 C.
It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
 D.
It generates an error because the condition specified for the PROD_CATEGORY column is not
 valid.
 
Explanation:
QUESTION NO: 112
Examine the structure of the EMPLOYEES table.
 You must display the maximum and minimum salaries of employees hired 1 year ago.
 Which two statements would provide the correct output? (Choose two.)
 A.
SELECT MIN(Salary) minsal, MAX(salary) maxsal
 FROM employees
 WHERE hire_date < SYSDATE-365
 GROUP BY MIN(salary), MAX(salary);
 B.
SELECT minsal, maxsal
 Oracle 1z0-071 Exam
  97
 FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
 FROM employees
 WHERE hire_date < SYSDATE-365)
 GROUP BY maxsal, minsal;
 C.
SELECT minsal, maxsal
 FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
 FROM employees
 WHERE hire_date < SYSDATE-365
 GROUP BY MIN(salary), MAX(salary);
 D.
SELECT MIN(Salary), MAX(salary)
 FROM (SELECT salary FROM
 employees
 WHERE hire_date < SYSDATE-365);
 ,D
Explanation:
QUESTION NO: 113
Which two statements are true regarding subqueries? (Choose two.)
 A.
A subquery can appear on either side of a comparison operator.
 B.
Only two subqueries can be placed at one level.
 C.
A subquery can retrieve zero or more rows.
 D.
A subquery can be used only in SQL query statements.
 E.
Oracle 1z0-071 Exam
  98
 There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
 ,C
Explanation:
QUESTION NO: 114
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.
 ,D
Explanation:
QUESTION NO: 115
Which two statement are true regarding table joins available in the Oracle Database server?
 (Choose two.)
 A.
You can use the ON clause to specify multiple conditions while joining tables.
 B.
You can explicitly provide the join condition with a NATURAL JOIN.
 C.
You can use the JOIN clause to join only two tables.
 D.
You can use the USING clause to join tables on more than one column.
 Oracle 1z0-071 Exam
  99
 ,D
Explanation:
QUESTION NO: 116
You issued this command:
 SQL > DROP TABLE employees;
 Which three statements are true? (Choose three.)
 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.
 ,C,F
Explanation:
QUESTION NO: 117
View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best
 answer.)
 Oracle 1z0-071 Exam
  100
 The PROJ_TASK_DETAILS table stores information about project tasks and the relation between
 them.
 The BASED_ON column indicates dependencies between tasks.
 Some tasks do not depend on the completion of other tasks.
 You must generate a report listing all task IDs, the task ID of any task upon which it depends and
 the name of the employee in charge of the task upon which it depends.
 Which query would give the required result?
 A.
SELECT p.task_id, p.based_on, d.task_in_charge
 FROM proj_task_details p JOIN proj_task_details d
 ON (p.task_id = d.task_id);
 B.
SELECT p.task_id, p.based_on, d.task_in_charge
 FROM proj_task_details p FULL OUTER JOIN proj_task_details d
 ON (p.based_on = d.task_id);
 C.
SELECT p.task_id, p.based_on, d.task_in_charge
 FROM proj_task_details p JOIN proj_task_details d
 ON (p.based_on = d.task_id);
 D.
SELECT p.task_id, p.based_on, d.task_in_charge
 FROM proj_task_details p LEFT OUTER JOIN proj_task_details d
 ON (p.based_on = d.task_id);
 Oracle 1z0-071 Exam
  101
 
Explanation:
QUESTION NO: 118
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
 In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table.
 You must list each product ID and the number of times it has been sold.
 Examine this query which is missing a JOIN operator:
 SQL > SELECT p.prod_id, count(s.prod_id)
 FROM products p ______________ sales s
 ON p.prod_id = s.prod_id
 GROUP BY p.prod_id;
 Which two JOIN operations can be used to obtain the required output?
 A.
FULL OUTER JOIN
 Oracle 1z0-071 Exam
  102
 B.
JOIN
 C.
LEFT OUETR JOIN
 D.
RIGHT OUTER JOIN
 ,C
Explanation:
QUESTION NO: 119
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
 You executed this SQL statement:
 SELECT first_name, department_id, salary
 FROM employees
 ORDER BY department_id, first_name, salary desc;
 Which two statements are true regarding the result? (Choose two.)
 A.
The values in the SALARY column would be returned in descending order for all employees
 having the same value in the DEPARTMENT_ID and FIRST_NAME column.
 B.
The values in the FIRST_NAME column would be returned in ascending order for all employees
 having the same value in the DEPARTMENT_ID column.
 Oracle 1z0-071 Exam
  103
 C.
The values in the SALARY column would be returned in descending order for all employees
 having the same value in the DEPARTMENT_ID column.
 D.
The values in the all columns would be returned in descending order.
 E.
The values in the FIRST_NAME column would be returned in descending order for all employees
 having the same value in the DEPARTMENT_ID column.
 ,B
Explanation:
QUESTION NO: 120
Examine the structure of the SALES table. (Choose two.)
 Examine this statement:
 Which two statements are true about the SALES1 table? (Choose two.)
 A.
It will not be created because the column-specified names in the SELECT and CREATE TABLE
 Oracle 1z0-071 Exam
  104
 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.
 ,D
Explanation:
QUESTION NO: 121
Examine this SELECT statement and view the Exhibit to see its output:
 SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule,
 status,
 FROM user_constraints
 WHERE table_name = 'ORDERS';
 Which two statements are true about the output? (Choose two.)
 A.
Oracle 1z0-071 Exam
  105
 The DELETE_RULE column indicates the desired state of related rows in the child table when the
 corresponding row is deleted from the parent table.
 B.
The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
 C.
In the second column, 'c' indicates a check constraint.
 D.
The STATUS column indicates whether the table is currently in use.
 ,C
Explanation:
QUESTION NO: 122
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.
 ,C
Explanation:
QUESTION NO: 123
Which two statements are true regarding working with dates? (Choose two.)
 Oracle 1z0-071 Exam
  106
 A.
The RR date format automatically calculates the century from the SYSDATE function but allows
 the session user to enter the century.
 B.
The RR date format automatically calculates the century from the SYSDATE function and does not
 allow a session user to enter the century.
 C.
The default internal storage of dates is in character format.
 D.
The default internal storage of dates is in numeric format.
 ,D
Explanation:
QUESTION NO: 124
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the
 best answer.)
 You executed this UPDATE statement:
 Oracle 1z0-071 Exam
  107
 Which statement is true regarding the execution?
 A.
It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE
 statement.
 B.
It would not execute because two tables cannot be referenced in a single UPDATE statement.
 C.
It would execute and restrict modifications to the columns specified in the SELECT statement.
 D.
It would not execute because a SELECT statement cannot be used in place of a table name.
 
Explanation:
QUESTION NO: 125
View the Exhibit and examine the structure of the PRODUCTS table.
 Oracle 1z0-071 Exam
  108
 You must display the category with the maximum number of items.
 You issue this query:
 What is the result?
 A.
It generates an error because = is not valid and should be replaced by the IN operator.
 B.
It executes successfully but does not give the correct output.
 C.
It executes successfully and gives the correct output.
 D.
It generate an error because the subquery does not have a GROUP BY clause.
 
Explanation:
QUESTION NO: 126
Examine the structure of the MEMBERS table: (Choose the best answer.)
 Oracle 1z0-071 Exam
  109
 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.
 
Explanation:
QUESTION NO: 127
View and Exhibit and examine the structure and data in the INVOICE table.
 Which two statements are true regarding data type conversion in query expressions? (Choose
 two.)
 A.
inv_date = '15-february-2008' :uses implicit conversion
 B.
inv_amt = '0255982' : requires explicit conversion
 Oracle 1z0-071 Exam
  110
 C.
inv_date > '01-02-2008' : uses implicit conversion
 D.
CONCAT(inv_amt, inv_date) : requires explicit conversion
 E.
inv_no BETWEEN '101' AND '110' : uses implicit conversion
 ,E
Explanation:
QUESTION NO: 128
Examine the structure of the EMPLOYEES table.
 You must display the details of employees who have manager with MANAGER_ID 100, who were
 hired in the past 6 months and who have salaries greater than 10000.
 A.
SELECT last_name, hire_date, salary
 FROM employees
 WHERE salary > 10000
 UNION ALL
 SELECT last_name, hire_date, salary
 FROM employees
 WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)
 INETRSECT
 SELECT last_name, hire_date, salary
 FROM employees
 WHERE hire_date > SYSDATE- 180;
 B.
SELECT last_name, hire_date, salary
 Oracle 1z0-071 Exam
  111
 FROM employees
 WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
 UNION ALL
 (SELECT last_name, hire_date, salary
 FROM employees
 WHERE hire_date > SYSDATE -180
 INTERSECT
 SELECT last_name, hire_date, salary
 FROM employees
 WHERE salary > 10000);
 C.
SELECT last_name, hire_date, salary
 FROM employees
 WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')
 UNION
 SELECT last_name, hire_date, salary
 FROM employees
 WHERE hire_date > SYSDATE -180
 INTERSECT
 SELECT last_name, hire_date, salary
 FROM employees
 WHERE salary > 10000;
 D.
(SELECT last_name, hire_date, salary
 FROM employees
 WHERE salary > 10000
 UNION ALL
 SELECT last_name, hire_date, salary
 Oracle 1z0-071 Exam
  112
 FROM employees
 WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))
 UNION
 SELECT last_name, hire_date, salary
 FROM employees
 WHERE hire_date > SYSDATE -180;
 
Explanation:
QUESTION NO: 129
Examine the structure of the PROMOTIONS table: (Choose the best answer.)
 Management requires a report of unique promotion costs in each promotion category.
 Which query would satisfy this requirement?
 A.
SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
 B.
SELECT promo_category, DISTINCT promo_cost FROM promotions;
 C.
SELECT DISTINCT promo_cost, promo_category FROM promotions;
 D.
SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
 
Oracle 1z0-071 Exam
  113
 Explanation:
QUESTION NO: 130
You must create a table for a banking application.
 One of the columns in the table has these requirements:
 A column to store the duration of a short team loan
 The data should be stored in a format supporting DATE arithmetic with DATE datatypes without
 using conversion functions.
 The maximum loan period is 30 days.
 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 DAY TO SECOND
 
Explanation:
QUESTION NO: 131
Examine the structure of the CUSTOMERS table: (Choose two.)
 Oracle 1z0-071 Exam
  114
 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
 ,B
Explanation:
QUESTION NO: 132
Which two are the minimal requirements for a self-join? (Choose two.)
 A.
Only equijoin conditions may be used in the query.
 B.
Outer joins must not be used in the query.
 C.
Oracle 1z0-071 Exam
  115
 There must be a condition on which the self-join is performed.
 D.
No other condition except the self-join may be specified.
 E.
The table used for the self-join must have two different alias names in the query.
 ,E
Explanation:
QUESTION NO: 133
Examine the SQL statement used to create the TRANSACTION table.
 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')
 
Explanation:
Oracle 1z0-071 Exam
  116
 QUESTION NO: 134
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
 A.
SELECT TO_CHAR (1890.55, '$99G999D00')
 FROM DUAL
 B.
SELECT TO_CHAR (1890.55, '$9,999V99')
 FROM DUAL;
 C.
SELECT TO_CHAR (1890.55, '$0G000D00')
 FROM DUAL;
 D.
SELECT TO_CHAR (1890.55, '$99,999D99')
 FROM DUAL;
 E.
SELECT TO_CHAR (1890.55, '$99G999D99')
 FROM DUAL
 ,C,E
Explanation:
QUESTION NO: 135
A subquery is called a single-row subquery when _______.
 A.
There is only one subquery in the outer query and the inner query returns one or more values
 B.
The inner query returns a single value to the outer query.
 C.
The inner query uses an aggregating function and returns one or more values.
 D.
Oracle 1z0-071 Exam
  117
 The inner query returns one or more values and the outer query returns a single value.
 
Explanation:
QUESTION NO: 136
You must write a query that prompts users for column names and conditions every time it is
 executed.
 The user must be prompted only once for the table name.
 Which statement achieves those objectives?
 A.
SELECT &col1, '&col2'
 FROM &table
 WHERE &&condition = '&cond';
 B.
SELECT &col1, &col2
 FROM "&table"
 WHERE &condition = &cond;
 C.
SELECT &col1, &col2
 FROM &&table
 WHERE &condition = &cond;
 D.
SELECT &col1, &col2
 FROM &&table
 WHERE &condition = &&cond
 
Explanation:
Oracle 1z0-071 Exam
  118
 QUESTION NO: 137
Which three statements are true regarding single-row functions? (Choose three.)
 A.
The data type returned, can be different from the data type of the argument that is referenced.
 B.
They can return multiple values of more than one data type.
 C.
They can accept only one argument.
 D.
They can be nested up to only two levels.
 E.
They can be used in SELECT, WHERE, and ORDER BY clauses.
 F.
They can accept column names, expressions, variable names, or a user-supplied constants as
 arguments.
 ,E,F
Explanation:
QUESTION NO: 138
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
 Examine this SQL statement:
 Oracle 1z0-071 Exam
  119
 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?
 A.
ORDER BY DEPT_NAME;
 B.
ORDER BY DEPT_ID;
 C.
ORDER BY 'b';
 D.
ORDER BY 3;
 ,D
Explanation:
QUESTION NO: 139
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.
Oracle 1z0-071 Exam
  120
 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.
 ,D
Explanation:
QUESTION NO: 140
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and
 LOGIN_ID must be unique and not null.
 Which two SQL statements would create the required table? (Choose two.)
 A.
CREATE TABLE employees
 (employee_id NUMBER,
 Login_id NUMBER,
 Employee_name VARCHAR2(100),
 Hire_date DATE,
 CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));
 B.
CREATE TABLE employees
 (employee_id NUMBER,
 login_id NUMBER,
 employee_name VARCHAR2(25),
 hire_date DATE,
 CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
 C.
CREATE TABLE employees
 Oracle 1z0-071 Exam
  121
 (employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
 Login_id NUMBER UNIQUE,
 Employee_name VARCHAR2(25),
 Hire_date DATE);
 D.
CREATE TABLE employees
 (employee_id NUMBER,
 Login_id NUMBER,
 Employee_name VARCHAR2(100),
 Hire_date DATE,
 CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);
 CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));
 E.
CREATE TABLE employees
 (employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,
 Login_id NUMBER CONSTRAINT login_id_nn NOT NULL,
 Employee_name VARCHAR2(100),
 Hire_date DATE,
 CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));
 ,E
Explanation:
QUESTION NO: 141
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the
 best answer.)
 Oracle 1z0-071 Exam
  122
 PRODUCT_ID column is the primary key.
 You create an index using this command:
 SQL > CREATE INDEX upper_name_idx
 ON product_information(UPPER(product_name));
 No other indexes exist on the PRODUCT_INFORMATION table.
 Which query would use the UPPER_NAME_IDX index?
 A.
SELECT product_id, UPPER(product_name)
 FROM product_information
 WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
 B.
SELECT UPPER(product_name)
 FROM product_information;
 C.
SELECT UPPER(product_name)
 FROM product_information
 WHERE product_id = 2254;
 D.
SELECT product_id
 Oracle 1z0-071 Exam
  123
 FROM product_information
 WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
 
Explanation:
QUESTION NO: 142
Examine the types and examples of relationships that follow: (Choose the best answer.)
 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
 
Explanation:
QUESTION NO: 143
A non-correlated subquery can be defined as __________. (Choose the best answer.)
 A.
A set of one or more sequential queries in which generally the result of the inner query is used as
 Oracle 1z0-071 Exam
  124
 the search value in the outer query.
 B.
A set of sequential queries, all of which must return values from the same table.
 C.
A set of sequential queries, all of which must always return a single value.
 D.
A SELECT statement that can be embedded in a clause of another SELECT statement only.
 
Explanation:
QUESTION NO: 144
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
 ,D,E
Explanation:
QUESTION NO: 145
Which three statements are true reading subqueries?
 A.
Oracle 1z0-071 Exam
  125
 A Main query can have many subqueries.
 B.
A subquery can have more than one main query.
 C.
The subquery and main query must retrieve date from the same table.
 D.
The subquery and main query can retrieve data from different tables.
 E.
Only one column or expression can be compared between the subquery and main query.
 F.
Multiple columns or expressions can be compared between the subquery and main query.
 ,D,F
Explanation:
QUESTION NO: 146
See the Exhibit and examine the structure of the PROMOTIONS table:
 Using the PROMOTIONS table,
 you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in
 category A.
 You issue the following SQL statements:
 Oracle 1z0-071 Exam
  126
 What would be the outcome?
 A.
It generates an error because multiple conditions cannot be specified for the WHEN clause.
 B.
It executes successfully and gives the required result.
 C.
It generates an error because CASE cannot be used with group functions.
 D.
It generates an error because NULL cannot be specified as a return value.
 
Explanation:
CASE Expression
 Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
 CASE expr WHEN comparison_expr1 THEN return_expr1
 [WHEN comparison_expr2 THEN return_expr2
 WHEN comparison_exprn THEN return_exprn
 ELSE else_expr]
 END
 QUESTION NO: 147
Which two statements are true regarding multiple-row subqueries? (Choose two.)
 A.
They can contain group functions.
 Oracle 1z0-071 Exam
  127
 B.
They always contain a subquery within a subquery.
 C.
They use the < ALL operator to imply less than the maximum.
 D.
They can be used to retrieve multiple rows from a single table only.
 E.
They should not be used with the NOT IN operator in the main query if NULL is likely to be a part
 of the result of the subquery.
 ,E
Explanation:
QUESTION NO: 148
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.
 Oracle 1z0-071 Exam
  128
 Which SET operator would you use to get the required output?
 A.
INTERSECT
 B.
UNION ALL
 C.
MINUS
 D.
UNION
 
Explanation:
QUESTION NO: 149
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:
 Oracle 1z0-071 Exam
  129
 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 execute successfully to produce the required output.
 B.
The statement would not execute because inline views and outer joins cannot be used together.
 C.
The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer
 query.
 D.
The statement would not execute because the GROUP BY clause cannot be used in the inline.
 
Explanation:
QUESTION NO: 150
Which statement is true regarding the UNION operator?
 A.
By default, the output is not sorted.
 B.
Null values are not ignored during duplicate checking.
 C.
Names of all columns must be identical across all select statements.
 D.
The number of columns selected in all select statements need not be the same.
 
Oracle 1z0-071 Exam
  130
 Explanation:
QUESTION NO: 151
You issued the following command:
 SQL> DROP TABLE employees;
 Which three statements are true?
 A.
All uncommitted transactions are committed.
 B.
All indexes and constraints defined on the table being dropped are also dropped.
 C.
Sequences used in the employees table become invalid.
 D.
The space used by the employees table is reclaimed immediately.
 E.
The employees table can be recovered using the rollback command.
 F.
The employees table is moved to the recycle bin
 ,B,F
Explanation:
QUESTION NO: 152
Examine the create table statements for the stores and sales tables.
 SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY,
 store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
 SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY,
 item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4),
 CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
 Oracle 1z0-071 Exam
  131
 You executed the following statement:
 SQL> DELETE from stores
 WHERE store_id=900;
 The statement fails due to the integrity constraint error:
 ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
 Which three options ensure that the statement will execute successfully?
 A.
Disable the primary key in the STORES table.
 B.
Use CASCADE keyword with DELETE statement.
 C.
DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from
 STORES table.
 D.
Disable the FOREIGN KEY in SALES table and then delete the rows.
 E.
Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE
 option.
 ,D,E
Explanation:
QUESTION NO: 153
n the customers table, the CUST_CITY column contains the value 'Paris' for the
 CUST_FIRST_NAME 'Abigail'.
 Evaluate the following query:
 Oracle 1z0-071 Exam
  132
 What would be the outcome?
 A.
Abigail PA
 B.
Abigail Pa
 C.
Abigail IS
 D.
An error message
 
Explanation:
QUESTION NO: 154
Which two statements are true regarding constraints?
 A.
A table can have only one primary key and one foreign key.
 B.
A table can have only one primary key but multiple foreign keys.
 C.
Only the primary key can be defined at the column and table levels.
 D.
The foreign key and parent table primary key must have the same name.
 E.
Both primary key and foreign key constraints can be defined at both column and table levels.
 ,E
Explanation:
QUESTION NO: 155
Oracle 1z0-071 Exam
  133
 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.
 ,C
Explanation:
QUESTION NO: 156
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.
 
Oracle 1z0-071 Exam
  134
 Explanation:
QUESTION NO: 157
Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?
 A.
By default, EM express is available for a database after database creation.
 B.
You can use EM express to manage multiple databases running on the same server.
 C.
You can perform basic administrative tasks for pluggable databases by using the EM express
 interface.
 D.
You cannot start up or shut down a database Instance by using EM express.
 E.
You can create and configure pluggable databases by using EM express.
 
Explanation:
QUESTION NO: 158
Which two partitioned table maintenance operations support asynchronous Global Index
 Maintenance in Oracle database 12c?
 A.
ALTER TABLE SPLIT PARTITION
 B.
ALTER TABLE MERGE PARTITION
 C.
ALTER TABLE TRUNCATE PARTITION
 D.
ALTER TABLE ADD PARTITION
 Oracle 1z0-071 Exam
  135
 E.
ALTER TABLE DROP PARTITION
 F.
ALTER TABLE MOVE PARTITION
 ,E
Explanation:
QUESTION NO: 159
View the Exhibits and examine PRODUCTS and SALES tables.
 Exhibit 1
 Exhibit 2
 Oracle 1z0-071 Exam
  136
 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?
 A.
The statement executes successfully and produces the required output.
 B.
The statement produces an error because a subquery in the FROM clause and outer-joins cannot
 be used together.
 C.
The statement produces an error because the GROUP BY clause cannot be used in a subquery in
 the FROM clause.
 D.
The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
 
Explanation:
QUESTION NO: 160
Examine the structure of the BOOKS_TRANSACTIONS table:
 Examine the SQL statement:
 Oracle 1z0-071 Exam
  137
 Which statement is true about the outcome?
 A.
It displays details only for members who have borrowed before today with RM as
 TRANSACTION_TYPE.
 B.
It displays details for members who have borrowed before today’s date with either RM as
 TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
 C.
It displays details for only members A101 and A102 who have borrowed before today with RM
 TRANSACTION_TYPE.
 D.
It displays details for members who have borrowed before today with RM as
 TRANSACTION_TYPE and the details for members A101 or A102.
 
Explanation:
QUESTION NO: 161
View the Exhibit and examine the data in the EMPLOYEES table.
 Exhibit
 You want to generate a report showing the total compensation paid to each employee to date.
 You issue the following query:
 Oracle 1z0-071 Exam
  138
 What is the outcome?
 A.
It executes successfully but does not give the correct output.
 B.
It generates an error because the concatenation operator can be used to combine only two items.
 C.
It generates an error because the usage of the ROUND function in the expression is not valid.
 D.
It generates an error because the alias is not valid.
 E.
IT executes successfully and gives the correct output.
 
Explanation:
QUESTION NO: 162
Evaluate the following query
 What is the correct output of the above query?
 A.
+00-300, +00-650, +00 11:12:10.123457
 B.
+25-00, +54-02, +00 11:12:10.123457
 C.
Oracle 1z0-071 Exam
  139
 +00-300, +54-02, +00 11:12:10.123457
 D.
+25-00, +00-650, +00 11:12:10.123457
 
Explanation:
QUESTION NO: 163
Which two statements are true regarding savepoints? (Choose two.)
 A.
Savepoints may be used to ROLLBACK.
 B.
Savepoints can be used for only DML statements.
 C.
Savepoints are effective only for COMMIT.
 D.
Savepoints are effective for both COMMIT and ROLLBACK.
 E.
Savepoints can be used for both DML and DDL statements.
 ,B
Reference:
 https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10001.htm#SQLRF01701
 QUESTION NO: 164
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS
 tables:
 Oracle 1z0-071 Exam
  140
 You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
 You execute the SQL statement:
 What is the outcome?
 A.
It fails because the join type used is incorrect.
 B.
It executes successfully and displays the required list.
 C.
It executes successfully but displays an incorrect list.
 D.
It fails because the ON clause condition is not valid.
 
Explanation:
QUESTION NO: 165
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
 Exhibit
 Oracle 1z0-071 Exam
  141
 You must 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 this SQL statement:
 Which statement is true regarding the execution?
 A.
It would not execute because the entire WHERE clause is not enclosed within parentheses.
 B.
It would execute but would return no rows.
 C.
It would not execute because the same column has been used twice with the AND logical
 operator.
 D.
It would execute and return the desired.
 
Explanation:
NEW QUESTIONS
 Oracle 1z0-071 Exam
  142
 QUESTION NO: 166
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 + 1000
 AS “New Credit Limit”
 FROM customers;
 B.
SELECT cust_last_name AS Name, cust_credit_limit + 1000
 AS New Credit Limit
 FROM 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 + 1000
 INITCAP (“NEW CREDIT LIMIT”)
 FROM customers;
 
Explanation:
QUESTION NO: 167
Oracle 1z0-071 Exam
  143
 View the Exhibit and examine the structure of the ORDERS table.
 Which UPDATE statement is valid?
 A.
UPDATE orders
 SET order_date = ’12-mar-2007’,
 order_total IS NULL
 WHERE order_id = 2455;
 B.
UPDATE orders
 SET order_date = ’12-mar-2007’,
 AND order_total = TO_NUMBER(NULL)
 WHERE order_id = 2455;
 C.
UPDATE orders
 SET order_date = ’12-mar-2007’,
 order_total = NULL
 WHERE order_id = 2455;
 D.
UPDATE orders
 SET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),
 Oracle 1z0-071 Exam
  144
 SET order_total = TO_NUMBER (NULL)
 WHERE order_id = 2455;
 
Explanation:
QUESTION NO: 168
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.
 
Explanation:
Oracle 1z0-071 Exam
  145
 QUESTION NO: 169
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
 Which two tasks would require subqueries? (Choose two.)
 A.
displaying all the products whose minimum list prices are more than average list price of products
 having the status orderable
 B.
displaying the total number of products supplied by supplier 102071 and having product status
 OBSOLETE
 C.
displaying the number of products whose list prices are more than the average list price
 D.
displaying all supplier IDs whose average list price is more than 500
 E.
displaying the minimum list price for each product status
 ,C
Explanation:
QUESTION NO: 170
View the Exhibit and examine the description of the EMPLOYEES table.
 Oracle 1z0-071 Exam
  146
 You want to calculate the total renumeration for each employee. Total renumeration is the sum of
 the annual salary and the percentage commission earned for a year. Only a few employees earn
 commission.
 Which SQL statement would you execute to get the desired output?
 A.
SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,
 salary,salary+commission_pct))“Total”
 FROM EMPLOYEES;
 B.
SELECT first_name, salary, salary*12+salary*commission_pct “Total”
 FROM EMPLOYEES;
 C.
SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total”
 FROM EMPLOYEES;
 D.
SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, “Total”
 FROM EMPLOYEES;
 
Explanation:
QUESTION NO: 171
View the Exhibit and examine the structure of the PROMOTIONS table.
 Oracle 1z0-071 Exam
  147
 Evaluate the following SQL statement:
 Which statement is true regarding the outcome of the above query?
 A.
It produces an error because subqueries cannot be used with the CASE expression.
 B.
It shows COST_REMARK for all the promos in the promo category ‘TV’.
 C.
It shows COST_REMARK for all the promos in the table.
 D.
It produces an error because the subquery gives an error.
 
Explanation:
QUESTION NO: 172
Which statement is true regarding the USING clause in table joins? (Choose two.)
 Oracle 1z0-071 Exam
  148
 A.
It can be used to join a maximum of three tables.
 B.
It can be used to access data from tables through equijoins as well as nonequijoins.
 C.
It can be used to join tables that have columns with the same name and compatible data types.
 D.
It can be used to restrict the number of columns used in a NATURAL join.
 ,D
Explanation:
QUESTION NO: 173
Examine the structure proposed for the TRANSACTIONS table:
 Which two statements are true regarding the storage of data in the above table structure? (Choose
 two.)
 A.
The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
 B.
The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and
 seconds.
 C.
The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of
 4,000 characters.
 D.
The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
 Oracle 1z0-071 Exam
  149
 ,B
Explanation:
QUESTION NO: 174
Examine the structure of the MARKS table:
 Which two statements would execute successfully? (Choose two.)
 A.
SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
 FROM marks
 WHERE subject1 > subject2;
 B.
SELECT student_name subject1
 FROM marks
 WHERE subject1 > AVG(subject1);
 C.
SELECT SUM(subject1+subject2+subject3)
 FROM marks
 WHERE student_name IS NULL;
 D.
SELECT student_name,SUM(subject1)
 FROM marks
 WHERE student_name LIKE ‘R%’;
 Oracle 1z0-071 Exam
  150
 ,C
Explanation:
QUESTION NO: 175
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
 ,D
Oracle 1z0-071 Exam
  151
 Explanation:
QUESTION NO: 176
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.
 ,F
Explanation:
Oracle 1z0-071 Exam
  152
 QUESTION NO: 177
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES
 tables.
 You have a requirement from the supplies department to give a list containing PRODUCT_ID,
 SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is
 less than five.
 Which two SQL statements can accomplish the task? (Choose two.)
 A.
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id
 FROM product_information pi JOIN inventories i
 ON (pi.product_id=i.product_id)
 WHERE quantity_on_hand < 5;
 B.
SELECT product_id, quantity_on_hand, supplier_id
 FROM product_information
 NATURAL JOIN inventories AND quantity_on_hand < 5;
 C.
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id
 FROM product_information pi JOIN inventories i
 ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
 Oracle 1z0-071 Exam
  153
 D.
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id
 FROM product_information pi JOIN inventories i
 ON (pi.product_id=i.product_id)
 USING (product_id) AND quantity_on_hand < 5;
 ,C
Explanation:
QUESTION NO: 178
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 executes successfully but no rows updated.
 B.
It executes successfully and updates the records of those employees who have been working in
 the company for more than 600 days.
 C.
It gives an error because multiple NVL functions are used in an expression.
 D.
It gives an error because NVL function cannot be used with UPDATE.
 
Explanation:
Oracle 1z0-071 Exam
  154
 Oracle 1z0-071 Exam
  155