1z0-071: Oracle Database 12c SQL

Question #: 151
Topic #: 1
Which two queries return the string Hello! We’re ready? (Choose two.)

A. SELECT “Hello! We’re ready” FROM DUAL;
B. SELECT ‘Hello! We’re ready’ FROM DUAL;
C. SELECT q’!Hello! We’re ready!’ FROM DUAL;
D. SELECT q'[Hello! We’re ready]’ FROM DUAL;
E. SELECT ‘Hello! We\’re ready’ ESCAPE ‘\’ FROM DUAL;

Selected Answer: CD

Question #: 152
Topic #: 1
Which three statements are true about the DESCRIBE command? (Choose three.)

A. It can be used from SQL Developer.
B. It displays the PRIMARY KEY constraint for any column or columns that have that constraint.
C. It displays all constraints that are defined for each column.
D. It displays the NOT NULL constraint for any columns that have that constraint.
E. It can be used only from SQL* Plus.
F. It can be used to display the structure of an existing view.

Selected Answer: ADF

Question #: 153
Topic #: 1
Which two statements are true about dropping views? (Choose two.)

A. The creator of a view to be dropped must have the DROP ANY VIEW privilege.
B. Data selected by a view’s defining query is deleted from its underlying tables when the view is dropped.
C. Views referencing a dropped view become invalid.
D. Read only views cannot be dropped.
E. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.

Selected Answer: CE

Question #: 154
Topic #: 1
Which two are true about the MERGE statement? (Choose two.)

A. The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.
B. The WHEN NOT MATCHED clause can be used to specify the updates to be performed.
C. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
D. The WHEN WATCHED clause can be used to specify the inserts to be performed.
E. The WHEN WATCHED clause can be used to specify the updates to be performed.

Selected Answer: CE

Question #: 156
Topic #: 1
Examine the description of the PRODUCTS table which contains data:

Which two are true? (Choose two.)

A. The PROD_NAME column cannot have a DEFAULT clause added to it.
B. The EXPIRY_DATE column cannot be dropped.
C. The EXPIRY_DATE column data type can be changed to TIMESTAMP.
D. The PROD_ID column can be renamed.
E. The PROD_ID column data type can be changed to VARCHAR2(2).

Selected Answer: CD

Question #: 157
Topic #: 1
Examine this query:
SELECT SUBSTR(SYSDATE, 1, 5) “Result” FROM DUAL;

Which statement is true?

A. It fails unless the expression is modified to SUBSTR(TO_CHAR(SYSDATE), 1, 5).
B. It fails unless the expression is modified to SUBSTR(TO_CHAR(TRUNC(SYSDATE), 1, 5).
C. It fails unless the expression is modified to TO_CHAR(SUBSTR(SYSDATE), 1, 5)).
D. It executes successfully with an implicit data type conversion.

Selected Answer: D

Question #: 158
Topic #: 1
Which statement is true about the INTERSECT operator used in compound queries?

A. It processes NULLs in the selected columns.
B. It ignores NULLs.
C. INTERSECT is of lower precedence than UNION or UNION ALL.
D. Multiple INTERSECT operators are not possible in the same SQL statement.

Selected Answer: A

Question #: 159
Topic #: 1
You currently have an active transaction in your session and have been granted SELECT access to
V$TRANSACTION.

Executing:

SELECT xid, status FROM v$transaction;

in your session returns:

In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started? (Choose three.)

A. after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement
B. after successfully executing a TRUNCATE statement followed by a DML statement
C. after successfully executing a DML statement following a failed DML statement
D. after successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement
E. after successfully executing a COMMIT or ROLLBACK followed by a DML statement
F. after successfully executing a COMMIT or ROLLBACK followed by a SELECT statement

Selected Answer: BDE

Question #: 162
Topic #: 1
Examine the description of the CUSTOMERS table:

Which two statements will do an implicit conversion? (Choose two.)

A. SELECT * FROM customers WHERE customer_id – ‘0001’;
B. SELECT * FROM customers WHERE customer_id – 0001;
C. SELECT * FROM customers WHERE insert_date – DATE ‘2019-01-01′;
D. SELECT * FROM customers WHERE insert_date – ’01-JAN-19’;
E. SELECT * FROM customers WHERE TO_CHAR(customer_id) – ‘0001’;

Selected Answer: AD

Question #: 163
Topic #: 1
Which two statements are true about CURRENT_TIMESTAMP? (Choose two.)

A. The value varies depending on the setting of SESSIONTIMEZONE.
B. It returns a value of data type TIMESTAMP.
C. The date is in the time zone of DBTIMEZONE.
D. It returns the same date as CURRENT_TIME.
E. The time is in the time zone of DBTIMEZONE.
F. It always returns the same value as SYSTEMTIMESTAMP.

Selected Answer: AB

Question #: 164
Topic #: 1
Examine the description of the CUSTOMERS table:

You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters.

Which query can be used?

A. SELECT * FROM customers WHERE city LIKE ‘D__%’;
B. SELECT * FROM customers WHERE city = ‘%D__’;
C. SELECT * FROM customers WHERE city = ‘D__%’;
D. SELECT * FROM customers WHERE city LIKE ‘D__’;

Selected Answer: A

Question #: 165
Topic #: 1
Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.)

A. It can be used with SET operators (UNION, INTERSECT etc.).
B. It cannot be used with the DISTINCT keyword.
C. If the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait.
D. The statement skips rows locked by other transactions.
E. It can be used with joins.

Selected Answer: BE

Question #: 166
Topic #: 1
You must find the number of employees whose salary is lower than employee 110.

Which statement fails to do this?

A.
B.
C.
D.

Selected Answer: A

Question #: 168
Topic #: 1
Which two are true about constraints? (Choose two.)

A. A column with a FOREIGN KEY constraint can never contain a NULL value.
B. A constraint can be disabled even if the constrained column contains data.
C. Constraints are enforced only during INSERT operations.
D. All constraints can be defined at the table or column level.
E. A column with a UNIQUE constraint can contain a NULL value.

Selected Answer: B

Question #: 169
Topic #: 1
Examine the ORDER_ITEMS table:

Which two queries return rows where QUANTITY is a multiple of ten? (Choose two.)

A. SELECT * FROM order_items WHERE quantity / 10 – TRUNC(quantity
B. SELECT * FROM order_items WHERE MOD(quantity, 10) – 0;
C. SELECT * FROM order_items WHERE FLOOR(quantity / 10) = TRUNC(quantity / 10);
D. SELECT * FROM order_items WHERE quantity = TRUNC(quantity, -1);
E. SELECT * FROM order_items WHERE quantity = ROUND(quantity, 1);

Selected Answer: D

Question #: 170
Topic #: 1
Which two statements are true about indexes and their administration in an Oracle database? (Choose two.)

A. A new index can be created or an existing one reused when a primary key constraint is created.
B. An INVINSIBLE index is maintained by DML operations on the underlying table.
C. If a query filters on an indexed column, the index will always be accessed during execution of the query.
D. A DROP INDEX statement always prevents updates to the table during the drop operation.
E. The same table column cannot be part of a unique and non-unique index.

Selected Answer: AB

Question #: 171
Topic #: 1
Examine this incomplete query:

SELECT DATE ‘2019-01-01’ +
FROM DUAL;

Which three clauses can replace to add 12 hours to the date? (Choose three.)

A. INTERVAL ‘0.5’ DAY
B. INTERVAL ‘720’ MINUTE
C. INTERVAL ’11:60′ HOUR TO MINUTE
D. INTERVAL ’12:00′ HOUR TO SECOND
E. INTERVAL ‘0 12′ DAY TO HOUR
F. INTERVAL ’12’ HOUR

Selected Answer: BEF

Question #: 172
Topic #: 1
Which two are true about the data dictionary? (Choose two.)

A. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
B. All user actions are recorded in the data dictionary.
C. All users have permissions to access all information in the data dictionary by default.
D. The SYS user owns all base tables and user-accessible views in the data dictionary.
E. Base tables in the data dictionary have the prefix DBA_.

Selected Answer: AD

Question #: 173
Topic #: 1
Which two statements are true about the DUAL table: (Choose two.)

A. It can display multiple rows but only a single column.
B. It can be accessed only by the SYS user.
C. It can be accessed by any user who has the SELECT privilege in any schema.
D. It can display multiple rows and columns.
E. It consists of a single row and single column of VARCHAR2 data type.
F. It can be used to display only constants or pseudo columns.

Selected Answer: CE

Question #: 174
Topic #: 1
Which statement is true about TRUNCATE and DELETE?

A. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE.
B. For large tables TRUNCATE is faster than DELETE.
C. You can never TRUNCATE a table if foreign key constraints will be violated.
D. You can never DELETE rows from a table if foreign key constraints will be violated.

Selected Answer: B

Question #: 175
Topic #: 1
Examine these statements and the result:

Now examine this command:

What must replace MISSING CLAUSE for CUSTOMER_SEQ.NEXTVAL to return 11?

A. NOCACHE
B. INCREMENT BY 10
C. START WITH 11
D. MINVALUE 11
E. CYCLE 11

Selected Answer: B

Question #: 176
Topic #: 1
Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:

Which two can replace so the query completes successfully?

A. quantity * unit_price
B. quantity
C. total_paid
D. product_id
E. quantity, unit_price

Selected Answer: AC

Question #: 177
Topic #: 1
Which set of commands will prompt only once for the name of the table to use in the query?

A. PROMPT Enter table name &x –
SELECT employee_id FROM &x WHERE last_name = ‘King’;
B. DEFINE x = ‘employees’

PROMPT Enter table name &x –
SELECT employee_id FROM &x WHERE last_name = ‘King’;
C. PROMPT Enter table name &x –
SELECT employee_id FROM &&x WHERE last_name = ‘King’;
D. PROMPT Enter table name &&x –
SELECT employee_id FROM &x WHERE last_name = ‘King’;

Selected Answer: D

Question #: 178
Topic #: 1
The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.

Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

A. SELECT REPLACE(REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘O’) FROM customers;
B. SELECT REPLACE(TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘O’) FROM customers;
C. SELECT REPLACE(SUBSTR(cust_last_name, -3), ‘An’, ‘O’) FROM customers;
D. SELECT INITCAP(REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘O’)) FROM customers;

Selected Answer: A

Question #: 179
Topic #: 1
Examine the description of the PRODUCT_STATUS table:

The STATUS column contains the values IN STOCK or OUT OF STOCK for each row. Which two queries will execute successfully?

A. SELECT prod_id || q”’s not available” FROM product_status WHERE status = ‘OUT OF STOCK’;
B. SELECT prod_id || q’(‘s not available)’ ‘CURRENT AVAILABILITY’ FROM product_status WHERE status = ‘OUT OF STOCK’;
C. SELECT prod_id q’s not available” FROM product_status WHERE status = ‘OUT OF STOCK’;
D. SELECT prod_id “CURRENT AVAILABILITY” || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;
E. SELECT prod_id || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;
F. SELECT prod_id || q’(‘s not available)’ “CURRENT AVAILABILITY” FROM product status WHERE status = ‘OUT OF STOCK’;

Selected Answer: EF

Question #: 180
Topic #: 1
Which two statements are true about INTERVAL data types?

A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
B. INTERVAL DAY TO SECOND columns support fractions of seconds.
C. INTERVAL YEAR TO MONTH columns support yearly intervals.
D. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
E. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
F. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

Selected Answer: BC

Question #: 181
Topic #: 1
Which two statements are true about the data dictionary?

A. The data dictionary is accessible when the database is closed.
B. The data dictionary does not store metadata in tables.
C. Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.
D. Views with the prefix DBA_ display only metadata for objects in the SYS schema.
E. Views with the prefix ALL_ display metadata for objects to which the current user has access.

Selected Answer: C

Question #: 182
Topic #: 1
Examine the description of the CUSTOMERS table:

You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000.
Examine this partial query:

SELECT cust_last_name, cust_credit_limit FROM customers

Which two WHERE conditions give the required result?

A. WHERE UPPER(cust_last_name) IN (‘AX’, ‘B%’)
AND cust_credit_limit < 1000; B. WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000; C. WHERE UPPER(cust_last_name) BETWEEN UPPER(‘A%’ AND ‘B%’) AND ROUND(cust_credit_limit) < 1000; D. WHERE (INITCAP(cust_last_name) LIKE ‘A%’ OR INITCAP(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000; E. WHERE (UPPER(cust_last_name) LIKE INITCAP(‘A’) OR UPPER(cust_last_name) LIKE INITCAP(‘B’)) AND ROUND(cust_credit_limit) < ROUND(1000); Selected Answer: BD Question #: 183 Topic #: 1 Which two statements are true about substitution variables? A. A substitution variable can be used only in a SELECT statement. B. A substitution variable used to prompt for a column name must be enclosed in double quotation marks. C. A substitution variable can be used with any clause in a SELECT statement. D. A substation variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session. E. A substitution variable prefixed with & always prompts only once for a value in a session. F. A substitution variable used to prompt for a column name must be enclosed in single quotation marks. Selected Answer: CD Question #: 184 Topic #: 1 Which two are true about scalar subquery expressions? A. They can return at most one row. B. You must enclose them in parentheses. C. You cannot correlate them with a table in the parent statement. D. They can return two columns. E. You can use them as a default value for a column. Selected Answer: AB Question #: 185 Topic #: 1 Examine the description PRODUCTS table: Examine the description of the NEW_PRODUCTS table: Which two queries execute successfully? A. SELECT prod_id FROM products - UNION ALL - SELECT prod_id, prod_name FROM new_products; B. SELECT prod_id, exp_date FROM products UNION ALL - SELECT prod_id, NULL FROM new_products; C. SELECT * FROM products - MINUS - SELECT prod_id, FROM new_products; D. SELECT prod_id, prod_name FROM products INTERSECT - SELECT 100, prod_name FROM new_products; E. SELECT * FROM products - UNION - SELECT * FROM new_products; Selected Answer: BE Question #: 187 Topic #: 1 Which two are true about multitable INSERT statements? A. The conditional INSERT FIRST statement always inserts a row into a single table. B. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables. C. They can transform a row from a source table into multiple rows in a target table. D. The conditional INSERT ALL statement inserts rows into a singe table by aggregating source rows. E. They always use subqueries. Selected Answer: CE Question #: 188 Topic #: 1 Which statement is true about using functions in WHERE and HAVING? A. Using aggregate functions in the WHERE clause requires a subquery. B. Using aggregate functions in the HAVING clause requires a subquery. C. Using single-row functions in the WHERE clause requires a subquery. D. Using single-row functions in the HAVING clause requires a subquery. Selected Answer: A Question #: 189 Topic #: 1 You execute these commands: Which two, used independently, can replace so the query returns 1? A. ROLLBACK; B. ROLLBACK TO SAVEPOINT post_insert; C. ROLLBACK TO post_insert; D. COMMIT; E. COMMIT TO SAVEPOINT post_insert; Selected Answer: BC Question #: 190 Topic #: 1 Examine the description of the EMPLOYEES table: Which two queries return the highest salary in the table? A. SELECT MAX(salary) FROM employees - GROUP By department_id - HAVING MAX(salary) = MAX(MAX(salary)); B. SELECT MAX(salary) FROM employees - GROUP By department_id; C. SELECT department_id, MAX(salary) FROM employees - GROUP By department_id; D. SELECT MAX(salary) FROM employees; E. SELECT MAX(MAX(salary)) FROM employees - GROUP By department_id; Selected Answer: DE Question #: 191 Topic #: 1 Examine this data in the EMPLOYEES table: Which statement will execute successfully? A. SELECT dept_id, INSTR(last_name, ‘A’), SUM(salary) FROM employees GROUP BY dept_id; B. SELECT dept_id, STDDEV(last_name), SUM(salary) FROM employees GROUP BY dept_id; C. SELECT dept_id, LENGTH(last_name), SUM(salary) FROM employees GROUP BY dept_id; D. SELECT dept_id, MAX(last_name), SUM(salary) FROM employees GROUP BY dept_id; Selected Answer: D Question #: 192 Topic #: 1 Examine the description of the EMPLOYEES table: NLS_DATE_FORMAT is set to DD-MON-YY. Which query requires explicit data type conversion? A. SELECT join_date FROM employees WHERE join_datee > ’10-02-2018’;
B. SELECT join_date || ‘ ‘ || salary FROM employees;
C. SELECT salary + ’120.50’ FROM employees;
D. SELECT join_date + ‘20’ FROM employees;
E. SELECT SUBSTR(join_date, 1, 2) -1’

Selected Answer: A

Question #: 193
Topic #: 1
Which three statements are true about external tables? (Choose three.)

A. They can be temporary tables.
B. DML statements can modify them.
C. They can be used in queries containing joins.
D. They can be used in queries containing sorts.
E. They can be indexed.
F. Their metadata is stored in the database.

Selected Answer: CDF

Question #: 194
Topic #: 1
Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109.

User ALICE has no privileges to access HR.EMPLOYEES.

User ALICE starts a session.

User HR starts a session and successfully executes these statements:

GRANT DELETE ON employees TO alice;

UPDATE employees SET salary = 24000 WHERE employee_id = 109;

In her existing session ALICE then executes:

DELETE FROM hr.employees WHERE employee_id = 109;

What is the result?

A. The DELETE command will wait for HR’s transaction to end then return an error.
B. The DELETE command will immediately delete the row.
C. The DELETE command will wait for HR’s transaction to end then delete the row.
D. The DELETE command will immediately return an error.

Selected Answer: D

Question #: 195
Topic #: 1
Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined? (Choose three.)

A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
B. Delete statements can always be done on a table through a view.
C. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
D. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword,
F. Insert statements can always be done on a table through a view.

Selected Answer: CDE

Question #: 196
Topic #: 1
In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR.

Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? (Choose two.)

A. TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number.
B. PROMO_BEGIN_DATE – SYSDATE will return a number.
C. PROMO_BEGIN_DATE – SYSDATE will return an error.
D. PROMO_BEGIN_DATE – 5 will return a date.
E. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

Selected Answer: BD

Question #: 198
Topic #: 1
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?

A. Column positions must be used in the ORDER BY clause.
B. Only column names from the first SELECT statement in the compound query are recognized.
C. Each SELECT statement in the compound query must have its own ORDER BY clause.
D. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.
E. Each SELECT statement in the compound query can have its own ORDER BY clause.

Selected Answer: BD

Question #: 199
Topic #: 1
Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

A. The number of columns in each SELECT in the compound query can be different.
B. INTERSECT returns rows common to both sides of the compound query.
C. INTERSECT ignores NULLs.
D. Columns named in each SELECT in the compound query can be different.
E. Reversing the order of the intersected tables can sometimes affect the output.

Selected Answer: BD

Question #: 200
Topic #: 1
Examine these statements:

Which is true about modifying the columns in ALTER_TEST?

A. c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAR2(10).
B. c1 can be changed to NUMBER(10) and c2 cannot be changed to VARCHAR2(10).
C. c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAR2(5).
D. c1 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER(10).
E. c1 can be changed to VARCHAR2(5) but c2 can be changed to NUMBER(12,2).

Selected Answer: E