1z0-071: Oracle Database 12c SQL

Question #: 101
Topic #: 1
Examine this query:

SELECT INTERVAL ‘100’ MONTH DURATION FROM DUAL;

What will be the output?

A. an error
B. DURATION –
———-
+100
C. DURATION –
———-
+08
D. DURATION –
———-
+08-04

Selected Answer: D

Question #: 102
Topic #: 1
Examine this query:

SELECT TRUNC(ROUND(156.00,-2),-1) FROM DUAL;

What is the result?

A. 150
B. 200
C. 160
D. 100
E. 16

Selected Answer: B

Question #: 103
Topic #: 1
You want to write a query that prompts for two column names and the where condition each time it is executed in a session but only prompts for the table name the first time it is executed.

The variables used in your query are never undefined in your session.

Which query can be used?

A.
B.
C.
D.
E.

Selected Answer: D

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

A. The same table column can be part of a unique and non-unique index.
B. A descending index is a type of function-based index.
C. An INVINSIBLE index is not maintained when DML is performed on its underlying table.
D. If a query filters on an indexed column then it will always be used during execution of the query.
E. An index can be created as part of a CREATE TABLE statement.
F. An UNUSABLE index is maintained when DML is performed on its underlying table.

Selected Answer: ABE

Question #: 105
Topic #: 1
Examine this description of the EMP table:

You execute this query:

What is the result?

A. only departments where the total salary is greater than 3000, returned in no particular order
B. only departments where the total salary is greater than 3000, ordered by department
C. all departments and a sum of the salaries of employees with a salary greater than 3000
D. an error

Selected Answer: B

Question #: 106
Topic #: 1
Which two are true about virtual columns? (Choose two.)

A. They can be indexed.
B. They can be referenced in the column expression of another virtual column.
C. They cannot have a data type explicitly specified.
D. They can be referenced in the set clause of an update statement as the name of the column to be updated.
E. They can be referenced in the where clause of an update or delete statement.

Selected Answer: AE

Question #: 107
Topic #: 1
A session’s NLS_DATE_FORMAT is set to DD Mon YYYY.

Which two queries return the value 1 Jan 2019? (Choose two.)

A. SELECT TO_DATE(‘2019-01-01’) FROM DUAL;
B. SELECT DATE ‘2019-01-01’ FROM DUAL;
C. SELECT ‘2019-01-01’ FROM DUAL;
D. SELECT TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’) FROM DUAL;
E. SELECT TO_CHAR(‘2019-01-01’) FROM DUAL;

Selected Answer: C

Question #: 108
Topic #: 1
Examine this SQL statement:

Which two are true? (Choose two.)

A. The DELETE statement executes successfully even if the subquery selects multiple rows.
B. The subquery is executed before the DELETE statement is executed.
C. The subquery is not a correlated subquery.
D. All existing rows in the EMPLOYEES table are deleted.
E. The subquery is executed for every row in the EMPLOYEES table.

Selected Answer: AE

Question #: 109
Topic #: 1
Examine this constraint information:

Which three statements are true? (Choose three.)

A. The SALARY column must have a value.
B. The DEPTNO column in the EMP table can contain NULLS.
C. The COMMISION column can contain negative values.
D. The DEPTNO column in the EMP table can contain the value 1.
E. The MANAGER column is a foreign key referencing the EMPNO column.
F. The DNAME column has a unique constraint.
G. An index is created automatically in the MANAGER column.

Selected Answer: CDE

Question #: 110
Topic #: 1
Which two are true about creating tables in an Oracle database? (Choose two.)

A. Creating an external table will automatically create a file using the specified directory and file name.
B. A system privilege is required.
C. The same table name can be used for tables in different schemas.
D. A primary key constraint is mandatory.
E. A CREATE TABLE statement can specify the maximum number of rows the table will contain.

Selected Answer: BC

Question #: 111
Topic #: 1
Examine this partial statement:

SELECT ename, sal, comm FROM emp

Now examine this output:

Which ORDER BY clause will generate the displayed output?

A. ORDER BY comm DESC NULLS LAST, ename
B. ORDER BY NVL(coram, 0) ASC NULLS FIRST, ename
C. ORDER BY NVL(coram, 0) ASC NULLS LAST, ename
D. ORDER BY NVL(ccmm, 0) DESC, ename

Selected Answer: A

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

Which two SELECT statements will return these results: (Choose two.)

A. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA*’;
B. SELECT customer_name FROM customers WHERE customer_name = ‘*Ma*’;
C. SELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma*’;
D. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA%’;
E. SELECT customer_name FROM customers WHERE customer_name LIKE ‘%a%’;
F. SELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma%’;
G. SELECT customer_name FROM customers WHERE customer_name LIKE ‘*Ma*’;

Selected Answer: EF

Question #: 113
Topic #: 1
The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2).

Evaluate this SQL statement:

SELECT TO_CHAR(unit_price, ‘$9,999’) FROM product_information;

Which two statements are true about the output? (Choose two.)

A. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236.
B. A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.
C. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023.
D. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.
E. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

Selected Answer: BD

Question #: 114
Topic #: 1
Which two statements are true about Oracle databases and SQL? (Choose two.)

A. Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.
B. A query can access only tables within the same schema.
C. The database guarantees read consistency at select level on user-created tables.
D. A user can be the owner of multiple schemas in the same database.
E. When you execute an update statement, the database instance locks each updated row.

Selected Answer: CE

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

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

Selected Answer: B

Question #: 116
Topic #: 1
Which two statements are true? (Choose two.)

A. CASE is a function and DECODE is not.
B. Neither CASE nor DECODE is a function.
C. All conditions evaluated using CASE can also be evaluated using DECODE.
D. All conditions evaluated using DECODE can also be evaluated using CASE.
E. DECODE is a function and CASE is not.
F. Both CASE and DECODE are functions.

Selected Answer: DE

Question #: 117
Topic #: 1
Examine these statements executed in a single Oracle session:

Which three statements are true? (Choose three.)

A. The code for pen is 10.
B. There is no row containing fountain pen.
C. There is no row containing pen.
D. There is no row containing pencil.
E. The code for fountain pen is 3.
F. The code for pen is 1.

Selected Answer: ADE

Question #: 118
Topic #: 1
Which is true about the & and && prefixes with substitution variables? (Choose all that apply.)

A. Both & and && can prefix a substitution variable name in queries and DML statements.
B. An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice.
C. & can prefix a substitution variable name only in queries.
D. An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.
E. The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

Selected Answer: AB

Question #: 119
Topic #: 1
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

A.
B.
C.
D.

Selected Answer: A

Question #: 120
Topic #: 1
Examine the data in the COLORS table:

Examine the data in the BRICKS table:

Which two queries return all the rows from COLORS? (Choose two.)
A.
B.
C.
D.
E.

Selected Answer: AB

Question #: 121
Topic #: 1
Which two queries execute successfully? (Choose two.)

A. SELECT INTERVAL ‘1’ DAY – INTERVAL ‘1’ MINUTE FROM DUAL
B. SELECT SYSTIMESTAMP + INTERVAL ‘1’ DAY FROM DUAL;
C. SELECT INTERVAL ‘1’ DAY – SYSDATE FROM DUAL;
D. SELECT INTERVAL ‘1’ DAY + INTERVAL ‘1’ MONTH FROM DUAL;
E. SELECT SYSDATE * INTERVAL ‘1’ DAY FROM DUAL;

Selected Answer: AB

Question #: 123
Topic #: 1
Examine these statements which execute successfully:

Which two are true? (Choose two.)

A. User FIN_CLERK can grant SELECT on SCOTT.EMP to user FIN_MANAGER.
B. Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.
C. User FINANCE can grant CREATE SESSION to user FIN_MANAGER.
D. Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.
E. User FINANCE is unable to grant all on SCOTT.EMP to FIN_MANAGER.

Selected Answer: DE

Question #: 124
Topic #: 1
Which two are true about granting privileges on objects? (Choose two.)

A. An object privilege can be granted to other users only by the owner of that object.
B. The owner of an object acquires all object privileges on that object by default.
C. The WITH GRANT OPTION clause can be used only by DBA users.
D. An object privilege can be granted to a role only by the owner of that object.
E. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.

Selected Answer: BE

Question #: 125
Topic #: 1
Examine the description of the EMPLOYEES table:

Which two queries will execute successfully? (Choose two.)

A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > ’01-JAN-19′;
B. SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > ’01-JAN-19′ GROUP BY dept_id;
C. SELECT dept_id, MAX(SUM(salary)) FROM employees GROUP BY dept_id;
D. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
E. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

Selected Answer: BE

Question #: 127
Topic #: 1
Examine data in the BRICKS table:

Examine the BOXES table:

Which two queries only return CUBE? (Choose two.)
A.
B.
C.
D.
E.

Selected Answer: CD

Question #: 128
Topic #: 1
Which two statements will return the names of the three employees with the lowest salaries? (Choose two.)
A.
B.
C.
D.
E.

Selected Answer: BC

Question #: 129
Topic #: 1
Examine this query which executes successfully:

What will be the result?

A. It will return rows from both SELECT statements including duplicate rows.
B. It will return rows from both SELECT statements after eliminating duplicate rows.
C. It will return rows that are not common to both SELECT statements.
D. It will return rows common to both SELECT statements.

Selected Answer: A

Question #: 130
Topic #: 1
Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A. A sequence’s unallocated cached values are lost if the instance shuts down.
B. A sequence number that was allocated can be rolled back if a transaction fails.
C. A sequence can only be dropped by a DBA.
D. A sequence can issue duplicate values.
E. Sequences can always have gaps.
F. Two or more tables cannot have keys generated from the same sequence.

Selected Answer: ADE

Question #: 131
Topic #: 1
Examine this description of the PRODUCTS table:

You successfully execute this command:

CREATE TABLE new_prices (prod_id NUMBER(2), price NUMBER(8,2))

Which two statements execute without errors? (Choose two.)
A.
B.
C.
D.

Selected Answer: CD

Question #: 132
Topic #: 1
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type number.

Which two queries execute successfully? (Choose two.)

A. SELECT NVL2(cust_credit_limit * .15, ‘Not Available’) FROM customers;
B. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers
C. SELECT NVL(cust_credit_limit * .15, ‘Not Available’) FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
E. SELECT TO_CHAR(NVL(cust_credit_iimit * .15, ‘Not Available’)) FROM customers;

Selected Answer: BD

Question #: 133
Topic #: 1
Examine this statement which executes successfully:

Which statement will violate the CHECK constraint?
A.
B.
C.
D.

Selected Answer: D

Question #: 135
Topic #: 1
Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

A. An UNUSED column’s space is reclaimed automatically when the row containing that column is next queried.
B. A column that is set to UNUSED still counts towards the limit of 1000 columns per table.
C. A DROP COLUMN command can be rolled back.
D. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
E. An UNUSED column’s space is reclaimed automatically when the block containing that column is next queried.
F. Partition key columns cannot be dropped.

Selected Answer: BDF

Question #: 136
Topic #: 1
Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

A. Create a directory object for a flat file.
B. Create a directory object for an external table.
C. Read data from an external table and load it into a table in the database.
D. Execute DML statements on an external table.
E. Read data from a table in the database and insert it into an external table.
F. Query data from an external table.

Selected Answer: CEF

Question #: 137
Topic #: 1
Which statement is true about aggregate functions?

A. Aggregate functions can be nested to any number of levels.
B. The AVG function implicitly converts NULLS to zero.
C. The MAX and MIN functions can be used on columns with character data types.
D. Aggregate functions can be used in any clause of a SELECT statement.

Selected Answer: C

Question #: 138
Topic #: 1
Which three are true about multitable INSERT statements? (Choose three.)

A. They can insert each computed row into more than one table.
B. They can be performed on remote tables.
C. They can be performed on external tables using SQL*Loader.
D. They can be performed on views.
E. They can be performed only by using a subquery.
F. They can be performed on relational tables.

Selected Answer: AEF

Question #: 139
Topic #: 1
Which three statements are true regarding single row subqueries? (Choose three.)

A. They must be placed on the left side of the comparison operator or condition.
B. They must be placed on the right side of the comparison operator or condition.
C. They must return a row to prevent errors in the SQL statement.
D. A SQL statement may have multiple single row subquery blocks.
E. They can be used in the HAVING clause.
F. They can be used in the WHERE clause.

Selected Answer: A

Question #: 140
Topic #: 1
In your session NLS_DATE_FORMAT is set to DD-MON-RR.

Which two queries display the year as four digits? (Choose two.)

A. SELECT TO_DATE(SYSDATE, ‘RRRR-MM-DD’) FROM DUAL;
B. SELECT TO_CHAR(SYSDATE, MM/DD/YYYY) FROM DUAL;
C. SELECT TO_DATE(ADD_MONTHS(SYSDATE, 6), ‘dd-non-yyyy’) FROM DUAL;
D. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6)) FROM DUAL;
E. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6), ‘dd-mon-yyyy’) FROM DUAL;
F. SELECT TO_DATE(TO_CHAR(SYSDATE, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’) FROM DUAL;

Selected Answer: EF

Question #: 141
Topic #: 1
Which two are true about savepoints? (Choose two.)

A. After issuing a savepoint, you can roll back to the savepoint name within the current transaction.
B. They make uncommitted updates visible to sessions owned by other users.
C. You can commit updates done between two savepoints without committing other updates in the current transaction.
D. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.
E. They make uncommitted updates visible to other sessions owned by the same user.
F. After issuing a savepoint, you cannot roll back the complete transaction.

Selected Answer: AD

Question #: 142
Topic #: 1
Examine these statements executed in a single Oracle session:

Which three statements are true? (Choose three.)

A. There is no row containing pencil.
B. The code for pen is 10.
C. There is no row containing fountain pen.
D. The code for pen is 1.
E. There is no row containing pen.
F. The code for fountain pen is 3.

Selected Answer: ABF

Question #: 143
Topic #: 1
The ORDERS table has a column ORDER_DATE of data type DATE.

The default display format for a date is DD-MON-RR.

Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.)

A. WHERE order_date > TO_DATE(‘JUL 10 2018’, ‘ MON DD YYYY’)
B. WHERE order_date > TO_DATE(ADD_MONTH(SYSDATE, 6), ‘MON DD YYYY’)
C. WHERE order_date IN (TO_DATE(‘Oct 21 2018’, ‘Mon DD YYYY’), TO_CHAR(‘Nov 21 2018’, ‘Mon DD YYYY’))
D. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), ‘MON DD YYYY’)
E. WHERE TO_CHAR(order_date. ‘MON DD YYYY’) = ‘JAN 20 2019’

Selected Answer: AE

Question #: 144
Topic #: 1
Examine this query:

What is the result?

A. an error
B. no rows
C. 1 row
D. 3 rows
E. 6 rows
F. 8 rows

Selected Answer: B

Question #: 145
Topic #: 1
Which two object privileges can be restricted to a subset of columns in a table? (Choose two.)

A. INDEX
B. ALTER
C. UPDATE
D. INSERT
E. DELETE

Selected Answer: CD

Question #: 146
Topic #: 1
Examine the description of the BOOKS table:

Examine these requirements:

1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000.
2. Sort the titles by date of purchase, starting with the most recently purchased book.

Which two queries can be used? (Choose two.)
A.
B.
C.
D.
E.

Selected Answer: CE

Question #: 147
Topic #: 1
View the Exhibit and examine the description of the tables.

You execute this SQL statement:

Which three statements are true? (Choose three.)

A. The statement will execute successfully and a new row will be inserted into the SALES table.
B. A product can have a different unit price at different times.
C. The statement will fail if a row already exists in the SALES table for product 23.
D. The statement will fail because a subquery may not be contained in a VALUES clause.
E. A customer can exist in many countries.
F. The SALES table has five foreign keys.

Selected Answer: ABF

Question #: 148
Topic #: 1
Which three statements are true about an ORDER BY clause? (Choose three.)

A. By default an ORDER BY clause sorts rows in descending order
B. An ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query.
C. An ORDER BY clause always sorts NULL values last.
D. By default an ORDER BY clause sorts rows in ascending order.
E. An ORDER BY clause can perform a binary sort.
F. An ORDER BY clause can perform a linguistic sort.

Selected Answer: DEF

Question #: 149
Topic #: 1
Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY + COMMISSION is greater than 20000? (Choose three.)

A. SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;
B. SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000
C. SELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;
D. SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;
E. SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;
F. SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

Selected Answer: ABF

Question #: 150
Topic #: 1
Examine the description of EMPLOYEES table:

The session time zone is the same as the database server.

Which two statements will list only the employees who have been working with the company for more than five years? (Choose two.)

A. SELECT employee_name FROM employees WHERE (SYSDATE – hire_date) / 12 > 5;
B. SELECT employee_name FROM employees WHERE (SYSTIMSSTAMF – hire_date) / 12 > INTERVAL ‘5’ YEAR;
C. SELECT employee_name FROM employees WHERE (CURRENT_DATE – hire_date) / 12 > 5
D. SELECT employee_name FROM employees WHERE (CURRENT_DATE – hire_date) / 365 >
E. SELECT employee_name FROM employees WHERE (SYSDATE – hire_date) / 365 > 5;
F. SELECT employee_name FROM employees WHERE (SYSTIMESTAMP – hire_date) / 365 > INTERVAL ‘1825’ DAY;

Selected Answer: DE