1Z0-171 sample questions

Question 1

You need to retrieve the top 5 earners from the `EMPLOYEES` table. If there is a tie for the 5th position, you want to include all tied rows. Which FETCH clause syntax should you use?

Accepted Answer

FETCH FIRST 5 ROWS WITH TIES

Answer

WHERE ROWNUM <= 5 WITH TIES

Answer

FETCH FIRST 5 ROWS ONLY

Answer

LIMIT 5 WITH TIES

Question 2

You want to create a report that lists all employees and their managers. The result should include employees who do not have a manager (like the CEO). Which type of join is best suited for this requirement?

Accepted Answer

Left Outer Self Join

Answer

Cross Join

Answer

Natural Join

Answer

Inner Self Join

Question 3

Which two statements about the `ROUND` and `TRUNC` functions applied to DATE values are true? (Select TWO)

Accepted Answer

TRUNC(date, 'YEAR') returns the first day of the current year.

Accepted Answer

ROUND(date, 'MONTH') rounds up to the first day of the next month if the day is past the 15th.

Answer

TRUNC(date) creates a timestamp with fractional seconds.

Answer

ROUND(date) without a format model defaults to rounding to the nearest Month.

Question 4

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

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

Answer

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

Answer

Define the column as NUMBER DEFAULT SEQ_CAT.NEXTVAL

Question 5

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 6

Review the following query structure intended to filter employee records.

SELECT employee_id, last_name
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'AD_PRES'
AND salary &gt; 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

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

Answer

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

Answer

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

1Z0-171 Database 23ai SQL Certified Associate Practice Questions

Create an account to continue.

Create an account to continue.

Related exams