1Z0-171 sample questions

Question 1

A database consultant is designing a schema for a new inventory system. The system requires a table to store product categories where each category must have a unique numeric identifier that is automatically generated by the database upon insertion. The identifier should not allow gaps if the database restarts, even at the cost of performance. Which DDL strategy fulfills this requirement?

Accepted Answer

Define the column as NUMBER GENERATED ALWAYS AS IDENTITY (NOCACHE ORDER)

Answer

Define the column as NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 20)

Answer

Create a standard SEQUENCE with CACHE 100 and use a BEFORE INSERT trigger

Answer

Define the column as NUMBER DEFAULT SEQ_CAT.NEXTVAL

Question 2

You are querying the `SALES_DATA` table to generate a report. You need to return the string 'No Commission' for any row where the `COMMISSION_PCT` column is NULL. If `COMMISSION_PCT` is not NULL, you must return the commission value converted to a string. Which function is best suited for this specific requirement?

Accepted Answer

NVL(TO_CHAR(COMMISSION_PCT), 'No Commission')

Answer

COALESCE(COMMISSION_PCT, 'No Commission')

Answer

NULLIF(COMMISSION_PCT, 'No Commission')

Answer

NVL2(COMMISSION_PCT, 'No Commission', TO_CHAR(COMMISSION_PCT))

Question 3

Review the following query structure intended to filter employee records.

```sql
SELECT employee_id, last_name
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'AD_PRES'
AND salary > 15000;
```

Which statement accurately describes how the database interprets the precedence of operators in this query?

Accepted Answer

It returns all IT_PROG employees regardless of salary, plus AD_PRES employees who earn more than 15000.

Answer

It returns employees who are either IT_PROG or AD_PRES, provided they earn more than 15000.

Answer

It returns IT_PROG employees with salary > 15000 and all AD_PRES employees.

Answer

The query will fail because parentheses are required when mixing AND and OR.

Question 4

You are performing a data cleanup operation. You need to remove all rows from the `STAGING_LOGS` table to free up storage space immediately. The operation must not generate undo logs for individual rows and cannot be rolled back. Which statement should you use?

Accepted Answer

TRUNCATE TABLE STAGING_LOGS;

Answer

DELETE FROM STAGING_LOGS;

Answer

DROP TABLE STAGING_LOGS PURGE;

Answer

ALTER TABLE STAGING_LOGS DROP ALL ROWS;

Question 5

A developer needs to calculate the number of months between two dates: `start_date` and `end_date`. The result should be precise, including the fractional part of the month if the days differ. Which function should be used?

Accepted Answer

MONTHS_BETWEEN(end_date, start_date)

Answer

ROUND(end_date - start_date) / 30

Answer

ADD_MONTHS(start_date, end_date)

Answer

TRUNC(MONTHS_BETWEEN(end_date, start_date))

Question 6

Which of the following scenarios describes the creation of a Cartesian Product (Cross Join) in a SQL query? (Select TWO)

Accepted Answer

When a join condition is omitted in a join between two tables.

Accepted Answer

When the CROSS JOIN syntax is explicitly used.

Answer

When two tables are joined using a NATURAL JOIN but share no common column names.

Answer

When a LEFT OUTER JOIN is used on tables with different row counts.

Top vendors

Top exams

Free 1Z0-171 sample questions

You are performing a data cleanup operation. You need to remove all rows from the `STAGING_LOGS` table to free up storage space immediately. The operation must not generate undo logs for individual rows and cannot be rolled back. Which statement should you use?

A developer needs to calculate the number of months between two dates: `start_date` and `end_date`. The result should be precise, including the fractional part of the month if the days differ. Which function should be used?

Which of the following scenarios describes the creation of a Cartesian Product (Cross Join) in a SQL query? (Select TWO)

6 more free samples are waiting