Is this exam completely free?

Examcademy offers a free preview for every exam, covering around 20% of the total questions. Full access is available with a paid upgrade.

Can I practice IT certification exams from Microsoft, AWS, or CompTIA?

Yes! Examcademy supports a wide range of IT certification exams including Microsoft Azure, AWS Cloud Practitioner, and CompTIA A+ and Security+.

How accurate are the mock exams?

Our mock exams are built and reviewed with the help of AI and community contributions, staying aligned with real-world exam objectives.

1z0-071Free trial

By oracle

Verified

25Q per page

Question 1

Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)

A: SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
B: SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
C: SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
D: SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
E: SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

—

Question 2

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted.
You only want to display details of rows where START_DATE is within the last 25 months.
Which WHERE clause can be used?

A: WHERE TO_NUMBER(start_date - SYSDATE) <= 25
B: WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
C: WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
D: WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

—

Question 3

Examine the BRICKS table:

You write this query:

How many rows will the query return?

A: 4
B: 6
C: 16
D: 0
E: 1
F: 10

—

Question 4

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

—

Question 5

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

—

Question 6

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?

—

Question 7

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.

—

Question 8

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

—

Question 9

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.

—

Question 10

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;

—

Question 11

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.

—

Question 12

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.

—

Question 13

Which three are true about scalar subquery expressions? (Choose three.)

A: They can be nested.
B: They cannot be used in the VALUES clause of an INSERT statement.
C: A scalar subquery expression that returns zero rows evaluates to zero.
D: They can be used as default values for columns in a CREATE TABLE statement.
E: A scalar subquery expression that returns zero rows evaluates to NULL.
F: They cannot be used in GROUP BY clauses.

—

Question 14

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.

—

Question 15

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

—

Question 16

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*’;

—

Question 17

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.

—

Question 18

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.

—

Question 19

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.

—

Question 20

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.

—

Question 21

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.

—

Question 22

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.

—

Question 23

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

—

Question 24

Examine this query:

Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.)

A: Use the DEFINE command before executing the query.
B: Replace '&1' with '&&1' in the query.
C: Use the UNDEFINE command before executing the query.
D: Execute the SET VERIFY OFF command before executing the query.
E: Execute the SET VERIFY ON command before executing the query.
F: Store the query in a script and pass the substitution value to the script when executing it.

—

Question 25

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.)

—

Page 1 of 11 • Questions 1-25 of 267

1 2 3 4 5

→

Free preview mode

Enjoy the free questions and consider upgrading to gain full access!